Tregoreg Tregoreg - 26 days ago 13
SQL Question

Empty GROUP BY in PostgreSQL

I have a table of association rules (say antecedent, succedent, and support). I would like to draw them in GraphViz. I have became so lazy that I don't want to code any imperative script for that, I would just like to select the DOT source directly in SQL.

My idea was as simple as

SELECT string_agg('"' || x || '" -> "' || y || '" [weight="' || weight || '"]', E'\n')
FROM rules
GROUP BY ...;


Then I realized I'm having surprising difficulties with the
GROUP BY
clause. Because I need to group all the rows, the clause should be left empty.

What is the most elegant way of saying
GROUP BY NOTHING
, i.e. perform the grouping on an empty set of columns?

Answer

To aggregate all rows, you don't need to form groups with GROUP BY and can can just omit the GROUP BY clause. The manual:

If there are aggregate functions but no GROUP BY clause, the query is treated as having a single group comprising all the selected rows.

Or (if you are building the query string dynamically) you can use any constant expression like:

...
GROUP BY true

You can't use GROUP BY 1 for this purpose because integer constants serve as positional references (ordinal numbers) in GROUP BY:

Comments