Pick the lowest and highest numbers in an array of numbers in PostgreSQL

I thought solving this problem took longer than it should have. I thought there would have been an integrated function in PostgreSQL to pick the lowest (smallest or minimum) and highest (largest or maximum) numbers in an ARRAY of numbers.

LEAST and GREATEST didn’t work, since those work on expressions, not arrays.

MIN and MAX don’t work because they are aggregating functions, and I didn’t want that.

Of course I found the solution on StackOverflow, but not after a lot of searching and trying other potential solutions.

Here it is!

Given an array of numbers, pick the lowest and highest ones using two custom functions.

CREATE OR REPLACE FUNCTION small(anyarray, int)

 RETURNS anyelement AS $$

  SELECT (ARRAY(SELECT unnest($1) ORDER BY 1 asc))[$2]

 $$ LANGUAGE sql;

The second argument in this function is to extract the Nth smallest number. In my case I want the smallest number so I set “1” for the second argument.

Example array in PostgreSQL:

{45.04,124.90,45.04,124.90}

Example query:

SELECT small(‘{45.04,124.90,45.04,124.90}’::numeric[], 1)

Output: 45.04

You can rewrite the query to select the Nth largest number by changing the “ORDER BY 1 asc” to “ORDER BY 1 desc” (reversing the order of the array’s unnesting.)

CREATE OR REPLACE FUNCTION small(anyarray, int)

RETURNS anyelement AS $$

SELECT (ARRAY(SELECT unnest($1) ORDER BY 1 asc))[$2]

$$ LANGUAGE sql;

Example array in PostgreSQL:

{45.04,124.90,45.04,124.90}

Example query:

SELECT large(‘{45.04,124.90,45.04,124.90}’::numeric[], 1)

Output: 124.9