CODEWITHSUNDEEP
postgresqlupsertpostgresql-9.5
usethe4ce
usethe4ce

Reputation: 23819

How to upsert multiple rows in PostgreSQL

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?

Upvotes: 29

Views: 20428

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 61

Related Questions