仅当值不同时 Postgres UPSERT（插入或更新）

Question

我正在更新 Postgres 8.4 数据库（来自 C# 代码），基本任务非常简单：更新现有行或插入新行（如果尚不存在）。通常我会这样做：

UPDATE my_table
SET value1 = :newvalue1, ..., updated_time = now(), updated_username = 'evgeny'
WHERE criteria1 = :criteria1 AND criteria2 = :criteria2

如果 0 行受到影响，则执行 INSERT：

INSERT INTO my_table(criteria1, criteria2, value1, ...)
VALUES (:criteria1, :criteria2, :newvalue1, ...)

不过，有一个轻微的变化。我不想更改 updated_time 和 updated_username 列，除非任何新值实际上与现有值不同，以避免在数据更新时间上误导用户。

如果我只进行更新，那么我也可以为值添加 WHERE 条件，但这在这里不起作用，因为如果数据库已经是最新的，更新将影响 0 行，然后我会尝试插入。

除了 SELECT 之外，还有 UPDATE 或 INSERT 之外，谁能想到一种优雅的方法来做到这一点？

Answer 1

看一下 BEFORE UPDATE 触发器来检查并设置正确的值：

CREATE OR REPLACE FUNCTION my_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS
$
BEGIN
    IF OLD.content = NEW.content THEN
        NEW.updated_time= OLD.updated_time; -- use the old value, not a new one.
    ELSE
        NEW.updated_time= NOW();
    END IF;
    RETURN NEW;
END;
$;

现在您甚至不必在 UPDATE 查询中提及字段 Updated_time，它将由触发器处理。

http://www.postgresql.org/docs/current/interactive/plpgsql -trigger.html

Answer 2

这里有两件事。
首先，根据数据库中的活动级别，您可能会在检查记录和将其插入另一个进程可能会临时创建该记录的位置之间遇到竞争条件。
该手册包含如何执行此操作的示例
链接示例

以避免这样做更新是suppress_redundant_updates_trigger()过程。要按照您希望的方式使用此功能，您必须有两个更新前触发器，第一个触发器将调用suppress_redundant_updates_trigger()以在未进行任何更改的情况下中止更新，第二个触发器将在进行更新时设置时间戳和用户名。触发器按字母顺序触发。
这样做还意味着更改上面示例中的代码，以便在更新之前先尝试插入。

抑制更新如何工作的示例：

    DROP TABLE sru_test;

    CREATE TABLE sru_test(id integer not null primary key,
    data text,
    updated timestamp(3));

    CREATE TRIGGER z_min_update
    BEFORE UPDATE ON sru_test
    FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();

    DROP FUNCTION set_updated();

    CREATE FUNCTION set_updated()
    RETURNS TRIGGER
    AS $
    DECLARE
    BEGIN
        NEW.updated := now();
        RETURN NEW;
    END;
    $ LANGUAGE plpgsql;

    CREATE TRIGGER zz_set_updated
    BEFORE INSERT OR UPDATE ON sru_test
    FOR EACH ROW EXECUTE PROCEDURE  set_updated();

insert into sru_test(id,data) VALUES (1,'Data 1');
insert into sru_test(id,data) VALUES (2,'Data 2');

select * from sru_test;

update sru_test set data = 'NEW';

select * from sru_test;

update sru_test set data = 'NEW';

select * from sru_test;

update sru_test set data = 'ALTERED'  where id = 1;

select * from sru_test;

update sru_test set data = 'NEW' where id = 2;

select * from sru_test;

Answer 3

Postgres 正在获得 UPSERT 支持。它目前位于 自 2015 年 5 月 8 日起（提交 ）：

此功能通常称为 upsert。

这是使用称为“推测性”的新基础设施来实现的
插入”。这是常规插入的乐观变体
首先对现有元组进行预检查，然后尝试
插入。如果同时插入违规元组，
推测插入的元组被删除并进行新的尝试。如果
预检查找到匹配的元组替代 DO NOTHING 或 DO
采取更新操作。如果插入成功而没有检测到
冲突，该元组被视为已插入。

快照可供下载。它尚未发布版本。

Answer 4

插入表名（列列表）值（值列表）
ON CONFLICT 目标操作；

https://www.postgresqltutorial.com/postgresql-upsert/

虚拟示例：

insert into user_profile (user_id, resident_card_no, last_name) values
 (103, '14514367', 'joe_inserted' ) 
on conflict on constraint user_profile_pk do 
update set resident_card_no = '14514367', last_name = 'joe_updated';

Answer 5

