Thread: How to round a double8 data type in sql?
Postgres 8.4
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.
Thank you.
Chuck Roberts wrote > Postgres 8.4 This release is now out of support and is no longer receiving updates. > 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. While they may monitor this list (doubtful) you are better off contacting them directly. David J. -- View this message in context: http://postgresql.nabble.com/How-to-round-a-double8-data-type-in-sql-tp5836730p5836737.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
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
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