BUG #15192: Implement option to use columns order defined at CSV

On Thu, May 10, 2018 at 1:18 PM, PG Bug reporting form
<noreply@postgresql.org> wrote:

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.

As many have been faced before..

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"

That's not a bug, that's pilot error, explained by yourself.

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;" ||:

I normally use explicit column list on both sides, but that is good (
as long as first line is fully double quoted to avoid capitalisation
errors ), so
error has already been solved.

Implement option to force postgres to use columns order from CSV

That is a feature request, not a bug. I think there are better places
to post those.

Francisco Olarte.

Yes. This is feature request.
May you please provide link to better places. I will post this there.

10.05.2018, 17:59, "Francisco Olarte" <folarte@peoplecall.com>: