Обсуждение: Using COPY FROM on a subset of the file's column

Hello,

I'm trying to import a text file into a table using the COPY command.

The text file contains four columns: id, col_1, col_2, col_3 (in that order).
My target table only has id and col_1.

 From the documentation I assumed that the following statement

   copy foo (id, col_1)
   from 'foo.txt'
   with (format csv, delimiter ';', header true);

would only import id and col_1, but instead it fails with "ERROR:  extra data after last expected column".

Am I missing something, or is this a limitation of the COPY command?

Regards
Thomas

On 11/11/2012 05:33 PM, Thomas Kellerer wrote:
> Hello,
>
> I'm trying to import a text file into a table using the COPY command.
>
> The text file contains four columns: id, col_1, col_2, col_3 (in that
> order).
> My target table only has id and col_1.
>
> From the documentation I assumed that the following statement
>
>   copy foo (id, col_1)
>   from 'foo.txt'
>   with (format csv, delimiter ';', header true);
>
> would only import id and col_1, but instead it fails with "ERROR:
> extra data after last expected column".
>
> Am I missing something, or is this a limitation of the COPY command?

\copy (VALUES (1,2,3),(1,4,5),(1,9,21),(42,42,42)) TO 'test.csv' CSV
CREATE TABLE test_copy (id integer, col_1 integer);
\copy test_copy (id, col_1) FROM 'test.csv' CSV
ERROR:  extra data after last expected column
CONTEXT:  COPY test_copy, line 1: "1,2,3"

Looks like a limitation in COPY. The column-list is specifying how you
want the data COPY reads to be inserted into the target table; it's
expected to have fields compatible with that column list.

It seems like you want to be able to say something like this (imaginary)
syntax:

\copy test_copy (id, col1) FROM 'test.csv' CSV COLUMNS(1,2,IGNORE)

or some similar way to provide a column mapping from the CSV columns to
the output of the COPY command.

That isn't directly possible at the moment. You could:

- \copy into a view that had a view trigger (or possibly rules;
untested) to rewrite the incoming inserts and store them in the real
target table; or
- Just \copy into an UNLOGGED or TEMPORARY table then INSERT INTO ...
SELECT the data to the real destination.

--
Craig Ringer

Craig Ringer wrote on 11.11.2012 11:23:
> It seems like you want to be able to say something like this (imaginary)
> syntax:
>
> \copy test_copy (id, col1) FROM 'test.csv' CSV COLUMNS(1,2,IGNORE)

> or some similar way to provide a column mapping from the CSV columns to
> the output of the COPY command.

right, that was what I was hoping fro.

> - \copy into a view that had a view trigger (or possibly rules;
> untested) to rewrite the incoming inserts and store them in the real
> target table; or
> - Just \copy into an UNLOGGED or TEMPORARY table then INSERT INTO ...
> SELECT the data to the real destination.

Thanks for the tips, I do have another option to use an external tool that will let me do that without problem.
The COPY solution would have been faster though, but it's a one-off thing anyway.

Regards
Thomas