Thread: Create a deferrably-unique index

I'm trying to create a unique index where the unique constraint is
`deferrable initially immediate`. But I don't see any way to do this
in the syntax of the `create index` command. It looks like the only
way to do it is via `alter table foo add unique`. Is that right, or
can I do it as part of `create index`?

If I have to use `alter table add unique`, is there any way I can make
sure the implicitly-created index also has a `where` clause? Or is it
impossible to create an index that is unique + deferrable + partial?

Thank you,
Paul


--
_________________________________
Pulchritudo splendor veritatis.

Paul Jungwirth <pj@illuminatedcomputing.com> writes:
> I'm trying to create a unique index where the unique constraint is
> `deferrable initially immediate`. But I don't see any way to do this
> in the syntax of the `create index` command. It looks like the only
> way to do it is via `alter table foo add unique`. Is that right, or
> can I do it as part of `create index`?

Deferrability is a property of a constraint, not an index, so you can
only specify it for indexes that are associated with constraints.
Yes, that limits the kinds of indexes that can be used ...

            regards, tom lane

> Deferrability is a property of a constraint, not an index

Yes, but creating a unique constraint implicitly creates an index, and
creating a unique index implicitly creates a constraint. So I'm
wondering whether I can create a pair where the index is partial and
the constraint is deferrable. It sounds like the answer is no? Is
there a workaround where I first create one and then alter the other
one?

Thanks,
Paul

--
_________________________________
Pulchritudo splendor veritatis.

Paul Jungwirth <pj@illuminatedcomputing.com> writes:
>> Deferrability is a property of a constraint, not an index

> Yes, but creating a unique constraint implicitly creates an index, and
> creating a unique index implicitly creates a constraint.

No, it doesn't.  I'm using "constraint" in a technical sense here,
that is something that is recorded as a constraint in the system
catalogs.

regression=# select count(*) from pg_constraint;
 count
-------
    34
(1 row)

regression=# create table foo(f1 int unique);
CREATE TABLE
regression=# select count(*) from pg_constraint;
 count
-------
    35
(1 row)

regression=# create table bar(f1 int);
CREATE TABLE
regression=# create unique index on bar(f1);
CREATE INDEX
regression=# select count(*) from pg_constraint;
 count
-------
    35
(1 row)

The index on bar didn't create a constraint.

            regards, tom lane