Thread: JDBC bug?

JDBC bug?

From
Nate Gelbard
Date:
Hi,

I've found an annoyance with the postgresql JDBC driver (from the 7.1.3
release).

Pardon if this has been asked before, but the web archives are offline...

In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines
that call toLowerCase() on the table string names. This causes 'table
unknown' erros when accessing tables with mixed case in the name. This
popped out at me when trying to use the XML-DBMS package and also
DbVisualizer.

Also, when using the pgsql shell, \d createdBy returns table unknown,
but \d "createBy" returns the right info.

So does postgres not support mixed case in table names, or the backend
does but the clients dont, or what?

nate


Re: JDBC bug?

From
Bruce Momjian
Date:
> Hi,
>
> I've found an annoyance with the postgresql JDBC driver (from the 7.1.3
> release).
>
> Pardon if this has been asked before, but the web archives are offline...
>
> In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines
> that call toLowerCase() on the table string names. This causes 'table
> unknown' erros when accessing tables with mixed case in the name. This
> popped out at me when trying to use the XML-DBMS package and also
> DbVisualizer.
>
> Also, when using the pgsql shell, \d createdBy returns table unknown,
> but \d "createBy" returns the right info.
>
> So does postgres not support mixed case in table names, or the backend
> does but the clients dont, or what?

Well, we support mixed case identifiers, but they need to be
double-quoted, as you saw.  The \d behavior looks correct.  The jdbc
issue looks more complicated because we are forcing lowercase in the
jdbc code.  Perhaps some jdbc folks can comment on that.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: JDBC bug?

From
Barry Lind
Date:
Nate,

In general it seems that doing the toLowerCase is the correct behavior.
  SQL is case insensitive, and in postgres this is acheived by
lowercasing all names (tablenames, columnnames, functionnames, etc).  So
in general there is no difference between  createdBy, createdby,
CreatedBy, CREATEDBY from a SQL perspective.  However SQL also allows
mixed case identifiers if they are quoted.  Thus "createdBy" will only
match "createdBy" and will not match createdBy without quotes (because
the latter if folded to lower case).

I am not sure exactly what your problem is.  Can you provide some more
details given the explanation presented above?

thanks,
--Barry


Nate Gelbard wrote:

> Hi,
>
> I've found an annoyance with the postgresql JDBC driver (from the 7.1.3
> release).
>
> Pardon if this has been asked before, but the web archives are offline...
>
> In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines
> that call toLowerCase() on the table string names. This causes 'table
> unknown' erros when accessing tables with mixed case in the name. This
> popped out at me when trying to use the XML-DBMS package and also
> DbVisualizer.
>
> Also, when using the pgsql shell, \d createdBy returns table unknown,
> but \d "createBy" returns the right info.
>
> So does postgres not support mixed case in table names, or the backend
> does but the clients dont, or what?
>
> nate
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>



Re: JDBC bug?

From
"Colin Freas"
Date:
>> Hi,
>>
>> I've found an annoyance with the postgresql JDBC driver (from the 7.1.3
>> release).
>>
>> Pardon if this has been asked before, but the web archives are offline...
>>
>> In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines
>> that call toLowerCase() on the table string names. This causes 'table
>> unknown' erros when accessing tables with mixed case in the name. This
>> popped out at me when trying to use the XML-DBMS package and also
>> DbVisualizer.
>>
>> So does postgres not support mixed case in table names, or the backend
>> does but the clients dont, or what?
>
>Well, we support mixed case identifiers, but they need to be
>double-quoted, as you saw.  The \d behavior looks correct.  The jdbc
>issue looks more complicated because we are forcing lowercase in the
>jdbc code.  Perhaps some jdbc folks can comment on that.

I think Nate's being kind calling this an annoyance.  I may have missed
something in the docs, but I just spent a few frustrating hours confronting
a similar issue with tables of mixed case.  Everything was working correctly
with my servlet, except when I referenced a column with a name like
questionID in a where clause.  Note the casing.  I had no idea this is what
was wrong until, other ideas exhausted, I recreated my query text with
pgadmin's query wizard and it put quotes around everything in the SQL it
generated.

Is there a pqsql-jdbc log somewhere that this might have shown up?  I was so
sure it was my java code (which in the end it was, sort of :) I never
bothered to check any Postgres logs.  Would it have shown up somewhere
there?  Either way, personally, I think this isn't the best behavior.  I
don't want to get into a casing flame war, but, I would've expected an
exception if it couldn't figure out what was happening with the where
clause, something at least.

