From: | Szymon Guz <mabewlun(at)gmail(dot)com> |
---|---|
To: | Jeremy Lowery <jslowery(at)gmail(dot)com> |
Cc: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: to_number, to_char inconsistency. |
Date: | 2013-02-10 20:24:13 |
Message-ID: | CAFjNrYtu+58C62jG9M6VXikjhwBvnMsnFruiNoCp4O-Nzmf+8Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10 February 2013 20:50, Jeremy Lowery <jslowery(at)gmail(dot)com> wrote:
> I load and dump text files with currency values in it. The decimal in
> these input and output formats in implied. The V format character works
> great for outputing numeric data:
>
> # select to_char(123.45, '999V99');
> to_char
> ---------
> 12345
> (1 row)
>
>
> However, when importing data, the V doesn't do the same thing:
>
> # select to_number('12345', '999V99');
> ERROR: numeric field overflow
> DETAIL: A field with precision 3, scale 0 must round to an absolute value
> less than 10^3.
>
>
> So I have to do this:
> # select to_number('12345', '99999')/100;
>
> Is there an easier way to insert this into a NUMERIC(5, 2) field?
>
Hi Jeremy,
I've always been doing such conversions in quite a different way:
SELECT 123.45::TEXT;
The conversion from text to numeric I'd do like:
SELECT '12345'::NUMERIC(10,2)/100;
regards
Szymon
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Misch | 2013-02-10 21:02:59 | Re: BUG #7493: Postmaster messages unreadable in a Windows console |
Previous Message | Jeremy Lowery | 2013-02-10 19:50:46 | to_number, to_char inconsistency. |