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@[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