如何在postgres中获取UPSERT操作的插入和更新行

如果将布尔更新的列添加到

people

ALTER TABLE people ADD COLUMN updated bool DEFAULT FALSE;

那么您可以通过

updated = TRUE

DO UPDATE SET

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')