Colin Freas


Re: JDBC bug?

From
"Ross J. Reedstrom"
Date:
Note that this is SQL92 standards compliant behavior: identifiers
are supposed to be case-insensitive, unless quoted.

Ross

On Mon, Nov 26, 2001 at 01:30:24PM -0500, Bruce Momjian wrote:
> > Hi,
> >
> > I've found an annoyance with the postgresql JDBC driver (from the 7.1.3
> > release).
> >
> > Pardon if this has been asked before, but the web archives are offline...
> >
> > In org/postgresql/jdbc2/DatabaseMetaData.java, there are several lines
> > that call toLowerCase() on the table string names. This causes 'table
> > unknown' erros when accessing tables with mixed case in the name. This
> > popped out at me when trying to use the XML-DBMS package and also
> > DbVisualizer.
> >
> > Also, when using the pgsql shell, \d createdBy returns table unknown,
> > but \d "createBy" returns the right info.
> >
> > So does postgres not support mixed case in table names, or the backend
> > does but the clients dont, or what?
>
> Well, we support mixed case identifiers, but they need to be
> double-quoted, as you saw.  The \d behavior looks correct.  The jdbc
> issue looks more complicated because we are forcing lowercase in the
> jdbc code.  Perhaps some jdbc folks can comment on that.
>

Re: JDBC bug?

From
Bruce Momjian
Date:
> >> So does postgres not support mixed case in table names, or the backend
> >> does but the clients dont, or what?
> >
> >Well, we support mixed case identifiers, but they need to be
> >double-quoted, as you saw.  The \d behavior looks correct.  The jdbc
> >issue looks more complicated because we are forcing lowercase in the
> >jdbc code.  Perhaps some jdbc folks can comment on that.
>
> I think Nate's being kind calling this an annoyance.  I may have missed
> something in the docs, but I just spent a few frustrating hours confronting
> a similar issue with tables of mixed case.  Everything was working correctly
> with my servlet, except when I referenced a column with a name like
> questionID in a where clause.  Note the casing.  I had no idea this is what
> was wrong until, other ideas exhausted, I recreated my query text with
> pgadmin's query wizard and it put quotes around everything in the SQL it
> generated.
>
> Is there a pqsql-jdbc log somewhere that this might have shown up?  I was so
> sure it was my java code (which in the end it was, sort of :) I never
> bothered to check any Postgres logs.  Would it have shown up somewhere
> there?  Either way, personally, I think this isn't the best behavior.  I
> don't want to get into a casing flame war, but, I would've expected an
> exception if it couldn't figure out what was happening with the where
> clause, something at least.

Yes, I agree, it clearly looks like a jdbc bug.  If you have created
mixed-case identifiers, the jdbc toLower call make accessing it
impossible.  If we can trust the case of the indentifier supplied, we
can just put double-quotes around it to preserve the case as passed to
the backend.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: JDBC bug?

From
Bruce Momjian
Date:
> I haven't seen any evidence in this thread of a JDBC bug.  The original
> posting refered to calls in the DatabaseMetaData classes doing
> toLowerCase().  This is appropriate because the DatabaseMetaData class
> is querying the pg_* tables to get information about database objects,
> thus since the backend stores the identifiers in lower case, therefore
> the jdbc code needs to do a toLowerCase().  The followup below is more

But the backend doesn't store identifiers in lowercase if they have
created mixed-case identifiers with double-quotes, right?  I am
confused.


> of a complaint on how SQL is case insensitive and if you have created
> your objects with quoted mixed case identifiers you need to access them
> via quoted mixed case identifiers.  I haven't seen any evidence that
> basic SQL operations (select, insert, update, delete) have a bug in them.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: JDBC bug?

From
Barry Lind
Date:
I haven't seen any evidence in this thread of a JDBC bug.  The original
posting refered to calls in the DatabaseMetaData classes doing
toLowerCase().  This is appropriate because the DatabaseMetaData class
is querying the pg_* tables to get information about database objects,
thus since the backend stores the identifiers in lower case, therefore
the jdbc code needs to do a toLowerCase().  The followup below is more
of a complaint on how SQL is case insensitive and if you have created
your objects with quoted mixed case identifiers you need to access them
via quoted mixed case identifiers.  I haven't seen any evidence that
basic SQL operations (select, insert, update, delete) have a bug in them.

thanks,
--Barry

Bruce Momjian wrote:

