Upsert on Postgres: update all fields

Question

I am trying to implement an upsert in postgres (an insert on constraint update). This is what my sql looks like:

INSERT into "foo" ("bar", "moo", "baz") VALUES (1, 2, 3), (3, 4, 5) 
        ON CONFLICT ON CONSTRAINT "composite_primary_key" DO NOTHING

I want to modify the DO NOTHING to something that will allow me to update ALL the fields for that row. I am not sure what the syntax should be since the docs do not explain it and there are not examples that do this.

Thanks!

Answer 1

You can use the magic EXCLUDED table to access the columns of your VALUES clause. That way, you don't have to repeat the values themselves in the DO UPDATE SET clause:

CREATE TABLE t (
  i int,
  j int, 
  k int, 
  l int,
  m int,

  CONSTRAINT composite_primary_key PRIMARY KEY (i, j)
);

INSERT INTO t VALUES (1, 1, 1, 1, 1);

INSERT INTO t VALUES (1, 1, 2, 3, 4), (2, 2, 4, 6, 8) 
ON CONFLICT ON CONSTRAINT composite_primary_key DO UPDATE
SET
  k = excluded.k,
  l = excluded.l,
  m = excluded.m
RETURNING *;

The result is:

|i  |j  |k  |l  |m  |
|---|---|---|---|---|
|1  |1  |2  |3  |4  |
|2  |2  |4  |6  |8  |

It's not as poweful as you expected, but better than nothing, especially if you have a SQL builder available that can generate the query for you, dynamically, based on known schema meta data.

More info in the PG documentation.

Answer 2

The syntax for performing an upsert using a named constraint looks like this:

on conflict on constraint "foo_pkey" do update
set moo = excluded.moo, boo = excluded.boo;

Applied to your original example with some modifications for clarity:

insert into foo (bar, baz, bat, moo, boo)
values ('a', 'b', 'c', 10, 20), ('d', 'e', 'f', 30, 40)
-- on conflict do nothing;
on conflict on constraint "foo_pkey" do update
set moo = excluded.moo, boo = excluded.boo;

Fully working example. And a reference guide from Prisma.