RETURNING 子句使您能够链接查询；第二个查询使用第一个查询的结果。 （在这种情况下是为了避免重新触摸相同的行）（自 postgres 8.4 起可使用 RETURNING）

这里显示嵌入在 aa 函数中，但它也适用于普通 SQL

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE my_table
        ( updated_time timestamp NOT NULL DEFAULT now()
        , updated_username varchar DEFAULT '_none_'
        , criteria1 varchar NOT NULL
        , criteria2 varchar NOT NULL
        , value1 varchar
        , value2 varchar
        , PRIMARY KEY (criteria1,criteria2)
        );

INSERT INTO  my_table (criteria1,criteria2,value1,value2)
SELECT 'C1_' || gs::text
        , 'C2_' || gs::text
        , 'V1_' || gs::text
        , 'V2_' || gs::text
FROM generate_series(1,10) gs
        ;

SELECT * FROM my_table ;

CREATE function funky(_criteria1 text,_criteria2 text, _newvalue1 text, _newvalue2 text)
RETURNS VOID
AS $funk$
WITH ins AS (
        INSERT INTO my_table(criteria1, criteria2, value1, value2, updated_username)
        SELECT $1, $2, $3, $4, COALESCE(current_user, 'evgeny' )
        WHERE NOT EXISTS (
                SELECT * FROM my_table nx
                WHERE nx.criteria1 = $1 AND nx.criteria2 = $2
                )
        RETURNING criteria1 AS criteria1, criteria2 AS criteria2
        )
        UPDATE my_table upd
        SET value1 = $3, value2 = $4
        , updated_time = now()
        , updated_username = COALESCE(current_user, 'evgeny')
        WHERE 1=1
        AND criteria1 = $1 AND criteria2 = $2 -- key-condition
        AND (value1 <> $3 OR value2 <> $4 )   -- row must have changed
        AND NOT EXISTS (
                SELECT * FROM ins -- the result from the INSERT
                WHERE ins.criteria1 = upd.criteria1
                AND ins.criteria2 = upd.criteria2
                )
        ;
$funk$ language sql
        ;

SELECT funky('AA', 'BB' , 'CC', 'DD' );            -- INSERT
SELECT funky('C1_3', 'C2_3' , 'V1_3', 'V2_3' );    -- (null) UPDATE 
SELECT funky('C1_7', 'C2_7' , 'V1_7', 'V2_7777' ); -- (real) UPDATE 

SELECT * FROM my_table ;

结果：

        updated_time        | updated_username | criteria1 | criteria2 | value1 | value2  
----------------------------+------------------+-----------+-----------+--------+---------
 2013-03-13 16:37:55.405267 | _none_           | C1_1      | C2_1      | V1_1   | V2_1
 2013-03-13 16:37:55.405267 | _none_           | C1_2      | C2_2      | V1_2   | V2_2
 2013-03-13 16:37:55.405267 | _none_           | C1_3      | C2_3      | V1_3   | V2_3
 2013-03-13 16:37:55.405267 | _none_           | C1_4      | C2_4      | V1_4   | V2_4
 2013-03-13 16:37:55.405267 | _none_           | C1_5      | C2_5      | V1_5   | V2_5
 2013-03-13 16:37:55.405267 | _none_           | C1_6      | C2_6      | V1_6   | V2_6
 2013-03-13 16:37:55.405267 | _none_           | C1_8      | C2_8      | V1_8   | V2_8
 2013-03-13 16:37:55.405267 | _none_           | C1_9      | C2_9      | V1_9   | V2_9
 2013-03-13 16:37:55.405267 | _none_           | C1_10     | C2_10     | V1_10  | V2_10
 2013-03-13 16:37:55.463651 | postgres         | AA        | BB        | CC     | DD
 2013-03-13 16:37:55.472783 | postgres         | C1_7      | C2_7      | V1_7   | V2_7777
(11 rows)

Answer 6

这是一种根本不需要存储过程或触发器的方法。

create table my_table (
    id       varchar(32) primary key not null,
    username varchar(32),
    -- ...
    etag     varchar(32), -- use a hash of the fields you want to trigger a version increment
    version  int default 1 not null
);

insert into my_table 
    (id, username, etag) 
values 
    (?, ?, ?) 
on conflict 
    (id) 
do update set 
    username = excluded.username,
    etag     = excluded.etag,
    version  = case when excluded.etag <> my_table.etag then my_table.version + 1 else my_table.version end
;

Answer 7

开始交易。使用 select 来查看要插入的数据是否已经存在，如果存在，则不执行任何操作，否则更新，如果不存在，则插入。最后关闭交易。