471,310 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,310 software developers and data experts.

ALTER TABLE ADD UNIQUE .....


Hi ,

Looks like ADD UNIQUE( some_fuc( some_feild) ) is not supported with add constraint.
the only way is to add the constriant is using UNIQUE INDEX .

Is it a bug or intended behaviour?

Regds
Mallah.
in 7.3.4
~~~~~~~~~~~~

tradein_clients=# ALTER TABLE general.email_master ADD CONSTRAINT
email_master_uniq_lower_btrim_email UNIQUE( lower(email) );

ERROR: parser: parse error at or near "(" at character 99
tradein_clients=#

tradein_clients=#
tradein_clients=# CREATE UNIQUE INDEX email_master_uniq_lower_btrim_email on general.email_master( lower(email) );
ERROR: Cannot create unique index. Table contains non-unique values

Well the SQL has failed but it was parsed successfully.

tradein_clients=#

SAME PROBLEM IN PGSQL 7.4 RC2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

template1=# alter table t_a add constraint "a" UNIQUE (email);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "a" for table "t_a"
ALTER TABLE
template1=# alter table t_a add constraint "b" UNIQUE (lower(email));
ERROR: syntax error at or near "(" at character 54
template1=#


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
1 8990
Rajesh Kumar Mallah <ma****@trade-india.com> writes:
Looks like ADD UNIQUE( some_fuc( some_feild) ) is not supported with add constraint.
the only way is to add the constriant is using UNIQUE INDEX .
Is it a bug or intended behaviour?


It's intended. The syntax and behavior of unique constraints is defined
by the standard, and it doesn't include anything about functional
indexes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by me | last post: by
6 posts views Thread by Brian Basquille | last post: by
reply views Thread by anzenews | last post: by
7 posts views Thread by Serge Rielau | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.