Thread: copy command with PSQL

copy command with PSQL

From
shaunnx@my-deja.com (X)
Date:
Sorry if this is a repost --

Howdy:

Regarding the 'copy' command, are there any examples
as to it's usage?  Let me give you some background on
what I'm doing.

From my DB2/Mainframe to NT/DB2Connect environment, I have
an SQL script that pulls data down.  I can append the
data into a flat ASCII file format, but because some of the data
in some columns are broken up, it doesn't seem possible to
import the data into Postgres.  (e.g., the address col. may
have "66 w. baker street" ... but the format of the file is
space delimited, so each group of words are looked at as a
column).

DB2 will, however, let me create a file that is a binary
format. (e.g., EXPORT to F:\backup\test.ixf of IXF)
After looking at some of the Postgres documentation,
there is a copy command that says, 'copies data between files
and tables'.

Now, I'm trying to figure out how can I copy this binary file
and load it into a table on Postgres?  Is it possible?

Thanks!

-X

Re: copy command with PSQL

From
Jeff Eckermann
Date:
You need some kind of field separator in your data, a
single character that is not found elsewhere in your
data.
That means you will need to find a way to get your
source application to export the data with separator
characters in place; or else you will need to edit the
output to add those separators.  How you do the
editing depends on the nature of the data and the
format of the output.  If you send me a sample I may
be able to suggest something for you (if the first
alternative is unworkable for you).
I have written some notes on the use of "copy", which
you can find at the techdocs site:
http://techdocs.postgresql.org

--- X <shaunnx@my-deja.com> wrote:
> Sorry if this is a repost --
>
> Howdy:
>
> Regarding the 'copy' command, are there any examples
> as to it's usage?  Let me give you some background
> on
> what I'm doing.
>
> From my DB2/Mainframe to NT/DB2Connect environment,
> I have
> an SQL script that pulls data down.  I can append
> the
> data into a flat ASCII file format, but because some
> of the data
> in some columns are broken up, it doesn't seem
> possible to
> import the data into Postgres.  (e.g., the address
> col. may
> have "66 w. baker street" ... but the format of the
> file is
> space delimited, so each group of words are looked
> at as a
> column).
>
> DB2 will, however, let me create a file that is a
> binary
> format. (e.g., EXPORT to F:\backup\test.ixf of IXF)
> After looking at some of the Postgres documentation,
> there is a copy command that says, 'copies data
> between files
> and tables'.
>
> Now, I'm trying to figure out how can I copy this
> binary file
> and load it into a table on Postgres?  Is it
> possible?
>
> Thanks!
>
> -X
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

Re: copy command with PSQL

From
Mark Nickel
Date:

X wrote:

> import the data into Postgres.  (e.g., the address col. may
> have "66 w. baker street" ... but the format of the file is
> space delimited, so each group of words are looked at as a
> column).

This is pretty tricky if you have spaces within a field and you are using
the space as a field delimiter.  The COPY isn't going to be able to help you
here.

You should try and select a field delimiter that you know will not occur in
your dataset.  Nice candidates for field delimiters I find are the tilde '~'
and the veritical bar '|'.  If you can tweek your export script on the
DB2/Mainframe NT/DB2Connect environment to perform it's exports using one of
the above field delimiters, you should have better success with the COPY
command.

Eg:
simple record structure:
name | address | zip

In your example, you ASCII file probably looks like this:

Joe Sixpack 66 w. backer street 99999

This is very difficult for COPY to make sense of because what should be used
as the field delimiter?

After you modify your DB2/Mainframe NT/DB2Connect environment to use a field
delimiter of the '~', your ASCII file would look something like this:

Joe Sixpack~66 w. backer street~99999

Now COPY has something to use as a delimiter.  Within psql:  COPY address
from 'C:\asciifile.txt' using delimiters '~'

There is a gotcha, your ascii file *must* contain something for each field
in your record.  If in the previous example there was no value in the zip
field:

Joe Sixpack~66 w. backer street~

The COPY will not necessarily put a NULL into the table for you.  You must
explicitly put the NULL in there from your export routines:

Joe Sixpack~66 w. backer street~NULL

Since I cannot control all the data in my export routines, we've added
business logic that creates INSERT sql code right into our export file.  We
simply pipe this to psql <database name> and everything gets inserted
correctly.

Granted the performance of COPY is faster than an INSERT, but because we
cannot control the export as well, this mudging is exceptable...


> there is a copy command that says, 'copies data between files
> and tables'.

Technically it *does* copy data between files and tables if you maintain
exact field matching in your copy files...


> Now, I'm trying to figure out how can I copy this binary file
> and load it into a table on Postgres?  Is it possible?

It doesn't appear that this is going to work because the DB2 IXF file format
may be propietary.


Good Luck!
Mark


Re: copy command with PSQL

From
Andrew Gould
Date:
I'm a little late stepping into this thread.  (sorry)

Does the space delimited file enclose text in quotes
or double quotes?  Most database applications that
export data in a space delimited format will export
text enclosed in quotes or double quotes (or will at
least have the option).

I've written a python script that will copy a space
delimited file with double quotes around text fields
to to a tab delimited file without the double quotes.
It tracks the occurrences of double quotes to know
whether to convert a space to a tab or treat it as
text.  Postgres copies the new file format easily.

If my script will help, let me know and I'll attach it
to an email.

Best of luck,

Andrew Gould


--- Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:
> You need some kind of field separator in your data,
> a
> single character that is not found elsewhere in your
> data.
> That means you will need to find a way to get your
> source application to export the data with separator
> characters in place; or else you will need to edit
> the
> output to add those separators.  How you do the
> editing depends on the nature of the data and the
> format of the output.  If you send me a sample I may
> be able to suggest something for you (if the first
> alternative is unworkable for you).
> I have written some notes on the use of "copy",
> which
> you can find at the techdocs site:
> http://techdocs.postgresql.org
>
> --- X <shaunnx@my-deja.com> wrote:
> > Sorry if this is a repost --
> >
> > Howdy:
> >
> > Regarding the 'copy' command, are there any
> examples
> > as to it's usage?  Let me give you some background
> > on
> > what I'm doing.
> >
> > From my DB2/Mainframe to NT/DB2Connect
> environment,
> > I have
> > an SQL script that pulls data down.  I can append
> > the
> > data into a flat ASCII file format, but because
> some
> > of the data
> > in some columns are broken up, it doesn't seem
> > possible to
> > import the data into Postgres.  (e.g., the address
> > col. may
> > have "66 w. baker street" ... but the format of
> the
> > file is
> > space delimited, so each group of words are looked
> > at as a
> > column).
> >
> > DB2 will, however, let me create a file that is a
> > binary
> > format. (e.g., EXPORT to F:\backup\test.ixf of
> IXF)
> > After looking at some of the Postgres
> documentation,
> > there is a copy command that says, 'copies data
> > between files
> > and tables'.
> >
> > Now, I'm trying to figure out how can I copy this
> > binary file
> > and load it into a table on Postgres?  Is it
> > possible?
> >
> > Thanks!
> >
> > -X
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> > send an appropriate
> > subscribe-nomail command to
> majordomo@postgresql.org
> > so that your
> > message can get through to the mailing list
> cleanly
>
>
> __________________________________________________
> Do You Yahoo!?
> Find a job, post your resume.
> http://careers.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com