Can INSERT [...] ON CONFLICT be used for foreign key violations?

Question

Considering

=> select * from referenced; referenced_id | name ---------------+------- 1 | one 2 | two 3 | three 

and

 => select * from entries; entry_id | referenced_id | name ----------+---------------+------------------ 1 | 3 | references three 

where referenced_id and entry_id are primary keys.

I need an insert statement for entries that skips pasting if either entry_id already exists or the reference element does not exist. The first is easy to do:

 INSERT INTO entries VALUES (1, 2, 'references two') ON CONFLICT (entry_id) DO NOTHING; 

Can I also check for the presence of a foreign key here?

Answer 1

Yes, attach your input lines to the specified table, thereby deleting the non-matching lines in the FK column:

 INSERT INTO entries(entry_id, referenced_id, name) SELECT val.entry_id, val.referenced_id, val.name FROM ( VALUES (1, 2, 'references two') -- more? ) val (entry_id, referenced_id, name) JOIN referenced USING (referenced_id) -- drop rows without matching FK ON CONFLICT (entry_id) DO NOTHING; -- drop rows with duplicate id 

UPSERT itself ( INSERT ... ON CONFLICT DO NOTHING ) only responds to unique violations. Guide:

ON CONFLICT can be used to indicate an alternative action to increase the error constraint constraint constraint exception. (See CONFLICT section below.)