Postgres - find min of array

Question

Suppose I have a table like this:

  link_ids  |  length
------------+-----------
 {1,4}      | {1,2}
 {2,5}      | {0,1}

How can I find the min length for each link_ids?

So the final output looks something like:

  link_ids  |  length
------------+-----------
 {1,4}      | 1
 {2,5}      | 0

Answer 1

Assuming a table like:

CREATE TABLE tbl (
  link_ids int[] PRIMARY KEY     -- which is odd for a PK
, length int[]
, CHECK (length <> '{}'::int[])  -- rules out null and empty in length
);

Query for Postgres 9.3 or later:

SELECT link_ids, min(len) AS min_length
FROM   tbl t, unnest(t.length) len  -- implicit LATERAL join
GROUP  BY 1;

Or create a tiny function (Postgres 8.4+):

CREATE OR REPLACE FUNCTION arr_min(anyarray)
  RETURNS anyelement LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT min(i) FROM unnest($1) i';

Only add PARALLEL SAFE in Postgres 9.6 or later. Then:

SELECT link_ids, arr_min(length) AS min_length FROM t;

The function can be inlined and is fast.

Or, for integer arrays of trivial length, use the additional module intarray and its built-in sort() function (Postgres 8.3+):

SELECT link_ids, (sort(length))[1] AS min_length FROM t;