Re: stddev returns 0 when there is one row

Douglas Trainor <trainor@uic.edu> writes:
> For example, both of these calculations produce answers of 0 (zero)
> but they should produce answers of 1 (one):

>     =stdev(80000000,80000001,80000002)
>     =var(80000000,80000001,80000002)

Looks like roundoff error to me.  That's pushing the limit of what you
can hope to do in float8 math.  Postgres gets the right answer with
NUMERIC data, but not with FLOAT8:

regression=# create table foo (f1 float8, f2 numeric, f3 int);
CREATE TABLE
regression=# insert into foo values(80000000, 80000000, 80000000);
INSERT 291676 1
regression=# insert into foo values(80000001, 80000001, 80000001);
INSERT 291677 1
regression=# insert into foo values(80000002, 80000002, 80000002);
INSERT 291678 1
regression=# select * from foo;
    f1    |    f2    |    f3
----------+----------+----------
 80000000 | 80000000 | 80000000
 80000001 | 80000001 | 80000001
 80000002 | 80000002 | 80000002
(3 rows)

regression=# select stddev(f1), variance(f1) from foo;
      stddev      |     variance
------------------+------------------
 1.15470053837925 | 1.33333333333333
(1 row)

regression=# select stddev(f2), variance(f2) from foo;
         stddev         |        variance
------------------------+------------------------
 1.00000000000000000000 | 1.00000000000000000000
(1 row)

regression=# select stddev(f3), variance(f3) from foo;
         stddev         |        variance
------------------------+------------------------
 1.00000000000000000000 | 1.00000000000000000000
(1 row)


(The integer case uses NUMERIC arithmetic under the hood.)

            regards, tom lane