How to UPSERT multiple rows with individual values in one statement?

Question

I've created the following table:

CREATE TABLE t1(
a   INT UNIQUE, 
b   varchar(100) NOT NULL,
c   INT, 
d   INT DEFAULT 0,  
PRIMARY KEY (a,b));

On a single row this SQL statement works great (the SQL is generated in code):

INSERT INTO t1 (a, b, c, d)
VALUES($params.1, '${params.2}', $params.3, params.4) 
ON CONFLICT (a,b) DO 
UPDATE SET d=params.4

Is it possible to upsert multiple rows at once? For each update the value of params.4 is different.

var sqlStr = 'INSERT INTO t1 (a, b, c, d) VALUES '
for(let i =0 i < params.length; i++){
   sqlStr += `(${params[i].1}, '${params[i].2}', ${params[i].3}, ${params[i].4}),`
   
}
sqlStr = sqlStr.substring(0, sqlStr .length - 2) +')';
sqlStr += 'ON CONFLICT (a,b) DO UPDATE SET **d=???**' <-- this is the problem

params[i].4 has different value for each row and the ON CONFLICT statement appears only once (not per row) and SET doesn't support a WHERE.

Example, if my table has the following rows:

 a | b | c | d 
---+---+---+---
 1 | 1 | 1 | 1
 2 | 2 | 2 | 2

And my new input is [(1,'1',1,11),(2,'2',2,22),(3,'3',3,3)].
There are two conflicts - (1,1) and (2,2). The result should be:

 a | b | c | d 
---+---+---+---
 1 | 1 | 1 | 11
 2 | 2 | 2 | 22
 3 | 3 | 3 | 3

Erwin Brandstetter · Accepted Answer · 2021-08-13T22:35:26.750

UPSERT (INSERT ... ON CONFLICT ... DO UPDATE) keeps track of excluded rows in the special table named EXCLUDED automatically. The manual:

Note that the special excluded table is used to reference values originally proposed for insertion

So it's really very simple:

INSERT INTO t1 (a, b, c, d)
VALUES (...) 
ON     CONFLICT (a,b) DO UPDATE
SET    d = EXCLUDED.d;          -- that's all !!!

Besides being much simpler and faster, there is a subtle corner-case difference from your proposed solution. The manual:

Note that the effects of all per-row BEFORE INSERT triggers are reflected in excluded values, since those effects may have contributed to the row being excluded from insertion.

Plus, column DEFAULT values are already applied in the EXCLUDED row, where no input was given. (Like DEFAULT 0 for your column d.)

Both are typically what you want.

Thanks for noticing the typo ;) I've updated my question with an example to fine tune the problem I'm trying to solve — Kukula Mula, Aug 13 '21 at 16:58
@KukulaMula: You seem to have missed the essential part. Use `EXCLUDED`! — Erwin Brandstetter, Aug 13 '21 at 22:22

score -3 · Answer 2 · answered Aug 13 '21 at 18:14

Writing the solution for future users dealing with the same issue

Step 1: Create a temporary table (clone of t1)

CREATE TABLE t2(                 
  a   INT UNIQUE, 
  b   varchar(100) NOT NULL,
  c   INT,                                  
  d   INT DEFAULT 0,  
  PRIMARY KEY (a,b));

OR

create table t2 as (select * from t1) with no data;

Step 2: Insert the new input to t2

INSERT INTO t2 (a, b, c, d) 
values (1,'1',1,1),(2,'2',2,2),(3,'3',3,3)`

Step 3: UPSERT to t1 in case of conflict select d from t2

INSERT INTO t1 (a, b, c, d)
VALUES (1,'1',1,1),(2,'2',2,2),(3,'3',3,3)
ON CONFLICT(a,b) DO UPDATE
SET d=(SELECT d FROM t2 WHERE t2.a=t1.a);

Step 4: delete t2

DROP TABLE t2;

No. Just no. Use the special `EXCLUDED` table instead. So much simpler and cheaper. And this is incorrect on top. `WHERE t2.a=t1.a` would have to be `WHERE t2.a=t1.a AND t2.b=t1.b`. — Erwin Brandstetter, Aug 13 '21 at 22:20

How to UPSERT multiple rows with individual values in one statement?

2 Answers2

Linked