BUG #13886: When INSERT ON CONFLICT DO UPDATE updates, it returns INSERT rather than UPDATE

From: rwestlun(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13886: When INSERT ON CONFLICT DO UPDATE updates, it returns INSERT rather than UPDATE
Date: 2016-01-25 07:48:06
Message-ID: 20160125074806.2555.44482@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13886
Logged by: Randy Westlund
Email address: rwestlun(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Marko Tiikkaja 2016-01-25 10:00:38 Re: BUG #13886: When INSERT ON CONFLICT DO UPDATE updates, it returns INSERT rather than UPDATE
Previous Message xtracoder 2016-01-24 20:29:51 BUG #13885: float->string conversion loses precision server-side on JDBC connection