Postgres upsert：区分新行和更新行 [英] Postgres upsert: distinguish between new and updated rows

问题描述

我正在考虑使用PostgreSQL INSERT .. on CONFLICT UPDATE 功能。理想情况下，我将能够区分哪些行已成功插入和哪些行已更新。有办法吗？

I'm thinking of using PostgreSQL INSERT .. ON CONFLICT UPDATE functionality. Ideally I would be able to distinguish between which rows were successful inserted and which were updated. Is there a way to do it?

推荐答案

为此您需要一个附加的辅助列（ updated ）。

You need an additional auxiliary column for this (updated in the example).

create table test (id int primary key, str text, updated boolean);
insert into test values (1, 'old', false);

insert into test values
    (1, 'new 1', false),
    (2, 'new 2', false)
on conflict (id) do
update set 
    str = excluded.str, updated = true
returning *;

 id |  str  | updated 
----+-------+---------
  1 | new 1 | t
  2 | new 2 | f
(2 rows)

这篇关于Postgres upsert：区分新行和更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！