Column name case conversion

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:

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:

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

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:

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:

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