Re: float to numeric(7,3)

From: Frank Bax pgsql-novice(at)postgresql(dot)org Re: float to numeric(7,3) 2012-03-10 13:59:55 4F5B5E5B.2030401@sympatico.ca (view raw, whole thread or download thread mbox) 2012-03-10 01:51:54 from Steve Horn  2012-03-10 13:59:55 from Frank Bax   2012-03-10 16:14:53 from Tom Lane  2012-03-10 16:04:10 from Bryan Lee Nuse pgsql-novice
```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?

```

pgsql-novice by date

 Next: From: Bryan Lee Nuse Date: 2012-03-10 16:04:10 Subject: Re: float to numeric(7,3) Previous: From: Frank Lanitz Date: 2012-03-10 13:20:25 Subject: When to choose putting logic into PL/pgSQL and when to use an app server