pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: copy files to postgresql
4+ messages / 4 participants
[nested] [flat]
* Re: copy files to postgresql
@ 2002-08-06 12:14 John Zhang <jzhang@jimmy.harvard.edu>
2002-08-06 13:23 ` Re: copy files to postgresql Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: John Zhang @ 2002-08-06 12:14 UTC (permalink / raw)
To: pgsql-sql
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.
Attachments:
NoName (text/plain, 43B) download
Put the text of the welcome message here.
* Re: copy files to postgresql
2002-08-06 12:14 Re: copy files to postgresql John Zhang <jzhang@jimmy.harvard.edu>
@ 2002-08-06 13:23 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-08-06 14:01 ` Re: copy files to postgresql Tom Lane <tgl@sss.pgh.pa.us>
2002-08-06 16:20 ` Re: copy files to postgresql Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 4+ messages in thread
From: Jeff Eckermann @ 2002-08-06 13:23 UTC (permalink / raw)
To: John Zhang <jzhang@jimmy.harvard.edu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: copy files to postgresql
2002-08-06 12:14 Re: copy files to postgresql John Zhang <jzhang@jimmy.harvard.edu>
2002-08-06 13:23 ` Re: copy files to postgresql Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2002-08-06 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-08-06 14:01 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: John Zhang <jzhang@jimmy.harvard.edu>; pgsql-sql
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 do
INSERT 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: copy files to postgresql
2002-08-06 12:14 Re: copy files to postgresql John Zhang <jzhang@jimmy.harvard.edu>
2002-08-06 13:23 ` Re: copy files to postgresql Jeff Eckermann <jeff_eckermann@yahoo.com>
@ 2002-08-06 16:20 ` Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-08-06 16:20 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: John Zhang <jzhang@jimmy.harvard.edu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-08-06 16:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-06 12:14 Re: copy files to postgresql John Zhang <jzhang@jimmy.harvard.edu>
2002-08-06 13:23 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-08-06 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-06 16:20 ` Jean-Luc Lachance <jllachan@nsd.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox