PSQL - IF CONDITION for DO UPDATE-CodePudding

I'm currently using the following query to update my PSQL table:

INSERT INTO table(key, value, value_first_seen) VALUES ('test', 'value', '2022-04-14 20:50:23.02858 00') 
                ON CONFLICT (key) DO 
                UPDATE
                SET key = excluded.key;

I would like to also update value_first_seen only if the value column has changed.

I want to achieve something like the query below, but was not able to find any solutions:

INSERT INTO table(key, value, value_first_seen) VALUES ('test', 'value', '2022-04-14 20:50:23.02858 00') 
                ON CONFLICT (key) DO 
                UPDATE
                SET value = excluded.value
                IF value != excluded.value SET value_first_seen = excluded.value_first_seen;

Thanks!

CodePudding user response：

Use a case to choose.

INSERT INTO table(key, value, value_first_seen) VALUES ('test', 'value', '2022-04-14 20:50:23.02858 00') 
                ON CONFLICT (key) DO 
                UPDATE
                SET key = excluded.key,
                    value_first_seen = case
                       when value != excluded.value then
                         excluded.value_first_seen
                       else
                         value_first_seen
                       end,
                    value = excluded.value;