Re: Case sensitivity - Mailing list pgsql-general

On Tue, Aug 09, 2005 at 09:35:25AM +0200, Frank Millman wrote:
> Frank Millman wrote:
>
> > Hi all
> >
> > Is there an LC_COLLATE setting, or any other method, which allows all
> > data in a database to be treated in a case-insensitive manner?
>
> I was hoping to stimulate some discussion on this topic, but it seems I will
> have to kick-start it myself and see if anyone responds.

I know there have been complaints from people that their database is
sorting case-insensetively when they wish it wouldn't. This generally
happens when the LC_COLLATE is set to en_US or some such. However, I
think that even the en_US locale just fiddles the sort order, but
doesn't make upper and lowercase equal.

> I mentioned in my original post that there are workarounds for these
> problems. However, it seems to me that in a typical system you would want to
> apply the workaround on every table, and therefore there is a case for
> saying that the database should handle it.

These "workarounds" are the recommended way of dealing with this issue.
Another option would be to create a new datatype 'itext' which works
like text except it compares case insensetively. PostgreSQL is flexible
like that. Here's something to get you started, see below for example.

http://svana.org/kleptog/pgsql/type_itext.sql

At the moment it uses SQL functions for the comparisons, for production
you'd probably want to have them in C for performance. Also, it's not
pg_dump safe (no operator class support).

BTW, I can't beleive I'm the first to do this, but hey. It's also my
first type with index support so it may be buggy. But it does work for
basic tests...

Have a nice day,
--- snip ---

test=# create table itest ( pkey serial primary key, val itext );
NOTICE:  CREATE TABLE will create implicit sequence "itest_pkey_seq"
for "serial" column "itest.pkey"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"itest_pkey" for table "itest"
CREATE TABLE
test=# insert into itest (val) values ('a');
INSERT 72279 1
test=# insert into itest (val) values ('A');
INSERT 72280 1
test=# insert into itest (val) values ('b');
INSERT 72281 1
test=# select * from itest where val = 'a';
 pkey | val
------+-----
    1 | a
    2 | A
(2 rows)

test=# create unique index itest_val on itest(val);
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.
test=# delete from itest where val = 'a';
DELETE 2
test=# create unique index itest_val on itest(val);
CREATE INDEX
test=# insert into itest (val) values ('a');
INSERT 72284 1
test=# insert into itest (val) values ('A');
ERROR:  duplicate key violates unique constraint "itest_val"
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.