Skip site navigation (1) Skip section navigation (2)

Re: Data Migration from Access to Postgresql

From: <operationsengineer1(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Data Migration from Access to Postgresql
Date: 2005-06-20 16:00:19
Message-ID: 20050620160019.56747.qmail@web33310.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)yahoo(dot)com(dot)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 

In response to

pgsql-novice by date

Next:From: Matt IskraDate: 2005-06-20 17:15:44
Subject: Re: Trigger and Trigger function, Part 3 - success!
Previous:From: Mace, RichardDate: 2005-06-20 15:55:51
Subject:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group