2

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;
1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.