Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [upsert]

UPSERT: SQL-UPDATE a row, or if not found INSERT a new row.

The SQL:2003 standard defines MERGE. Some implementations use the key word UPSERT.
UPSERT could be considered a subset of MERGE.

Inserting or updating a row has to deal with a possible race condition between concurrent operations.

Oracle, DB2, Teradata and MS SQL support the standard syntax for MERGE.
MongoDB, MySQL, SQLite, Firebird support similar features.
Find an UPSERT example for PostgreSQL in the manual or a detailed assessment in this blog article by @Depesz.

119 questions
47
votes
2 answers

How to insert or update using single query?

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if annd only if there are no records.For example input is id=30122 and name =john if there are records with id 30122 then I have…
SpringLearner
  • 611
  • 1
  • 5
  • 9
42
votes
2 answers

'Column reference is ambiguous' when upserting element into table

I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set. I've used info from this question:…
serge1peshcoff
  • 595
  • 2
  • 6
  • 9
42
votes
3 answers

Idiomatic way to implement UPSERT in PostgreSQL

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert at all to find out immediately, whether these…
shabunc
  • 767
  • 2
  • 6
  • 10
32
votes
3 answers

How to get the ID of the conflicting row in upsert?

I have a table tag with 2 columns: id (uuid) and name (text). I now want to insert a new tag into the table, but if the tag already exists, I want to simply get the id of the existing record. I assumed I could just use ON CONFLICT DO NOTHING in…
Oliver Salzburg
  • 422
  • 1
  • 5
  • 12
31
votes
2 answers

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can be nullable. Below I have created a smaller…
Shaun McCready
  • 413
  • 1
  • 4
  • 6
31
votes
2 answers

UPSERT with ON CONFLICT using values from source table in the UPDATE part

Given: CREATE TABLE A ( PK_A INT8 NOT NULL, A INT8, PRIMARY KEY (PK_A) ); CREATE TABLE B ( PK_B INT8 NOT NULL, B INT8, PRIMARY KEY (PK_B) ); This query: insert into table_b (pk_b, b) select pk_a,a from table_a on conflict (b) do update set…
Tony Indrali
  • 311
  • 1
  • 3
  • 3
28
votes
1 answer

Multiple on conflict targets

I have two unique indexes on columns a and b. I need something like this: insert into my_table (a, b) values (1, 2), (1, 2) on conflict (a) do update set c = 'a_violation' on conflict (b) do update set c = 'b_violation' So generally I want to make…
user606521
  • 1,415
  • 6
  • 23
  • 28
25
votes
3 answers

Concurrent transactions result in race condition with unique constraint on insert

I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation and create the records in the database. I found a…
Elliot Blackburn
  • 365
  • 1
  • 3
  • 8
18
votes
1 answer

Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING

Setup I have a bulk insert function set_interactions(arg_rows text) that looks like this: with inserts as ( insert into interaction ( thing_id, associate_id, created_time) select t->>'thing_id', t->>'associate_id', now()…
Kev
  • 474
  • 1
  • 4
  • 15
18
votes
2 answers

ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does). My query is this INSERT INTO journals (ext_ids, title) VALUES ('{"nlmid": "000"}', 'blah') ON CONFLICT ON CONSTRAINT…
expert
  • 285
  • 1
  • 2
  • 7
15
votes
1 answer

UPSERT - Is there a better alternative to MERGE or @@rowcount?

I was wondering if you have encountered a T-SQL command similar to the concept of UPSERT? Performing INSERT|UPDATE operations using options (1) or (2) seems overly complex and error prone. OBJECTIVE To ensure that the desired record (in this case…
Pressacco
  • 463
  • 1
  • 4
  • 10
12
votes
1 answer

Why is CTE open to lost updates?

I don't understand what Craig Ringer meant when he commented: This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected any rows. on…
Gili
  • 1,049
  • 1
  • 16
  • 31
11
votes
1 answer

How to avoid using Merge query when upserting multiple data using xml parameter?

I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL Server database. If the row already exists in the table, we update that row with the information in the given…
GMalla
  • 189
  • 1
  • 1
  • 9
11
votes
2 answers

Why is the serial primary key incrementing despite a using "On Conflict Do Nothing?

I have the below table in PostgreSQL 13: table name: newtable field type ----- ---- Seq bigserial code varchar Seq is the primary key (auto-increment) Code is a unique key index Insert Into newtable (Code)…
Don2
  • 549
  • 1
  • 7
  • 9
10
votes
1 answer

Syntax shorthand for updating only changed rows in UPSERT

I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed): INSERT INTO public.test_upsert (some_id, a, b,…
Caustic
  • 203
  • 2
  • 5
1
2 3 4 5 6 7 8