precision and scale functions for numeric
I didn't find a good way to find out how many digits a numeric value has
or things like whether a numeric value is an integer. (I had to go
through bc(1) for the latter.) Functions like precision() and scale()
would have been quite handy. Are there other ways to do this, or would
this make a good addition?
On Sun, Apr 29, 2012 at 1:51 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
I didn't find a good way to find out how many digits a numeric value has
or things like whether a numeric value is an integer. (I had to go
through bc(1) for the latter.) Functions like precision() and scale()
would have been quite handy. Are there other ways to do this, or would
this make a good addition?
I think you could test for integer-ness by testing whether val % 0 = 0.
You could in general test of digits after the decimal point by casting
the value to text and using POSITION() to find the decimal point. If
POSITION() returns zero then zero; else subtract the result from the
string length. Similarly, it's pretty easy to regexp away the
non-digits and measure the length of the resulting string.
I don't have a good feeling for whether these operations are common
enough to justify adding a few more functions. They haven't yet come
up for me personally.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Robert Haas
Sent: Monday, April 30, 2012 2:20 PM
To: Peter Eisentraut
Cc: pgsql-hackers
Subject: Re: [HACKERS] precision and scale functions for numericI think you could test for integer-ness by testing whether val % 0 = 0.
Either I am missing something here or you are. Since Modulus is a division
function anything "% 0" results in a division-by-zero ERROR - division has
to occur before a remainder can be obtained.
Maybe "val % 2 NOT IN (0,1)" ...
David J.
On Mon, Apr 30, 2012 at 3:33 PM, David Johnston <polobo@yahoo.com> wrote:
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
owner@postgresql.org] On Behalf Of Robert Haas
Sent: Monday, April 30, 2012 2:20 PM
To: Peter Eisentraut
Cc: pgsql-hackers
Subject: Re: [HACKERS] precision and scale functions for numericI think you could test for integer-ness by testing whether val % 0 = 0.
Either I am missing something here or you are. Since Modulus is a division
function anything "% 0" results in a division-by-zero ERROR - division has
to occur before a remainder can be obtained.Maybe "val % 2 NOT IN (0,1)" ...
Oops. I meant (val % 1) = 0, not (val % 0) = 0.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
"David Johnston" <polobo@yahoo.com> wrote:
I think you could test for integer-ness by testing whether val % 0 =
0.
Modulus is a division function anything "% 0" results in a
division-by-zero
It seems pretty clear that he meant "% 1".
test=# select '1.01'::numeric % 1;
?column?
----------
0.01
(1 row)
-Kevin