From: John McCawley <nospam@hardgeus.com>
To: Martijn van Oosterhout <kleptog@svana.org>
Cc: dfx@dfx.it
Cc: pgsql-general@postgresql.org
Subject: Re: Case sensitivity?
Date: Tue, 27 Dec 2005 09:56:06 -0600
Message-ID: <43B16416.3060401@hardgeus.com> (raw)
In-Reply-To: <20051227152919.GB32404@svana.org>
References: <000501c60b40$ea790510$fc00a8c0@dfx>
<43B15A4C.9040809@hardgeus.com>
<20051227152919.GB32404@svana.org>
>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: nospam@hardgeus.com, kleptog@svana.org, dfx@dfx.it
Subject: Re: Case sensitivity?
In-Reply-To: <43B16416.3060401@hardgeus.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox