From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew D <andrew.do1613@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Postgres Bug - Aggregate with order by
Date: Thu, 31 Mar 2022 16:34:02 -0400
Message-ID: <731795.1648758842@sss.pgh.pa.us> (raw)
In-Reply-To: <CAD9rp8B=YkedfmZhn5aRjcmm75s15y78g7jk45FShPvMPbFb3A@mail.gmail.com>
References: <CAD9rp8B=YkedfmZhn5aRjcmm75s15y78g7jk45FShPvMPbFb3A@mail.gmail.com>
Andrew D <andrew.do1613@gmail.com> writes:
> I believe that I've found a bug within postgres where an aggregate function
> is not accepted when an order by clause is specified, here is a short
> example:
> postgres=# select COUNT(i) from test_table order by i;
> ERROR: column "test_table.i" must appear in the GROUP BY clause or be used
> in an aggregate function
> LINE 1: select COUNT(i) from test_table order by i;
> ^
This looks perfectly normal to me. The ORDER BY clause is
supposed to be performed after the aggregation step. But
there's no meaningful value of i to associate with the
aggregated rows (well, row, in this case).
It's not very clear what you were hoping to accomplish, but
if you intended to sort before the aggregation, the correct
way is to write
select COUNT(i order by i) from test_table;
(Ordering the input to COUNT() is pretty pointless, of course,
but there are other aggregates for which it's useful to do this.)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, andrew.do1613@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Postgres Bug - Aggregate with order by
In-Reply-To: <731795.1648758842@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox