HAVING clause in PostgreSQL

Question

I'm rewriting the MySQL queries to PostgreSQL. I have table with articles and another table with categories. I need to select all categories, which has at least 1 article:

SELECT c.*,(
    SELECT COUNT(*) 
    FROM articles a 
    WHERE a."active"=TRUE AND a."category_id"=c."id") "count_articles" 
FROM articles_categories c 
HAVING (
    SELECT COUNT(*) 
    FROM articles a 
    WHERE a."active"=TRUE AND a."category_id"=c."id" ) > 0

I don't know why, but this query is causing an error:

ERROR:  column "c.id" must appear in the GROUP BY clause or be used in an aggregate function at character 8

Answer 1

The HAVING clause is a bit tricky to understand. I'm not sure about how MySQL interprets it. But the Postgres documentation can be found here:

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-HAVING

It essentially says:

The presence of HAVING turns a query into a grouped query even if there is no GROUP BY clause. This is the same as what happens when the query contains aggregate functions but no GROUP BY clause. All the selected rows are considered to form a single group, and the SELECT list and HAVING clause can only reference table columns from within aggregate functions. Such a query will emit a single row if the HAVING condition is true, zero rows if it is not true.

The same is also explained in this blog post, which shows how HAVING without GROUP BY implicitly implies a SQL:1999 standard "grand total", i.e. a GROUP BY ( ) clause (which isn't supported in PostgreSQL)

Since you don't seem to want a single row, the HAVING clause might not be the best choice.

Considering your actual query and your requirement, just rewrite the whole thing and JOIN articles_categories to articles:

SELECT DISTINCT c.*
FROM articles_categories c
JOIN articles a 
ON a.active = TRUE 
AND a.category_id = c.id

alternative:

SELECT *
FROM articles_categories c
WHERE EXISTS (SELECT 1 
                FROM articles a
               WHERE a.active = TRUE 
                 AND a.category_id = c.id)