I did something different for this bug, as follows;
----8<-----------8<-------------
koichi=# create table T1
koichi-# (
koichi(# C1 varchar(128),
koichi(# C2 numeric(9,2),
koichi(# C3 int);
CREATE TABLE
koichi=#
koichi=# insert into T1 values
koichi-# ('N1', 1.1, 20121001),
koichi-# ('N1', 2.1, 20121002);
INSERT 0 2
koichi=#
koichi=# select
koichi-# C1 "NewC1",
koichi-# avg(C2)::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231
koichi-# group by C1;
NewC1 | NewC2
-------+-------
N1 | 1.60
(1 row)
koichi=#
koichi=# create table newt as
koichi-# select
koichi-# C1 "NewC1",
koichi-# avg(C2)::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231
koichi-# group by C1;
ERROR: function pg_catalog.numeric_avg(numeric) does not exist
LINE 4: avg(C2)::Numeric(9,2) "NewC2"
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
koichi=#
koichi=# create table newt1 as
koichi-# select
koichi-# C1 "NewC1",
koichi-# C2::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231;
INSERT 0 2
koichi=#
koichi=# create table newt2 as
koichi-# select
koichi-# C1 "NewC1",
koichi-# sum(C2)::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231
koichi-# group by C1;
INSERT 0 1
koichi=# create table newt4 as
koichi-# select
koichi-# C1 "NewC1",
koichi-# count(*)::Numeric(9,2) "NewC2"
koichi-# from T1
koichi-# where C3 between 20121001 and 20121231
koichi-# group by C1;
INSERT 0 1
koichi=#
---->8----------->8-------------
Interesting result is:
can use sum() and count() in CREATE TABLE AS SELECT ... but not avg(). I
suspect this issue occurs with aggregate where we have different functions
for two-phase and three-phase aggregation.
Hope this is a good hint to fix the problem.
Regards;
----------
Koichi Suzuki
2013/6/19 Koichi Suzuki <koi...@gm...>
> I've checked the current master status for the bug 3602898.
> Unfortunately, the bug still exists.
>
> Attached is the script to reproduce it.
>
> Regards;
> ----------
> Koichi Suzuki
>
|
