How to upsert multiple rows in PostgreSQL

postgresql upsert postgresql-9.5
usethe4ce picture usethe4ce · Apr 29, 2016 · Viewed 8.3k times · Source

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?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Apr 29, 2016

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;

Related questions

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values

When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this: INSERT INTO tablename (id, username, password, level, email) VALUES (1, 'John', 'qwerty', 5, …

Read More
Use multiple conflict_target in ON CONFLICT clause

I have two columns in table col1, col2, they both are unique indexed (col1 is unique and so is col2). I need at insert into this table, use ON CONFLICT syntax and update other columns, but I can't use both …

Read More
How to correctly do upsert in postgres 9.5

correct syntax of upsert with postgresql 9.5, below query shows column reference "gallery_id" is ambiguous error , why? var dbQuery = `INSERT INTO category_gallery ( category_id, gallery_id, create_date, create_by_user_id ) VALUES ($1, $2, $3, $4) ON CONFLICT (category_id) DO UPDATE …

Read More