Re: to_number, to_char inconsistency. - Mailing list pgsql-general

On 10 February 2013 20:50, Jeremy Lowery <jslowery@gmail.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