>>>>So does postgres not support mixed case in table names, or the backend
>>>>does but the clients dont, or what?
>>>>
>>>Well, we support mixed case identifiers, but they need to be
>>>double-quoted, as you saw.  The \d behavior looks correct.  The jdbc
>>>issue looks more complicated because we are forcing lowercase in the
>>>jdbc code.  Perhaps some jdbc folks can comment on that.
>>>
>>I think Nate's being kind calling this an annoyance.  I may have missed
>>something in the docs, but I just spent a few frustrating hours confronting
>>a similar issue with tables of mixed case.  Everything was working correctly
>>with my servlet, except when I referenced a column with a name like
>>questionID in a where clause.  Note the casing.  I had no idea this is what
>>was wrong until, other ideas exhausted, I recreated my query text with
>>pgadmin's query wizard and it put quotes around everything in the SQL it
>>generated.
>>
>>Is there a pqsql-jdbc log somewhere that this might have shown up?  I was so
>>sure it was my java code (which in the end it was, sort of :) I never
>>bothered to check any Postgres logs.  Would it have shown up somewhere
>>there?  Either way, personally, I think this isn't the best behavior.  I
>>don't want to get into a casing flame war, but, I would've expected an
>>exception if it couldn't figure out what was happening with the where
>>clause, something at least.
>>
>
> Yes, I agree, it clearly looks like a jdbc bug.  If you have created
> mixed-case identifiers, the jdbc toLower call make accessing it
> impossible.  If we can trust the case of the indentifier supplied, we
> can just put double-quotes around it to preserve the case as passed to
> the backend.
>
>



Re: JDBC bug?

From
"Colin Freas"
Date:
> via quoted mixed case identifiers.  I haven't seen any evidence that
> basic SQL operations (select, insert, update, delete) have a bug in them.

I'd peg a where clause as a basic SQL operation, and be it bug, feature,
annoyance, whatever, it wasn't functioning because of the case of a column
name.

This worked:
1.  rs = stmt.executeQuery("select * from response");

But I wanted to do this, which didn't work:
2.  rs = stmt.executeQuery("select * from response where questionID=16");

This is how I got it to work:
3.  rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
\"response\".\"respondentID\", \"response\".\"answer\", \"response\".\"ID\"
FROM \"response\" WHERE "\response\".\"questionID\"=16");

Sorry, does anyone think the third is better than the second?


Re: JDBC bug?

From
Barry Lind
Date:

Bruce Momjian wrote:

>>I haven't seen any evidence in this thread of a JDBC bug.  The original
>>posting refered to calls in the DatabaseMetaData classes doing
>>toLowerCase().  This is appropriate because the DatabaseMetaData class
>>is querying the pg_* tables to get information about database objects,
>>thus since the backend stores the identifiers in lower case, therefore
>>the jdbc code needs to do a toLowerCase().  The followup below is more
>>
>
> But the backend doesn't store identifiers in lowercase if they have
> created mixed-case identifiers with double-quotes, right?  I am
> confused.
>


Correct.  But if you ask the API to get information about table myTable,
you should get an answer back for a table created as (create table
myTable (foo text)).  The only way to do this is to lowercase the input
to match what the database is storing (mytable in this case).  I agree
that if the table is created with a mixed case identifier (create table
"myTable" (foo text)) then there isn't a need to lower case in the
driver.  Therefore the fact that there are a bunch of calls to
toLowerCase() in the jdbc code for DatabaseMetaData doesn't mean that
there is a bug here as they are entirely appropriate under some
circumstances.  If someone produces a test case that demonstrates a bug
I will be glad to look into it.  But up to this point there has only
been a lot of speculation on code that may be entirely correct.  Test
cases demonstrating a real problem would be much apprecieated in this case.

thanks,
--Barry


>
>
>>of a complaint on how SQL is case insensitive and if you have created
>>your objects with quoted mixed case identifiers you need to access them
>>via quoted mixed case identifiers.  I haven't seen any evidence that
>>basic SQL operations (select, insert, update, delete) have a bug in them.
>>
>



Re: JDBC bug?

From
Barry Lind
Date:
Colin,

Whether it is better or not isn't the issue.  That is how the SQL
standard says it is supposed to work.  If you create the objects with
quoted mixed case identifiers you must use quoted mixed case identifiers
to access the objects.  A better solution to your problem would be to
create the tables without using quoted indentifiers and then your
original query would work.

thanks,
--Barry

Colin Freas wrote:

