CODEWITHSUNDEEP
sqlpostgresql
Matt
Matt

Reputation: 22911

Check if Postgres query inserted or updated (via upsert)

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

Upvotes: 3

Views: 2246

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125304

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

Upvotes: 2

Rahul
Rahul

Reputation: 77876

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.

Upvotes: 0

Related Questions