ERROR: WITHIN GROUP is required for ordered-set aggregate mode

Question

I have been using Postgres (version 9.2 and 9.3) with this function: https://wiki.postgresql.org/wiki/Aggregate_Mode for a while. Now recently after upgrading to version 9.4 I run into the following error when using the function:

PG::WrongObjectType: ERROR:  WITHIN GROUP is required for ordered-set aggregate mode
       LINE 1: SELECT  mode(logins_count) AS mode_value FROM "registrations"  WHERE "registrations"."cr...

The error occurs when doing:

SELECT mode(logins_count) AS mode_value FROM registrations
WHERE registrations.created_at > '20141105';

I do not understand the error message and I do not get what I have to change?

Answer 1

Postgres 9.4 introduced a new subclass of aggregate functions. Per documentation:

There is a subclass of aggregate functions called ordered-set aggregates for which an order_by_clause is required, usually because the aggregate's computation is only sensible in terms of a specific ordering of its input rows.

One of the new built-in ordered-set aggregate functions is mode() which happens to collide with the name of your custom aggregate function. (The Postgres Wiki page you are referring to has not been updated since 2013.)

Solution

Use a different name for your custom aggregate function to avoid the collision.
Or better yet: use the new built-in function instead. Read the updated Postgres Wiki for details.