Date: Tue, 9 Aug 2005 11:57:48 +0200
From: Martijn van Oosterhout <kleptog@svana.org>
To: Frank Millman <frank@chagford.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Case sensitivity
Message-ID: <20050809095743.GB8244@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
Mail-Followup-To: Frank Millman <frank@chagford.com>,
	pgsql-general@postgresql.org
References: <42F72C06.8000000@archonet.com>
	<VPOP32.1.0e.20050809093619.625.7.1.a40c1f81@chagford.com>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="huq684BweRXVnRxX"
Content-Disposition: inline
In-Reply-To: <VPOP32.1.0e.20050809093619.625.7.1.a40c1f81@chagford.com>
User-Agent: Mutt/1.3.28i


--huq684BweRXVnRxX
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Aug 09, 2005 at 09:35:25AM +0200, Frank Millman wrote:
> Frank Millman wrote:
>=20
> > Hi all
> >=20
> > Is there an LC_COLLATE setting, or any other method, which allows all=
=20
> > data in a database to be treated in a case-insensitive manner?
>=20
> I was hoping to stimulate some discussion on this topic, but it seems I w=
ill
> 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.=20

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=3D# 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=3D# insert into itest (val) values ('a');
INSERT 72279 1
test=3D# insert into itest (val) values ('A');
INSERT 72280 1
test=3D# insert into itest (val) values ('b');
INSERT 72281 1
test=3D# select * from itest where val =3D 'a';
 pkey | val=20
------+-----
    1 | a
    2 | A
(2 rows)

test=3D# create unique index itest_val on itest(val);
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.
test=3D# delete from itest where val =3D 'a';
DELETE 2
test=3D# create unique index itest_val on itest(val);
CREATE INDEX
test=3D# insert into itest (val) values ('a');
INSERT 72284 1
test=3D# insert into itest (val) values ('A');
ERROR:  duplicate key violates unique constraint "itest_val"
--=20
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.

--huq684BweRXVnRxX
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFC+H4WIB7bNG8LQkwRAmRcAJ4q3Sra4ggzncOhkdjlOy5dNeI7CACfWOwI
tDoqV2A6SjRPTXizzoxV0FU=
=bYtD
-----END PGP SIGNATURE-----

--huq684BweRXVnRxX--