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

user_2255632 0
Viewed 873

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
upsert postgresql sql
2 Answers

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.

user_9398600

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;
user_22556320
Related