The following bug has been logged on the website:
Bug reference: 13886
Logged by: Randy Westlund
Email address: rwestlun@gmail.com
PostgreSQL version: 9.5.0
Operating system: FreeBSD-10.2
Description:
Situation: When INSERT ON CONFLICT DO UPDATE does an update.
Current behavior: Postgres returns "INSERT"
Expected behavior: Postgres should return "UPDATE"
Steps:
uvbase=> create table test ( id integer primary key, str text );
CREATE TABLE
uvbase=> insert into test (id, str) values (1, 'insert') on conflict(id) do
update set str = 'update';
INSERT 0 1
uvbase=> select * from test;
id | str
----+--------
1 | insert
(1 row)
uvbase=> insert into test (id, str) values (1, 'insert') on conflict(id) do
update set str = 'update';
INSERT 0 1
uvbase=> select * from test;
id | str
----+--------
1 | update
(1 row)
I believe postgres should be returning "UPDATE" here instead of "INSERT"
because I see no way to know which operation took place. I believe my only
options at the moment are using a second query or adding a column like
"just_created BOOLEAN DEFAULT true", setting it to false in the UPDATE
clause, and returning it.