Re: BUG #7525: Deferred unique index with predicate

On Sat, 2012-09-08 at 12:40 +0000, rikard(dot)pavelic(at)zg(dot)htnet(dot)hr wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7525
> Logged by: Rikard Pavelic
> Email address: rikard(dot)pavelic(at)zg(dot)htnet(dot)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