Thread: Column name case conversion
Hello, 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.
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. 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. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "He hath not dealt with us after our sins; nor rewarded us according to our iniquities. For as the heaven is high above the earth, so great is his mercy toward them that fear him. As far as the east is from the west, so far hath he removed our transgressions from us." Psalms 103:10-12
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.
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. >
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.
>>
>
Sorry I didn't respond sooner -- lost the thread. :(
Yeah, I can see where you're seeing this now. Not so much
in the queries as in the code that needs to access the
results.
Your suggestion would work (theoretically the server could do
something similar in reverse (store a real name and lower cased
name for comparison)) Although, it might cause some wierdness
with quoted identifiers then...
Imagining someone with ID and "ID" and how that would interact
and how to not break the backward compatibility.
On Tue, 5 Sep 2000, Zeljko Trogrlic wrote:
> 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.
First, having id and ID is a BAD naming practice.
Compatibility could be solved with compatibility flags. I guess this is not
the only place where PostgreSQL breaks compatibility (like any other database).
I remember milion of swithches when I worked with old Turbo c :(
At 02:22 9.9.2000 , Stephan Szabo wrote:
>
>Sorry I didn't respond sooner -- lost the thread. :(
>
>Yeah, I can see where you're seeing this now. Not so much
>in the queries as in the code that needs to access the
>results.
>
>Your suggestion would work (theoretically the server could do
>something similar in reverse (store a real name and lower cased
>name for comparison)) Although, it might cause some wierdness
>with quoted identifiers then...
>Imagining someone with ID and "ID" and how that would interact
>and how to not break the backward compatibility.
>
>On Tue, 5 Sep 2000, Zeljko Trogrlic wrote:
>
>> 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.
>
>
On Sat, 9 Sep 2000, Zeljko Trogrlic wrote: > First, having id and ID is a BAD naming practice. True, but someone could have a database like that right now, and we'd have to keep it working as separate identifiers for some number of versions to allow people's dump files to be restorable. > Compatibility could be solved with compatibility flags. I guess this is not > the only place where PostgreSQL breaks compatibility (like any other database). I was more thinking about backwards compatibility. I guess you could switch forward by having the database have two name columns, one for the search name (lowercased) and one for the "real" name. Then you'd need a hack that would lowercase the real name of the identifier if you had a quoted and unquoted version with the same case. Cases such as ID, "id" would be failures (duplicate identifier) just like it is now, Cases such as "ID", id would work (the quoted one also only matches to "ID") and cases such as "ID", ID are the same as it is now.
Look at the Interbase solution: they have so called "dialects". Dialect 1 is currently old Interbase 5.x and older. Dialect 2 is intermediate dialect for easier transition. Dialect 3 is Interbase 6 dialect. They did some pretty nasty changes between 1 and 3, but you can select in client (ODBC driver etc.) which dialect you'll use. >> Compatibility could be solved with compatibility flags. I guess this is not >> the only place where PostgreSQL breaks compatibility (like any other >database). > >I was more thinking about backwards compatibility. >I guess you could switch forward by having the database have two name >columns, one for the search name (lowercased) and one for the "real" >name. Then you'd need a hack that would lowercase the real name of >the identifier if you had a quoted and unquoted version with the >same case. >Cases such as ID, "id" would be failures (duplicate identifier) just like >it is now, Cases such as "ID", id would work (the quoted one also only >matches to "ID") and cases such as "ID", ID are the same as it is now. v Zeljko Trogrlic ____________________________________________________________ Aeris d.o.o. Sv. Petka 60 b, HR-31000 Osijek, Croatia Tel: +385 (31) 53 00 15 Email: mailto:zeljko@post.hinet.hr