From: Martijn van Oosterhout <kleptog@svana.org>
To: Frank Millman <frank@chagford.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Case sensitivity
Date: Tue, 9 Aug 2005 11:57:48 +0200
Message-ID: <20050809095743.GB8244@svana.org> (raw)
In-Reply-To: <VPOP32.1.0e.20050809093619.625.7.1.a40c1f81@chagford.com>
References: <42F72C06.8000000@archonet.com>
<VPOP32.1.0e.20050809093619.625.7.1.a40c1f81@chagford.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: kleptog@svana.org, frank@chagford.com
Subject: Re: Case sensitivity
In-Reply-To: <20050809095743.GB8244@svana.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox