to_number, to_char inconsistency.

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

Jeremy Lowery <jslowery@gmail.com> writes:

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');

A look at the source code shows that to_number doesn't do anything at
all with the V format code, so this isn't terribly surprising. It
wouldn't be very hard to make it do the right thing, probably, but
nobody's had that particular itch yet. Feel free to scratch it and
send a patch ...

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Feb 10, 2013 at 06:27:02PM -0500, Tom Lane wrote:

Jeremy Lowery <jslowery@gmail.com> writes:

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');

A look at the source code shows that to_number doesn't do anything at
all with the V format code, so this isn't terribly surprising. It
wouldn't be very hard to make it do the right thing, probably, but
nobody's had that particular itch yet. Feel free to scratch it and
send a patch ...

(This is for 9.6.)

I have developed the attached patch to support 'V' with to_number().
Oracle doesn't support that, so we are on our own in defining the API.

The patch doesn't handle non-whole-number strings very well as there is
no way for the user to specify decimal precision because we have
overridden the decimal digit meaning, but that seems fine to me as most
users will be using whole numbers.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Thu, May 14, 2015 at 01:02:01PM -0400, Bruce Momjian wrote:

On Sun, Feb 10, 2013 at 06:27:02PM -0500, Tom Lane wrote:

Jeremy Lowery <jslowery@gmail.com> writes:

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');

A look at the source code shows that to_number doesn't do anything at
all with the V format code, so this isn't terribly surprising. It
wouldn't be very hard to make it do the right thing, probably, but
nobody's had that particular itch yet. Feel free to scratch it and
send a patch ...

(This is for 9.6.)

I have developed the attached patch to support 'V' with to_number().
Oracle doesn't support that, so we are on our own in defining the API.

The patch doesn't handle non-whole-number strings very well as there is
no way for the user to specify decimal precision because we have
overridden the decimal digit meaning, but that seems fine to me as most
users will be using whole numbers.

Patch applied.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general