Postgres: Finding max value in an int array?

using Postgres 9.3...

Can someone please explain why I can't use a max function directly on an unnested array..?

It is my understanding the unnest function returns a "setof" just like the select statement. So why does the short version of this query not work? (Am I missing something conceptually or is my issue a syntax related one?)

table: foo_history: id | history::smallint ----------------------------------- 1 | {10,20,30,50,40} 

This doesn't work ?

Select id, max(unnest(history)) as vMax from foo_history; 

...but this one does...?

WITH foo as ( select id, unnest(history) as history from foo_history ) Select id, max(history) as vMax From foo Group by id; 

3 Answers

If you install the intarray module it provides some extra array operators that'll let you write what you want, albeit somewhat inefficiently:

CREATE EXTENSION intarray; SELECT id, (sort_desc(history))[1] as vMax FROM foo_history; 

It would be pretty easy to write greatest and least functions for arrays to add to intarray, the code is pretty simple.

Otherwise you can just write an SQL function:

CREATE OR REPLACE FUNCTION array_greatest(anyarray) RETURNS anyelement LANGUAGE SQL AS $$ SELECT max(elements) FROM unnest($1) elements $$; 

and use that:

SELECT id, array_greatest(history) as vMax FROM foo_history; 

in PostgreSQL 9.6 and 8.4:

SELECT max(x) FROM unnest(ARRAY[1,2,80,3,15,4]) as x; 

You must remember that SQL is designed to operate on sets of data. The MAX function actually does work in the first example, it just doesn't work as you are expecting it to. It will return the maximum value for each row that matches.

The group by clause works as expected because you are now aggregating into a set and then getting the maximum from the set. :)