Re: Use of !~* to keep a varchar column UNIQUE case-insensitive - Mailing list pgsql-novice

>        create unique index fooi on foo (lower(name));
>
> Suppressing whitespace is left as an exercise for the reader...

create unique index fooi on foo (lower(trim both
'\040\f\t\r\n\a\b\e\v' from name));

The above works well enough, and I can live with it, but it is not
comprehensive...it would be nice to use a regular expression....and do
something like:

create unique index fooi on foo (lower(substring(name FROM '^\s*(\S*)\s*$')));

but (at least in postgres 7.4.13) the use of substring doesn't
work...I tried it and no index violation is generated even when
inserting duplicates with no spaces to begin with.  (side-note: not
sure that regex would work anyways, as I'm not sure \S would exclude
all non-printables, just some maybe)...