Upserts are powerful, but our parser doesn't support UPSERT semantics #237
Replies: 2 comments
-
Yesterday while doing some support for a user, I noticed another potential use case. The user had a table with a primary key column and was doing things like: The user intended to allow the other two to succeed if any of the 3 (...) would have a primary key violation. That doesn't work with a regular INSERT (as with any normal SQL database). But I think it could work if this had an Not sure if this case is a big justification for the feature, but it would be something that could allow inserting multiple values in a single statement, allowing some of them to be "ignored" because of constraint violation (compared to N independent write-queries). |
Beta Was this translation helpful? Give feedback.
-
I created an issue to track work. I'll lock this discussion. |
Beta Was this translation helpful? Give feedback.
-
SQLite supports upsert not via the
UPSERT
keyword but throughON CONFLICT
. See more about it here: https://www.sqlite.org/lang_UPSERT.htmlHere's an example from those docs:
Caveats:
INSERT
with aSELECT
, but for many cases this isn't needed, as in the example above (enforced by our parser).ON CONFLICT
must be a primary key or have a unique constraint (enforced by SQLite).This should all be deterministic.
We've had at least one request for this. I selfishly would like this feature to explore flow where a row is written by multiple contracts, where each touches different columns.
Beta Was this translation helpful? Give feedback.
All reactions