I have a destination table
a and a set of source tables
c. I'm trying to do an insert to
c, and additionally check if such combination of
(column1, column2) do not already exists in
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
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
I found two solutions (PostgreSQL v13):
UNIONwhich ignores duplicates.
- Combine the
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?