Date: Sat, 01 Jul 2006 11:48:12 -0400
From: Daniel CAUNE <d.caune@free.fr>
Subject: Re: Constraint UNIQUE on a column not case sensitive
In-reply-to: <07BE168B-0F4D-417A-8EB4-E1D55F753E19@seespotcode.net>
To: 'Michael Glaesemann' <grzm@seespotcode.net>,
	'Jorge Godoy' <jgodoy@gmail.com>
Cc: pgsql-sql@postgresql.org
Message-id: <0J1Q006T7EKBA1C0@VL-MH-MR002.ip.videotron.ca>
MIME-version: 1.0
Content-type: text/plain; charset=UTF-8
Content-transfer-encoding: quoted-printable
Thread-index: AcadFtVLrTmR1qz5QBuJDcwUyfyrVwACiEqw

> -----Message d'origine-----
> De : Michael Glaesemann [mailto:grzm@seespotcode.net]
> Envoy=C3=A9 : samedi 1 juillet 2006 10:01
> =C3=80 : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Constraint UNIQUE on a column not case sensitive
>=20
>=20
> On Jul 1, 2006, at 22:47 , Daniel CAUNE wrote:
>=20
> > The idea is to have an index on that column, in a not case
> > sensitive form, i.e. lower(MyColumn).
>=20
> I think you're really close. Try
>=20
> CREATE UNIQUE INDEX ci_mycolumn_idx ON mytable(lower(mycolumn));
>=20
> Does that do what you're looking for?
>=20
> Michael Glaesemann
> grzm seespotcode net
>=20
>=20

Yes, indeed!  I'm stupid!  I was searching a constraint form while the =
creation of an UNIQUE index makes the job too!

The following form is not valid:

ALTER TABLE MyTable
  ADD CONSTRAINT UNQ_MyTable_MyColumn
      UNIQUE (lower(MyColumn));

But your form makes the expected job:

CREATE UNIQUE INDEX IDX_MyTable_MyColumn
  ON MyTable (lower(MyColumn));

Thanks,

--
Daniel CAUNE