I'm trying to UPSERT
multiple rows into a Postgres table (ie. INSERT ON CONFLICT DO UPDATE SET
multiple rows).
But I want the ON CONFLICT DO UPDATE SET
conditional check/update do be done row-wise (ie. per-row).
Eg. there's a table:
DROP TABLE IF EXISTS t00;
CREATE TABLE IF NOT EXISTS t00(
userid int8 PRIMARY KEY,
col00 int8 DEFAULT 0,
col01 int8 DEFAULT 0
);
such that I want to update col01
conditionally based on the value of col00
.
So you INSERT
rows and update 1 row:
INSERT INTO t00 (userid) VALUES (0),(1);
UPDATE t00 SET col00=1 WHERE userid=0;
And now comes the problem.
What I want is something like the following (invalid Postgres, I checked):
INSERT INTO t00 (userid,col00,col01)
VALUES (0,1,2) ON CONFLICT (userid) DO UPDATE SET col00=EXCLUDED.col00, col01=EXCLUDED.col01 WHERE t00.col00!=EXCLUDED.col00,
VALUES (1,1,2) ON CONFLICT (userid) DO UPDATE SET col00=EXCLUDED.col00, col01=EXCLUDED.col01 WHERE t00.col00!=EXCLUDED.col00;
or (also invalid Postgres, I checked):
INSERT INTO t00 (userid,col00,col01) VALUES
(0,1,2) ON CONFLICT (userid) DO UPDATE SET col00=EXCLUDED.col00, col01=EXCLUDED.col01 WHERE t00.col00!=EXCLUDED.col00,
(1,1,2) ON CONFLICT (userid) DO UPDATE SET col00=EXCLUDED.col00, col01=EXCLUDED.col01 WHERE t00.col00!=EXCLUDED.col00;
So I'm only left with the following:
INSERT INTO t00 (userid,col00,col01) VALUES
(0,1,2),
(1,1,2) ON CONFLICT (userid) DO UPDATE SET col00=EXCLUDED.col00, col01=EXCLUDED.col01 WHERE t00.col00!=EXCLUDED.col00;
The desired result (after the upsert) is that:
userid=0
has:col00=1
andcol01=0
userid=1
has:col00=1
andcol01=2
The (undesired) result is that:
userid=0
hascol00=1
andcol01=2
userid=1
hascol00=1
andcol01=2
So, what I want is a fine-grained "row-by-row" independent upsert, not an "all-or-nothing" upsert over ALL rows.
I'm already doing what I want with a for-loop (by doing 1 separate Postgres call for each row upsert, which is ugly), but I wonder if the last call I proposed is guaranteed to do what I want? (It does in this case, at least.)
(I also wonder if it's higher-performing that doing multiple separate Postgres calls for millions of rows.)