如何在 postgres 中获取 UPSERT 操作的 INSERTED 和 UPDATED 行

本文介绍了如何在 postgres 中获取 UPSERT 操作的 INSERTED 和 UPDATED 行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个像这样的 UPSERT 操作:

I've got an UPSERT Operation like this:

INSERT INTO people (SELECT * FROM people_update) ON CONFLICT (name,surname) DO UPDATE SET age = EXCLUDED.age , street = EXCLUDED.street , city = EXCLUDED.city , postal = EXCLUDED.postal WHERE (people.age,people.street,people.city,people.postal) IS DISTINCT FROM (EXCLUDED.age,EXCLUDED.street,EXCLUDED.city,EXCLUDED.postal) RETURNING case when xmax::text::int > 0 then 'updated' else 'inserted' end,name,surname,age,street,city,postal;

(name,surname) 是一个复合主键，与 'people' 表相比，'people_update' 表包含额外的和更改的行.

(name,surname) is a composite primary key and the 'people_update' table contains additional and changed rows in comparison to the 'people' table.

我的问题是:有没有办法让这个查询的插入和更新的行作为返回行?

My question is: Is there a way to get the inserted and updated rows of this query as the returning rows?

我通过添加 RETURNING 子句解决了这个问题，但我还想在返回值中获取旧值.

I halfway solved the problem by adding a RETURNING clause but I would also like to get the old values within my return values.

推荐答案

如果向 people 表中添加一个布尔值更新列:

If you add a boolean updated column to the people table:

ALTER TABLE people ADD COLUMN updated bool DEFAULT FALSE;

然后您可以通过在 DO UPDATE SET 子句中设置 updated = TRUE 来识别更新的行:

then you could identify updated rows by setting updated = TRUE in the DO UPDATE SET clause:

INSERT INTO people (SELECT * FROM people_update) ON CONFLICT (name,surname) DO UPDATE SET age = EXCLUDED.age , street = EXCLUDED.street , city = EXCLUDED.city , postal = EXCLUDED.postal , updated = TRUE WHERE (people.age,people.street,people.city,people.postal) IS DISTINCT FROM (EXCLUDED.age,EXCLUDED.street,EXCLUDED.city,EXCLUDED.postal) RETURNING *;

例如

CREATE TABLE test.people ( name text , surname text , age float , street text , city text , postal int ); CREATE UNIQUE INDEX people_idx on people (name, surname); ALTER TABLE people ADD COLUMN updated bool; ALTER TABLE people ADD COLUMN prior_age float; ALTER TABLE people ADD COLUMN prior_street text; ALTER TABLE people ADD COLUMN prior_city text; ALTER TABLE people ADD COLUMN prior_postal int; INSERT INTO people (name, surname, age, street, city, postal) VALUES ('Sancho', 'Panza', 414, '1 Manchego', 'Barcelona', 01605) , ('Oliver', 'Twist', 182, '2 Stilton', 'London', 01837) , ('Quasi', 'Modo', 188, $$3 Rue d'Arcole$$, 'Paris' , 01831 ) ; CREATE TABLE test.people_update ( name text , surname text , age float , street text , city text , postal int ); INSERT INTO people_update (name, surname, age, street, city, postal) VALUES ('Sancho', 'Panza', 4140, '10 Idiazabal', 'Montserrat', 16050) , ('Quasi', 'Modo', 1880, $$30 Champs Elysée$$ , 'Paris', 18310 ) , ('Pinocchio', 'Geppetto', 1380, '40 Nerbone', 'Florence', 18810) ; INSERT INTO people (SELECT * FROM people_update) ON CONFLICT (name,surname) DO UPDATE SET updated = TRUE , prior_age = (CASE WHEN people.age = EXCLUDED.age THEN NULL ELSE people.age END) , prior_street = (CASE WHEN people.street = EXCLUDED.street THEN NULL ELSE people.street END) , prior_city = (CASE WHEN people.city = EXCLUDED.city THEN NULL ELSE people.city END) , prior_postal = (CASE WHEN people.postal = EXCLUDED.postal THEN NULL ELSE people.postal END) , age = EXCLUDED.age , street = EXCLUDED.street , city = EXCLUDED.city , postal = EXCLUDED.postal WHERE (people.age,people.street,people.city,people.postal) IS DISTINCT FROM (EXCLUDED.age,EXCLUDED.street,EXCLUDED.city,EXCLUDED.postal) RETURNING *;

收益

| name | surname | age | street | city | postal | updated | prior_age | prior_street | prior_city | prior_postal | |------------+----------+------+------------------+------------+--------+---------+-----------+----------------+------------+--------------| | Sancho | Panza | 4140 | 10 Idiazabal | Montserrat | 16050 | t | 414 | 1 Manchego | Barcelona | 1605 | | Quasi | Modo | 1880 | 30 Champs Elysée | Paris | 18310 | t | 188 | 3 Rue d'Arcole | | 1831 | | Pinocchio | Geppetto | 1380 | 40 Nerbone | Florence | 18810 | f | | | | |

updated 列显示 ('Sancho', 'Panza') 和 ('Quasi', 'Modo') 行有已更新，并且('Pinocchio', 'Geppetto') 是一个新的插入.

The updated column shows the ('Sancho', 'Panza') and ('Quasi', 'Modo') lines have been updated, and ('Pinocchio', 'Geppetto') is a new insert.