I'm going to guess you're using 7.0, right?
This should work under 7.1 (or at least your test case
does), but there were serious problems with views containing
group by in earlier versions.
On Tue, 27 Feb 2001, Limin Liu wrote:
> Hi,
>
> Can anyone please tell me whether this is a bug or not?
>
> I used the following SQL to create my table and view with some data.
>
> =======================
> create table tbl (no int, cnt int);
> create view tbl_view as
> select no, count(cnt) as count
> from tbl
> group by no
> having count(cnt) > 2;
> insert into tbl values(1, 1);
> insert into tbl values(1, 2);
> insert into tbl values(1, 3);
> insert into tbl values(1, 4);
> insert into tbl values(2, 1);
> insert into tbl values(2, 2);
> insert into tbl values(2, 3);
> =======================
>
> Here is soem SQL I issued; first one looks fine to me, but the second
> one is weird.
>
> =======================
> db=> select * from tbl_view;
> no | count
> ----+-------
> 1 | 4
> 2 | 3
> (2 rows)
>
> db=> select * from tbl_view where count > 5;
> no | count
> ----+-------
> 1 | 4
> 2 | 3
> (2 rows)