Re: BUG #5115: ADD UNIQUE table_constraint with expression

Vladimir Kokovic wrote:
> For ALTER TABLE ADD CONSTRAINT documentation says:
> ADD table_constraint
> This form adds a new constraint to a table using the same syntax as
> CREATE TABLE.
>
> But if expression is used in the constraint definition
> server says:
> # ALTER TABLE asoft_finansije.gk_promene ADD CONSTRAINT vk2
> UNIQUE((substring(broj,10)),id)
> asoft-# ;
> ERROR: 42601: syntax error at or near "("
> LINE 1: ...ft_finansije.gk_promene ADD CONSTRAINT vk2 UNIQUE((substring...
> ^
> LOCATION: base_yyerror, scan.l:907
>
>
> Create index is OK:
> *# CREATE UNIQUE INDEX vk2 on
> adefault_finansije.gk_promene((substring(broj,10)),id);
> CREATE INDEX
> (vlada(at)[local]:5432) 16:51:39 [asoft]
> *#

The docs says "This form adds a new constraint to a table using the same
syntax as *CREATE TABLE*", not CREATE INDEX. More precisely,
table_constraint is referring to the table_constraint rule in the
documentation of CREATE TABLE:

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 ]

That doesn't allow using an expression with UNIQUE. There is currently
no way to create a unique constraint on an expression. However as you
noticed, you can create a unique index on one with the same effect.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com