BUG #5115: ADD UNIQUE table_constraint with expression

"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

=?UTF-8?B?VmxhZGltaXIgS29rb3ZpxIc=?= <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