SQLite Forum

inconsistent output: insert on conflict with returning
Login

inconsistent output: insert on conflict with returning

(1) By MK (mkSqliteId) on 2021-10-13 12:53:41 [link] [source]

create temp table foo (name text unique);
insert into foo values ('name1') on conflict(name) do nothing returning *;
-->output: name1
insert into foo values ('name1') on conflict(name) do nothing returning *;
-->output: no output here

drop table foo;
create temp table foo (name text unique, amount int);
insert into foo values ('name1', 0) on conflict(name) do update set amount=excluded.amount returning *;
-->output: name1|0
insert into foo values ('name1', 1) on conflict(name) do update set amount=excluded.amount returning *;
-->output: no output here

Thanks.

(2) By Simon Slavin (slavin) on 2021-10-13 19:18:48 in reply to 1 [link] [source]

Can I ask where you got the idea to use 'excluded.' in those commands ?

(3) By Ryan Smith (cuz) on 2021-10-13 22:58:17 in reply to 2 [link] [source]

I suppose he got it from the documentation.

See the INSERT...ON CONFLICT (aka UPSERT) documentation here: sqlite.org/lang_upsert.html#examples

As to the original question...

The OP is doing an UPSERT with RETURNING clause that works when the INSERT happens, but if conflicted and in stead an UPDATE happens, no row is returned (since no insert happened), which is probably as designed and as documented since the full RETURNING field-set might not be available to the UPDATE mechanism, the same as when the INSERT mechanism runs and the "excluded.xxx" construct is not available when INSERTing since no exclusion happened - so not sure the RETURNING clause can deal with both.

I suspect it simply works for the INSERT part only as designed, but that is pure speculation on my part and as far as I can tell, no such limitation is documented, so it may simply be a bug.

(4) By ddevienne on 2021-10-14 06:54:12 in reply to 3 [source]

Well, PostgreSQL does behave differently here (see last insert below), again. So I'd be leaning more toward the bug side:

D:\oss>psql -h pq
Password for user ddevienne:
psql (12.1, server 12.5)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

ddevienne=> create temp table foo (name text unique);
CREATE TABLE
ddevienne=> insert into foo values ('name1') on conflict(name) do nothing returning *;
 name
-------
 name1
(1 row)


INSERT 0 1
ddevienne=> insert into foo values ('name1') on conflict(name) do nothing returning *;
 name
------
(0 rows)


INSERT 0 0
ddevienne=> drop table foo;
DROP TABLE
ddevienne=> create temp table foo (name text unique, amount int);
CREATE TABLE
ddevienne=> insert into foo values ('name1', 0) on conflict(name) do update set amount=excluded.amount returning *;
 name  | amount
-------+--------
 name1 |      0
(1 row)


INSERT 0 1
ddevienne=> insert into foo values ('name1', 1) on conflict(name) do update set amount=excluded.amount returning *;
 name  | amount
-------+--------
 name1 |      1
(1 row)


INSERT 0 1
ddevienne=>