Thread: BUG #5115: ADD UNIQUE table_constraint with expression

The following bug has been logged online:

Bug reference:      5115
Logged by:          Vladimir Kokovic
Email address:      vladimir.kokovic@a-asoft.com
PostgreSQL version: PostgreSQL 8.4.
Operating system:   Linux vlD-kuci 2.6.28-15-generic #52-Ubuntu SMP Wed Sep
9 10:49:34 UTC 2009 i686 GNU/Linux
Description:        ADD UNIQUE table_constraint with expression
Details:

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]
*#

"Vladimir Kokovic" <vladimir.kokovic@a-asoft.com> 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.

Which is specified as UNIQUE ( column_name [, ... ] )

> But if expression is used

it's not supported syntax, per the above.

> Create index is OK:

as one would expect from the documentation:

( { column | ( expression ) } [ opclass ] [, ...] )

This is not a bug.

Maybe there's a feature request in there, but that would belong on
a different list.

-Kevin

"Vladimir Kokovic" <vladimir.kokovic@a-asoft.com> writes:
> 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...
>                                                              ^

Yeah, if you tried writing that in CREATE TABLE, it would complain too.

> Create index is OK:
> *# CREATE UNIQUE INDEX vk2 on
> adefault_finansije.gk_promene((substring(broj,10)),id);
> CREATE INDEX

This is not a CONSTRAINT clause in a CREATE TABLE.

            regards, tom lane

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

Vladimir Koković <vladimir.kokovic@a-asoft.com> writes:
> Real question is "Why we need two syntaxes for the same thing ?"

Because the SQL standard says so: UNIQUE-constraint syntax is limited
to simple column names.  We can't just extend that because it would
break the information_schema views, which are only capable of
representing unique/pk constraints on simple columns.

CREATE INDEX, being outside the scope of the spec, doesn't have to worry
about that.
        regards, tom lane