27

I'm trying to write a query like this in PostgreSQL 9.5.2:

INSERT INTO a (id, x)
    SELECT id, x FROM b
ON CONFLICT (id) DO UPDATE
    SET x = b.x
    WHERE b.y < 100

but I get ERROR: missing FROM-clause entry for table "b". I must be missing something basic, but how do I refer to the row being inserted in the UPDATE clause? Or is there some other way?

usethe4ce
  • 23,261
  • 4
  • 30
  • 30

1 Answers1

57

The conflicting values are available through the excluded alias:

INSERT INTO a (id, x)
SELECT id, x 
FROM b
ON CONFLICT (id) DO UPDATE
    SET x = excluded.x;
  • 1
    Amazing answer, was hard to find and provides a super elegant solution to the multiple row upsert problem. Thanks! – mattdlockyer May 20 '20 at 01:09