Re: 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(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 15192
> Logged by: Eugen Konkov
> Email address: kes-kes(at)yandex(dot)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.