================================================== ==========================
POSTGRESQL BUG REPORT TEMPLATE
================================================== ==========================


Your name : David Schweikert
Your email address : dws (AT) ee (DOT) 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 @@

+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:
---------------------------------------------------------------------



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

David Schweikert <dws (AT) ee (DOT) ethz.ch> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Sun, Nov 21, 2004 at 20:10:08 -0500, Tom Lane wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Mon, 22 Nov 2004, David Schweikert wrote:

No, but I think you're supposed to use FM in such cases.

select to_number(1000, 'FM999,999');

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Mon, 22 Nov 2004, Stephan Szabo wrote:

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Mon, Nov 22, 2004 at 05:47:19 -0800, Stephan Szabo wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Mon, 2004-11-22 at 11:08 -0500, Tom Lane 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 already work on new version for next release. It will use
unit-tests -- I hope it will prevent a lot of bugs like this.

Yes.

Karel

--
Karel Zak
http://home.zf.jcu.cz/~zakkr


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Hi Karel,

On Tue, Nov 23, 2004 at 09:39:21 +0100, Karel Zak wrote:
I have the workaround with 'FM' so it is not urgent for me...

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html