PostgreSQL Newbies - Data Migration from Access to Postgresql

Author

Data Migration from Access to Postgresql

sara simoes

2005-06-20, 7:23 am

Hello,

I've got an Access database and I need to migrate the data from the Access database to a Postgresql database.
Up to this moment, I've done the following:

- Create a table in postgresql
create table clients
(
name text,
phone text,
age integer
);

- export the table client in "tab" delimited format from access with the
fields in the same order as in postgresql :
mary 5551212 25
peter 5551313 26
paul 5551414 30
john 5551515 27

- use "copy table from..." command

copy client from 'C:/teste.txt';

Nevertheless, the data was not inserted and I received the following error message:

Line with OID 0 inserted.
ERROR: syntax error at or near "COPY" at character 9

What might be the problem? If export the table with comma delimited and use
COPY client FROM 'C:/teste.txt' using delimiters ',';

I get the following error message:

Line with OID 0 inserted.

ERROR: syntax error at or near "COPY" at character 9

Note: I'm working with PgAdminIII

I would appreciate your help,

---------------------------------
Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!

operationsengineer1@yahoo.com

2005-06-20, 11:23 am

a quick look at the docs indicates some possible
problems...

http://www.postgresql.org/docs/8.0/...e/sql-copy.html

1. it is recimmended to have OIDs turned off and to
use a serial data type to act as your primary key,
unique identifier. do you have OIDs turned off?

this might help you...

http://www.frankhilliard.com/serialstory.cfm

2. "COPY FROM will raise an error if any line of the
input file contains more or fewer columns than are
expected. If OIDS is specified, the OID is read or
written as the first column, preceding the user data
columns." IOW, every line must have the exact amount
of data sets as columns in the db.

--- sara simoes <riojan2005@yahoo.com.br> wrote:

> Hello,
>
> I've got an Access database and I need to migrate
> the data from the Access database to a Postgresql
> database.
> Up to this moment, I've done the following:
>
> - Create a table in postgresql
> create table clients
> (
> name text,
> phone text,
> age integer
> );
>
> - export the table client in "tab" delimited format
> from access with the
> fields in the same order as in postgresql :
> mary 5551212 25
> peter 5551313 26
> paul 5551414 30
> john 5551515 27
>
> - use "copy table from..." command
>
> copy client from 'C:/teste.txt';
>
>
> Nevertheless, the data was not inserted and I
> received the following error message:
>
> Line with OID 0 inserted.
> ERROR: syntax error at or near "COPY" at character 9
>
> What might be the problem? If export the table with
> comma delimited and use
> COPY client FROM 'C:/teste.txt' using delimiters
> ',';
>
> I get the following error message:
>
> Line with OID 0 inserted.
>
> ERROR: syntax error at or near "COPY" at character 9
>
> Note: I'm working with PgAdminIII
>
> I would appreciate your help,
>
>
>
> ---------------------------------
> Yahoo! Acesso Grátis: Internet rápida e grátis.
> Instale o discador agora!

____________________
____________________
__________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Sponsored Links