如何使用 INSERT ... ON CONFLICT ... 更新所有列? [英] How to update all columns with INSERT ... ON CONFLICT ...?
问题描述
我有一个只有一个主键的表.当我尝试插入时，尝试使用现有键插入行可能会导致冲突.我想允许插入更新所有列?有什么简单的语法吗?我试图让它更新"所有列.
I have a table with a single primary key. When I attempt to do an insert there may be a conflict caused by trying to insert a row with an existing key. I want to allow the insert to update all columns? Is there any easy syntax for this? I am trying to let it "upsert" all columns.
我使用的是 PostgreSQL 9.5.5.
I am using PostgreSQL 9.5.5.
推荐答案
UPDATE 语法 要求明确命名目标列.避免这种情况的可能原因:
The UPDATE syntax requires to explicitly name target columns.
Possible reasons to avoid that:
- 您有很多列，只想缩短语法.
- 除了唯一的列之外，您知道列名.
"All columns" 必须表示"目标表的所有列"(或至少 "目标表的前导列表") 以匹配的顺序和匹配的数据类型.否则，无论如何您都必须提供目标列名列表.
"All columns" has to mean "all columns of the target table" (or at least "leading columns of the table") in matching order and matching data type. Else you'd have to provide a list of target column names anyway.
测试表:
CREATE TABLE tbl (
id int PRIMARY KEY
, text text
, extra text
);
INSERT INTO tbl AS t
VALUES (1, 'foo')
, (2, 'bar');
1.删除 &INSERT 在单个查询中代替
不知道除了 id 之外的任何列名.
1. DELETE & INSERT in single query instead
Without knowing any column names except id.
仅适用于目标表的所有列".虽然该语法甚至适用于前导子集，但目标表中的多余列将通过 DELETE 和 INSERT 重置为 NULL.
Only works for "all columns of the target table". While the syntax even works for a leading subset, excess columns in the target table would be reset to NULL with DELETE and INSERT.
UPSERT (INSERT ... ON CONFLICT ...) 是为了避免并发写入负载下的并发/锁定问题，只是因为没有通用的方法来锁定尚不存在的Postgres 中的行(值锁定).
UPSERT (INSERT ... ON CONFLICT ...) is needed to avoid concurrency / locking issues under concurrent write load, and only because there is no general way to lock not-yet-existing rows in Postgres (value locking).
您的特殊要求仅影响 UPDATE 部分.可能的并发症不适用于 现有 行受到影响的情况.那些被正确锁定.再简化一些，您可以将您的案例简化为 DELETE 和 INSERT:
Your special requirement only affects the UPDATE part. Possible complications do not apply where existing rows are affected. Those are locked properly. Simplifying some more, you can reduce your case to DELETE and INSERT:
WITH data(id) AS ( -- Only 1st column gets explicit name!
VALUES
(1, 'foo_upd', 'a') -- changed
, (2, 'bar', 'b') -- unchanged
, (3, 'baz', 'c') -- new
)
, del AS (
DELETE FROM tbl AS t
USING data d
WHERE t.id = d.id
-- AND t <> d -- optional, to avoid empty updates
) -- only works for complete rows
INSERT INTO tbl AS t
TABLE data -- short for: SELECT * FROM data
ON CONFLICT (id) DO NOTHING
RETURNING t.id;
在 Postgres MVCC 模型中，UPDATE 基本上与 DELETE 和 INSERT 相同(除了一些具有并发性的极端情况，热更新和大列值存储在行外).由于您无论如何都想替换所有行，只需删除 INSERT 之前的冲突行.在提交事务之前，已删除的行保持锁定状态.INSERT 可能仅在并发事务碰巧同时插入它们时(在 DELETE 之后，但在 INSERT).
In the Postgres MVCC model, an UPDATE is largely the same as DELETE and INSERT anyway (except for some corner cases with concurrency, HOT updates, and big column values stored out of line). Since you want to replace all rows anyway, just remove conflicting rows before the INSERT. Deleted rows remain locked until the transaction is committed. The INSERT might only find conflicting rows for previously non-existing key values if a concurrent transaction happens to insert them concurrently (after the DELETE, but before the INSERT).
在这种特殊情况下，您将丢失受影响行的其他列值.没有提出异常.但是，如果竞争查询具有相同的优先级，那几乎不是问题:另一个查询赢得了 一些 行.此外，如果另一个查询是类似的 UPSERT，它的替代方法是等待此事务提交，然后立即更新.胜利"可能是一场代价高昂的胜利.
You would lose additional column values for affected rows in this special case. No exception raised. But if competing queries have equal priority, that's hardly a problem: the other query won for some rows. Also, if the other query is a similar UPSERT, its alternative is to wait for this transaction to commit and then updates right away. "Winning" could be a Pyrrhic victory.
关于空更新":
好的，你要求的:
WITH data(id) AS ( -- Only 1st column gets explicit name!
VALUES -- rest gets default names "column2", etc.
(1, 'foo_upd', NULL) -- changed
, (2, 'bar', NULL) -- unchanged
, (3, 'baz', NULL) -- new
, (4, 'baz', NULL) -- new
)
, ups AS (
INSERT INTO tbl AS t
TABLE data -- short for: SELECT * FROM data
ON CONFLICT (id) DO UPDATE
SET id = t.id
WHERE false -- never executed, but locks the row!
RETURNING t.id
)
, del AS (
DELETE FROM tbl AS t
USING data d
LEFT JOIN ups u USING (id)
WHERE u.id IS NULL -- not inserted !
AND t.id = d.id
-- AND t <> d -- avoid empty updates - only for full rows
RETURNING t.id
)
, ins AS (
INSERT INTO tbl AS t
SELECT *
FROM data
JOIN del USING (id) -- conflict impossible!
RETURNING id
)
SELECT ARRAY(TABLE ups) AS inserted -- with UPSERT
, ARRAY(TABLE ins) AS updated -- with DELETE & INSERT;
怎么做?
- 第一个 CTE
data只是提供数据.可以是一张桌子. - 第二次 CTE
ups:UPSERT.id冲突的行不会更改，但也会锁定. - 第三个 CTE
del删除冲突的行.它们保持锁定状态. - 第 4 个 CTE
ins插入 整行.只允许同一笔交易 - 最终的 SELECT 仅用于演示以显示发生了什么.
- The 1st CTE
datajust provides data. Could be a table instead. - The 2nd CTE
ups: UPSERT. Rows with conflictingidare not changed, but also locked. - The 3rd CTE
deldeletes conflicting rows. They remain locked. - The 4th CTE
insinserts whole rows. Only allowed for the same transaction - The final SELECT is only for the demo to show what happened.
检查空更新测试(之前和之后):
To check for empty updates test (before and after) with:
SELECT ctid, * FROM tbl; -- did the ctid change?
(已注释掉)检查行中的任何更改 AND t <>d 甚至可以使用 NULL 值，因为我们正在比较两个类型化的行值 根据手册:
The (commented out) check for any changes in the row AND t <> d works even with NULL values because we are comparing two typed row values according to the manual:
两个 NULL 字段值被认为相等，一个 NULL 被认为大于一个非 NULL
two NULL field values are considered equal, and a NULL is considered larger than a non-NULL
2.动态 SQL
这也适用于前导列的子集，保留现有值.
2. Dynamic SQL
This works for a subset of leading columns too, preserving existing values.
诀窍是让 Postgres 使用系统目录中的列名动态构建查询字符串，然后执行它.
The trick is to let Postgres build the query string with column names from the system catalogs dynamically, and then execute it.
查看代码的相关答案:
这篇关于如何使用 INSERT ... ON CONFLICT ... 更新所有列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
