Thread: Re: copy files to postgresql

I was trying to copy files to columns of a PostgreSQL database table using \copy 
( e. g. \copy mytable (column names here) from 'my file' using delimiters '\t'). 
PostgreSQL does not seem to like the column names part. Could someone help me 
with that? Thanks. Put the text of the welcome message here.

I believe that soon-to-be-released version 7.3 will
have the ability to import data to specific columns,
as you are trying to do.  For now, you will have to
edit your input file to insert values (even if null)
for the missing columns.  The default null
representation expected by COPY is "\N" (backslash,
upper case "N"), or you can specify another using the
syntax "\copy .... with null as 'whatever'"

--- John Zhang <jzhang@jimmy.harvard.edu> wrote:
> I was trying to copy files to columns of a
> PostgreSQL database table using \copy 
> ( e. g. \copy mytable (column names here) from 'my
> file' using delimiters '\t'). 
> PostgreSQL does not seem to like the column names
> part. Could someone help me 
> with that? Thanks.
> >   Put the text of the welcome message here.
> > 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 


__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com

Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> I believe that soon-to-be-released version 7.3 will
> have the ability to import data to specific columns,
> as you are trying to do.  For now, you will have to
> edit your input file to insert values (even if null)
> for the missing columns.

A usually-easier way is to make a temp table that matches the column
format of the source file, COPY into that, and then doINSERT INTO finaltable SELECT foo,bar,... FROM temptable
In the INSERT/SELECT it's trivial to do any rearrangement you want.

7.3 will add some more flexibility to COPY, but it only saves a
step, doesn't really do anything you can't do with a temp table.
        regards, tom lane

A much simpler solution would be to:

create a table from the original one without the missing fields;
use the copy command to import the file;
insert into the original table from the new table, supplying null for
the missing fields.

JLL

Jeff Eckermann wrote:
> 
> I believe that soon-to-be-released version 7.3 will
> have the ability to import data to specific columns,
> as you are trying to do.  For now, you will have to
> edit your input file to insert values (even if null)
> for the missing columns.  The default null
> representation expected by COPY is "\N" (backslash,
> upper case "N"), or you can specify another using the
> syntax "\copy .... with null as 'whatever'"
> 
> --- John Zhang <jzhang@jimmy.harvard.edu> wrote:
> > I was trying to copy files to columns of a
> > PostgreSQL database table using \copy
> > ( e. g. \copy mytable (column names here) from 'my
> > file' using delimiters '\t').
> > PostgreSQL does not seem to like the column names
> > part. Could someone help me
> > with that? Thanks.
> > >   Put the text of the welcome message here.
> > >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> 
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> 
> ---------------------------(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