Thread: Feature request - function-based deferrable uniques.

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? 

Regards, 
Dmitry

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

Dmitry Fefelov wrote:

> 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? 

Sure -- use CREATE UNIQUE INDEX.

alvherre=# create function singleton(int) returns int immutable language sql as $$ select 1 $$;
CREATE FUNCTION
alvherre=# create table singleton (a int);
CREATE TABLE
alvherre=# create unique index only_one on singleton (singleton(a));
CREATE INDEX
alvherre=# insert into singleton values (3);
INSERT 0 1
alvherre=# insert into singleton values (6);
ERROR:  llave duplicada viola restricción de unicidad «only_one»


The reason it's not supported in table_constraint, IIUC, is that the
standard doesn't allow it, and that syntax is standards-mandated.
CREATE INDEX, however, is not.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> The reason it's not supported in table_constraint, IIUC, is that the
> standard doesn't allow it, and that syntax is standards-mandated.

The real problem is not so much extending the syntax as that there'd be
no way to represent the constraint in information_schema.  Exclusion
constraints are outside the standard already, so omitting them from the
information_schema views is less bad than missing unique constraints
would be.
        regards, tom lane

> Sure -- use CREATE UNIQUE INDEX.
>
> alvherre=# create function singleton(int) returns int immutable language sql
as $$ select 1 $$;
> CREATE FUNCTION
> alvherre=# create table singleton (a int);
> CREATE TABLE
> alvherre=# create unique index only_one on singleton (singleton(a));
> CREATE INDEX
> alvherre=# insert into singleton values (3);
> INSERT 0 1
> alvherre=# insert into singleton values (6);
> ERROR:  llave duplicada viola restricción de unicidad «only_one»

And it's possible to make this check deferrable? Uniqueness will be validated
on commit or SET ALL CONSTRAINTS IMMEDIATE command?

>
>
> The reason it's not supported in table_constraint, IIUC, is that the
> standard doesn't allow it, and that syntax is standards-mandated.
> CREATE INDEX, however, is not.