Postgres UPSERT 在更新时重用 INSERT 中的列值 [英] Postgres UPSERT reuse column values from INSERT on UPDATE

问题描述

我有一个可以正常工作的基本 upsert 查询:

I have a basic upsert query that works ok:

insert into table (id, data) values (1, '<data_string>') 
    on conflict (id) do update set data='<data_string>';

唯一的问题是我需要在网络上发送大量这样的查询，所以我想知道是否有办法通过不将 <data_string> 在查询?

The only problem is I need to fly lots of these queries over the network so I wonder if there is a way to cut the traffic in half by not having <data_string> listed twice in the query?

推荐答案

是的，有专门的表EXCLUDED为目的:

Yes, there is the special table EXCLUDED for the purpose:

INSERT INTO tbl (id, data)
VALUES (1, '<data_string>') 
ON CONFLICT (id) DO UPDATE SET data = EXCLUDED.data;

像手册解释:

请注意，特殊的 excluded 表用于引用最初建议插入的值:

Note that the special excluded table is used to reference values originally proposed for insertion:

也适用于多行插入:

INSERT INTO tbl (id, data)
VALUES (1, '<data_string1>') 
     , (2, '<data_string2>') 
     , (3, '<data_string3>') 
ON CONFLICT (id) DO UPDATE
SET data = EXCLUDED.data;

这篇关于Postgres UPSERT 在更新时重用 INSERT 中的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！