PostgreSQL Upsert with multiple fields

PostgreSQL Upsert with multiple fields

A while I ago I covered the upsert feature PostgreSQL introduced with version 9.5. Back then I showed you how to make use of upsert with one field - the primary key - as conflict target. In a recent attempt to modify the upsert logic to take 2 fields into account I tried to add the additional field to the conflict target:

INSERT INTO "user_reg_projection" ("reg_date", "reg_type", "reg_count") 
VALUES ('2017-01-02', 'typeA', 1) 
ON CONFLICT ("reg_date", "reg_type") DO 
UPDATE SET "reg_count" = "user_reg_projection"."reg_count" + 1 WHERE 
"user_reg_projection"."reg_date" = '2017-01-02' AND 
"user_reg_projection"."reg_type" = 'typeA' ;


That did not work. Look through the PostgreSQL manual I figured out that it is possible to use a unique index inference as conflict target. All I needed to do was change the table definition to:

CREATE TABLE user_reg_projection
(
    reg_date DATE DEFAULT now(),
    reg_type VARCHAR(255) NOT NULL,
    reg_count INT DEFAULT 0
);


As well as define a unique key constraint like this:

ALTER TABLE user_reg_projection ADD CONSTRAINT "user_reg_projection_pk"
UNIQUE ("reg_date", "reg_type");


In the end it as necessary to slightly change the UPSERT code and refer to the index user_reg_projection_pk instead of both fields:

INSERT INTO "user_reg_projection" ("reg_date", "reg_type", "reg_count")
VALUES ('2017-01-02', 'typeA', 1) 
ON CONFLICT ON CONSTRAINT "stats_per_day_pk" DO 
UPDATE SET  "reg_count" = "user_reg_projection"."reg_count" + 1 WHERE 
"user_reg_projection"."reg_date" = '2017-01-02' AND 
"user_reg_projection"."reg_type" = 'typeA' ;


Tags: ,

Eintrag von Stephan Hochdörfer am 06.09.2017

comments powered by Disqus