Re: getTables not returning 10 columns etc - Mailing list pgsql-jdbc

The java documentation you link to certainly does seem to imply that all 10 columns should be there and doesn't seem to make allowances for any of them being optional. As for the lower case thing, I recently noticed that all of my column names come back in lower case, even if explicitly aliased to a mixed case or upper case name.  A bit of googling revealed that the sql standard says nothing is case sensitive unless double quoted, so something like this:

select columnName as "columnName" from table

should return a mixed-case column name and anything else is fair game for any form of capitalization or lack thereof.  Alternatively, if the table is created with column names in double quotes:

create table "MixedCase" ("MyColumn" varchar); 

the case of the letters will be preserved without the explicit alias - and you will also have to use mixed case and double quotes when referring to the table and column names in any queries.  Since the java docs you pointed to don't specify that the names should be anything other than the usual case-insensitive style of column names that sql says is the default, the driver clearly feels free to return them in all lower case.  A quick test shows that it doesn't appear that the db even remembers your original capitalization if you don't use double quotes in the create statement, so it probably simply isn't possible for the dd or driver to offer a switch that would force the columns to come back in their original form. I have always used all-lower-case until I inherited code from elsewhere that assumed mixed case in queries would deliver mixed case in column metadata. Once I found the problem, I simply started writing code that never assumes case sensitivity with table and column names, which is arguably more correct.  The unfortunate thing is that if someone should write code that assumes lower case, a bug isn't likely to be detected unless/until the behaviour of the code changes or you switch/upgrade databases.  It's unfortunate that the widespread use of ORM makes database switches easy enough in many cases that they may be performed with insufficient testing of dependent code. That said, it is hard to see where someone modifying an underlying db, even just upgrading to a new version, without testing sufficient to detect this problem has anyone to blame but themselves.  That's the argument I expect you'd get from the developers, anyway.  Assuming the more simple argument of "all databases force everything to lower case" isn't valid.  I don't know, since I've been postgres-only for so long, I can't remember what anything else does.

Sio the short answer is that unless you explicitly request them in a case-sensitive manner, all column names should be handled in a case-insensitive manner - so your code shouldn't complain about the capitalization.  The missing columns does appear to be a real problem, based on my very quick perusal of the documentation, but I'm hardly an expert.