Re: Column name case conversion
От | Zeljko Trogrlic |
---|---|
Тема | Re: Column name case conversion |
Дата | |
Msg-id | 4.1.20000905205121.014948f8@pop.tel.hr обсуждение исходный текст |
Ответ на | Re: Column name case conversion (Zeljko Trogrlic <zeljko@technologist.com>) |
Ответы |
Re: Column name case conversion
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
|
Список | 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. >> >
В списке pgsql-general по дате отправления: