15

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
peterwimsey
  • 532
  • 2
  • 6
  • 17

1 Answers1

19

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • That works very nicely indeed, much appreciated. Thanks for the clarification about the `ON CONFLICT` clause too. – peterwimsey Mar 16 '16 at 16:19
  • This work if inserting on the same table I try to reference? so something like `INSERT INTO entries ... JOIN entries` ? – Juan Carlos Oropeza Feb 07 '17 at 19:04
  • 2
    @JuanCarlosOropeza: You can join to the same table in the same way, just make sure the join condition can only match a *single* row. Else you have to use a different technique. – Erwin Brandstetter Feb 07 '17 at 19:09
  • What about tables that contain different types like timestamps and byte arrays. Is there any convenient way to use them in `FROM ( VALUES (1, 2, 'references two') -- more? ) val (entry_id, referenced_id, name)` instead casting every single of it inside VALUES ? – madnan Jan 14 '20 at 13:45
  • @madnan: Here are some techniques to cast fields of a free-standing `VALUES` expression: https://stackoverflow.com/a/42217872/939860 – Erwin Brandstetter Jan 14 '20 at 14:10