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@xxxxxxxxx> 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.
Attachment:
pgphcM5wiPGEl.pgp
Description: PGP signature
