5 ms·
I prefer to use CTE in postgres to do upserts, this is a non complete example, but you get the general idea: WITH upsert AS ( UPDATE some_table SET
by np422 13y ago
I prefer to use CTE in postgres to do upserts, this is a non complete example, but you get the general idea:
WITH upsert AS (
UPDATE some_table
SET attrib = 'foo'
WHERE id = 123
RETURNING *
) INSERT INTO some_table ( id , attrib )
SELECT 123 as id, 'foo' as attrib
WHERE 123 NOT IN (SELECT id FROM upsert );
- jeltz 13y agoThis does not handle concurrent inserts though unless you rerun the code on unique violation.
- np422 13y agoCorrect, but if I write the upsert in that way - I actually understand what I'm doing... :) If this is standard upsert query in your app, you should turn it into a procedure and parameterize, catch exceptions anyway. Or you could mess around with transaction isolation.