Re: float to numeric(7,3)

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