Re: Fwd: Problem with pg_dump and decimal mark

[Eric Svenson <esvenson74@xxxxxxxxxxxxxx>]

> So what if you load to the VM Postgres using the psql from the host?

I tried that: On the host I started psql with

psql -h 192.168.2.55 -U postgres -p 5432 my_Database < myFile.sql

I get the same error

ERROR: invalid input syntax for type double precision "0.2"

KONTEXT: COPY dev_my_settings, line xxx, column xxx: "0.2"

ERROR: invalid input syntax for type double precision "15.776653623"

Regards,
Eric Svenson

2014-12-04 11:00 GMT+01:00 Eric Svenson <esvenson74@xxxxxxxxxxxxxx>:

> So what if you load to the VM Postgres using the psql from the host?

I tried that: On the host I started psql with

psql -h 192.168.2.55 -U postgres -p 5432 my_Database < myFile.sql

I get the same error

ERROR: invalid input syntax for type double precision "0.2"

KONTEXT: COPY dev_my_settings, line xxx, column xxx: "0.2"

ERROR: invalid input syntax for type double precision "15.776653623"

Regards,
Eric Svenson

2014-12-03 16:24 GMT+01:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:

On 12/03/2014 12:23 AM, Eric Svenson wrote:

Hi Adrian,

thank you for your support. Here are the informations you requested


    I have already done that and found something strange:

    On the PC where the backup was done with pg_dump, all locale settings of
    Postgres were English/United States. (LC_COLLATE, LC_CTYPE, LC_MONETARY,
    LC_NUMERIC in postgresql.conf)


 > OS and OS version?

Windows Server 2012R2

 > Postgres version?

Version 9.2.1 (same version used on all machines)

 > What was the pg_dump command used?

pg_dump -p 6789 -EUTF8 -f myFile.sql my_Database

 > How was it loaded via psql?

psql -p 6789 -U postgres my_Database < myFile.sql

 > Was the psql on the same machine and from the same version of Postgres?

Same version, but on different machines.


    On the second PC (Virtual Machine) I had the SAME settings in
    postgresql.conf (German_Germany)


 > OS and OS version?

Windows 7 Ultimate SP 1

 > Postgres version?

Version 9.2.1 (same version used on all machines)

 > How was it loaded via psql?

psql -p 6789 -U postgres my_Database < myFile.sql

 > Did you use the psql on the VM or did you use the psql on the host?

psql on the VM

So what if you load to the VM Postgres using the psql from the host?

Again, thank you for the support.

As Tom said LC_NUMERIC is set to C in main.c:

/*
         * We keep these set to "C" always, except transiently in pg_locale.c; see
         * that file for explanations.
         */

The transiently part intrigued me so I went to pg_locale.c and there was a big:

* !!! NOW HEAR THIS !!!

with regards to resetting locales. Honestly the explanation is beyond my expertise. I offer it only as a starting point for those that can understand it.

Regards,
Eric Svenson

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx