Re: Constraint UNIQUE on a column not case sensitive

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

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