Feature Request: insert/on conflict update status

Started by Robyover 6 years ago1 messages
#1Roby
pacman@finefun.com.au

Hi all,

Following a recent bug report
(</messages/by-id/20190725015448.e5a3rwa22kpnzfe3@alap3.anarazel.de&gt;)
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.