Thread: round(real,integer)
History question: Why does select round(3,3) work, select round(3.0,3) work, but select round(3.0::real,1) not work? There's a utility cast in the integer case (described here http://www.postgresql.org/docs/9.3/static/typeconv-func.html),but not in the real case. Is this on purpose, or just an oversight? Obviously one can work around it, but I'm sick of doing so, it makes my SQL examples ugly. I’d like to fix it, but not ifthere’s a reason it’s not supposed to be done. P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net
Paul Ramsey <pramsey@cleverelephant.ca> writes:
> History question:
> Why does select round(3,3) work,
> select round(3.0,3) work,
> but select round(3.0::real,1) not work?
The 2-argument round() function actually takes (numeric, integer).
There's an implicit cast from int to numeric, but not an implicit
cast from real to numeric (for that pair of types, the direction
of implicit casting is from numeric to real).
The choices we made for implicit casting behavior among the numeric
datatypes are based on what it says in the SQL standard about exact and
approximate numeric types. There's probably room for argument about
the details, but it seems unlikely that we'd risk the breakage that'd
ensue from rejiggering the casting rules at this stage.
If this annoys you enough, a more realistic solution would be to add
an additional round(float8, int) function. I think it'd have to return
numeric though if you don't want surprises.
regards, tom lane
What surprises do you think would come from a
round(real, integer) returns real
function?
Just asking the question, I guess I can see the answer, since though round() is usually used to reduce precision, it’s also possible to use it to increase it arbitrarily… bah.
It does bug me a fair bit, so perhaps I’ve finally found my bite-size pgsql contribution project :)
P.
Paul Ramsey
http://cleverelephant.ca
http://postgis.net
On January 10, 2014 at 12:37:04 PM, Tom Lane (tgl@sss.pgh.pa.us) wrote:
Paul Ramsey <pramsey@cleverelephant.ca> writes:
> History question:
> Why does select round(3,3) work,
> select round(3.0,3) work,
> but select round(3.0::real,1) not work?
The 2-argument round() function actually takes (numeric, integer).
There's an implicit cast from int to numeric, but not an implicit
cast from real to numeric (for that pair of types, the direction
of implicit casting is from numeric to real).
The choices we made for implicit casting behavior among the numeric
datatypes are based on what it says in the SQL standard about exact and
approximate numeric types. There's probably room for argument about
the details, but it seems unlikely that we'd risk the breakage that'd
ensue from rejiggering the casting rules at this stage.
If this annoys you enough, a more realistic solution would be to add
an additional round(float8, int) function. I think it'd have to return
numeric though if you don't want surprises.
regards, tom lane
Paul Ramsey <pramsey@cleverelephant.ca> writes:
> What surprises do you think would come from a
> round(real, integer) returns real
> function?
People might expect that rounding to, say, 6 digits produces an exact
decimal answer. Even if you're not exceeding 6 digits overall, it's
unlikely that the answer is *exact*, if it's represented as a float.
regards, tom lane
True (?) though I’m guessing the real test for most folks is if printf renders it as expected. Anything else if icing on the cake, no?
P
Paul Ramsey
http://cleverelephant.ca
http://postgis.net
On January 10, 2014 at 1:09:24 PM, Tom Lane (tgl@sss.pgh.pa.us) wrote:
to, say, 6 digits produces an exact
decimal answer. Even if you're not exceeding 6 digits overall, it's
unlikely that the answ