Re: Feature request - function-based deferrable uniques.

On 31 March 2010 06:58, Dmitry Fefelov <fozzy@ac-sw.com> wrote:
> For now Postgres able to create deferrable uniques with following syntax:
>
> ...
> and table_constraint is:
>
> [ CONSTRAINT constraint_name ]
> { UNIQUE ( column_name [, ... ] ) index_parameters |
>  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
>  CHECK ( expression ) |
>  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [,
> ... ] ) ]
>    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON
> UPDATE action ] }
> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
>
> So, deferrable uniques now can be based on column/columns list only. It will
> be very useful if there will be possibility to specify functions in this list.
> Is it possible?
>

It's not currently possible using the unique constraint syntax, but I
think that it would be nice to extend this syntax to support this. I
don't think the SQL spec says anything about this, but I think it
would be a useful extension.

Note, however, that there is a workaround to achieve this, which is to
use exclusion constraints. For example:

create table foo(a text, constraint c exclude ((lower(a)) with =)
deferrable initially deferred);

Regards,
Dean