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.

Wearproof

How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT? – Inanna

@lad2025 Did not find this question before, thanks. However, the suggested solution is what I said that I have tried, but would like to avoid because of the additional column in my table for some functionality that I feel that is missing from this UPSERT / ON CONFLICT feature. – Wearproof

PostgreSQL Upsert differentiate inserted and updated rows using system columns XMIN, XMAX and others – Ofelia

To anyone coming across this, I strongly recommend looking at the other two answers linked in these comments from LukaszSzozda and klin. Sagi's comment is incorrect, there is no need to add a column to the table to use those answers. – Corium

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

Bienne

Would not that work only for the first update? – Magner

Answer 2

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 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.Attach the procedure to foo tables:

CREATE TRIGGER update_on_duplicate 
BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE
f0_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() );

Lovesick