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.

score 29 · Accepted Answer · answered Jul 11 '16 at 11:45

29

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)

answered Jul 11 '16 at 11:45

Clodoaldo Neto

118,695
26
233
260

1

Can i also ignore some columns? – Amin Shojaei Apr 25 '20 at 11:59
2

Is there a performance penalty if the columns are not in the same order as they're stored? – Mukul Gupta May 14 '20 at 03:17

score 0 · Answer 2 · answered Sep 17 '18 at 15:27

Assuming the order of the columns we need is the one of the table from which we are copying the results, the next logical step would be to simulate a sub-query through a Bash script.

psql schema_origin -c 'COPY table_origin TO stdout' | \
psql schema_destination -c \
"$(echo 'COPY table_destination (' \
$(psql schema_origin -t -c "select string_agg(column_name, ',') \
from information_schema.columns where table_name = 'table_origin'") \
') FROM stdin')"

StackOverflow answer on COPY command
StackExchange answer on fetching column names
StackOverflow answer on fetching results as tuples

score 0 · Answer 3 · answered Jan 30 '20 at 11:22

I came up with the following setup for making COPY TO/FROM successful even for quite sophisticated JSON columns:

COPY "your_schema_name.yor_table_name" (
    SELECT string_agg(
        quote_ident(column_name), 
        ','
    ) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'yuour_table_name' 
    AND TABLE_SCHEMA = 'your_schema_name'
) FROM STDIN WITH CSV DELIMITER E'\t' QUOTE '\b' ESCAPE '\';
--here rows data
\.

the most important parts:

be explicit in filtering from information_schema.columns and user also the table_schema. Otherwise, you may end up with unexpected columns when one table name occurs in multiple schemas.
use quote_ident to make sure your command does not crash if someone made poor naming of table columns using Postgres registred keywords like user or unique. Thanks to quote_ident you will get them wrapped in double-quotes what makes them safe for importing.
I also found the following setup:
- QUOTE '\b' - quote with backspace
- DELIMITER E'\t' - delimiter with tabs
- ESCAPE '\' - and escape with a backslash
for making both COPY to and from most reliable also for dealing with sophisticated/nested JSON columns.

Doesn't the SELECT of the column_names just replicate the default behaviour when no parens and column_names are listed? — cstork, Jan 04 '21 at 16:32

COPY column order

3 Answers3