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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
