How to force postgres to use columns order from csv when dumping/restore data?

Question

I dump my table data:

COPY( SELECT * FROM tariff_details ) TO STDOUT WITH( FORMAT CSV, HEADER )

The data:

id,tariff_id,name,price,option,periodic,value,sorder
17,1,Setup fee,5.000000000000000000,,f,,0

When I restore the data:

COPY tariff_details FROM STDIN WITH( FORMAT CSV, HEADER )

I get the error:

ERROR:  null value in column "periodic" violates not-null constraint
DETAIL:  Failing row contains (17, 1, Setup fee, 5.000000000000000000, null, f, null, 0).
CONTEXT:  COPY tariff_details, line 2: "17,1,Setup fee,5.000000000000000000,,f,,0"

The table in database is defined as next:

  Column   |         Type          |                          Modifiers                          
-----------+-----------------------+-------------------------------------------------------------
 id        | integer               | not null default nextval('tariff_details_id_seq'::regclass)
 tariff_id | integer               | not null
 name      | character varying(64) | not null
 price     | tmoney                | not null
 periodic  | boolean               | not null default false
 option    | character varying(16) | 
 value     | text                  | 
 sorder    | integer               | not null default 0

As you can see the fields option and periodic are flipped.

Postgres documentation says:

HEADER

Specifies that the file contains a header line with the names of each column in the file. On output, the first line contains the column names from the table, and on input, the first line is ignored. This option is allowed only when using CSV format.

How to to tell postgres to use columns order from CSV file? Is it possible?

UPD

As work around I use: line=$$(head -n 1 ${APP_ROOT}/db/${TABLE}.dump.csv)

dbrestoretable: export PGPASSWORD =  ${DB_PASS}
dbrestoretable:
    line=$$(head -n 1 ${APP_ROOT}/db/${TABLE}.dump.csv)
    @cat ${APP_ROOT}/db/${TABLE}.dump.csv | \
        psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} ${DB_NAME} -c \
            "BEGIN;COPY ${TABLE}($$line) FROM STDIN WITH( FORMAT CSV, HEADER );COMMIT;"  ||:

`COPY tariff_details (list,of,your,columns,here,in,order) FROM STDIN WITH( FORMAT CSV, HEADER )` And the same can be done for`COPY ...TO...;` — wildplasser, May 10 '18 at 10:58
@wildplasser: No. This is makefile. As parameter it get table name. We should not require columns from user. — Eugen Konkov, May 10 '18 at 11:00
There is no Makefile and no user in your question. The bad news is: there is no *order* in the columns if you use `SELECT *` — wildplasser, May 10 '18 at 11:01
RE(UPD) you could try to replace `\t` in $$line by `,` , using sed or tr. — wildplasser, May 10 '18 at 11:33

score 1 · Answer 1 · edited May 10 '18 at 15:57

1

You can just specify target column names in COPY FROM:

COPY tariff_details(id,tariff_id,name,option,price,periodic,value,sorder)
FROM STDIN WITH (FORMAT CSV, HEADER);

(I flipped option and periodic.)

Aside: SELECT * FROM tbl does return columns in deterministic order - as defined in pg_attribute.attnum. So something had have changed in your DB between dump and restore.

edited May 10 '18 at 15:57

Eugen Konkov

22,193
17
108
158

answered May 10 '18 at 15:52

Erwin Brandstetter

605,456
145
1,078
1,228

As you can see I did this: `COPY ${TABLE}($$line) FROM STDIN` – Eugen Konkov May 10 '18 at 15:58

score 0 · Answer 2 · answered Jul 08 '18 at 08:20

As work around I use: columns=$$(head -n 1 ${APP_ROOT}/db/${TABLE}.dump.csv)

dbrestoretable: export PGPASSWORD =  ${DB_PASS}
dbrestoretable:
    columns=$$(head -n 1 ${APP_ROOT}/db/${TABLE}.dump.csv)
    @cat ${APP_ROOT}/db/${TABLE}.dump.csv | \
        psql -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} ${DB_NAME} -c \
            "BEGIN;COPY ${TABLE}($$columns) FROM STDIN WITH( FORMAT CSV, HEADER );COMMIT;"  ||:

How to force postgres to use columns order from csv when dumping/restore data?

2 Answers2