Create a deferrably-unique index

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general