Question 90275 in PostgreSQL Question posted in
The official documentation can be found here.

How to get the minimum value from an array – Postgresql

If I have an array of ints, how can I get the min value out of the array?

The min() and least() functions won’t work directly on the array.

for example if I have an array like below:

select 'a', array[1,2,3];

I want ‘a’, 1 as the results of my query.

I’ve tried select 'a', min(array[1,2,3]); and select 'a', least(array[1,2,3]);

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    we can unnest the array, then group by the unique id, and use the min() function on that query to get the min for each unique row.

    run the following queries one by one to see how it works.

    SELECT
    'a' AS id,
    array [1,2,3] AS the_array;

SELECT
    id,
    unnest(the_array) unnested_array
FROM
    (
        SELECT
            'a' id,
            array [1,2,3] AS the_array
    ) AS src;

SELECT
    id,
    min(unnested_array) AS min
FROM
    (
        SELECT
            id,
            unnest(the_array) unnested_array
        FROM
            (
                SELECT
                    'a' id,
                    array [1,2,3] AS the_array
            ) AS src
    ) AS subquery
GROUP BY
    id;

  2. 0 
    select 'a', min(t) from unnest(array[1,2,3]) as t;
 ?column? | min 
----------+-----
 a        |   1
    Login or Signup to reply.
  3. 0

    You can install the intarray extension to sort the array and then pick the first element:

    select (sort(array[3,2,1], 'asc'))[1]

    Alternatively you can write such a function:

    create function array_min(p_input int[])
  returns int
as
$$
   select *
   from unnest(p_input) as x(v)
   order by x.v nulls last
   limit 1
$$
language sql
immutable;

    For the corresponding implementation of array_max() you need to use an order by ... desc

    Login or Signup to reply.
  4. 0

    LEAST() is only good for a small, known number of array elements, allowing us to spell it out like this:

    SELECT LEAST(arr[1], arr[2], arr[3]);

    Else look to the answers of Adrian and a_horse.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search