Feature Request: insert/on conflict update status

From: Roby <pacman(at)finefun(dot)com(dot)au>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Feature Request: insert/on conflict update status
Date: 2019-08-11 01:16:55
Message-ID: 1565486215.7551.0@finefun.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

Following a recent bug report
(<https://www.postgresql.org/message-id/20190725015448.e5a3rwa22kpnzfe3%40alap3.anarazel.de>)
it was suggested that that I submit a feature request for the ability
to test whether an (insert / on conflict update) "upsert" resulted in
an insert or an update.

I am currently testing if xmax = 0 to achieve this however I understand
this is not reliable.

An excerpt follows - I am performing bulk data maintenance, hence the
insert into/select from. The "returning" clause would ideally reference
something more reliable than xmax.

insert into test_table (test_id, test_code, test_name)
select test_code, test_name
from bulk_test_data
on conflict (test_code) do update
set test_name = test_name_in
where test_table.test_name is distinct from excluded.test_name
returning test_id, case when (xmax = 0)::boolean as inserted

Regards,
Roby.

Browse pgsql-hackers by date

  From Date Subject
Next Message Konstantin Knizhnik 2019-08-11 06:52:50 Re: Global temporary tables
Previous Message legrand legrand 2019-08-10 20:34:38 Re: [survey] New "Stable" QueryId based on normalized query text