Re: Column name case conversion - Mailing list pgsql-general

Let's say you select * from table where ID = 1
Then you want to put all column name/value pairs into HashMap:

for (int columnNo = 1; columnNo <= md.getColumnCount(); ++columnNo) {
 String  name = md.getColumnName(columnNo);
 map.put(name, rs.getObject(columnNo));
}

And later you want to retrieve that value:
map.get("ID");

You system fails because you got null value. The only solution I can think
of is to use toUpper for keys and to store column names separately, if
needed for update.

At 18:59 5.9.2000 , Stephan Szabo wrote:
>
>But if you didn't double quote the name when you created it,
>it should have been lowercased there too, and either form of q
>and Q will work (note, "q" will also work, but "Q" won't).
>Like Oliver said, it's case-insensitive when double quotes
>aren't used at any point in the process.
>
>If you didn't use double quotes on the create and it got an upper
>case name, that's definately a bug.
>
>On Tue, 5 Sep 2000, Zeljko Trogrlic wrote:
>
>> The problem is it's not case-insensitive; it's case sensitive with unwanted
>> conversion to lower case. This makes problem with database-independent
>> applications.
>>
>> Details: some databases are case-sensitive and some are not. In order to
>> avoid problems, we write all table and column names in upper case. It
>> worked fine until we started to use PostgreSQL. Our upper case names in
>> source are converted to lower case names that cannot be found in upper case
>> database. Our previous applications won't work with PostgreSQL because of
>that.
>>
>> So we have to rewrite every field name to lower case and change field names
>> in running systems or we have to double-quote every field name in source
>> code. But we can't really doublequote fields because some database don't
>> use double quotes for names - some use square brackets. And if we go for
>> lower case field names, maybe some other database wouldn't recognize that
>> (it will work opposite to PostgreSQL).
>>
>> The best solution will be to leave case as is, if that won't break
>> someone's compatibility.
>>
>> >If your table is called "LOCATION" it will only be matched by "LOCATION";
>> >the original use of double quotes makes their use mandatory for ever
>> >after.  SQL is case-insensitive when double quotes are not used;
>> >your second example (select * from LOCATION) gets translated to lower-case
>> >immediately, which is why you don't get a match on it.  This is correct
>> >behaviour.
>> >
>> >Best to avoid case-sensitive names altogether.
>>
>> At 16:52 5.9.2000 , Oliver Elphick wrote:
>> >Zeljko Trogrlic wrote:
>> >  >Column names are behaving very strangely. In queries, all names are
>> >  >converted to lowercase.
>> >  >
>> >  >Example:
>> >  >
>> >  >I have a table named "LOCATION".
>> >  >
>> >  >select * from location;
>> >  >I get an error message (this is OK)
>> >  >
>> >  >select * from LOCATION;
>> >  >I get an error message (this is NOT OK)
>> >  >
>> >  >select * from "location";
>> >  >I get an error message (this is OK)
>> >  >
>> >  >select * from "LOCATION";
>> >  >Everything works (this is OK)
>> >  >
>> >  >Is it by design? I think that database shouldn't convert name case
in SQL
>> >  >statements, or it should try to make case-insensitive match (like
>> >  >Interbase) if double quotes are not used.
>>
>