Re: How to create case insensitive unique constraint - Mailing list pgsql-general

From John D. Burger
Subject Re: How to create case insensitive unique constraint
Date
Msg-id c57a8ecec259afdc4f4caafc5d0e92eb@mitre.org
Whole thread Raw
In response to How to create case insensitive unique constraint  ("Andrus" <eetasoft@online.ee>)
List pgsql-general
> 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




pgsql-general by date:

Previous
From: "Stas Oskin"
Date:
Subject: Postgres locks table schema?
Next
From: "Andrus"
Date:
Subject: Re: How to create case insensitive unique constraint