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

Question

Given

=> 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 want an insert statement for entries that skips insertion if either the entry_id already exists or the referenced item does not exist. The first is easily done:

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

Is it possible to check for the existence of the foreign key here too?

Answer 1

Yes, join your input rows to the referenced table, thereby removing rows without a match on 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

The UPSERT itself (INSERT ... ON CONFLICT DO NOTHING) only reacts to unique violations. The manual:

ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)

Since the VALUES expression is now not attached to an INSERT directly, column types are not derived from the target table. You may need to cast input values explicitly when operating with non-basic types. See:

• Casting NULL type when updating multiple rows