Amazon Redshift "cache lookup failed for aggregate" error when using median aggregate

Question

I recently ran into this error message and am posting an answer here so that the solution is easily searchable in the future.

Problem: The following query returns an error message when run on Amazon Redshift. What does it mean and does Amazon Redshift support the 'median' aggregate?

dev=# select median(5);
ERROR: cache lookup failed for aggregate 2762

Answer 1

Amazon recently added support for the 'median' window function. If you forget that it's only a window function and try to use it like an aggregate, you'll get this error:

dev=# select median(5);
ERROR: cache lookup failed for aggregate 2762

The solution is to rephrase the query to use it as a window function:

dev=# select median(5) over ();
 median 
--------
 5

(1 row)