On Fri, 24 May 2019 at 18:17, Vik Fearing <vik.fear...@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