Re: How to create case insensitive unique constraint

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

Any idea ?

Andrus.

I guess you can create a unique functional index with upper as the
function. Try to look up the docs for CREATE INDEX.

HTH,
Csaba.

On Wed, 2005-09-21 at 18:15, Andrus wrote:
> 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
>
> Any idea ?
>
> Andrus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

> 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

> You could use upper() similarly - lower() is better for Unicode data, like
> mine.

John,

thank you. Excellent.
I have database encoding UNICODE. Why lower() is better than upper()?

Andrus.