[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create a deferrably-unique index
Paul Jungwirth p...@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
Re: [GENERAL] Create a deferrably-unique index
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
Re: [GENERAL] Create a deferrably-unique index
Paul Jungwirth p...@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
