Re: How to round a double8 data type in sql?

David G Johnston <david.g.johnston@gmail.com> writes:
> Chuck Roberts wrote
>> How do I use round() in sql on a double8 data type? While in an SQL
>> statement, I can use round just fine on a field that is a type of double.
>> Ex: Select round(esthours,2) as ehrs from job;
>> But a different field has a type of double8 and I get an error when I try
>> to use round on it:
>>
>> select round(acthours,2) as ahrs from job;
>>
>> The error is: "No function matches the given name and argument types. You
>> might need to add explicit type casts."
>>
>> I searched around on Google but found no help on doing type casts.

> Since this is not a native PostgreSQL type you either need to covert it to
> one or ask whomever created this type to also create a rounding function for
> it.

I assume he meant float8 (aka double precision), which certainly is
native.

The answer can be found here:

# \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
(3 rows)

That is, the two-argument version of round() only exists for numeric not
for float8.

You could cast to numeric and back as the hint is suggesting:

round(acthours::numeric, 2)::float8

However you might run into issues with the fact that float8 doesn't store
decimal fractions exactly.

Unless you're doing very heavy computations there'd be something to be
said for making acthours numeric in the first place; it's slower but
probably less likely to surprise you.

            regards, tom lane