Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
PG Bug reporting form
Date:
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

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

From
Francisco Olarte
Date:
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.

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

From
KES
Date:
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>:
> 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.