DEFERRABLE INITIALLY DEFERRED on index contraint in postgresql

Question

I can add DEFERRABLE INITIALLY DEFERRED using the following query:

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

Now I have another column and I want to add DEFERRABLE INITIALLY DEFERRED. The constraint is on index so I am getting error when I do so.

Here is the index constraint:

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

I modified it to say,

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

Also tried,

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

So don't know how to apply the same on index.

I see the following official doc: 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.

And here:

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.

Is it something I need to use and if yes then how?

Answer 1

You cannot define an index as deferrable. Deferrable is an attribute of constraints, not indexes.

CREATE TABLE test_table
(
    test_col integer not null
);

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

However, you cannot use arbitrary expressions for unique constraints, only direct column references:

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

will report

ERROR:  syntax error at or near "("

because the parser only handles simple column references.

So you can't make this check deferrable.