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!

Both! I have not found any examples that update all fields, nor the documentation shows syntax on how to do that. — Miguel Velez, Mar 23 '16 at 14:36
That's because there is no special syntax, like `ON CONFLICT ... UPDATE SET ALL FIELDS`, you must mention all fields by name after `SET`, like the documentation suggests. — pozs, Mar 23 '16 at 15:00
I'm wondering the same thing 2.5 years later; in a database with many fields, it would be convenient to have a special syntax to update all fields in upsert with conflict. — Jonathan Lam, Nov 08 '18 at 21:12
Does this answer your question? [How to update all columns with INSERT ... ON CONFLICT ...?](https://stackoverflow.com/questions/40687267/how-to-update-all-columns-with-insert-on-conflict) — Emadpres, Mar 13 '20 at 09:43

Lukas Eder · Answer 1 · 2022-05-07T09:20:39.287

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.

Will this syntax work for a composite primary key as mentioned in OP? — vhs, May 07 '22 at 09:13
@vhs: Yes of course. Just list all the columns, or list the constraint explicitly. — Lukas Eder, May 07 '22 at 09:13

score 0 · Answer 2 · answered May 07 '22 at 08:46

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.

Upsert on Postgres: update all fields

2 Answers2