Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

mirabilos <tg(at)evolvis(dot)org> writes:
> This works well. However, what I seem to be not allowed to do is
> (without the extra COALESCE, to simplify):
> ...
> This is because, when I use DISTINCT (but only then‽), the ORDER BY
> arguments must be… arguments to the function, or something.

Well, yeah. Simplify it to

SELECT array_agg(DISTINCT x ORDER BY y) FROM mytable;

If there are several rows containing the same value of x and different
values of y, which y value are we supposed to sort the unique-ified x
value by? It's an ill-defined query.

For the specific example you give, it's true that any specific
possible output of jsonb_build_object() would correspond to
a unique set of cot.weekday, cot.from_hour, cot.to_hour values.
But the parser can't be expected to know that. Many functions
can produce the same output for different sets of inputs.

I'd suggest moving the distinct-ification into an earlier
processing step (i.e. a sub-select), or maybe thinking harder
about why you're getting duplicates in the first place.

regards, tom lane