PostgreSQL – GROUP BY clause

Postgres 9.1 or later, quoting the release notes of 9.1 …

Allow non-GROUP BY columns in the query target list when the primary
key is specified in the GROUP BY clause (Peter Eisentraut)

The SQL standard allows this behavior, and because of the primary key,
the result is unambiguous.


  • Return a grouped list with occurrences using Rails and PostgreSQL

The queries in the question and in @Michael’s answer have the logic backwards. We want to count how many tags match per article, not how many articles have a certain tag. So we need to GROUP BY, not by

list all articles with that tag, and also how many of given tags they match

To fix this:

SELECT count(t.tag) AS ct, a.*  -- any column from table a allowed ...
FROM   a_tags         t
JOIN   w_articles2tag a2t ON a2t.tag =
JOIN   w_article      a   ON = a2t.article
WHERE  t.tag IN ('css', 'php')
GROUP  BY                  -- ... since PK is in GROUP BY

Assuming id is the primary key of w_article.
However, this form will be faster while doing the same:

SELECT a.*, ct
   SELECT a2t.article AS id, count(*) AS ct
   FROM   a_tags         t
   JOIN   w_articles2tag a2t ON a2t.tag = 
   GROUP  BY 1
   LIMIT  9      -- LIMIT early - cheaper
   ) sub
JOIN   w_article a USING (id);  -- attached alias to article in the sub

Closely related answer from just yesterday:

  • Why does the following join increase the query time significantly?

Leave a Comment