Does the PostgreSQL COPY command have the option of choosing which fields to map the CSV columns to?

Question

Does the PostgreSQL COPY command have the option of choosing which fields to map the CSV columns to?

The PostgreSQL COPY command appears to expect that the target table matches its columns exactly. Am I missing something or is that how it actually works?

Is there some alternative command that enables that?

Answer 1

It is absolutely possible - the ever helpful documentation comes to the rescue, again:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

Which means you can do something like this:

COPY my_table (mt_id, mt_name, mt_created_by, ...)
    FROM 'filename' [...]

What you cannot do is to refer columns of the CSV file. To overcome this, one can create an intermediate table with the matching number and type of columns, do the COPY into it, then do an INSERT ... SELECT ... to the final destination. Based on an important remark from Patrick7, the intermediate table can be defined as UNLOGGED, saving a lot of WAL overhead when the table is big.

Answer 2

Grab yourself a copy of CSVKit

in2csv foo.csv 
  | csvcut -C "columnIDontWant,AnotherIDontWant" 
  | csvsql --db postgresql://localhost/dbname --insert --tables Foo --no-create

Or csvquote

csvquote | cut -d, -f... | csvquote - u | psql ...