Обсуждение: 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)
select round(1.2000::numeric, 1);
Regards,
--------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 13 Nov 2006 05:45:44 -0800, SunWuKung <Balazs.Klein@t-online.hu> wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
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
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
On 11/14/06, Berend Tober <btober@seaworthysys.com> wrote:
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.