Menu
Writable CTEs were considered a solution to UPSERT prior to 9.5 as described in Insert, on duplicate update in PostgreSQL?
It is possible to perform an UPSERT with the information whether it ended up as an UPDATE or an INSERT with the following Writable CTEs idiom:
WITH update_cte AS ( UPDATE t SET v = $1 WHERE id = $2 RETURNING 'updated'::text status ), insert_cte AS ( INSERT INTO t(id, v) SELECT $2, $1 WHERE NOT EXISTS (SELECT 1 FROM update_cte) RETURNING 'inserted'::text status ) (SELECT status FROM update_cte) UNION (SELECT status FROM insert_cte) This query will return either "updated" or "inserted", or may (rarely) fail with a constraint violation in as described in https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates
Can something similar be achieved using PostgreSQL 9.5+ new "UPSERT" syntax, benefiting from its optimization and avoiding the possible constraint violation?
788
In relational databases, the term upsert is referred to as merge. The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).
Introduction. PostgreSQL lets you either add or modify a record within a table depending on whether the record already exists. This is commonly known as an "upsert" operation (a portmanteau of "insert" and "update").
Using the UPSERT Statement. The word UPSERT combines UPDATE and INSERT , describing it statement's function. Use an UPSERT statement to insert a row where it does not exist, or to update the row with new values when it does.
I believe xmax::text::int > 0 would be the easiest trick:
so=# DROP TABLE IF EXISTS tab; NOTICE: table "tab" does not exist, skipping DROP TABLE so=# CREATE TABLE tab(id INT PRIMARY KEY, col text); CREATE TABLE so=# INSERT INTO tab(id, col) VALUES (1,'a'), (2, 'b'); INSERT 0 2 so=# INSERT INTO tab(id, col) VALUES (3, 'c'), (4, 'd'), (1,'aaaa') ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid; id | col | case | ctid ----+------+----------+------- 3 | c | inserted | (0,3) 4 | d | inserted | (0,4) 1 | aaaa | updated | (0,5) (3 rows) INSERT 0 3 so=# INSERT INTO tab(id, col) VALUES (3, 'c'), (4, 'd'), (1,'aaaa') ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid; id | col | case | ctid ----+------+---------+------- 3 | c | updated | (0,6) 4 | d | updated | (0,7) 1 | aaaa | updated | (0,8) (3 rows) INSERT 0 3 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
