主页 / user-241254

albttx's questions

Martin Hope
albttx
Asked: 2021-10-29 02:03:23 +0800 CST
  • 1

我有一张桌子，需要将我的物品存储在特定位置，用户可以“移动”物品的位置以为新物品“腾出空间”。

这是桌子的样子

CREATE TABLE   keys (
    key_name   VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY,
    context    VARCHAR(128) NOT NULL,
    position   INTEGER      NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    UNIQUE (context, position)
);

INSERT INTO keys (key_name, context, position)
VALUES
('A.1', 'ctx_A', 0), 
('A.2', 'ctx_A', 1), 
('A.3', 'ctx_A', 2), 
('A.4', 'ctx_A', 3),
('B.1', 'ctx_B', 0), 
('B.2', 'ctx_B', 1), 
('B.3', 'ctx_B', 2), 
('B.4', 'ctx_B', 3);

我希望能够在位置 1 插入一个键或移动position一个 ( UNIQUE) 键，并且position对于每个大于 new 的值，它都会自动将整数增加 1 INSERT。

这是我到目前为止尝试过的

UPDATE keys
SET position = position + 1
WHERE context = 'ctx_A' AND position >= 2;

错误：重复的键值违反了唯一约束“keys_context_position_key”详细信息：键（上下文，“位置”）=（ctx_A，3）已经存在。

但它不起作用。

编辑

我正在使用 Docker 映像postgres:12.7

我发现使用UNIQUE (context, position) DEFERRABLE允许我执行

UPDATE keys SET position = position + 1 WHERE context = 'ctx_A' AND position > 1;

但是有点像

BEGIN;
  UPDATE keys SET position=2 WHERE context='ctx_A' AND key_name='A.4';
  UPDATE keys SET position=3 WHERE context='ctx_A' AND key_name='A.3';
COMMIT;

仍然无法正常工作！

postgresql unique-constraint