主页 / dba / 问题 / 285910
Accepted
Paul Draper
Asked: 2021-02-24 10:10:20 +0800 CST

如何同时更新 PostgreSQL 中的唯一键？

  • 772
CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE
);

我有数据

 id | name | ordinal 
----+------+---------
  1 | A    |       1
  2 | B    |       2
  3 | C    |       3

我想将其更新为

 id | name | ordinal 
----+------+---------
  1 | A    |       3
  2 | B    |       2
  3 | C    |       1

尽可能少地接触记录（即不要重写整个记录集，不要启动不必要的触发器），更新ordinal为目标值的普遍适用的方法是什么？

普通更新只会让我违反约束，即使它发生在单个语句中。

并且删除和重新创建唯一约束是昂贵的并且并发性差。

这似乎是一个足够普遍的问题，应该有一个很好的方法来做到这一点，我只是想不出。

postgresql update

2 个回答

  1. Best Answer

    将约束定义为可延迟的：

    CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE DEFERRABLE
);

    然后你可以在一个语句中更新它：

    update widget
  set ordinal = t.new_ordinal
from (
  values (1, 3), (3,1) 
) as t(id, new_ordinal)
where t.id = widget.id
    • 3

  2. 默认情况下是普通UNIQUE约束NOT DEFERRABLE。在每一行之后检查唯一的违规行为。手册将其表述为：

    每个命令后立即检查

    但它确实检查了每个单独的书面行。

    如果您定义UNIQUE约束DEFERRABLE（如提供的 a_horse），则会在每个 statement 之后检查唯一的违规行为。在这方面，同一查询中的多个 CTE 仍算作一条语句。

    CREATE TABLE widget_deferrable (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE DEFERRABLE  -- !
);

    如果您还实际将约束设置为DEFERRED，则对唯一违规的检查将推迟到每个事务之后。

    CREATE TABLE widget_deferred (
  id serial PRIMARY KEY,
  ordinal int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED  --!
);

    综合演示：

    db<>在这里摆弄

    进一步阅读：

    • 1

相关问题