Thread: 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

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
>

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/
---------------------------------------------------------------------

> 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