Thread: select to_number('1,000', '999,999');
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : David Schweikert
Your email address : dws@ee.ethz.ch
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium 4
Operating System (example: Linux 2.4.18) : 2.4.25
PostgreSQL version (example: PostgreSQL-7.4.6): PostgreSQL-7.4.6
Compiler used (example: gcc 2.95.2) : gcc 3.3.3
Please enter a FULL description of your problem:
------------------------------------------------
select to_number('1,000', '999,999') returns '100'.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
Proposed addition to the regression tests:
--- src/test/regress/sql/numeric.sql.orig 2000-04-07 21:17:50.000000000 +0200
+++ src/test/regress/sql/numeric.sql 2004-11-19 12:51:57.071152000 +0100
@@ -701,3 +701,4 @@
SELECT '' AS to_number_11, to_number('.-01', 'S99.99');
SELECT '' AS to_number_12, to_number('.01-', '99.99S');
SELECT '' AS to_number_13, to_number(' . 0 1 -', ' 9 9 . 9 9 S');
+SELECT '' AS to_number_14, to_number(to_char('1000'::int, '999G999'),'999G999');
--- src/test/regress/expected/numeric.out.orig 2003-09-25 08:58:06.000000000 +0200
+++ src/test/regress/expected/numeric.out 2004-11-19 12:56:13.464401000 +0100
@@ -1112,3 +1112,9 @@
| -0.01
(1 row)
+SELECT '' AS to_number_14, to_number(to_char('1000'::int, '999G999'),'999G999');
+ to_number_14 | to_number
+--------------+-----------
+ | 1000
+(1 row)
+
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
David Schweikert <dws@ee.ethz.ch> writes:
> select to_number('1,000', '999,999') returns '100'.
I'm not entirely convinced this is a bug. I get the right answer from
regression=# select to_number('001,000', '999,999') ;
to_number
-----------
1000
(1 row)
It's arguable that to_number() should throw an error when the input
doesn't match the format, but right now it doesn't ...
regards, tom lane
On Sun, Nov 21, 2004 at 20:10:08 -0500, Tom Lane wrote:
> I'm not entirely convinced this is a bug. I get the right answer from
>
> regression=# select to_number('001,000', '999,999') ;
> to_number
> -----------
> 1000
> (1 row)
>
> It's arguable that to_number() should throw an error when the input
> doesn't match the format, but right now it doesn't ...
It seems strange to me that to_char(1000,'999,999') works (it returns
1,000), but the reverse doesn't.
I want to convert a formatted number with group separators, but I don't
know how many digits it has: should I count the digits myself and adapt
the mask (which is a customization and thus entered by the user)?
Cheers
David
--
David Schweikert | phone: +41 44 632 7019
System manager ISG.EE | walk: ETH Zentrum, ETL F24.1
ETH Zurich, Switzerland | web: http://people.ee.ethz.ch/dws
On Mon, 22 Nov 2004, David Schweikert wrote:
> On Sun, Nov 21, 2004 at 20:10:08 -0500, Tom Lane wrote:
> > I'm not entirely convinced this is a bug. I get the right answer from
> >
> > regression=# select to_number('001,000', '999,999') ;
> > to_number
> > -----------
> > 1000
> > (1 row)
> >
> > It's arguable that to_number() should throw an error when the input
> > doesn't match the format, but right now it doesn't ...
>
> It seems strange to me that to_char(1000,'999,999') works (it returns
> 1,000), but the reverse doesn't.
>
> I want to convert a formatted number with group separators, but I don't
> know how many digits it has: should I count the digits myself and adapt
> the mask (which is a customization and thus entered by the user)?
No, but I think you're supposed to use FM in such cases.
select to_number(1000, 'FM999,999');
On Mon, 22 Nov 2004, Stephan Szabo wrote:
> On Mon, 22 Nov 2004, David Schweikert wrote:
>
> > On Sun, Nov 21, 2004 at 20:10:08 -0500, Tom Lane wrote:
> > > I'm not entirely convinced this is a bug. I get the right answer from
> > >
> > > regression=# select to_number('001,000', '999,999') ;
> > > to_number
> > > -----------
> > > 1000
> > > (1 row)
> > >
> > > It's arguable that to_number() should throw an error when the input
> > > doesn't match the format, but right now it doesn't ...
> >
> > It seems strange to me that to_char(1000,'999,999') works (it returns
> > 1,000), but the reverse doesn't.
> >
> > I want to convert a formatted number with group separators, but I don't
> > know how many digits it has: should I count the digits myself and adapt
> > the mask (which is a customization and thus entered by the user)?
>
> No, but I think you're supposed to use FM in such cases.
>
> select to_number(1000, 'FM999,999');
Of course, I don't think the fact that it does that is actually documented
in the 7.4 docs now that I look. I must have just run into it through
experimentation, so I wonder if that's actually intended behavior or not.
On Mon, Nov 22, 2004 at 05:47:19 -0800, Stephan Szabo wrote: >No, but I think you're supposed to use FM in such cases. > >select to_number(1000, 'FM999,999'); Indeed, it works with 'FM'. Thanks! David -- David Schweikert | phone: +41 44 632 7019 System manager ISG.EE | walk: ETH Zentrum, ETL F24.1 ETH Zurich, Switzerland | web: http://people.ee.ethz.ch/dws
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> No, but I think you're supposed to use FM in such cases.
>
> select to_number(1000, 'FM999,999');
Good point --- I had forgot about FM. In that case there *is* a bug
here, but I'm not sure if it's with to_char or to_number:
regression=# select to_number(to_char(1000, 'FM999,999'),'FM999,999');
to_number
-----------
1000
(1 row)
regression=# select to_number(to_char(1000, '999,999'),'999,999');
to_number
-----------
100
(1 row)
Whatever your opinion is about the behavior of the non-FM format, surely
to_char and to_number should be inverses.
regards, tom lane
On Mon, 2004-11-22 at 11:08 -0500, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > No, but I think you're supposed to use FM in such cases.
> >
> > select to_number(1000, 'FM999,999');
>
> Good point --- I had forgot about FM. In that case there *is* a bug
> here, but I'm not sure if it's with to_char or to_number:
>
> regression=# select to_number(to_char(1000, 'FM999,999'),'FM999,999');
> to_number
> -----------
> 1000
> (1 row)
>
> regression=# select to_number(to_char(1000, '999,999'),'999,999');
> to_number
> -----------
> 100
> (1 row)
It's to_number() bug. I'm not sure if now (before release) is good time
to fix it. The code of to_number() is not stable for changes and maybe
we can fix this bug add some other new...
I already work on new version for next release. It will use
unit-tests -- I hope it will prevent a lot of bugs like this.
> Whatever your opinion is about the behavior of the non-FM format, surely
> to_char and to_number should be inverses.
Yes.
Karel
--
Karel Zak
http://home.zf.jcu.cz/~zakkr
Hi Karel, On Tue, Nov 23, 2004 at 09:39:21 +0100, Karel Zak wrote: > It's to_number() bug. I'm not sure if now (before release) is good time > to fix it. The code of to_number() is not stable for changes and maybe > we can fix this bug add some other new... I have the workaround with 'FM' so it is not urgent for me... > I already work on new version for next release. It will use > unit-tests -- I hope it will prevent a lot of bugs like this. Sounds great, thank you. Cheers David -- David Schweikert | phone: +41 44 632 7019 System manager ISG.EE | walk: ETH Zentrum, ETL F24.1 ETH Zurich, Switzerland | web: http://people.ee.ethz.ch/dws