Como descobrir se um upsert foi uma atualização com o PostgreSQL 9.5+ UPSERT?

CTEs graváveis ​​foram considerados uma solução para o UPSERT antes de 9.5, conforme descrito em Insert, em atualização duplicada no PostgreSQL?

É possível executar um UPSERT com as informações, seja como um UPDATE ou um INSERT com o seguinte idioma de CTEs graváveis:

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) 

Essa consulta retornará “atualizada” ou “inserida” ou poderá (raramente) falhar com uma violação de restrição conforme descrito em https://dba.stackexchange.com/questions/78510/why-is-cte-open-to -lost-updates

Pode algo similar ser alcançado usando a nova syntax “UPSERT” do PostgreSQL 9.5+, beneficiando-se de sua otimização e evitando a possível violação de restrição?

Eu acredito que xmax::text::int > 0 seria o truque mais 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 da resposta de @ lad2025 , o resultado pode ser alcançado abusando de configurações e opções personalizadas com funções relacionadas em cláusulas WHERE para obter um efeito colateral necessário.

 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"; 

O terceiro parâmetro do set_config é is_local : true significa que a configuração irá desaparecer no final da transação. Mais precisamente, current_setting('upsert.action') retornará NULL (e não lançará um erro) até o final da session.

Na SQL Server MERGE SQL Server , a $action retorna a string 'INSERT', 'UPDATE', or 'DELETE' .

Para Postgresql não consigo encontrar function / variável que faz coisa semelhante para RETURNING .

Uma maneira de solucionar isso é adicionar a coluna is_updated à sua tabela:

 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; 

Demonstração do Rextester

Saída:

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