Date: Fri, 27 Oct 2006 22:38:52 -0400
From: Joe <dev@freedomcircle.net>
Subject: Re: Case Preservation disregarding case sensitivity?
In-reply-to: <200610271623.37525.beauh@bluefrogmobile.com>
To: beau hargis <beauh@bluefrogmobile.com>
Cc: pgsql-sql@postgresql.org
Message-id: <1162003132.687.8.camel@pampa>
Organization: Freedom Circle, LLC
MIME-version: 1.0
Content-type: text/plain
Content-transfer-encoding: 7bit
References: <200610271623.37525.beauh@bluefrogmobile.com>

Hi Beau,

On Fri, 2006-10-27 at 16:23 -0700, beau hargis wrote:
> I am hoping that there is an easy way to obtain case-preservation with 
> case-insensitivity, or at the very least, case-preservation and complete 
> case-sensitivity, or case-preservation and a consistant case-conversion 
> strategy. 
> 
> The case of the column names need to be preserved because that is the way the 
> schema is designed and most importantly (VERY, VERY IMPORTANT), column names 
> are used in apps as hash values, or as named references which are case 
> sensitive and as such need to be delivered to the client in exactly in the 
> manner specified at the time of table creation.
> 
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give me 
> case-preservation with EITHER case-sensitivity OR case-insensitivity, but not 
> both as I am seeing.
> 
> Thanks in advance. I am hoping to find a solution to this so I can actually 
> convert one of our databases to use Postgres. And I can say that little 
> issues like this are precisely why Postgres was never used in this 
> organization before, even though several of the other database developers 
> like the features, stability and performance of Postgres.

I went through the same issue in my conversion from MySQL to Postgres
and (since I had a small application) I ended up changing up all my
tables and columns "UserProfile" to user_profile.

I'm afraid however, that it's MySQL that is the odd man out.  I haven't
researched this completely but I believe PG follows either the FIPS-127
or SQL-92 standard with respect to what are called "delimited
identifiers".  Basically, this says if you want case sensitivity in
identifier names, you have to use double quotes wherever you refer to
the identifier.  Without the double quotes, the SQL implementor can
either use UPPERCASE (as I believe Oracle and DB2 do) or lowercase (as
PG does) when it displays those identifiers.

Joe