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

You can create trigger function on foo table. Then catch the insert operation with TG_OP reserved word, if key exists do update, else insert a new row. Not yet tested with huge rows :)

1.Create the procedure :

CREATE OR REPLACE FUNCTION public.f0_update_on_duplicate() RETURNS trigger LANGUAGE plpgsqlAS $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.Attach the procedure to foo tables:

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

3.Test insert. This should update f0 with new time_stamp (assumming f0 = 5 already exists in foo tables):

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

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 UPDATESET f0=EXCLUDED.f0, update_timestamp=NOW()RETURNING update_timestamp IS NULL