Re: Using COPY FROM on a subset of the file's column

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