I want to disable dupplicate customer names in a database regardless to
case.
I tried
CREATE TABLE customer ( id SERIAL, name CHARACTER(70));
ALTER TABLE customer
ADD constraint customer_name_unique UNIQUE (UPPER(name));
but this is not allowed in Postgres
As Csaba suggested, a unique functional index does the trick - here's
how I do it in something I'm working on right now:
CREATE UNIQUE INDEX gazPlaceNames_lower_PlaceName2_Index on
gazPlaceNames (lower(placeName));
You could use upper() similarly - lower() is better for Unicode data,
like mine. Now, If I try to add an alternate casing for an existing
name, I get slapped:
> select * from gazPlaceNames where lower(placeName) like lower('New
York');
placenameid | placename | lang | script
-------------+-----------+------+--------
291642 | New York | |
(1 row)
> insert into gazPlaceNames (placename) values ('NeW yOrK');
ERROR: duplicate key violates unique constraint
"gazplacenames_lower_placename2_"
As a bonus, Postgres will use the index for selects involving
lower(placename), like the one above.
- John Burger
MITRE
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
