Thread: Data Migration from Access to Postgresql

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

http://www.postgresql.org/docs/8.0/interactive/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

Hi,

"pg_ctl reload" outputs

/usr/lib/postgresql/bin/pg_ctl : line 328 : ( 20055) no such process
postmaster succefuly signaled

and doesn't reload config files in fact !

Any idea would be welcome

Thanks

Al

i'm having the darndest time with some code...  i had
it working like a charm and then *improved* the code
by apparently breaking it!  -lol-

anyway, i've gone back to an old rev of the code and
i'm trying to tweak it to work.  once i get it
working, then i can *improve* the code once again
without, hopefully, breaking the code again.

anyway, pgsql is gave me the following error - one
error for each of three tries to test the db.

ERROR: syntax error at the end of input at character
69
ERROR: syntax error at the end of input at character
70
ERROR: syntax error at the end of input at character
69

how can i interpret this error information so that i
know where to look in my code to remove this error?

tia...



____________________________________________________
Yahoo! Sports
Rekindle the Rivalries. Sign up for Fantasy Football
http://football.fantasysports.yahoo.com

i had my code tied up with the name of a file.  when i
changed the name of the file, it didn't match anything
in the db and my code broke.  when i used a correct
file name, all works well.  looks like i have some
more error correction code to develop.

i'd still appreciate any insight into how to interpret
pgsql errors so i know where to attack my code,
though.

tia...

--- operationsengineer1@yahoo.com wrote:

> i'm having the darndest time with some code...  i
> had
> it working like a charm and then *improved* the code
> by apparently breaking it!  -lol-
>
> anyway, i've gone back to an old rev of the code and
> i'm trying to tweak it to work.  once i get it
> working, then i can *improve* the code once again
> without, hopefully, breaking the code again.
>
> anyway, pgsql is gave me the following error - one
> error for each of three tries to test the db.
>
> ERROR: syntax error at the end of input at character
> 69
> ERROR: syntax error at the end of input at character
> 70
> ERROR: syntax error at the end of input at character
> 69
>
> how can i interpret this error information so that i
> know where to look in my code to remove this error?
>
> tia...
>
>
>
> ____________________________________________________
>
> Yahoo! Sports
> Rekindle the Rivalries. Sign up for Fantasy Football
>
> http://football.fantasysports.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


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

Hello,

If you are still interested in data migration try Access2PostgreSQL at
http://data-conversions.net/access-to-postgresql-pro-converter.html

Best regards,
Dmitry