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

Answer 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