Sample Header Ad - 728x90

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