On Sat, 2012-09-08 at 12:40 +0000, rikard.pavelic@zg.htnet.hr wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7525
> Logged by: Rikard Pavelic
> Email address: rikard.pavelic@zg.htnet.hr
> PostgreSQL version: 9.1.2
> Operating system: Windows 7
> Description:
>
> Is there a way in Postgres to create a unique constraint with predicate or
> an unique index which is deferred?
>
> Depesz said not yet.
> http://www.depesz.com/2009/08/11/waiting-for-8-5-deferrable-uniqueness/
Both features exist.
http://www.postgresql.org/docs/9.2/static/sql-createtable.html
CREATE TABLE xyz(i int, j int, unique (i) deferrable);
To create a unique index with a predicate, do:
CREATE UNIQUE INDEX xyz_uniq_idx ON xyz(j) WHERE j > 10;
However, it can't both be deferrable and have a predicate. In order to
do that, you need to use an exclusion constraint:
http://www.postgresql.org/docs/9.2/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
To make it equivalent to UNIQUE, set all operators to "=", e.g.:
CREATE TABLE xyz(i int, exclude (i WITH =) where (i > 10) deferrable);
Regards,
Jeff Davis
