postgresql - update - upsert on conflict

¿Cómo saber si una actualización fue una actualización con PostgreSQL 9.5+ UPSERT?

Question

Los CTEs grabables se consideraron una solución para UPSERT antes de 9.5 como se describe en Insertar, ¿en una actualización duplicada en PostgreSQL?

Es posible realizar un UPSERT con la información ya sea que termine como ACTUALIZACIÓN o INSERTAR con el siguiente lenguaje de escritura CTE:

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á "actualizado" o "insertado", o puede (raramente) fallar con una infracción de restricción como se describe en https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates

¿Se puede lograr algo similar utilizando la nueva sintaxis "UPSERT" de PostgreSQL 9.5+, beneficiándose de su optimización y evitando la posible violación de restricciones?

Answer 1

A partir de la respuesta de @ lad2025 , el resultado se puede lograr al abusar de las settings y las opciones personalizadas con funciones relacionadas en las cláusulas WHERE para obtener el 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 lanzará un error) hasta el final de la sesión.

Answer 2

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

Answer 3

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

Para Postgresql no puedo encontrar una función / variable que haga algo similar 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;

Demo Rextester

Salida:

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

Answer 4

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