SQLite Query Language: upsert

Re:SQLite Query Language: upsert by cobythedog on Thursday May 10, 2018 @05:27PM (#17043113)

They are run together every time. You could run a check but it would probably cost as much as the update, which does nothing when a record is not found.

In PostgreSQL (for example) you could use a CTE and the RETURNING clause to prevent two statements from having to run. I don't think sqlite supports that. Current version of PostgreSQL makes that unnecessary as it has "upsert" as well.

[ Parent | on HN ]

Re:SQLite Query Language: upsert by kodablah on Thursday May 10, 2018 @05:36PM (#17043181)

> You could run a check but it would probably cost as much as the update, which does nothing when a record is not found.

True if the generation of the insert doesn't have some heavier burdens on the caller side. Otherwise, start a txn, do the first, check if 0 updated, then do the second.

[ Parent | on HN ]

Re:SQLite Query Language: upsert by jedberg on Thursday May 10, 2018 @05:35PM (#17043174)

You are right.

You've chosen to trade off network round trips for extra work in the database and slightly longer row locks while both commands run, which seems like a fair tradeoff.

[ Parent | on HN ]