Re: Problem with pg_dump and decimal mark

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> On 11/28/2014 05:35 AM, Eric Svenson wrote:
>> I have done a backup of a postgres database on a virtual machine
>> (Windows 8.1) using pg_dump.
>>
>> On another (non-virtual) machine the restore (with psql) worked without
>> problems.
>>
>> On the third virtual machine, however, the restore fails.
>>
>> (ERROR: invalid input syntax for type double precision: 0.100000000001)
>>
>> When I change the value in the sql file manually to 0,100000000001 the
>> and try again, the restore resumes until the next double value.
>>
>> How is this possible? Does psql really expect comma-seperated decimal
>> values in the sql file? How can I change this behaviour?

> 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.

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.

Of course, this wouldn't be the first time we've found out that Microsoft
can't read the POSIX spec :-(.

regards, tom lane