to_number, to_char inconsistency.

From: Jeremy Lowery <jslowery(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: to_number, to_char inconsistency.
Date: 2013-02-10 19:50:46
Message-ID: CAPzVRBq32NQRx9+WZboOCRdUS_V__O9TWq76_hUWTLE=5oiJNg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Szymon Guz 2013-02-10 20:24:13 Re: to_number, to_char inconsistency.
Previous Message Kenneth Tilton 2013-02-10 17:20:14 Re: Swapping volumes under tablespaces: supported?