时间戳

首页 > 解决方案 > 如何在覆盖“CREATE UNIQUE INDEX”约束时批量更新？

postgresql - 如何在覆盖“CREATE UNIQUE INDEX”约束时批量更新？

问题描述

我有桌子

CREATE TABLE my_table (
    ranking    INTEGER NOT NULL,
    name         TEXT  NOT NULL,
    is_deleted BOOLEAN NOT NULL
);
CREATE UNIQUE INDEX exists_const ON my_table ranking WHERE (is_deleted = FALSE);

| ranking |  name  | is_deleted |
|   1     |  A     |   true     |
|   2     |  B     |   true     |
|   1     |  C     |   true     |
|   1     |  D     |   false    |
|   2     |  E     |   false    |
|   3     |  F     |   false    |

所以当我想更新 D、E、F 的新排名时

UPDATE "my-table" AS t SET "ranking"=v."ranking" FROM 
(VALUES(1, 'F', 'false'),(2, 'D', 'false'), (3, 'E', 'false'))
AS v("ranking","name", 'is_deleted') WHERE v.name = t.name

|   1     |  F     |   false    |
|   2     |  D     |   false    |
|   3     |  E     |   false    |

我有duplicate key value violates unique constraint "exists_const"错误。即使新行根本没有重复项。如何执行此类更新？

标签： postgresql

解决方案

The problem can be solved by Deferring the check at the end of the transaction, but you cannot do it with INDEX (as far as I know). The workaround I can give is to use EXCLUDE as described below:

CREATE TABLE my_table (                                                         
    ranking    INTEGER NOT NULL,
    name         TEXT  NOT NULL,
    is_deleted BOOLEAN NOT NULL,
    EXCLUDE USING BTREE (ranking WITH =) WHERE (not is_deleted) DEFERRABLE
);

Attention to the 'DEFERRABLE'

Here to populate initial data:

insert into my_table ( ranking,  name, is_deleted)
values 
  (1, 'A', true),
  (2, 'B', true),
  (1, 'C', true),
  (1, 'D', false),
  (2, 'E', false),
  (3, 'F', false);

Now if you execute :

UPDATE "my_table" AS t 
  SET "ranking" = v."ranking" 
FROM 
(VALUES(1, 'F', 'false'),(2, 'D', 'false'), (3, 'E', 'false'))
  AS v("ranking","name", "is_deleted")
WHERE v.name = t.name;

It should work.

And to test the uniqueness I tested it with :

insert into my_table ( ranking,  name, is_deleted)
  values 
  (3, 'G', false);

推荐阅读