Postgres: Finding max value in an int array?

Question

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;

Margaux

Even though I wrote my own function so solve the issue, I'm still a little confused. The 'unnest' function returns a set of rows and the max function operates on a set of rows. Is there a bug or is there something I'm just not getting? – Margaux

Yes, there is. max is an aggregate, so it operates on one input per tuple. It can't take a set as an input like that. To do what you describe the way you express it you'd use a subquery, like select id, (select max(x) from unnest(history) x) as vmax from foo_history. – Farmyard

Answer 1

in PostgreSQL 9.6 and 8.4:

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

Forceful

When we get to 9.6, that will be great! – Margaux

Answer 2

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;

Farmyard

Thanks. Just after writing this, I ended up writing my own similar function that returns the position of the largest value in the array. It was not particularly difficult, however, I'm surprised with all the other functionality already built in, something this simple wasn't already supported. – Margaux

@user2259963 Every function and utility has to be written by someone, and if someone hasn't wanted it badly enough it doesn't get written. In this particular one's case I'll try again soon, I learned a lot about coding for Pg since last time I had a go at this, and I want to see it supported in core. – Farmyard

I appreciate that someone else see's the need. Let me know if i can help. Unfortunately in my case, I'm still not understanding the fundamental difference between what I think is a a record-set and row-set. It very easy to turn a record-set into an array - but not the other way around; If we go the other way the data would be supported by existing aggregate functions. Now I'm working on a function to calculate the standard deviation of a array subset. :) – Margaux

I'm getting: "SQL Error [42703]: ERROR: column "x" does not exist Where: SQL function "array_greatest" during inlining". You need to put an x after unnest($1) in your function or it doesn't work. – Guttate

Answer 3

That work for me

select *,
(SELECT max(a) from unnest(ARRAY[1,2,80,3,15,4]) a ) as mx,
 from table

this find max in array

Languish

Answer 4

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. :)

Bojorquez

Just seems like my 2nd example is very inefficient when you consider there could be many millions of records. Kind of seems backwards...?? Maybe a custom function is the way to go in this case... – Margaux

Based on your comment, then the greatest function should work but that doesn't work either. Both functions return... 'ERROR: set-valued function called in context that cannot accept a set' – Margaux