Thread: numeric to text (7.3)
Hello
In PostgreSQL 7.2/7.1:
template1=# select text(2.000::numeric);text
------2
(1 row)
In 7.3:
template1=# select text(2.000::numeric);text
-------2.000
(1 row)
The text(numeric) function doesn't round numbers. :(
This is bug or feature? :)
-Sygma
> template1=# select text(2.000::numeric); > text > ------- > 2.000 > (1 row) > > The text(numeric) function doesn't round numbers. :( > > This is bug or feature? :) I'd say feature in that it doesn't reduce the precision of the number. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
On Mon, Dec 02, 2002 at 01:35:47PM -0500, Rod Taylor wrote:
> > template1=# select text(2.000::numeric);
> > text
> > -------
> > 2.000
> > (1 row)
> >
> > The text(numeric) function doesn't round numbers. :(
> >
> > This is bug or feature? :)
>
> I'd say feature in that it doesn't reduce the precision of the number.
... and, of course, you can round with:
joel@joel=# select round('2.000'::numeric);round
------- 2
(1 row)
joel@joel=# select round('2.000'::numeric,2);round
------- 2.00
(1 row)
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
> This feature is missing from 7.3..
>
> (new round function is good idea (e.g. fround(numeric))
double precision | pg_catalog | round | double precisionnumeric | pg_catalog | round |
numericnumeric | pg_catalog | round | numeric, integer
Looks like round still exists to me.
rbt=# select round('2.4555', 2);round
------- 2.46
(1 row)
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
On Mon, Dec 02, 2002 at 08:23:24PM +0100, Szima Gábor wrote:
> OK, but:
>
> template1=# select round('2.001'::numeric);
> round
> -------
> 2
> (1 row)
>
> template1=# select round('2.001'::numeric,2);
> round
> -------
> 2.00
> (1 row)
>
>
> The good idea (in 7.2):
>
> template1=# select text('2.000'::numeric);
> text
> ------
> 2
> (1 row)
>
> template1=# select text('2.001'::numeric);
> text
> -------
> 2.001
> (1 row)
>
>
> This feature is missing from 7.3..
Not sure I'd call it a feature -- ISTM that text(numeric) should show
all the precision you gave it, and not shave it down to the
least-precise number that is still equal.
Anyhoo, you can get what you want with some ugly-but-straightforward
trimming:
(in 7.3):
joel@joel=# select rtrim(rtrim('2.000'::numeric, '0'),'.');rtrim
-------2
(1 row)
joel@joel=# select rtrim(rtrim('2.001'::numeric, '0'),'.');rtrim
-------2.001
(1 row)
Easy enough to make this into a function trim_as_much(numeric) or
somesuch.
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
On Mon, 2 Dec 2002, Joel Burton wrote:
> joel@joel=# select round('2.000'::numeric);
> round
> -------
> 2
> (1 row)
>
> joel@joel=# select round('2.000'::numeric,2);
> round
> -------
> 2.00
> (1 row)
OK, but:
template1=# select round('2.001'::numeric);round
------- 2
(1 row)
template1=# select round('2.001'::numeric,2);round
------- 2.00
(1 row)
The good idea (in 7.2):
template1=# select text('2.000'::numeric);text
------2
(1 row)
template1=# select text('2.001'::numeric);text
-------2.001
(1 row)
This feature is missing from 7.3..
(new round function is good idea (e.g. fround(numeric))
-Sygma
On Mon, 2 Dec 2002, Rod Taylor wrote:
> double precision | pg_catalog | round | double precision
> numeric | pg_catalog | round | numeric
> numeric | pg_catalog | round | numeric, integer
>
> Looks like round still exists to me.
Rod, you don't understand me. :)
I needn't round, or the valueless zeroes too.
It's good (in older version of pSQL):
2.000::numeric -> 2
2.001::numeric -> 2.001
It's "ugly" (in 7.3):
2.000::numeric -> 2.000
2.001::numeric -> 2.001
or
round(2.000::numeric,2) -> 2.00
round(2.001::numeric,2) -> 2.00
Joel had got a good idea:
joel@joel=# select rtrim(rtrim('2.000'::numeric, '0'),'.');rtrim
-------2
(1 row)
.. but i prefer the old text(numeric) function :)
Thanks!
-Sygma