INSERT ON CONFLICT returning old values

In SQL when you insert and the primary or unique key value is already there, the statement fails with duplicate key value violates unique constraint. In PostgreSQL or YugabyteDB, you can skip those conflicts with ON CONFLICT DO NOTHING and continue. You can UPDATE the existing rows instead of skipping with ON CONFLICT DO UPDATE and even return the inserted and updated rows with RETURNING.

However, there no way to return the skipped rows to show the old values that are already there and that you don't want to change.

A solution was proposed in the pgsql-general mailing list (Returning Values from INSERT ON CONFLICT DO NOTHING) but it was a really bad one: updating a column, setting the same value, to be able to use the RETURNING clause.

Updating a column in PostgreSQL copies the whole row, has to update all index entries if this new row doesn't fit in the same block as the old one, and generates WAL for those changes, which can be full page logging if not already in cache.

In YugabyteDB this is lighter, but still is more expensive that just reading the old values. It is never a good idea to write something that you only need to read.

SQL works with sets of rows and the WITH clause makes it easy to define those sets of rows and do DML with them.

Here is an example:

CREATE TABLE IF NOT EXISTS demo
(
    id1 bigint    NOT NULL,
    id2 character NOT NULL,
    value bigint  NOT NULL,
    PRIMARY KEY (id1, id2)
);

Here is the bad solution:

INSERT into demo (id1, id2,value) values
-- the rows you want to insert
(1,'a',0),(2,'a',0),(3,'a',0),(3,'a',9)
---
ON CONFLICT (id1, id2) 
DO UPDATE SET id1 = excluded.id1, id2 = excluded.id2 RETURNING *
;

Here is a good solution:

with new(id1,id2,value) as (
-- the rows you want to insert
values
(3,'a',2),(4,'a',2),(5,'a',2)--,(5,'a',9)
---
), dup as (
-- the one that already exists (conflicting key)
select demo.* from demo
where (id1,id2)     in ( select id1, id2 from new)
---
), ins as (
-- the ones to insert
insert into demo
select * from new
where (id1,id2) not in ( select id1, id2 from dup)
returning *
---
) 
--- finally the concatenation of inserted values and old value for skipped ones
select * from dup union all select * from ins
---
;

You can see that I had to deduplicate my input here because there's no ON CONFLICT clause.

Here is the execution plan on YugabyteDB:

This will be faster when providing the values in the subqueries, as the expression can be pushed down:

with new(id1,id2,value) as (
-- the rows you want to insert
values
(3,'a',2),(4,'a',2),(5,'a',2)--,(5,'a',9)
---
), dup as (
-- the one that already exists (conflicting key)
select demo.* from demo
where (id1,id2)     in ( 
 (3,'a'),(4,'a'),(5,'a'),(5,'a')
)
---
), ins as (
-- the ones to insert
insert into demo
select * from new
where (id1,id2) not in ( 
 (3,'a'),(4,'a'),(5,'a'),(5,'a')
)
returning *
---
) 
--- finally the concatenation of inserted values and old value for skipped ones
select * from dup union all select * from ins
---
;

If my input list contains duplicates, an alternative is to use ON CONFLICT DO NOTHING and get the old values after it:

explain (analyze, costs off, dist)
with new(id1,id2,value) as (
-- the rows you want to insert
values
(5,'a',3),(6,'a',3),(7,'a',3),(5,'a',9)
---
), ins as (
-- the ones to insert
insert into demo
select * from new
on conflict do nothing
returning *
---
), dup as (
-- the one that already exists (conflicting key)
select demo.* from demo
where (id1,id2)         in ( 
(5,'a'),(6,'a'),(7,'a'),(5,'a')
)
  and (id1,id2)     not in ( select id1, id2 from ins)
---
)
--- finally the concatenation of inserted values and old value for skipped ones
select * from dup union all select * from ins
---
;

This execution plan looks better:

However, the ON CONFLICT has also to read before inserting so if you have many duplicates, they may be read two times. If you use this, you should test with your data and larger volume. Finally, I hope you are not afraid of the verbosity of SQL. Using Common Table Expressions (CTE) is a great way to self-document and test each step of a complex SQL program.