Check if Postgres query inserted or updated (via upsert)

Question

I'm wondering if it's somehow possible to figure out if a postgres upsert query resulted in an insert or an update.

Something like this psuedocode:

insert into teammates (id, email, avatar, timezone)
values ($1, $2, $3, $4)
on conflict (id) do update set
  avatar = $3,
  timezone = $4
returning id, (updated = didUpdate);

Does this answer your question? [How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?](https://stackoverflow.com/questions/34762732/how-to-find-out-if-an-upsert-was-an-update-with-postgresql-9-5-upsert) — Gajus, Jan 27 '22 at 23:37

score 1 · Answer 1 · answered Mar 08 '17 at 12:22

1

It is necessary to do a manual CTE upsert:

with u as (
    update teammates
    set (avatar, timezone) = ($3, $4)
    where id = $1
    returning id, true as updated
), i as (
    insert into teammates (id, email, avatar, timezone)
    select $1, $2, $3, $4
    where not exists (select 1 from u)
    returning id, false as updated
)
select id, updated from u
union all
select id, updated from i

answered Mar 08 '17 at 12:22

Clodoaldo Neto

118,695
26
233
260

That's the best answer I found among multiple similar questions. Because this answer doesn't require neither any additional `is_updated` / `update_timestamp` column nor does it use the `xmax` hack. Great! – Lars Blumberg Oct 03 '22 at 19:52

score 0 · Answer 2 · answered Mar 08 '17 at 10:31

One way ti to manually check or performing a SELECT followed by UPSERT the below columns and see.

  avatar = $3,
  timezone = $4

You can as well have a DATETIME column named LastModified which should be updated on every UPDATE operation. It's then pretty straight forward for you to find out.

Check if Postgres query inserted or updated (via upsert)

2 Answers2