copy command with PSQL

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

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

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