How to do INSERT ... ON CONFLICT DO UPDATE and update TIMESTAMP automatically?

Question

I have a table with PostgreSQL similar to this one:

mytable:

id
col2
col3
import_ts TIMESTAMP(0) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP

So when I do an insert, I do not have to specify any value for this column and the current timestamp is automatically inserted.

However, when using a query like the following one:

INSERT INTO mytable (id, col2, col3) VALUES (%s, %s, %s)
                            ON CONFLICT (id) DO UPDATE
                            SET col2 = %s, col3 = %s;

the result is that in the case of INSERT, import_ts is automatically inserted, as expected; but in case of UPDATE the column import_ts will not be updated.

In order to have import_ts updated, as I am using python and psycopg2, I could add in SET import_ts = %s and pass the current time. However, it does not seem elegant and consistent that in one case the timestamp is added automatically and in the other one I have to add it. I would prefer that it is always done automatically by PostgreSQL. Is that possible?

Answer 1

Sure, just use the now() function:

INSERT INTO mytable (id, col2, col3) VALUES (%s, %s, %s)
    ON CONFLICT (id) DO UPDATE
    SET col2 = %s, col3 = %s, import_ts = now();