Skip to main content

PostgreSQL Upsert with multiple fields

This blog post might be outdated!
This blog post was published more than one year ago and might be outdated!
· 2 min read
Stephan Hochdörfer
Head of IT Business Operations

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' ;