Message-ID: <43B16416.3060401@hardgeus.com>
Date: Tue, 27 Dec 2005 09:56:06 -0600
From: John McCawley <nospam@hardgeus.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716)
MIME-Version: 1.0
To: Martijn van Oosterhout <kleptog@svana.org>
Cc: dfx@dfx.it, pgsql-general@postgresql.org
Subject: Re: Case sensitivity?
References: <000501c60b40$ea790510$fc00a8c0@dfx>
	<43B15A4C.9040809@hardgeus.com> <20051227152919.GB32404@svana.org>
In-Reply-To: <20051227152919.GB32404@svana.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>Well, the rule is very simple, either always quote your identifiers, or
>never quote them. If you always quote them, then you always need to
>specify them in the same case. If you never quote them, then you always
>get case-insensetivity.
>  
>
My memory is a little hazy, as it's been a few years, but I believe my 
ultimate problem was that my code ended up being quite confusing.  I was 
able to use my mixed case, as you said, when I used quotes in the SQL 
query (which had to be escaped in PHP), however, the column names came 
in all lowercase, so I ended up having to switch between the two, 
leading to me making mistakes.  See this example (note my example is 
using my DB wrapper class):

<?

$obData = GetSQLHelper();
$sSQL = "SELECT \"FooBar\" FROM \"tbl_Foo\"";
$result = $obData->RunSQLReturnRS($sSQL);

$foobar = $result->fields["foobar"];
?>

I generally give my local variables the same name as their database 
counterparts, as shown by foobar in this example.  The problem is that 
foobar ends up as lowercase when it gets back to me.  If I get into the 
habit of typing "FooBar" mixed case in my SQL queries, it is only a 
matter of time before I do the following:

$Foobar = $result->fields["FooBar"];

PHP won't raise any error here, but rather just create a new "FooBar" 
variable in the fields map, which is a pretty insidious behavior. 

Rather than open myself up to inevitable bugs, I just abandoned mixed 
case.  I still end up with a similar problem when I misspell column 
names, but there isn't a whole lot I can do to avoid that other than 
type more carefully.

I know that this is more of a PHP issue than a Postgres issue, but these 
days a lot of people are using higher level languages for web 
development etc., and can run into similar issues, so I figured I'd 
mention this as an argument for avoiding mixed case in the database.

John