postgresql – Querying Single Column in All Rows of a Postgres 2D Array

arraypostgresql

Is there any way to fix the query below so I don't have to specify an arbitrary max row count?

DROP TABLE IF EXISTS array_test;

CREATE TABLE array_test (
  id serial primary key,
  name text,
  array_column integer[][]
);

insert into array_test (name, array_column) VALUES
  ('rowa', ARRAY[[1,2,3],[4,5,6]]),
  ('rowb', ARRAY[[4,5,6],[7,8,9],[10,11,12]]);

This does what I want, but I don't like hard-coding 9999 as an arbitrary max row count:

select array_column[1:9999][2:2] from array_test;

Best Answer

You can use the functions array_upper() and array_lower() to identify array dimensions dynamically:

select array_column[1:array_upper(array_column, 1)][2:2] from array_test;

While being at it, if arrays can have non-standard dimenstions, it would be safer to use

SELECT array_column[array_lower(array_column, 1):array_upper(array_column, 1)][2:2]
FROM   array_test;

One subtle difference: While your code returns an empty array for an empty array in the base table, my alternatives returns NULL since array_lower() and array_upper() return NULL. (For NULL in the base table, all return NULL.

I suggest to use a more revealing test case, so you don't miss corner cases:

INSERT INTO array_test (name, array_column) VALUES
  ('rowa', ARRAY[[1,2,3],[4,5,6]])
, ('rowb', ARRAY[[4,5,6],[7,8,9],[10,11,12]])
, ('rowc', ARRAY[[1,2,3]])
, ('rowd', ARRAY[[1],[2],[3]])
, ('rowe', '{}')
, ('rowf', NULL);

There is another "hack" to do exactly the same as your original, but never cut off elements. Use the maximum possible array bounds (which are integer numbers):

SELECT array_column[-2147483648:2147483647][2:2] FROM array_test;

Inspired by Daniel's answer on my related question:

Related Question