Re: Problem with pg_dump and decimal mark

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Eric Svenson <esvenson74(at)googlemail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Problem with pg_dump and decimal mark
Date: 2014-11-28 22:19:10
Message-ID: 5478F4DE.6030006@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua Boyd 2014-11-28 22:29:40 Postgres 9.2 PITR testing to before DROP DATABASE ends up removing file system files
Previous Message Tom Lane 2014-11-28 22:08:36 Re: Problem with pg_dump and decimal mark