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

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?