Re: copy command with PSQL

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