Aggregate error message
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t40655psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 20, 2026 at 12:41 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t40655_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t40655_1 && git checkout t40655_1Patchset v1 (message #1) is on t40655_1
With a sample query such as
SELECT x, avg(x)
FROM (VALUES (1), (2), (3)) AS v (x);
We give the error message "column "v.x" must appear in the GROUP BY
clause or be used in an aggregate function".
This is correct but incomplete. Attached is a trivial patch to also
suggest that the user might have been trying to use a window function.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Fri, 24 May 2019 at 18:17, Vik Fearing <vik.fearing@2ndquadrant.com> wrote:
With a sample query such as
SELECT x, avg(x)
FROM (VALUES (1), (2), (3)) AS v (x);We give the error message "column "v.x" must appear in the GROUP BY
clause or be used in an aggregate function".This is correct but incomplete. Attached is a trivial patch to also
suggest that the user might have been trying to use a window function.
I think you might have misthought this one. If there's an aggregate
function in the SELECT or HAVING clause, then anything else in the
SELECT clause is going to have to be either in the GROUP BY clause, be
functionally dependent on the GROUP BY clause, or be in an aggregate
function. Putting it into a window function won't help the situation.
postgres=# select sum(x) over(),avg(x) FROM (VALUES (1), (2), (3)) AS v (x);
psql: ERROR: column "v.x" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 1: select sum(x) over(),avg(x) FROM (VALUES (1), (2), (3)) AS v...
^
If there's any change to make to the error message then it would be to
add the functional dependency part, but since we're pretty bad at
detecting that, I don't think we should.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
David Rowley <david.rowley@2ndquadrant.com> writes:
On Fri, 24 May 2019 at 18:17, Vik Fearing <vik.fearing@2ndquadrant.com> wrote:
With a sample query such as
SELECT x, avg(x)
FROM (VALUES (1), (2), (3)) AS v (x);
We give the error message "column "v.x" must appear in the GROUP BY
clause or be used in an aggregate function".
This is correct but incomplete. Attached is a trivial patch to also
suggest that the user might have been trying to use a window function.
I think you might have misthought this one. If there's an aggregate
function in the SELECT or HAVING clause, then anything else in the
SELECT clause is going to have to be either in the GROUP BY clause, be
functionally dependent on the GROUP BY clause, or be in an aggregate
function. Putting it into a window function won't help the situation.
Yeah. Also, even if the problem really is that avg(x) should have had
an OVER clause, the fact that the error cursor will not be pointing
at avg(x) means that Vik's wording is still not that helpful.
This is a bit outside our usual error-writing practice, but I wonder
if we could phrase it like "since this query uses aggregation, column
"v.x" must appear in the GROUP BY clause or be used in an aggregate
function". With that, perhaps the user would realize "oh, I didn't
mean to aggregate" when faced with Vik's example. But this phrasing
doesn't cover the GROUP-BY-without-aggregate case, and I'm not sure
how to do that without making the message even longer and more unwieldy.
If there's any change to make to the error message then it would be to
add the functional dependency part, but since we're pretty bad at
detecting that, I don't think we should.
Yeah, that's another thing we're failing to cover in the message ...
but it seems unrelated to Vik's example.
regards, tom lane