Can INSERT […] ON CONFLICT be used for foreign key

问题:

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?

回答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.)