Skip site navigation (1) Skip section navigation (2)

Re: float to numeric(7,3)

From: Frank Bax <fbax(at)sympatico(dot)ca>
To:
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: float to numeric(7,3)
Date: 2012-03-10 13:59:55
Message-ID: 4F5B5E5B.2030401@sympatico.ca (view raw or flat)
Thread:
Lists: 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?

In response to

Responses

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group