2

So simple... I need to retrieve an item of an array-of-arrays, so the item must be a simple array. Example:

WITH t AS (SELECT '{{1,2,3},{333,444,555}}'::int[][] as a, '{{{{1,2}}}}'::int[] as b)
SELECT 
     a[2],    -- returns null (!), why not works?
     a[2:2],  -- returns array-into-array, not simple array
     b[1][1], -- also null
     b[1][1][1:2],  -- another ugly array-into-array-into...-array
     a[1][1]        -- is correct, but only works with scalars
FROM t;

The result is NULL,{{333,444,555}},NULL,{{{{1,2}}}},1... I need a simple array for a[2], {333,444,555}... How to access it as it?

PS: the guide and google-examples only show slices... Perhaps it is so obvious but I not remember why a[2] is invalid in PostgreSQL.

Improve this question
6
  • 1
    Please note that you are casting 2-dimensional arrays into an array of a single dimension for a, and a 4-dimension array into a single dimension for b. I believe you will need to create a function to perform the extraction a[2] the way you expect. Commented Jan 6, 2016 at 2:40
  • Hi @ViníciusGobboA.deOliveira, thanks. Well... if you change the code, '{{1,2,3},{333,444,555}}'::int[][] with more one [], the result is the same... I think that I not understand your point. Commented Jan 6, 2016 at 2:46
  • Sorry, it was late night here. I somewhat misunderstood you question. The types casts are wrong, but even fixing them, you need an specialized function to extract a single array from a n-dimension array. Commented Jan 6, 2016 at 10:58
  • Thanks @ViníciusGobboA.deOliveira, I edited, changing int[] to int[][] and, after also testing, unfortunately (I am using stable pg9.4), the problem is the same. Commented Jan 6, 2016 at 19:23
  • 1
    @ViníciusGobboA.deOliveira ... Hum (no solution!?)... more one item for my pg-wish-list :-) Why the PostgreSQL community adopted a so ugly convention? Why a[2] is NULL? .... I was also looking for an array-extract-item-as-it operator, but I didn't find it in the guide. Commented Jan 6, 2016 at 22:28

2 Answers 2

Reset to default
1

I had forgotten what we discussed here: it is a restricted form of "array of arrays"... named "multidimensional array"... But I persisted in thinking in ambiguous way, that is, thinking sometimes in array of arrays...

The best answer comes from Tom Lane at the pgsql-hackers.at.postgresql.org forum, "Multi-dimensional arrays in Postgres are not 'arrays of arrays'".

So, in the "multidimensional array" world, not exist that kind of "access to an array-item" that I expected.

As @ViníciusGobboA.deOliveira commented here, the only solution is to write a function, through PL/SQL (low performance) or C (high performance).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

a[2:2] will return integer[] type, while non-colon notation a[2] will return integer type, which in fact in your case should be an array. Based on the documentation:

An array slice is denoted by writing lower-bound:upper-bound for one or more array dimensions.

Check the code below:

WITH test AS (
    SELECT
      '{
        {11,12,13},
        {21,22,23},
        {31,32,33},
        {41,42,43}
       }'::INTEGER[][] as vv
)
SELECT
  array_dims(vv) dimensions,--  [1:4][1:3] - get all dimensions
  vv[1:4] all_slices, -- entire 2d-array
  vv[2:3] second_and_third_slices, -- {{21,22,23},{{31,32,33}}
  vv[2:2] second_slice, -- {{21,22,23}} - get specific slice
  pg_typeof(vv[2:2]) second_slice_type, -- type of slice above is INTEGER[]!
  pg_typeof(vv[2]) vv_type, -- type is INTEGER
  vv[2:2][2:2] second_slice_second_element, -- 22 - get specific element
  vv[2:2][2:3] second_slice_second_and_third_elements -- {{22,23}}
FROM test;
Improve this answer

1 Comment

Hi, thanks (!). Your vv[2:2] second_slice is my a[2], and you do exactly the same thing, that is not a solution. Your vv[2:2] returns {{21,22,23}} , but I need a simple access method that returns {21,22,23}... the question is "How to access it as it?".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.