Обсуждение: copy command with PSQL
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
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