Re: Problem with pg_dump and decimal mark

On 11/28/2014 02:08 PM, Tom Lane wrote:
> 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.

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.

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

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