¿Cómo averiguar si un upsert era una actualización con PostgreSQL 9.5+ UPSERT?

Los CTE escribibles se consideraron una solución para UPSERT antes de 9.5 como se describe en Insertar, en la actualización duplicada en PostgreSQL?

Es posible realizar un UPSERT con la información si terminó como una ACTUALIZACIÓN o un INSERT con el siguiente modificador CTE adaptable:

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) 

Esta consulta devolverá “actualizada” o “insertada”, o puede (raramente) fallar con una violación de restricción en como se describe en https://dba.stackexchange.com/questions/78510/why-is-cte-open-to -lost-updates

¿Se puede lograr algo similar usando la nueva syntax “UPSERT” de PostgreSQL 9.5+, beneficiándose de su optimización y evitando la posible violación de restricciones?

Creo que xmax::text::int > 0 sería el truco más fácil:

 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 

A partir de la respuesta de @ lad2025 , el resultado puede lograrse al abusar de configuraciones y opciones personalizadas con funciones relacionadas en las cláusulas WHERE para obtener un efecto secundario requerido.

 CREATE TABLE t(id INT PRIMARY KEY, v TEXT); INSERT INTO t (id, v) SELECT $1, $2 WHERE 'inserted' = set_config('upsert.action', 'inserted', true) ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v WHERE 'updated' = set_config('upsert.action', 'updated', true) RETURNING current_setting('upsert.action') AS "upsert.action"; 

El tercer parámetro de set_config es is_local : true significa que la configuración desaparecerá al final de la transacción. Más precisamente, current_setting('upsert.action') devolverá NULL (y no arrojará un error) hasta el final de la sesión.

En SQL Server instrucción MERGE tiene $action que devuelve la cadena 'INSERT', 'UPDATE', or 'DELETE' .

Para Postgresql no puedo encontrar la función / variable que haga lo mismo para RETURNING .

Una forma de solucionarlo es agregar la columna is_updated a su tabla:

 DROP TABLE IF EXISTS tab; CREATE TABLE tab(id INT PRIMARY KEY, col VARCHAR(100), is_updated BOOLEAN DEFAULT false); INSERT INTO tab(id, col) VALUES (1,'a'), (2, 'b'); -- main query INSERT INTO tab(id, col) VALUES (3, 'c'), (4, 'd'), (1,'aaaa') ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col, is_updated = true RETURNING id,col, CASE WHEN is_updated THEN 'UPDATED' ELSE 'INSERTED' END AS action; 

Rextester Demo

Salida:

 ╔════╦══════╦══════════╗ ║ id ║ col ║ action ║ ╠════╬══════╬══════════╣ ║ 3 ║ c ║ INSERTED ║ ║ 4 ║ d ║ INSERTED ║ ║ 1 ║ aaaa ║ UPDATED ║ ╚════╩══════╩══════════╝ 

(xmax::text::bigint > 0) o (NOT xmax = 0) . La conversión de Typecast a entero se interrumpirá una vez que el recuento de transacciones scope el desbordamiento de enteros.