Postgres - ON CONFLICT - HOW to know if an UPDATE has occurred instead of an INSERT

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 large volumes, so performance is key. Sometimes I write a record that has an existing value for f0 and will just update time_stamp to the current time. For this, I use the ON CONFLICT..DO UPDATE clause.

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

I am using the second is_update column. When pasting, insert false and

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

Then use RETURNING is_update to get what I want. The problem with this is introducing an extra column that is not related to the data itself.

Answer 1

You can create a trigger function in the foo table. Then run the insert operation with the reserved word TG_OP, if the key exists, update, otherwise insert a new line. Not yet verified with huge ranks :)

1.Create a procedure:

 CREATE OR REPLACE FUNCTION public.f0_update_on_duplicate() RETURNS trigger LANGUAGE plpgsql AS $function$ declare _exists boolean; begin if TG_OP = 'INSERT' then select exists(select true from foo where f0 = NEW.f0) into _exists; raise notice '%', _exists; if _exists = TRUE then update foo set time_stamp = NEW.time_stamp where f0 = NEW.f0; return NULL; else return NEW; end if; end if; end $function$; 

2. Set the procedure for the foo table:

 CREATE TRIGGER update_on_duplicate BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE f0_update_on_duplicate(); 

3. Insert the test. This should update f0 with the new time_stamp (the assumption f0 = 5 exists in foo tables):

INSERT INTO foo (f0, time_stamp) VALUES ( 5, now() );