Thread: using copy to insert data.

using copy to insert data.

From
"Jennifer Lee"
Date:
I'm trying to insert data from a delimited file using the copy command
(COPY table FROM 'filepath') this works fine, but I would like to insert
data into specific columns. I've read that this is possible but haven't
been able to find documentation on how to do it. I've been able to
insert the data as I'd like in two steps. 1. copy into a temporary table, 2.
insert using select columns from the temporary table. Is there a way to
do this in one step?

Thanks much,
Jennifer


Re: using copy to insert data.

From
"Chad Thompson"
Date:
Postgresql 7.3 will allow you to copy data into specific columns

copy table (col1, col2) from 'filepath' with delimiter ',';

HTH
Chad

----- Original Message -----
From: "Jennifer Lee" <jlee@scri.sari.ac.uk>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, February 06, 2003 9:40 AM
Subject: [NOVICE] using copy to insert data.


> I'm trying to insert data from a delimited file using the copy command
> (COPY table FROM 'filepath') this works fine, but I would like to insert
> data into specific columns. I've read that this is possible but haven't
> been able to find documentation on how to do it. I've been able to
> insert the data as I'd like in two steps. 1. copy into a temporary table,
2.
> insert using select columns from the temporary table. Is there a way to
> do this in one step?
>
> Thanks much,
> Jennifer
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


Re: using copy to insert data.

From
Andrew McMillan
Date:
On Fri, 2003-02-07 at 05:40, Jennifer Lee wrote:
> I'm trying to insert data from a delimited file using the copy command
> (COPY table FROM 'filepath') this works fine, but I would like to insert
> data into specific columns. I've read that this is possible but haven't
> been able to find documentation on how to do it. I've been able to
> insert the data as I'd like in two steps. 1. copy into a temporary table, 2.
> insert using select columns from the temporary table. Is there a way to
> do this in one step?

COPY table (column1, column2, column3 ) FROM ....

According to the docs it works in 7.3 but I have never had cause to use
that syntax myself.

Regards,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------


Re: using copy to insert data.

From
Antti Haapala
Date:
> I'm trying to insert data from a delimited file using the copy command
> (COPY table FROM 'filepath') this works fine, but I would like to insert
> data into specific columns. I've read that this is possible but haven't
> been able to find documentation on how to do it. I've been able to
> insert the data as I'd like in two steps. 1. copy into a temporary table, 2.
> insert using select columns from the temporary table. Is there a way to
> do this in one step?

This functionality is supported as of 7.3.

The syntax for COPY in brand new 7.3.2 docs is

COPY table [ ( column [, ...] ) ]
    FROM { 'filename' | stdin }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ] ]

COPY table [ ( column [, ...] ) ]
    TO { 'filename' | stdout }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ] ]

--
Antti Haapala