Postgres - ON CONFLICT - HOW to know if an UPDATE 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 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.

[How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?](http://stackoverflow.com/questions/34762732/how-to-find-out-if-an-upsert-was-an-update-with-postgresql-9-5-upsert) — Lukasz Szozda, Mar 30 '16 at 18:15
@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. — Sagi, Mar 31 '16 at 08:02
[PostgreSQL Upsert differentiate inserted and updated rows using system columns XMIN, XMAX and others](https://stackoverflow.com/q/39058213/1995738) — klin, Aug 30 '17 at 06:26
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. — harmic, Aug 16 '18 at 08:22

score 7 · Answer 1 · answered May 06 '21 at 20:17

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

Brain90 · Answer 2 · 2021-06-19T13:41:44.170

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() );

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

2 Answers2