X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2EEDAD1B511 for ; Tue, 10 Feb 2004 06:03:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05283-04 for ; Tue, 10 Feb 2004 02:03:14 -0400 (AST) Received: from builder.localdomain (unknown [61.94.124.219]) by svr1.postgresql.org (Postfix) with SMTP id 36DEED1D8B7 for ; Tue, 10 Feb 2004 02:03:08 -0400 (AST) Received: (qmail 4484 invoked by uid 0); 10 Feb 2004 06:02:58 -0000 Received: from NOINFO (HELO zara.6.isreserved.com) (192.168.0.113) by builder.localdomain (qpsmtpd/0.27-dev) with ESMTP; Tue, 10 Feb 2004 13:02:52 +0700 Message-ID: <4028740F.7000104@zara.6.isreserved.com> Date: Tue, 10 Feb 2004 13:02:55 +0700 From: David Garamond User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031208 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jason Tesser Cc: pgsql-general@postgresql.org Subject: Re: Case sensitivity References: <04875CB4331F0240A0AD66F970978651011377D5@paul> In-Reply-To: <04875CB4331F0240A0AD66F970978651011377D5@paul> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Check-By: builder.localdomain Spam: No ; 0.0 / 5.0 X-Virus-Checked: Checked X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/377 X-Sequence-Number: 57088 Jason Tesser wrote: > I am converting data from Access into Postgres and ran into an issue > with case sensitivity. Can I write queries in Access that will be case > insensitive without rewriting the queries. So I would like to know if > this be handled in Postgres or even if someone knows in Access. Thank > you. I believe no. You can index on LOWER(f) or UPPER(f) but that still requires a query rewriting to WHERE LOWER(f) = .... You can also write INSERT triggers to convert values to upper case, but that still requires query rewriting if you're comparing with mixed-case values. This has been discussed many times. See the archive for the various threads on this. To be safe, if you want your application to be portable, you should always assume case-sensitivity. AFAIK, only Access, SQL Server, and MySQL that are case-insensitive by default while others including Postgres are case-sensitive. Any other popular DB that are case-sensitive out there? -- dave