The following bug has been logged on the website:
Bug reference: 15192
Logged by: Eugen Konkov
Email address: kes-kes@yandex.ru
PostgreSQL version: 9.6.1
Operating system: SMP Debian 4.9.82-1+deb9u3
Description:
While dumping/restore data I faced into problem that column order are
different on different servers.
Restoring the data
COPY tariff_details FROM STDIN WITH( FORMAT CSV, HEADER )
cause 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"
To work around this problem I use next makefile:
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;" ||:
Implement option to force postgres to use columns order from CSV
For details: https://stackoverflow.com/q/50271162/4632019