Why is the Postgres "mode" function so different from "avg", "max", and other aggregates?

Question

In Postgres, I can say select avg(size) from images and select max(size) from images.

But when I want the mode, I may not do this:

select mode(uploaded_by_id) from images

Instead I must do this:

select mode() within group (order by uploaded_by_id desc) from images

The syntax seems a little funky to me. Does anyone know why the other syntax was not permitted?

NOTE: I know that allowing order by enables the user to define which mode to take in the case of a tie, but I don't see why that needs to prohibit the other syntax entirely.

Thanks!

Answer 1

After looking at the documentation it appears as though they moved away from a simple function in favour of the window function, theyre citing speed advantages as a reason for this.

https://wiki.postgresql.org/wiki/Aggregate_Mode

If you wanted to you could just create a function yourself but it seems as though the window function is the fastest way to get a NOT NULL result back from the db.