Обсуждение: float to numeric(7,3)
Have a very simple question, but cannot seem to find an answer anywhere.
Using the ST_Distance function from PostGIS (http://www.postgis.org/docs/ST_Distance.html) which returns a float.
I would like to return the result of this function rounded to 3 decimal places. What is the best way to do that?
On 03/09/12 20:51, Steve Horn wrote: > Have a very simple question, but cannot seem to find an answer anywhere. > > Using the ST_Distance function from PostGIS > (http://www.postgis.org/docs/ST_Distance.html) which returns a float. > > I would like to return the result of this function rounded to 3 decimal > places. What is the best way to do that? Excellent question! I had some trouble with this recently myself... shared=> select version(); version ------------------------------------------------------------------------------------------------- PostgreSQL 9.0.4 on x86_64-unknown-openbsd5.0, compiled by GCC cc (GCC) 4.2.1 20070719 , 64-bit (1 row) shared=> create function fl(int) returns float as $$ SELECT 3.0::float $$ LANGUAGE SQL IMMUTABLE; CREATE FUNCTION shared=> select gettype(fl(1)); ERROR: function gettype(double precision) does not exist LINE 1: select gettype(fl(1)); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. shared=> select round(fl(1),2.0); ERROR: function round(double precision, numeric) does not exist LINE 1: select round(fl(1),2.0); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. shared=> select round(fl(1),2.0::float); ERROR: function round(double precision, double precision) does not exist LINE 1: select round(fl(1),2.0::float); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. shared=> shared=> \df round List of functions Schema | Name | Result data type | Argument data types | Type ------------+-------+------------------+---------------------+-------- pg_catalog | round | double precision | double precision | normal pg_catalog | round | numeric | numeric | normal pg_catalog | round | numeric | numeric, integer | normal ******* The error message indicates round(dp,dp)does not exist; yet '\df' says there is. What is the correct syntax for this?
Hello Steve,
I can't claim it's the best way, but have you tried the following? Substituting a different function for ST_Distance, for this example:
Using the ST_Distance function from PostGIS (http://www.postgis.org/docs/ST_Distance.html) which returns a float.I would like to return the result of this function rounded to 3 decimal places. What is the best way to do that?
sar=> SELECT round(pi()::numeric,3);
round
-------
3.142
(1 row)
If you want to specify the number of decimal places using round(), you have to cast the value as numeric.
You could cast the result directly to numeric(7,3) if you wanted:
sar=> SELECT (pi()*1000)::numeric(7,3);
numeric
----------
3141.593
(1 row)
...but that will fail if your value is too large:
sar=> SELECT (pi()*10000)::numeric(7,3);
ERROR: numeric field overflow
DETAIL: A field with precision 7, scale 3 must round to an absolute value less than 10^4.
Hope that helps,
Bryan
Frank Bax <fbax@sympatico.ca> writes: > On 03/09/12 20:51, Steve Horn wrote: >> Have a very simple question, but cannot seem to find an answer anywhere. >> >> Using the ST_Distance function from PostGIS >> (http://www.postgis.org/docs/ST_Distance.html) which returns a float. >> >> I would like to return the result of this function rounded to 3 decimal >> places. What is the best way to do that? > Excellent question! I had some trouble with this recently myself... Uh, just cast it: select somefunction(...)::numeric(7,3) from ... The "::typename" locution for casting is a Postgres-ism. If you prefer to stick to SQL-standard spellings, then write select cast(somefunction(...) as numeric(7,3)) from ... As far as the other issue goes: > shared=> select round(fl(1),2.0); > ERROR: function round(double precision, numeric) does not exist > [ and assorted variants of that ] > shared=> \df round > List of functions > Schema | Name | Result data type | Argument data types | Type > ------------+-------+------------------+---------------------+-------- > pg_catalog | round | double precision | double precision | normal > pg_catalog | round | numeric | numeric | normal > pg_catalog | round | numeric | numeric, integer | normal > The error message indicates round(dp,dp)does not exist; yet '\df' says > there is. What is the correct syntax for this? No, \df says that the only two-argument form of round() takes numeric and integer as parameters. Everything you tried involved float or numeric spellings of the second parameter. There's no implicit downcast from those types to integer, so the parser won't match these calls to that function. Also, there's no implicit cast from float or double precision to numeric (there's one in the other direction), so if you were working with a function that returns float or dp then you'd additionally need to cast its result to numeric. So the formula that works is something like regression=# select round(sin(2)::numeric, 4); round -------- 0.9093 (1 row) The only real advantage of this form over a simple cast to length-limited numeric is you don't have to constrain the number of digits before the decimal point ... regards, tom lane