Stack Overflow
Stack Overflow

Questions tagged [upsert]

UPSERT is a combination of UPDATE and INSERT, typically used in relational databases.

UPSERT refers to types of statements (usually database statements) that INSERT an item (record) to a resource (table in a database) where, if the item already exists, it will update the existing item with the fields provided. The term UPSERT is an amalgamation of UPDATE and INSERT and is common slang among database developers.

1227 questions
43
votes
4 answers

UPSERT in PostgreSQL using jOOQ

I am trying to perform an UPSERT in PostgreSQL using the jOOQ library. For doing this I am currently trying to implement the following SQL statement in jOOQ: https://stackoverflow.com/a/6527838 My code looks like this so far: public class…
uldall
  • 2,458
  • 1
  • 17
  • 31
42
votes
8 answers

Upsert in Rails ActiveRecord

Does ActiveRecord have a built-in upsert functionality? I know I could write it myself but I obviously don't want to if such a thing already exists.
Jason Swett
  • 43,526
  • 67
  • 220
  • 351
36
votes
2 answers

How to update all columns with INSERT ... ON CONFLICT ...?

I have a table with a single primary key. When I attempt to do an insert there may be a conflict caused by trying to insert a row with an existing key. I want to allow the insert to update all columns? Is there any easy syntax for this? I am trying…
beatsforthemind
  • 879
  • 2
  • 8
  • 17
36
votes
4 answers

How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?

Writable CTEs were considered a solution to UPSERT prior to 9.5 as described in Insert, on duplicate update in PostgreSQL? It is possible to perform an UPSERT with the information whether it ended up as an UPDATE or an INSERT with the following…
Paul Guyot
  • 6,257
  • 1
  • 20
  • 31
33
votes
3 answers

Is SELECT or INSERT in a function prone to race conditions?

I wrote a function to create posts for a simple blogging engine: CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[]) RETURNS INTEGER AS $$ DECLARE InsertedPostId INTEGER; TagName VARCHAR; BEGIN INSERT INTO Posts…
user142019
31
votes
2 answers

Postgres on conflict do update on composite primary keys

I have a table where a user answers to a question. The rules are that the user can answer to many questions or many users can answer one question BUT a user can answer to a particular question only once. If the user answers to the question again, it…
pewpewlasers
  • 3,025
  • 4
  • 31
  • 58
28
votes
1 answer

Return rows from INSERT with ON CONFLICT without needing to update

I have a situation where I very frequently need to get a row from a table with a unique constraint, and if none exists then create it and return. For example my table might be: CREATE TABLE names( id SERIAL PRIMARY KEY, name TEXT, …
ira
  • 735
  • 1
  • 7
  • 12
28
votes
1 answer

Elasticsearch upserting and appending to array

I'm trying to write a script that will upsert a new user record to ElasticSearch, updating any information if the user already exists, and appending a new PaymentInfo object to the user's Payments array if it exists in the update object. Here's a…
Seventh Helix
  • 727
  • 4
  • 9
  • 18
27
votes
1 answer

How do I reference a unique index that uses a function in ON CONFLICT?

I'm using postgres 9.5.3, and I have a table like this: CREATE TABLE packages ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL ); I have defined a function, canonical_name, like this: CREATE FUNCTION canonical_name(text) RETURNS text AS $$ …
carols10cents
  • 6,943
  • 7
  • 39
  • 56
27
votes
1 answer

How to upsert multiple rows in PostgreSQL

I'm trying to write a query like this in PostgreSQL 9.5.2: INSERT INTO a (id, x) SELECT id, x FROM b ON CONFLICT (id) DO UPDATE SET x = b.x WHERE b.y < 100 but I get ERROR: missing FROM-clause entry for table "b". I must be missing…
usethe4ce
  • 23,261
  • 4
  • 30
  • 30
27
votes
1 answer

Entity Framework - UPSERT on unique indexes

I searched a bit regarding my problem but can't find anything that really to help. So my problem/dilema stays like this: I know that mysql database have a unique index system that can be used for insert/update in same query using this format: insert…
HellBaby
  • 550
  • 1
  • 6
  • 24
25
votes
2 answers

Postgres - ON CONFLICT - HOW to know if an UPDATE occurred instead of an INSERT

I have a table CREATE TABLE foo ( f0 int, time_stamp timestamp, CONSTRAINT foo_pk PRIMARY KEY (f0) ) I need to write to this table in high volumes, so performance is key. Sometimes, I will write a record that has an existing value for…
Sagi
  • 592
  • 5
  • 15
24
votes
1 answer

H2 Database Postgres Mode Upsert

When H2 database is in Postgres Mode, how do I check if it supports following statement (upsert / on conflict) INSERT INTO event_log_poller_state (aggregate_type, consumer_group_id, value) VALUES (?, ?, ?) on conflict (aggregate_type,…
Tony Murphy
  • 711
  • 9
  • 22
24
votes
2 answers

MongoDB: Update/Upsert vs Insert

Recently I notice a huge performance difference between doing multiple upserts (via bulk operations) vs an insert (multiple documents). I would like to know if I am correctly on this: Upsert/Updates will be like a find() and update() so it does 2…
Jiew Meng
  • 84,767
  • 185
  • 495
  • 805
23
votes
9 answers

Find or insert based on unique key with Hibernate

I'm trying to write a method that will return a Hibernate object based on a unique but non-primary key. If the entity already exists in the database I want to return it, but if it doesn't I want to create a new instance and save it before…
Mike Deck
  • 18,045
  • 16
  • 68
  • 92
1 2
3
…
81 82