How to put a condition in the conflict_action of an Upsert?

Question

I have a table like this:

CREATE TABLE mytable
(
    pk1               varchar(4),
    pk2               varchar(4),
    col1              varchar(7),
    col2              varchar(6),
    data_version      varchar(8),
    tech_change_date  timestamp,
    tech_change_user  varchar(255),
    tech_version      bigint,

    CONSTRAINT BMHI_PKEY PRIMARY KEY(pk1, pk2)
)
    WITH (
        OIDS=FALSE
        );

and I want to insert a bunch of new data, but if the key already exists I want the following update behaviour:

• iff the values to be inserted for (col1, col2, data_version) equal those already in the table: DO NOTHING
• otherwise override all values.

I have arrived at the following upsert statement:

INSERT INTO mytable AS existing
                            (pk1, pk2, 
                             col1, col2, data_version 
                             tech_change_date, tech_change_user, tech_version)
VALUES (%s,%s,%s,%s,%s,%s, 0)
ON CONFLICT (pk1, pk2) 
DO UPDATE SET (col1, col2,
               tech_change_date, tech_change_user, tech_version)
            = (excluded.col1,
               excluded.col2,
               excluded.tech_change_date,
               excluded.tech_change_user,
               existing.tech_version + 1);

But this will update whenever there is already an entry with the same key in the table. From the documentation: the conflict_action can be either DO NOTHING or DO UPDATE SET but I don't see how to make this conditional. Can I somehow put ifs into the DO UPDATE SET?

Answer 1

Just add a WHERE clause at the end of your statement:

WHERE (mytable.col1, mytable.col2, mytable.data_version) IS DISTINCT FROM
      (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.data_version)