Re: ODBC driver and non case-insensitivity?

On Fri, 19 Oct 2001, Chris Storah wrote:

> Just searched the archives but no match for this problem that I can see:
>
> Exporting an MS SQL 2000 table to PostgreSQL 7.1.3 fails due to uppercase
> table names.
>
> MS SQL is set to quote all identifiers so the queries are sent in the format:
> 'create table TEST(ABC int)'
> However, when MS SQL sends a query 'select * from test', the ODBC fails with
> "ERROR: Relation 'test' does not exist" as it is looking for the lowercase
> version of the table.
>
> Through psql, the uppercase table names are converted into lowercase so
> PostgreSQL seems to be okay. Is this a problem with the ODBC driver?
>
> If so, are there any patches available?

How are you "exporting" a MS SQL table to PG? If this is a dump utility in
MS SQL, there may be an option in this dumping program to not quote
identifiers (table names, etc.) to be uppercase.

However, if the dumping program is dumping with quoted uppercase
identifers, it's not a bug that PG doesn't find the table: "TEST" is a
different table than "test". This is not an ODBC problem at all, if I
understand your problem.

Unquoted identifiers, eg test (no quotes) is sent as uppercase regardless
of how you write it: test Test teST; if written w/quotes, case is
preserved.

If the MSSQL dump routine can't ditch the quoted identifiers, you could
either (a) search/replace/sed/perl/whatever the quotes out of the SQL dump
before importing to PG, or (b) once in PG, ALTER TABLE "FOO" RENAME TO
"foo".

(b) is tricky, though, as index names and ref integrity constraints and
such aren't corrected for this. (a) is a better solution.

HTH,
--

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant