Thread: SQL copy from csv with explicit field ordering

I guess this is really a SQL question:

I have a csv that I want to import, but the csv has different column
ordering.

I have tried putting the column names in the first row, but then the
copy command fails on field which is data type (eg it is seeing the
cells in first row as data, not header info).

ggtest=> copy ap_trans from '/var/dbase/ap_trans.csv' using delimiters
',';
ERROR:  pg_atoi: error in "id": can't parse "id"


The help indicates:
ggtest=> \h copy
Command: copy
Description: copy data to and from a table
Syntax:       COPY [BINARY] class_name [WITH OIDS]       TO|FROM filename|STDIN|STDOUT [USING DELIMITERS 'delim'];


I have tried WITH OIDS but with same results.

Is there somewhere that I can either enable the first line of CSV as
header names

OR

Can I explicitly define my import field ordering from within the select
statement?

Thanks

Terry Fielder
terry@greatgulfhomes.com

Terry Fielder writes:

> Is there somewhere that I can either enable the first line of CSV as
> header names
>
> OR
>
> Can I explicitly define my import field ordering from within the select
> statement?

No and no.  You will have to preprocess your file.  Something like this
will probably do:

#! /bin/sh
IFS=,
cat "inputfile" | \
while read COL1 COL2 COL3; do   echo "$COL3,$COL2,$COL1" >> "outputfile"
done

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/