Re: Problem with pg_dump and decimal mark

On 11/29/2014 12:25 AM, Eric Svenson wrote:
>
>
>
> Seems you have a locale mismatch issue. The dump is coming
> from a locale
> where a '.' is the decimal mark and is being restored to a
> locale where
> ',' is the mark. Look at what the locales are the machines
> that work and
> the one that does not.
>
>
>
> 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?
Postgres version?
What was the pg_dump command used?

>
> On the first PC on which I tried to load the backup file with psql, all
> locale settings if Postgres were German_Germany. Everything is ok, the
> SQL file with '.' as decimal point was accepted without a problem

OS and OS version?
Postgres version?
How was it loaded via psql?
Was the psql on the same machine and from the same version of Postgres?

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

OS and OS version?
Postgres version?
How was it loaded via psql?
Did you use the psql on the VM or did you use the psql on the host?
Was the Postgres/psql on the host the same as the VM?
What are you using for virtualization?
What is host OS?

>
> -> no success
>
> I tried to change all the settings to English/United States, restart
> postgres
>
> -> still no success
>
> Changed all Windows settings to English / United States
>
> -> still no success.
>
> So what I am searching for (at the moment without success) is the
> 'switch' which decides what decimal seperator to expect by psql.

Well according to below, flipping a switch is not necessary. There is a
mismatch occurring, which is why I posed all the questions above. To
narrow the possibilities it would help to a have fuller picture of what
the environment is in each situation.

>
> That's what it sounds like all right, but how could that be?
> The behavior
> of float8in/float8out is not supposed to be locale-dependent.
>
> float8in does depend on strtod(), whose behavior is locale-dependent
> according to POSIX, but we keep LC_NUMERIC set to "C" to force it to
> only believe that "." is decimal point.
>
>
> Not sure if this makes a difference but if I am reading the original
> post correctly the OP was trying a plain text restore via psql.
>
>
> This is correct.
>
>
> regards and thanks for your support,
> Eric Svenson
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com