Thread: SQL error: function round(double precision, integer) does not exist
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 < 237610and 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
On Sun, Feb 27, 2005 at 03:26:07PM -0800, TJ O'Donnell wrote: > ERROR: function round(double precision, integer) does not exist ^^^^^^^^^^^^^^^^ [snip] > The functions described at: > 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) ... -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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 Christoph Haller wrote: > TJ O'Donnell wrote: > >>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 >> > > > 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
TJ O'Donnell wrote: > > 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 > 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
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 Michael Fuhr wrote: > On Sun, Feb 27, 2005 at 03:26:07PM -0800, TJ O'Donnell wrote: > > >>ERROR: function round(double precision, integer) does not exist > > ^^^^^^^^^^^^^^^^ > [snip] > > >>The functions described at: >>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) ... >
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 On Sun, 27 Feb 2005 15:26:07 -0800, TJ O'Donnell <tjo@acm.org> wrote: > 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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
TJ, > SQL error: > 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}) -- Josh Berkus Aglio Database Solutions San Francisco
Re: [despammed] SQL error: function round(double precision, integer) does not exist
From
Andreas Kretschmer
Date:
am 27.02.2005, um 15:26:07 -0800 mailte TJ O'Donnell folgendes: > 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 try ... round((parameter*oe_count_matches(smiles,smarts))::numeric,2) Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
TJ O'Donnell wrote: > 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 round((...)::numeric, 2) -- Richard Huxton Archonet Ltd
"TJ O'Donnell" <tjo@acm.org> writes:
> 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