SQL error: function round(double precision, integer) does not exist

[TJ O'Donnell]

I received the following error when executing a SQL statement:

SQL error:
ERROR: function round(double precision, integer) does not exist

In statement:

select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count,
round((parameter*oe_count_matches(smiles,smarts)),2) as psa,tpsa(smiles) as ctpsa,tpsa
from structure,tpsa
where id < 237610
and oe_count_matches(smiles,smarts) > 0

order by id;

The functions described at:
http://www.postgresql.org/docs/7.4/static/functions-math.html
show that round(numeric,int) should work ok.
If I use round() without a second argument, it works OK, but
this gives a loss of precision which I do not want.

Can anyone help me with this?

Thanks,
TJ

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Michael Fuhr]

Post by TJ O'Donnell
ERROR: function round(double precision, integer) does not exist

^^^^^^^^^^^^^^^^
[snip]

Post by TJ O'Donnell
http://www.postgresql.org/docs/7.4/static/functions-math.html
show that round(numeric,int) should work ok.

^^^^^^^

The two-argument form of round() expects the first argument to be
numeric, not double precision. There's no implicit cast from double
precision to numeric, so you'll have to use an explicit cast:

SELECT ... round((expression)::numeric, 2) ...

[TJ O'Donnell]

I got round(numeric,int) working OK, but it's got me thinking (a dangerous thing!).
Is there some fundamental reason for round(dp) but round(numeric,int)?
Shouldn't they be, at least, consistent, having round(numeric)
or round(dp,int)?
Am I missing something?

Thanks,
TJ

Post by Michael Fuhr

Post by TJ O'Donnell
ERROR: function round(double precision, integer) does not exist

^^^^^^^^^^^^^^^^
[snip]

Post by TJ O'Donnell
http://www.postgresql.org/docs/7.4/static/functions-math.html
show that round(numeric,int) should work ok.

^^^^^^^
The two-argument form of round() expects the first argument to be
numeric, not double precision. There's no implicit cast from double
SELECT ... round((expression)::numeric, 2) ...

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by TJ O'Donnell
Is there some fundamental reason for round(dp) but round(numeric,int)?

I think the main argument against supporting round(dp,int) is that the
result would be inherently inexact (at least for int>0).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[TJ O'Donnell]

Thanks everyone. Your tips about casting my arg to round()
as ::numeric worked just fine. I guess I was surprised
that plpgsql didn't that on it's own! I'm used to too
many forgiving c compilers, and such.

TJ

Post by TJ O'Donnell
ERROR: function round(double precision, integer) does not exist
select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count,
round((parameter*oe_count_matches(smiles,smarts)),2) as psa,tpsa(smiles) as ctpsa,tpsa
from structure,tpsa
where id < 237610
and oe_count_matches(smiles,smarts) > 0
order by id;
http://www.postgresql.org/docs/7.4/static/functions-math.html
show that round(numeric,int) should work ok.
If I use round() without a second argument, it works OK, but
this gives a loss of precision which I do not want.
Can anyone help me with this?
Thanks,
TJ

And round(numeric,int) does work ok.
The error message is telling you
there is double precision argument where a numeric
is expected.
And with 7.4.5 it says in addition
HINT: No function matches the given name and argument types.
You may need to add explicit type casts.
Try
round((parameter*oe_count_matches(smiles,smarts))::numeric,2)
Works for me with double precision arguments.
Regards, Christoph

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Christoph Haller]

Post by TJ O'Donnell
ERROR: function round(double precision, integer) does not exist
select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count,
round((parameter*oe_count_matches(smiles,smarts)),2) as psa,tpsa(smiles) as ctpsa,tpsa
from structure,tpsa
where id < 237610
and oe_count_matches(smiles,smarts) > 0
order by id;
http://www.postgresql.org/docs/7.4/static/functions-math.html
show that round(numeric,int) should work ok.
If I use round() without a second argument, it works OK, but
this gives a loss of precision which I do not want.
Can anyone help me with this?
Thanks,
TJ

And round(numeric,int) does work ok.
The error message is telling you
there is double precision argument where a numeric
is expected.
And with 7.4.5 it says in addition
HINT: No function matches the given name and argument types.
You may need to add explicit type casts.

Try
round((parameter*oe_count_matches(smiles,smarts))::numeric,2)

Works for me with double precision arguments.

Regards, Christoph

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Din Adrian]

the round sintax is
round(numeric,int)
not
round (double,int)

you must cast the value into numeric:
ex: round (cast(doublecolumn as numeric),2) should work ok

Adrian Din,
Om Computer & SoftWare

Post by TJ O'Donnell
ERROR: function round(double precision, integer) does not exist
select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count,
round((parameter*oe_count_matches(smiles,smarts)),2) as
psa,tpsa(smiles) as ctpsa,tpsa
from structure,tpsa
where id < 237610
and oe_count_matches(smiles,smarts) > 0
order by id;
http://www.postgresql.org/docs/7.4/static/functions-math.html
show that round(numeric,int) should work ok.
If I use round() without a second argument, it works OK, but
this gives a loss of precision which I do not want.
Can anyone help me with this?
Thanks,
TJ
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
message can get through to the mailing list cleanly

[Josh Berkus]

TJ,

Post by TJ O'Donnell
ERROR: function round(double precision, integer) does not exist
http://www.postgresql.org/docs/7.4/static/functions-math.html
show that round(numeric,int) should work ok.
If I use round() without a second argument, it works OK, but
this gives a loss of precision which I do not want.

NUMERIC and FLOAT are different data types. Do:

round({value}::NUMERIC, {places})

[Richard Huxton]

Post by TJ O'Donnell
ERROR: function round(double precision, integer) does not exist
select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count,
round((parameter*oe_count_matches(smiles,smarts)),2) as

round((...)::numeric, 2)

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Andreas Kretschmer]

Post by TJ O'Donnell
ERROR: function round(double precision, integer) does not exist
select id,smiles,smarts,parameter,oe_count_matches(smiles,smarts) as count,
round((parameter*oe_count_matches(smiles,smarts)),2) as
psa,tpsa(smiles) as ctpsa,tpsa
from structure,tpsa
where id < 237610
and oe_count_matches(smiles,smarts) > 0

try ... round((parameter*oe_count_matches(smiles,smarts))::numeric,2)


Regards, Andreas