COPY column order

Question

I'm trying to use COPY with HEADER option but my header line in file is in different order than the column order specified in database. Is the column name order necessary in my file ??

My code is as below:

COPY table_name (                                  
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'schema_name'
  AND table_name   = 'table_name'

            )                        
       FROM 'file.csv'  
       WITH DELIMITER ',' CSV HEADER;

My database table has got a different order from file.csv and i wanted to select the table order and copy data from csv to table.

Answer 1

You can't issue an SQL query in copy from. You can only list the columns.

If the CSV columns are in the b, a, c order then list that in the copy from command:

copy target_table (b, a, c)
from file.csv
with (delimiter ',', format csv, header)