Re: to_number, to_char inconsistency.

[Szymon Guz <mabewlun@xxxxxxxxx>]

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