explicitly casting return value of avg() to float4

Started by Jon Laphamalmost 24 years ago3 messagesgeneral

lapham@extracta.com.br

almost 24 years ago

Is the preferred way to return the average of ::float4 values to
explicitly cast the returned value of avg() to ::float4?

If so, how do people deal with this in end user apps? Do you check to
see whether the column is ::float8 or ::float4 and then use a different
SQL statement? Ugh, I hope not.

main_v0_8=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

main_v0_8=# select avg(0.01::float4);
avg
---------------------
0.00999999977648258
(1 row)

main_v0_8=# select avg(0.01::float4)::float4;
avg
------
0.01
(1 row)

-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham
Extracta Molï¿½culas Naturais, Rio de Janeiro, Brasil
email: lapham@extracta.com.br web: http://www.extracta.com.br/
***-*--*----*-------*------------*--------------------*---------------

Tom Lane

tgl@sss.pgh.pa.us

almost 24 years ago

In reply to: Jon Lapham (#1)

Re: explicitly casting return value of avg() to float4

Jon Lapham <lapham@extracta.com.br> writes:

Is the preferred way to return the average of ::float4 values to
explicitly cast the returned value of avg() to ::float4?

Yeah, if you want it to float4 precision. The internal arithmetic
is always done in float8 to try to minimize cancellation problems.

main_v0_8=# select avg(0.01::float4);
avg
---------------------
0.00999999977648258
(1 row)

This isn't really any different from

regression=# select 0.01::float4::float8;
float8
---------------------
0.00999999977648258
(1 row)

regards, tom lane

Jon Lapham

lapham@extracta.com.br

almost 24 years ago

In reply to: Jon Lapham (#1)

Re: explicitly casting return value of avg() to float4

Tom Lane wrote:

[snip]
This isn't really any different from

regression=# select 0.01::float4::float8;
float8
---------------------
0.00999999977648258
(1 row)
[snip]

True enough.

So, I guess my applications will have to first determine what the column
precision is, an then use the appropriate ::float4 cast if the column is
a float4 column. This stinks... but I do understand why it is the way
it is.

Is there an SQL-ic way to determine the column type (ie: float4) of a
column?

Thanks,
-Jon