UPSERT ON CONFLICT enhancement

(1.1) By Rick Gumpertz (rgumpertz) on 2022-07-31 20:13:24 edited from 1.0 [source]

Sometimes the "indexed-column" list in UNIQUE constraints can be pretty long. Similarly, the "indexed-column" list in UNIQUE indexes can be pretty long.

Given that UNIQUE constraints CAN be named using the CONSTRAINT keyword and given that UNIQUE indexes are always named, it seems to me that it would be nice if the ON CONFLICT portion of "upsert-clause" could reference a constraint or index by name instead of replicating the "indexed-column" list as the "column-name-list" of the "upsert-clause".

For example, in:

CREATE Table TBL (a, b, c, d, e, f, g, h, count INTEGER DEFAULT 1);

CREATE UNIQUE INDEX idx on TBL tbl (a, b, c, d, e, f, g, h);

INSERT INTO tbl (a, b, c, d, e, f, g, h) VALUES (...) ON CONFLICT (a, b, c, d, e, f, g, h) DO UPDATE SET count = count + 1;

The upsert caluse could be changed to something like

ON CONFLICT (idx) DO UPDATE SET count = count + 1;

So far, the only issue with this proposal that I have been able to identify is that the name idx might be ambiguous if one of the columns were also named idx. This could be resolved with a little extra syntax:

ON CONFLICT WITH INDEX idx DO UPDATE SET count = count + 1;

ON CONFLICT WITH CONSTRAINT constraint-name DO UPDATE SET count = count + 1;

(I'm certainly open to other syntax choices for resolving the ambiguity...)

Has any body considered such an enhancement to SQLite3? If so, are there any other downsides?

Any thoughts on this proposal?

(2) By Keith Medcalf (kmedcalf) on 2022-07-31 21:31:08 in reply to 1.1 [link] [source]

In this particular case there is only one (1) index that can cause a conflict so there is no need to specify/identify the index on which a conflict arises.

That is, the statement:

insert into tbl (a,b,c,d,e,f,g,h) values (...) on conflict do update set count=count+1;

is entirely sufficient.

You point might be valid if there were indexes such as the following:

create unique index a on tbl (a,c,d,e,g);
create unique index b on tbl (b,d,e,f,h);

in which case knowing WHICH unique index had a conflict might be useful if and only if your conflict resolution differs because of that knowledge.

(3.1) By Rick Gumpertz (rgumpertz) on 2022-08-01 17:39:25 edited from 3.0 in reply to 2 [link] [source]

While my example only shows one INDEX, the table has a PRIMARY KEY and also some other INDEXES.

I simplified things for the discussion but perhaps went a bit too far. Dropping the "conflict target" won't work.

COMPLICATION: in at least one case in some real code, the UNIQUE INDEX contains "expr"s instead of a "column-name"s, such as "CASE d IS NULL THEN -0.5 ELSE d" (where column d will always contain an INTEGER or NULL). This is the best kludge I could come up with to get around the problem of UNIQUE INDEX allowing multiple NULL values in SQLite. (That is, UNIQUE in SQLite behaves as if it uses "=" instead of "IS" for all its comparisons.) Anyway, a few such expressions make the "conflict target" even more verbose for copying!

ASIDE: Does anybody have a suggestion of a better way to get around this multiple-NULL-allowed problem in UNIQUE?

(4) By Keith Medcalf (kmedcalf) on 2022-08-01 18:04:34 in reply to 3.1 [link] [source]

I see your problem. Consider:

create table x
(
  id integer primary key,
  b  integer unique
);
create unique index xnull on x(1) where b is null;

Now you can ON CONFLICT (id) DO ... ON CONFLICT (b) DO ... ON CONFLICT DO ...
where the last on conflict catches the condition where you attempt to insert a duplicate null. You would only run into a differentation problem if there where multiple columns in the same table to which such indexing applied.

Note that the index xnull is a unique index on a constant, so multiple entries will cause a unique violation, and the condition for an a tuple in x having an entry in xnull is b IS null, therefore only one tuple in x can have b IS null.

(5.1) By David Raymond (dvdraymond) on 2022-08-01 18:20:20 edited from 5.0 in reply to 3.1 [link] [source]

I don't want to say a "better" way, but for the unique index you can turn every nullable field into 2 fields in the index, an isnull field and a coalesce field.

create table t (a, b, c);

If you want a unique index on (a, b), where you want nulls to be "equal" you can turn it into something like

create unique index i on t (a is null, coalesce(a, ''), b is null, coalesce(b, ''));

Every column in that index is guaranteed to be non-null.

It can even be used as the target of an upsert (although it's ugly).

insert into t values (1, null, 1) on conflict (a is null, coalesce(a, ''), b is null, coalesce(b, '')) do update set c = excluded.c;

(6) By beetlejuice (coleifer) on 2022-08-04 17:36:02 in reply to 1.1 [link] [source]

This is supported in Postgres using ON CONFLICT ON CONSTRAINT "constraint" syntax, so if Sqlite did choose to adopt this, it would be nice to use the same syntax.

https://www.postgresql.org/docs/current/sql-insert.html -- see "conflict_target" in syntax diagram.