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
