Thread: case insensitive table names

Dear mailing list,

I'm trying to convert a MsSQL database to a Postgres database, with DTS i can
convert the data between the databases. DTS creates case sensitive tables
(using quotes), exactly like i want it to be.

So far so good. But the current code uses queries without quotes, like
select * from Table; and not select * from "Table";. The Table gets
converted to
lowercase and then compared (case sensitive) against the tables in the
database.

This means i have to convert all the table names to lowercase, losing a lot
of readability,
or i put quotes around all the table names in the current queries causing
also a lot of
possible problems and unneeded changes.

Is there an easier fix for this? Does the SQL92/SQL3/SQL2003 specify
unquoted table
names to be case-insensitive or does it say it needs to be converted to
lowercase?

It would be great having a global flag to enable or disable case sensitive
compares
everywhere.

Thanks.

Alwin

alwin <alwin@cs.nl> writes:
> So far so good. But the current code uses queries without quotes, like
> select * from Table; and not select * from "Table";.

You have a problem :-(

> Is there an easier fix for this? Does the SQL92/SQL3/SQL2003 specify
> unquoted table names to be case-insensitive or does it say it needs to
> be converted to lowercase?

The SQL spec is absolutely clear that the above code is wrong.  What the
spec actually says is that unquoted names should be forced to all upper
case (so Table is equivalent to "TABLE").  Postgres forces them to lower
case (so Table is equivalent to "table") which is not quite the same but
has the same effect that unquoted names are not case sensitive.  But
under both the exact spec rules and our rules, a mixed-case quoted name
is never equivalent to any unquoted name.  There is no such thing as
case insensitivity for quoted names.

            regards, tom lane

unsuscribe