2

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:

  1. userid=0 has: col00=1 and col01=0
  2. userid=1 has: col00=1 and col01=2

The (undesired) result is that:

  1. userid=0 has col00=1 and col01=2
  2. userid=1 has col00=1 and col01=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.)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.