I have created the following two tables:
CREATE TABLE tab1 (Id INTEGER, Name TEXT);
CREATE TABLE tab2 (Id INTEGER, Attribute INTEGER);
populated as:
INSERT INTO tab1 VALUES (0, 'name 1');
INSERT INTO tab1 VALUES (1, 'name 2');
INSERT INTO tab1 VALUES (2, 'name 3');
INSERT INTO tab2 VALUES (0, 0);
INSERT INTO tab2 VALUES (0, 1);
INSERT INTO tab2 VALUES (0, 4);
INSERT INTO tab2 VALUES (0, 5);
INSERT INTO tab2 VALUES (1, 0);
INSERT INTO tab2 VALUES (2, 0);
INSERT INTO tab2 VALUES (2, 1);
test=> SELECT * FROM tab1;
id|name
--+------
0|name 1
1|name 2
2|name 3
(3 rows)
test=> SELECT * FROM tab2;
id|attribute
--+---------
0| 0
0| 1
0| 4
0| 5
1| 0
2| 0
2| 1
(7 rows)
and I would like to list the 'names' which are associated to more than
'N'
attributes. For 'N=2' I can get the 'id's by
test=> SELECT Id FROM tab2 GROUP BY Id HAVING COUNT(*) > 2;
id
--
0
(1 row)
so that I expected to be able to use this information in a subquery
like:
test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM
tab2
GROUP BY tab2.Id HAVING COUNT(*) > 2);
ERROR: rewrite: aggregate column of view must be at rigth side in qual
which gives me nothing but the strange error above. Although I do not
completely
understand this message I have rewritten the query so that the aggregate
does
appear in the right side of the HAVING clause as:
test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM
tab2
GROUP BY tab2.Id HAVING 2 < COUNT(*));
name
------
name 1
name 2
name 3
(3 rows)
but now what I have is something I really do not want: all names! Notice
that the
"inverted" subquery still gives the correct result:
test=> SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING 2 < COUNT(*);
id
--
0
(1 row)
Is this a bug or did I miss some point? Does anybody have some ideas?
By now I do the job with the join:
test=> SELECT tab1.Name FROM tab1, tab2 WHERE tab1.Id=tab2.Id GROUP BY
tab1.Name
HAVING COUNT(*) > 2;
name
------
name 1
(1 row)
I believe this is less efficient than the above since I have to 'join'
two tables
before applying the HAVING clause. Any comments?
TIA
P.S.: I am using PostgreSQL v. 6.4.2
--
Mauricio C. de Oliveira
mailto:carvalho@dt.fee.unicamp.br
http://www.dt.fee.unicamp.br/~carvalho
