Обсуждение: Duplicate Table Names in PostgreSQL

I have just started to use PostgreSQL. Among creating sql scripts to create my database and schema, I also need to
exportexisting data from a MS SQL database. This has worked well using the postgreSQL ODBC driver in conjunction with
MSData Transformation Services (DTS) to export and move the data to PostgreSQL running on Linux. 

My question is this. When I run my create database scripts using psql, it converts all of my mixed table and column
namesinto lower case; even though I have specified mixed names in the SQL script file. However, when I run DTS and
exportthe data into the Postgres database (uses PostgreSQL ODBC), this process does in fact create a mixed table name
inaddition to the lower case version of the same table. 

Using psql, I can display the tables information (\dt) and see a display of both the lower and mixed case table.

However, when I drop a table (specifying either a mixed or lower case name), PostgreSQL seems to only have one instance
ofthe table. After dropping, a select statement against the table, is reported by psql, as not existing. 

How is this happening?

Jerry Schnetzer
Minneapolis

> -----Original Message-----
> From: Schnetzer, Jerry [mailto:schnetzerj@nextnetwireless.com]
> Sent: 20 November 2002 01:33
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Duplicate Table Names in PostgreSQL
>
>
> I have just started to use PostgreSQL. Among creating sql
> scripts to create my database and schema, I also need to
> export existing data from a MS SQL database. This has worked
> well using the postgreSQL ODBC driver in conjunction with MS
> Data Transformation Services (DTS) to export and move the
> data to PostgreSQL running on Linux.
>
> My question is this. When I run my create database scripts
> using psql, it converts all of my mixed table and column
> names into lower case; even though I have specified mixed
> names in the SQL script file. However, when I run DTS and
> export the data into the Postgres database (uses PostgreSQL
> ODBC), this process does in fact create a mixed table name in
> addition to the lower case version of the same table.
>
> Using psql, I can display the tables information (\dt) and
> see a display of both the lower and mixed case table.
>
> However, when I drop a table (specifying either a mixed or
> lower case name), PostgreSQL seems to only have one instance
> of the table. After dropping, a select statement against the
> table, is reported by psql, as not existing.
>
> How is this happening?

PostgreSQL folds unquoted names to lower case. DTS is presumably quoting
names whilst your scripts are not.

I would advise using all lowercase names if possible - it'll help avoid
any possible nasties in the future.

Regards, Dave.