Postgres - ON CONFLICT - HOW to know if an UPDATE occurred instead of an INSERT [duplicate]

Question

I have a table

CREATE TABLE foo (     f0 int,     time_stamp timestamp,     CONSTRAINT foo_pk PRIMARY KEY (f0) ) 

I need to write to this table in high volumes, so performance is key. Sometimes, I will write a record that has an existing value for f0 and will just update the time_stamp to the current time. For this I use an ON CONFLICT..DO UPDATE clause.

The problem is that I need to know whether an INSERT has occurred or an UPDATE.

I though on using a second is_update column. When inserting, insert false and

`ON CONFLICT .. DO UPDATE set is_update=true` 

Then use RETURNING is_update to get what I want. The issue with that is the introduction of an additional column that is not related to the data itself.

Answer 1

Using two columns for timestamps is common practice. A creation_timestamp column would be set once, on insertion. While an update_timestamp would keep the last overriding update's timestamp to that record.

On each "upsert", you may check if the update_timestamp wasn't set already.

INSERT INTO foo (f0, creation_timestamp) VALUES (1, NOW()) ON CONFLICT (f0) DO UPDATE SET f0=EXCLUDED.f0, update_timestamp=NOW() RETURNING update_timestamp IS NULL