>>via quoted mixed case identifiers.  I haven't seen any evidence that
>>basic SQL operations (select, insert, update, delete) have a bug in them.
>>
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1.  rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2.  rs = stmt.executeQuery("select * from response where questionID=16");
>
> This is how I got it to work:
> 3.  rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
> \"response\".\"respondentID\", \"response\".\"answer\", \"response\".\"ID\"
> FROM \"response\" WHERE "\response\".\"questionID\"=16");
>
> Sorry, does anyone think the third is better than the second?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>



Re: JDBC bug?

From
Bruce Momjian
Date:
> > via quoted mixed case identifiers.  I haven't seen any evidence that
> > basic SQL operations (select, insert, update, delete) have a bug in them.
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1.  rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2.  rs = stmt.executeQuery("select * from response where questionID=16");

Only this should have worked:

  rs = stmt.executeQuery("select * from response where \"questionID\"=16");

The expansion of the "*" should protect any mixed case items by default.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: JDBC bug?

From
Bruce Momjian
Date:
> Correct.  But if you ask the API to get information about table myTable,
> you should get an answer back for a table created as (create table
> myTable (foo text)).  The only way to do this is to lowercase the input
> to match what the database is storing (mytable in this case).  I agree
> that if the table is created with a mixed case identifier (create table
> "myTable" (foo text)) then there isn't a need to lower case in the
> driver.  Therefore the fact that there are a bunch of calls to
> toLowerCase() in the jdbc code for DatabaseMetaData doesn't mean that
> there is a bug here as they are entirely appropriate under some
> circumstances.  If someone produces a test case that demonstrates a bug
> I will be glad to look into it.  But up to this point there has only
> been a lot of speculation on code that may be entirely correct.  Test
> cases demonstrating a real problem would be much apprecieated in this case.
>

OK, I see what you mean.  If you pass in myTable, you want to look for
mytable in pg_class.  However, what if they pass in "myTable", with the
quotes?  Do we skip the lowercase step?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: JDBC bug?

From
Nate Gelbard
Date:
 > of a complaint on how SQL is case insensitive and if you have created
 > your objects with quoted mixed case identifiers you need to accessthem
 > via quoted mixed case identifiers.  I haven't seen any evidence that

I am mistaken. This is actually a bug in the applications that are
building SQL statements without quotes around tablenames of mixedcase.

nate





Re: JDBC bug?

From
"Colin Freas"
Date:
Barry,
I see what you're saying, and you're right.  But as for not using mixed case
identifiers as being the better solution, I have to disagree.  It's not like
I'm asking to put spaces in my column names and have the parser read my
mind.  I just think mixed case identifiers are easier on the eyes.
Although, the \"'s obviously aren't.  And is it \"s or \"'s?

I also just wanted to make sure I understand what's going on.  Postgres
stores the identifiers in a case insensitve manner by default, but if you
mix case when creating the tables or what have you, it stores them as case
sensitive.  Then, the JDBC driver would take 'select * from z where XyZZy =
"Fool!"' and send it as '...xyzzy...', which would...  what?  Is that going
to throw an error, or just not return anything?  pgadmin II says attribute
undefined or somesuch, and that is what quickly led me to the solution.

I'm just saying that it's frustrating because in one of my earlier checks to
make sure my SQL was ok, I used Access 2000 with some linked tables to my
Postgres db.  When I ran 'select * from response where questionID=16'
through that, it worked without a hitch (actually, the 16 needed to be
'16'), so I figured the problem was somewhere else.  Plus it was such simple
SQL, I mean, what could be wrong with it?  I'd be the first to admit I
don't...  well, I didn't... know the nuances of the SQL9X casing rules.  My
issue, as stated in my initial note, was more that nobody (jdbc, postgres,
tomcat) told me that my where clause was, essentially, bogus.  I mean, what
am I catching that SQLException for?

And Bruce wrote...
>rs = stmt.executeQuery("select * from response where \"questionID\"=16");

That works.

Doh!
Colin

------

Barry wrote:

Colin,

Whether it is better or not isn't the issue.  That is how the SQL
standard says it is supposed to work.  If you create the objects with
quoted mixed case identifiers you must use quoted mixed case identifiers
to access the objects.  A better solution to your problem would be to
create the tables without using quoted indentifiers and then your
original query would work.

thanks,
--Barry

Colin Freas wrote:

>>via quoted mixed case identifiers.  I haven't seen any evidence that
>>basic SQL operations (select, insert, update, delete) have a bug in them.
>>
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1.  rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2.  rs = stmt.executeQuery("select * from response where questionID=16");
>
> This is how I got it to work:
> 3.  rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
> \"response\".\"respondentID\", \"response\".\"answer\",
\"response\".\"ID\"
> FROM \"response\" WHERE "\response\".\"questionID\"=16");
>
> Sorry, does anyone think the third is better than the second?


