X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D898D52A11 for ; Tue, 9 Aug 2005 08:04:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02533-02 for ; Tue, 9 Aug 2005 11:04:24 +0000 (GMT) Received: from svana.org (svana.org [203.20.62.76]) by svr1.postgresql.org (Postfix) with ESMTP id 7940D52A25 for ; Tue, 9 Aug 2005 08:04:23 -0300 (ADT) Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian)) id 1E2RuB-00031A-00; Tue, 09 Aug 2005 21:04:23 +1000 Date: Tue, 9 Aug 2005 13:04:23 +0200 From: Martijn van Oosterhout To: Frank Millman , pgsql-general@postgresql.org Subject: Re: Case sensitivity Message-ID: <20050809110418.GC8244@svana.org> Reply-To: Martijn van Oosterhout Mail-Followup-To: Frank Millman , pgsql-general@postgresql.org References: <42F72C06.8000000@archonet.com> <20050809095743.GB8244@svana.org> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="XOIedfhf+7KOe/yw" Content-Disposition: inline In-Reply-To: <20050809095743.GB8244@svana.org> User-Agent: Mutt/1.3.28i X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6 X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522 48A7 6394 F08A 0DC6 7BE6 X-PGP-Key-URL: X-Warning: Normally my messages are signed, but not on the ozTiVo list due to legacy application support X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/414 X-Sequence-Number: 81822 --XOIedfhf+7KOe/yw Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Tue, Aug 09, 2005 at 11:57:48AM +0200, Martijn van Oosterhout wrote: > 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 >=20 > http://svana.org/kleptog/pgsql/type_itext.sql >=20 > 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). Oops, turns out there *is* a CREATE OPERATOR CLASS but my version of psql doesn't have it in command completion. And when you use that it *is* saved by pg_dump. Problem solved. I've tested various things, DISTINCT works, ORDER BY works, GROUP BY works. Neat huh? Have a nice day, --=20 Martijn van Oosterhout 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. --XOIedfhf+7KOe/yw 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+I2xIB7bNG8LQkwRAuHTAJ96X3+xLTFWHYA6KI0i6BLPixiHPwCfa7py 1kFjEHoql5JMrdHrSpZsBcY= =eYyi -----END PGP SIGNATURE----- --XOIedfhf+7KOe/yw--