java - DEFERRABLE INITIALLY DEFERRED 对 postgresql 中的索引约束

Question

我可以使用以下查询添加 DEFERRABLE INITIALLY DEFERRED:

ALTER TABLE user DROP CONSTRAINT unq_user_address, add CONSTRAINT unq_user_address UNIQUE(user_address) deferrable INITIALLY DEFERRED;

现在我有另一列，我想添加 DEFERRABLE INITIALLY DEFERRED。约束在索引上，所以我在这样做时遇到错误。

这是索引约束:

CREATE UNIQUE INDEX unq_account
  ON user 
  USING btree 
  (lower(account::text) COLLATE pg_catalog."default");

我修改它说，

CREATE UNIQUE INDEX unq_account
      ON user 
      USING btree 
      (lower(account::text) COLLATE pg_catalog."default") deferrable INITIALLY DEFERRED;

也试过了，

CREATE UNIQUE INDEX unq_account
      ON user 
      USING btree 
      (lower(account::text) COLLATE pg_catalog."default") UNIQUE_CHECK_PARTIAL;

所以不知道如何在索引上应用相同的内容。

我看到以下官方文档: https://www.postgresql.org/docs/9.6/static/index-unique-checks.html

UNIQUE_CHECK_PARTIAL indicates that the unique constraint is deferrable. PostgreSQL will use this mode to insert each row's index entry. The access method must allow duplicate entries into the index, and report any potential duplicates by returning FALSE from aminsert. For each row for which FALSE is returned, a deferred recheck will be scheduled.

在这里:

UNIQUE_CHECK_EXISTING indicates that this is a deferred recheck of a row that was reported as a potential uniqueness violation. Although this is implemented by calling aminsert, the access method must not insert a new index entry in this case. The index entry is already present. Rather, the access method must check to see if there is another live index entry. If so, and if the target row is also still live, report error.

它是我需要使用的东西吗？如果是，那么如何使用？

Answer 1

您不能将索引定义为可延迟的。可延迟是约束的属性，而不是索引。

CREATE TABLE test_table
(
    test_col integer not null
);

ALTER TABLE test_table
ADD constraint test_col_unique unique (test_col) deferrable initially deferred;

但是，您不能对唯一约束使用任意表达式，只能直接引用列:

ALTER TABLE test_table
ADD CONSTRAINT test_col_abs_unique UNIQUE (abs(test_col));

会报告

ERROR:  syntax error at or near "("

因为解析器只处理简单的列引用。

所以你不能让这个支票可以延期。