Re: JDBC bug?

From
"Ross J. Reedstrom"
Date:
On Mon, Nov 26, 2001 at 03:41:28PM -0500, Colin Freas wrote:
> > via quoted mixed case identifiers.  I haven't seen any evidence that
> > basic SQL operations (select, insert, update, delete) have a bug in them.
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1.  rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2.  rs = stmt.executeQuery("select * from response where questionID=16");
>
> This is how I got it to work:
> 3.  rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
> \"response\".\"respondentID\", \"response\".\"answer\", \"response\".\"ID\"
> FROM \"response\" WHERE "\response\".\"questionID\"=16");

How about:
4.  rs = stmt.executeQuery("select * from response where \"questionID\"=16");

Quote what you must, and no more.

>
> Sorry, does anyone think the third is better than the second?
>

Yes, since it conforms to SQL92. If you'd bothered to check the postgresql
log, you would have seen error messages of the form:
test=# select * from response where questionID=16;
ERROR:  Attribute 'questionid' not found

Which would have clued you into what the problem was. Alternatively,
try your queries in psql so you can _see_ the responses.

Ross
--
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005


Re: JDBC bug?

From
"Ross J. Reedstrom"
Date:
On Mon, Nov 26, 2001 at 01:02:53PM -0800, Nate Gelbard wrote:
>
>  > of a complaint on how SQL is case insensitive and if you have created
>  > your objects with quoted mixed case identifiers you need to accessthem
>  > via quoted mixed case identifiers.  I haven't seen any evidence that
>
> I am mistaken. This is actually a bug in the applications that are
> building SQL statements without quotes around tablenames of mixedcase.

Bad apps are everywhere. I've met quite a few that make the Oracle assumption
about casefolding: MixedCase == MIXEDCASE i.e. Oracle upcases everything.

The other place you'll hit this is NTFS (i.e. NT file system): names are
casepresevering but _not_ case sensitive, so MixEdCase == MIXEDCASE ==
mixecase all conflict, but will be stored (and returned) however the
app. (or user) spelled it the first time. Another example of being overly
helpful.

Ross

Re: JDBC bug?

From
"Ross J. Reedstrom"
Date:
On Mon, Nov 26, 2001 at 04:24:46PM -0500, Colin Freas wrote:
>
> Barry,
>
> I'm just saying that it's frustrating because in one of my earlier checks to
> make sure my SQL was ok, I used Access 2000 with some linked tables to my

Ah, here's the real problem: MS-Access 'helpfully' quotes all the identifiers
for you, behind your back. Kind of like the 'helpful' things Word does as you
type. I got bit by the same behavior: I created same tables from inside
Access, and got to spend some time finding all the MiXedCase BuGs in PostgreSQL
v 6.2 (or so).

> Postgres db.  When I ran 'select * from response where questionID=16'
> through that, it worked without a hitch (actually, the 16 needed to be
> '16'), so I figured the problem was somewhere else.  Plus it was such simple
> SQL, I mean, what could be wrong with it?  I'd be the first to admit I
> don't...  well, I didn't... know the nuances of the SQL9X casing rules.  My
> issue, as stated in my initial note, was more that nobody (jdbc, postgres,
> tomcat) told me that my where clause was, essentially, bogus.  I mean, what
> am I catching that SQLException for?
>
> And Bruce wrote...
> >rs = stmt.executeQuery("select * from response where \"questionID\"=16");
>
> That works.

Indeed.

BTW, as long as you stay away from software that meses with your queries,
you can get away with writing:

select * from response where questionID=16;

As long as the table was created the same way (i.e., no quotes):

create table response (questionID int , ...)

The only problem is that the column name returned by the system will
be questionid.

Ross

Re: JDBC bug?

From
Rene Pijlman
Date:
On Mon, 26 Nov 2001 16:24:46 -0500, you wrote:
>I just think mixed case identifiers are easier on the eyes.

That's fine. Since SQL is case insensitive you can write the
names anyway you like.

>Postgres stores the identifiers in a case insensitve manner by default

Yes.

>but if you mix case when creating the tables or what have you, it stores
>them as case sensitive.

No, only when you quote the names when creating the tables. Its
up to you. If you don't quote them your problems are gone,
IIUYC.

Regards,
René Pijlman <rene@lab.applinet.nl>