chop off non-meaningful digits

On 13 Nov 2006 at 5:45, SunWuKung wrote:

What would be the easiest way to get back only the meaningful digits of
a numeric value in a pgsql function? eg?

There are various rounding functions built in... have a look at
http://www.postgresql.org/docs/8.1/static/functions-math.html

--Ray.

----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------

ROUND function might help you there:

select round(1.2000::numeric, 1);

Regards,
--------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

am Mon, dem 13.11.2006, um 5:45:44 -0800 mailte SunWuKung folgendes:

What would be the easiest way to get back only the meaningful digits of
a numeric value in a pgsql function? eg?

1.002 --> 1.002
1.020 --> 1.02
1.200 --> 1.2
1.000 --> 1

You can use trim for this:

test=*> select 1.020 + 0.01;
?column?
----------
1.030
(1 row)

test=*> select trim(trailing '0' from 1.020 + 0.01);
rtrim
-------
1.03
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Yep, I think this is it:
select trim(trailing '0.' from 1.020)

Many thanks.
SWK

"A. Kretschmer" wrote:

"SunWuKung" <Balazs.Klein@t-online.hu> writes:

Yep, I think this is it:
select trim(trailing '0.' from 1.020)

Um, I think not:

regression=# select trim(trailing '0.' from 1000.000);
rtrim
-------
1
(1 row)

regards, tom lane

am Tue, dem 14.11.2006, um 0:58:56 -0500 mailte Tom Lane folgendes:

"SunWuKung" <Balazs.Klein@t-online.hu> writes:

Yep, I think this is it:
select trim(trailing '0.' from 1.020)

Um, I think not:

regression=# select trim(trailing '0.' from 1000.000);
rtrim
-------
1
(1 row)

;-)

For this case:

test=*> select trim(trailing '.' from trim(trailing '0' from 1000.000));
rtrim
-------
1000
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

A. Kretschmer wrote:

am Tue, dem 14.11.2006, um 0:58:56 -0500 mailte Tom Lane folgendes:

"SunWuKung" <Balazs.Klein@t-online.hu> writes:

Yep, I think this is it:
select trim(trailing '0.' from 1.020)

Um, I think not:

regression=# select trim(trailing '0.' from 1000.000);
rtrim
-------
1
(1 row)

For this case:

test=*> select trim(trailing '.' from trim(trailing '0' from 1000.000));
rtrim
-------
1000

Perhaps the OP doesn't really care about this, but FWIW one thing I
recall from high school science (or maybe it was middle school), the
zeros to the right of the decimal place ARE meaningful, i.e., 1000.000
signifies something different than 1000, namely, a measurement three
orders of magnitude more precise. So "chopping off non-meaningful
digits" is something you ought to do only for zeros on the left.

I just noticed this one:

postgres=# select 1000.000::float;
float8
--------
1000
(1 row)

postgres=# select 1000.0001::float;
float8
-----------
1000.0001
(1 row)

postgres=# select 1000.000100::float;
float8
-----------
1000.0001
(1 row)

postgres=#

HTH,

Best regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com