Thread: Migrating from MS-Access to Postgresql

Migrating from MS-Access to Postgresql

From
"Marc Cuypers"
Date:
Hi,
 
I migrated a few tables to postgresql. Everything migrated fine, but tablenames that contain capitals are converted just as is.  When I create a query, postgresql doesn't seem to use those captitals and converts them to lowercase.  This makes that the table is not found in the database and I get an error.  I the database is a table 'Invoices', so the respons to the query 'select * from Invoices' is 'Relation invoices doesn't exist' .
 
Can someone help me with this?
 
Thanx,
 
Marc

Re: Migrating from MS-Access to Postgresql

From
Dave Page
Date:
Hi,
 
Either change your query to:
 
SELECT * FROM "Invoices"
 
or, on the first step in the Migration Wizard, use the options to shift names to lower case, and re-migrate your data.
 
Of course, if you only have a few tables/columns you could just rename them in pgAdmin.
 
Regards, Dave.
-----Original Message-----
From: Marc Cuypers [mailto:m.cuypers@pandora.be]
Sent: 28 March 2002 06:57
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Migrating from MS-Access to Postgresql

Hi,
 
I migrated a few tables to postgresql. Everything migrated fine, but tablenames that contain capitals are converted just as is.  When I create a query, postgresql doesn't seem to use those captitals and converts them to lowercase.  This makes that the table is not found in the database and I get an error.  I the database is a table 'Invoices', so the respons to the query 'select * from Invoices' is 'Relation invoices doesn't exist' .
 
Can someone help me with this?
 
Thanx,
 
Marc

Re: Migrating from MS-Access to Postgresql

From
Dino Nardini
Date:
Hello,

I ran into similiar problems when migrating tables from MSSQL to Postgres.  Typically I create table names such as "TableName" and column names such as "TableID" in MSSQL.  If there exists a table called "TableName" and a column called "TableID" why won't the following query work in postgres:

SELECT TableID FROM TableName

I guess that my question is: why does Postgres muck around with the case?   Double quoting all tables and columns in a database that contains dozens of tables is far too much work, and I don't understand why I need to drop all my labels to lowercase in the DB.  Is there no way to override this behavior?

Cheers... Dino


At 09:10 AM 28/03/2002 +0000, Dave Page wrote:
Hi,
 
Either change your query to:
 
SELECT * FROM "Invoices"
 
or, on the first step in the Migration Wizard, use the options to shift names to lower case, and re-migrate your data.
 
Of course, if you only have a few tables/columns you could just rename them in pgAdmin.
 
Regards, Dave.
-----Original Message-----
From: Marc Cuypers [mailto:m.cuypers@pandora.be]
Sent: 28 March 2002 06:57
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Migrating from MS-Access to Postgresql

Hi,
 
I migrated a few tables to postgresql. Everything migrated fine, but tablenames that contain capitals are converted just as is.  When I create a query, postgresql doesn't seem to use those captitals and converts them to lowercase.  This makes that the table is not found in the database and I get an error.  I the database is a table 'Invoices', so the respons to the query 'select * from Invoices' is 'Relation invoices doesn't exist' .
 
Can someone help me with this?
 
Thanx,
 
Marc

_____________________________________
Rivendell Software - Dynamic Web Solutions
www.rivendellsoftware.com
tel 902.461.0720
cell 902.802.0867

Re: Migrating from MS-Access to Postgresql

From
Dave Page
Date:
 
-----Original Message-----
From: Dino Nardini [mailto:dnardini@rivendellsoftware.com]
Sent: 28 March 2002 13:45
To: Dave Page; 'Marc Cuypers'; pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] Migrating from MS-Access to Postgresql

Hello,

I ran into similiar problems when migrating tables from MSSQL to Postgres.  Typically I create table names such as "TableName" and column names such as "TableID" in MSSQL.  If there exists a table called "TableName" and a column called "TableID" why won't the following query work in postgres:

SELECT TableID FROM TableName

I guess that my question is: why does Postgres muck around with the case?   Double quoting all tables and columns in a database that contains dozens of tables is far too much work, and I don't understand why I need to drop all my labels to lowercase in the DB.  Is there no way to override this behavior?
 
PostgreSQL is doing this as per the SQL spec (don't ask me what part, I'm just quoting what others have told me). You would get more knowledgable answers on one of the PostgreSQL lists - all I can really offer is advice on how pgAdmin can help you (by dropping the case or quoting).
 
Regards, Dave.