Thread: chop off non-meaningful digits

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

Thanks
SWK

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:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

"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