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
