current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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;
share|improve this question

1 Answer 1

up vote 0 down vote accepted

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:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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