SQLite: excluded int primary key null value replaced with next auto value
0
votes
1
answer
449
views
I'm trying to create generic upsert query builder with specific behavior. Table has two unique constraints: primary key _itId_ and column _slug_:
create table it (
itId integer primary key -- an alias for rowid
, slug text
, text text
, CONSTRAINT "itId" UNIQUE("itId")
, CONSTRAINT "slug" UNIQUE("slug")
);
-- fill the table:
insert into it
(itId, slug, text)
values
(null, 'a', 'letter a')
, (null, 'b', 'letter b')
, (null, 'c', 'letter c')
;
| itId | slug | text |
| ---- | ---- | -------- |
| 1 | a | letter a |
| 2 | b | letter b |
| 3 | c | letter c |
Then, here's an attempt of batch upsert of rows using different keys:
insert into it
(itId, slug, text)
values
(null, 'a', 'latin letter a') -- update text by slug
, ( 2, 'β', 'greek letter beta') -- update slug and text by primary key
, ( 9, 'c', 'latin letter c') -- update primary key and text by slug
on conflict (itId) do update set
text = coalesce(excluded.text, text)
, itId = itId -- save current value for autoincremented column
, slug = coalesce(excluded.slug, slug)
on conflict (slug) do update set
text = coalesce(excluded.text, text)
, itId = coalesce(excluded.itId, itId) -- here's a trouble!
(I'm using [SQLite 3.35](https://www.sqlite.org/releaselog/3_35_4.html) , which allows multiple on conflict
clauses)
| itId | slug | text |
| ---- | ---- | ----------------- |
| 2 | β | greek letter beta |
| **4**| a | latin letter a |
| 9 | c | latin letter c |
As you can see, b
changed to β
-- that's okay; c
has changed its _itId_ according to upserted value 3
-> 9
. But there's a trouble with a
(was matched by slug): _excluded.itId_ resolves with a value 4
instead of null
within second on conflict
clause. It seems that the value of _itId_ for row a
is replaced with the next available autoincrement value for column _itId_ (an alias of _rowid_).
Expectation: itId = coalesce(excluded.itId, itId)
=> coalesce(null, 1)
=> 1
:
| itId | slug | text |
| ---- | ---- | ----------------- |
| **1**| a | latin letter a |
| 2 | β | greek letter beta |
| 9 | c | latin letter c |
Is there a way to achieve such a result using insert ... on conflict do update
for all of these cases?
[Online playground](https://sqlime.org/#deta:rwfs8hlesabw)
Asked by Denis Borzenko
(1 rep)
Apr 18, 2022, 05:20 PM
Last activity: Jan 12, 2025, 04:11 AM
Last activity: Jan 12, 2025, 04:11 AM