SQLite Forum

upsert-clause ON CONFLICT Railway Diagram
Login

upsert-clause ON CONFLICT Railway Diagram

(1) By MarcelF on 2021-07-19 12:33:04 [link] [source]

The railway diagram on https://www.sqlite.org/syntax/upsert-clause.html (and corresponding documentation at https://www.sqlite.org/lang_upsert.html) implies that the brackets with the column names can be left out, i.e. "ON CONFLICT DO UPDATE column = value" is a valid upsert-clause.

In practice, this does not work. You get an "Error: near "UPDATE": syntax error". To make it work you have to specify the relevant columns. So that clause like "ON CONFLICT (indexedcolumn) DO UPDATE column = value" works. Interestingly "ON CONFLICT DO NOTHING" works as described.

So I think this is a bug and either the documentation needs to be changed or the implementation needs to be fixed to conform to the documentation. Looking forward to your feedback.

Marcel

To reproduce run the following in a new SQLite DB:

INSERT INTO foobar (a, b) VALUES (1, 1) ON CONFLICT DO UPDATE SET a = 2;

Result:

Error: near "UPDATE": syntax error

Expected:

Error: no such table: foobar

Version

SQLite Version: SQLite version 3.31.1 2020-01-27 19:55:54
OS: Linux 5.8.0-59-generic #66~20.04.1-Ubuntu SMP Thu Jun 17 11:14:10 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

(2.1) By Larry Brasfield (larrybr) on 2021-07-19 14:59:11 edited from 2.0 in reply to 1 [source]

Presently documented behavior was effected for the 3.35.0 release of 2021-03-12, which was awhile after version 3.31.1 was released. The 3.35.0 release notes mention "Generalize UPSERT" at item 3.

I can attest that version 3.35.0 behaves as you Expected, having observed that personally using your input.

Unfortunately, short of using the documentation repository and rebuilding the docs for past versions, there is not a convenient way to see how past SQLite versions conform to present documentation.

(Added via edit:) BTW, thanks for your nicely done bug report. Even though this is not technically a bug (vis-à-vis doc/code tracking), we appreciate well-explained bug reports with repro steps, and observed/expected results.

(3) By MarcelF on 2021-07-21 09:28:32 in reply to 2.1 [link] [source]

I had just checked my version has the upsert feature. I hadn't thought of the syntax being updated in the mean time. Thank you!