Avoid duplicates in PostgreSQL among the rows that are going to be inserted

Question

I have a destination table a and a set of source tables b and c. I'm trying to do an insert to a from b and c, and additionally check if such combination of (column1, column2) do not already exists in a:

INSERT INTO a (column1, column2)
SELECT column1, column2 FROM b
UNION ALL
SELECT column1, column2 FROM c
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = column1 AND a.column2 = column2);

Unfortunately there is a small probability that b and c may contain several rows with the same combination of (column1, column2) which will generate duplicate key value violations because there is an unique index on (column1, column2) in a.

I found two solutions (PostgreSQL v13):

1. Replace UNION ALL with UNION which ignores duplicates.
2. Combine the INSERT with ON CONFLICT DO NOTHING.

The above solutions have some drawbacks (for me), e.g. the 1st will sort the data while the 2nd will create gaps in primary key values. I'm searching for the 3rd solution. The NOT EXISTS (SELECT 1 FROM a WHERE ...) condition access only the content of a before the INSERT (i.e. before the transaction), is there a possibility to access with NOT EXISTS (...) the content of a which was added by INSERT but was not yet committed?

Answer 1

First of all, if you want to check both the b and the c rows against a, you should have a WHERE clause after each SELECT. Currently you are checking only the c rows, because the WHERE at the end of the query applies to the second UNION leg only.

Therefore, your query should probably be amended like this:

INSERT INTO a (column1, column2)
SELECT column1, column2 FROM b
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = b.column1 AND a.column2 = b.column2)
UNION ALL
SELECT column1, column2 FROM c
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = c.column1 AND a.column2 = c.column2);

Secondly, in order to make sure the c rows exclude any duplicates of b, you can just add another NOT EXISTS check:

INSERT INTO a (column1, column2)
SELECT column1, column2 FROM b
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = b.column1 AND a.column2 = b.column2)
UNION ALL
SELECT column1, column2 FROM c
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.column1 = c.column1 AND a.column2 = c.column2)
  AND NOT EXISTS (SELECT 1 FROM b WHERE b.column1 = c.column1 AND b.column2 = c.column2);

All the above, however, seems to be overcomplicating matters a little to me. I would just go for something as simple as this:

INSERT INTO a (column1, column2)
SELECT column1, column2 FROM b
UNION
SELECT column1, column2 FROM c
EXCEPT
SELECT column1, column2 FROM a;

Or, if I wanted to state my intent as unambiguously as possible, this:

INSERT INTO a (column1, column2)
(
SELECT column1, column2 FROM b
UNION
SELECT column1, column2 FROM c
)
EXCEPT
SELECT column1, column2 FROM a;

Answer 2

Though what you are asking is not a good practice, still if it is your business requirement then try this hack using common table expression.

As you want to avoid the sequence skip and reordering of records, so in first step we will assign row_number() to each record along with table code.

In second step we will remove all the duplicate records in the result set using distinct on.

In final step we will filter the records already exists in table a by using not exists clause and reorder by table key and row number before final insert in table a.

with cte as (
SELECT 'b' "tab",row_number() over() "seq", column1, column2 FROM b
UNION all
SELECT 'c',row_number() over(),column1, column2 FROM c
),
cte1 as (
select distinct on (column1,column2) * from cte 
where not exists (select 1 from a where column1=cte.column1 and column2=cte.column2)
order by 3,4
)
insert into a(column1,column2)
select column1,column2 from cte1 order by tab,seq

DEMO