Postgres Pro
Downloads
Home   >   mailing lists

Thread: Data Import

Data Import

From
keith
Date:

Hi,

Can anyone tell me where to find out information about going from 6.3 to 6.5?  I have used the "\copy name to name" command to save all of the data to a file.  I now want to import this data in 6.5.  Any suggestions?

-Keith


Do You Yahoo!?
Yahoo! Auctions - Buy the things you want at great prices.

Re: Data Import

From
"Anthony E . Greene"
Date:
On Tue, 23 Jan 2001 16:07:17 keith wrote:
>Can anyone tell me where to find out information about going from 6.3 to
>6.5?  I have used the "\copy name to name" command to save all of the data
>to a file.  I now want to import this data in 6.5.  Any suggestions?

Export old database:

  pg_dump -d dabasename -D database.sql

This will output the SQL commands to recreate all your tables and data. Then
import it into the new database:

  psql < database.sql

Tony
--
Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/>
PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26  C484 A42A 60DD 6C94 239D
Chat:  AOL/Yahoo: TonyG05    ICQ: 91183266
Linux. The choice of a GNU Generation. <http://www.linux.org/>

Re: Re: Data Import

From
Guy Fraser
Date:
"Anthony E . Greene" wrote:
>
> On Tue, 23 Jan 2001 16:07:17 keith wrote:
> >Can anyone tell me where to find out information about going from 6.3 to
> >6.5?  I have used the "\copy name to name" command to save all of the data
> >to a file.  I now want to import this data in 6.5.  Any suggestions?
>
> Export old database:
>
>   pg_dump -d dabasename -D database.sql
>
> This will output the SQL commands to recreate all your tables and data. Then
> import it into the new database:
>
>   psql < database.sql
>
> Tony
> --
> Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/>
> PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26  C484 A42A 60DD 6C94 239D
> Chat:  AOL/Yahoo: TonyG05    ICQ: 91183266
> Linux. The choice of a GNU Generation. <http://www.linux.org/>

Here is a simple way to import a tab delimited.

cat file.tab | psql -c "copy table1 from stdin;" database1

And now for a comma delimited file.

cat file.cvs | psql -c "copy table1 from stdin using delimiters ``,'';"
database1

CAUTION CAUTION CAUTION CAUTION CAUTION CAUTION CAUTION CAUTION CAUTION
CAUTION                                                         CAUTION
CAUTION      make sure their are NO SINGLE '\' characters       CAUTION
CAUTION                                                         CAUTION
CAUTION CAUTION CAUTION CAUTION CAUTION CAUTION CAUTION CAUTION CAUTION

The ``\'' character is an escape character.

To fix this automatically use a sed command like this.

sed -e "s;\\\;\\\\\\\;g" file.tab | sql -c "copy table1 from stdin;"
database1

You will also need to make sure any caracter in your data that is also
your
delimiter is escaped.  For tabs escape tabs in the data with ``\    ''.
For comma's escape with ``\,''.

Escaping tabs can be difficult using a bash shell, you can do it within
a script.

This works for me.

Guy