This pgsql box is from a vendor and we cannot change the database. It is a
"closed box", we can look but cannot change it. But we have a spreadsheet
which lists all the tables and fields and the datatype of each field. The
datatype for this field is listed as "double8". All I needed was the
syntax to cast to a standard data type, so this worked:
SELECT round(acthours::numeric,2)...
Thank you!
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, February 04, 2015 11:29 AM
To: David G Johnston
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] 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
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice