SQL 仅当值不同时 Postgres UPSERT（插入或更新）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3464750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres UPSERT (INSERT or UPDATE) only if value is different
提问by EMP
I'm updating a Postgres 8.4 database (from C# code) and the basic task is simple enough: either UPDATE an existing row or INSERT a new one if one doesn't exist yet. Normally I would do this:
我正在更新 Postgres 8.4 数据库（来自 C# 代码），基本任务非常简单：更新现有行或插入新行（如果尚不存在）。通常我会这样做：
UPDATE my_table
SET value1 = :newvalue1, ..., updated_time = now(), updated_username = 'evgeny'
WHERE criteria1 = :criteria1 AND criteria2 = :criteria2
and if 0 rows were affected then do an INSERT:
如果 0 行受到影响，则执行 INSERT：
INSERT INTO my_table(criteria1, criteria2, value1, ...)
VALUES (:criteria1, :criteria2, :newvalue1, ...)
There is a slight twist, though. I don't want to change the updated_timeand updated_usernamecolumns unless any of the new values are actually different from the existing values to avoid misleading users about when the data was updated.
不过，有一个轻微的转折。我不想更改updated_time和updated_username列，除非任何新值实际上与现有值不同，以避免在数据更新时误导用户。
If I was only doing an UPDATE then I could add WHERE conditions for the values as well, but that won't work here, because if the DB is already up to date the UPDATE will affect 0 rows and then I would try to INSERT.
如果我只是在做一个更新，那么我也可以为这些值添加 WHERE 条件，但这在这里不起作用，因为如果数据库已经是最新的，更新将影响 0 行，然后我会尝试插入。
Can anyone think of an elegant way to do this, other than SELECT, then either UPDATE or INSERT?
除了 SELECT，然后是 UPDATE 或 INSERT 之外，有人能想出一种优雅的方法来做到这一点吗？
回答by Frank Heikens
Take a look at a BEFORE UPDATE trigger to check and set the correct values:
看一下 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;
$$;
Now you don't even have to mention the field updated_time in your UPDATE query, it will be handled by the trigger.
现在您甚至不必在 UPDATE 查询中提及字段 updated_time，它将由触发器处理。
http://www.postgresql.org/docs/current/interactive/plpgsql-trigger.html
http://www.postgresql.org/docs/current/interactive/plpgsql-trigger.html
回答by Gavin
Two things here. Firstly depending on activity levels in your database you may hit a race condition between checking for a record and inserting it where another process may create that record in the interim. The manual contains an example of how to do this link example
这里有两件事。首先，根据数据库中的活动级别，您可能会在检查记录并将其插入到另一个进程可能会在此期间创建该记录的位置之间遇到竞争条件。该手册包含如何执行此 链接示例的示例
To avoid doing an update there is the suppress_redundant_updates_trigger() procedure. To use this as you wish you wold have to have two before update triggers the first will call the suppress_redundant_updates_trigger() to abort the update if no change made and the second to set the timestamp and username if the update is made. Triggers are fired in alphabetical order. Doing this would also mean changing the code in the example above to try the insert first before the update.
为了避免进行更新，有suppress_redundant_updates_trigger() 过程。要按照您的意愿使用它，您必须在更新触发器之前有两个，第一个将调用suppress_redundant_updates_trigger() 以在未进行任何更改时中止更新，第二个将在进行更新时设置时间戳和用户名。触发器按字母顺序触发。这样做也意味着更改上面示例中的代码以在更新之前先尝试插入。
Example of how suppress update works:
抑制更新的工作原理示例：
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;
回答by Bas Peeters
Postgres is getting UPSERT support . It is currently in the treesince 8 May 2015 (commit):
Postgres 正在获得 UPSERT 支持。自 2015 年 5 月 8 日（提交）以来，它目前在树中：
This feature is often referred to as upsert.
This is implemented using a new infrastructure called "speculative insertion". It is an optimistic variant of regular insertion that first does a pre-check for existing tuples and then attempts an insert. If a violating tuple was inserted concurrently, the speculatively inserted tuple is deleted and a new attempt is made. If the pre-check finds a matching tuple the alternative DO NOTHING or DO UPDATE action is taken. If the insertion succeeds without detecting a conflict, the tuple is deemed inserted.
此功能通常称为 upsert。
这是使用称为“投机插入”的新基础架构来实现的。它是常规插入的乐观变体，首先对现有元组进行预检查，然后尝试插入。如果同时插入了违规元组，则删除推测插入的元组并进行新的尝试。如果预检查找到匹配的元组，则执行替代的 DO NOTHING 或 DO UPDATE 操作。如果插入成功而没有检测到冲突，则认为元组已插入。
A snapshot is available for download. It has not yet made a release.
回答by Rabhi salim
INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;
INSERT INTO table_name(column_list) VALUES(value_list)
ON CONFLICT target action;
https://www.postgresqltutorial.com/postgresql-upsert/
https://www.postgresqltutorial.com/postgresql-upsert/
Dummy example :
虚拟示例：
insert into public.user_profile (user_id, resident_card_no, first_name, last_name)
values (103, '14514367', 'don', 'joe_inserted' ) on conflict on constraint user_profile_pk
do
update set resident_card_no = '14514367', last_name = 'joe_updated';
回答by wildplasser
The RETURNINGclause enables you to chain your queries; the second query uses the results from the first. (in this case to avoid re-touching the same rows) (RETURNING is available since postgres 8.4)
该RETURNING子句使您能够链接您的查询；第二个查询使用第一个查询的结果。（在这种情况下，为了避免重新触摸相同的行）（从 postgres 8.4 开始可以使用 RETURNING）
Shown here embedded in a a function, but it works for plain SQL, too
此处显示嵌入在 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 , , , , COALESCE(current_user, 'evgeny' )
WHERE NOT EXISTS (
SELECT * FROM my_table nx
WHERE nx.criteria1 = AND nx.criteria2 =
)
RETURNING criteria1 AS criteria1, criteria2 AS criteria2
)
UPDATE my_table upd
SET value1 = , value2 =
, updated_time = now()
, updated_username = COALESCE(current_user, 'evgeny')
WHERE 1=1
AND criteria1 = AND criteria2 = -- key-condition
AND (value1 <> OR value2 <> ) -- 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 ;
RESULT:
结果：
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)
回答by Tobiasopdenbrouw
Start a transaction. Use a select to see if the data you'd be inserting already exists, if it does, do nothing, otherwise update, if it does not exist, then insert. Finally close the transaction.
开始交易。使用选择查看您要插入的数据是否已存在，如果存在，则不执行任何操作，否则进行更新，如果不存在，则插入。最后关闭交易。
