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.