A multi-dimensional array in PostgreSQL

Buy Me a Coffee☕

*Memos:

• An array has elements from [1] but not from [0] so [0] returns NULL.

• Basically, you should use type conversion to create an array except when you declare a non-empty array in a DECLARE clause in a function, procedure or DO statement because the type may be different from your expectation and there is some case which you cannot create an array without type conversion. *My answer explains type conversion in detail.

• The doc explains a multi-dimensional array in detail.

• My answer explains how to create and use the 1D(one-dimensional) array with VARCHAR[] in detail.

• My answer explains how to create and use the 1D array with INT[] in detail.

• My answer explains how to create an use an empty array in detail.

• My post explains how to iterate a 1D and 2D array with a FOREACH and FOR statement.

You can create and use a 2D(two dimensional) array with these ways below:

DO $$
DECLARE
  _2d_arr VARCHAR[] := ARRAY[
    ['a','b','c','d'],
    ['e','f','g','h'],
    ['i','J','k','l']
  ];
BEGIN
  RAISE INFO '%', _2d_arr; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}}
  RAISE INFO '%', _2d_arr[0][2]; -- NULL
  RAISE INFO '%', _2d_arr[2]; -- NULL
  RAISE INFO '%', _2d_arr[2][0]; -- NULL
  RAISE INFO '%', _2d_arr[2][3]; -- g
  RAISE INFO '%', _2d_arr[2:2]; -- {{e,f,g,h}}
  RAISE INFO '%', _2d_arr[1:1][2:3]; -- {{b,c}}
  RAISE INFO '%', _2d_arr[2:2][2:3]; -- {{f,g}}
  RAISE INFO '%', _2d_arr[3:3][2:3]; -- {{J,k}}
  RAISE INFO '%', _2d_arr[1:3][2:3]; -- {{b,c},{f,g},{J,k}}
  RAISE INFO '%', _2d_arr[:][:]; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}}
  RAISE INFO '%', _2d_arr[1][2:3]; -- {{b,c}} -- Tricky
  RAISE INFO '%', _2d_arr[2][2:3]; -- {{b,c},{f,g}} -- Tricky
  RAISE INFO '%', _2d_arr[3][2:3]; -- {{b,c},{f,g},{J,k}} -- Tricky
END
$$;

*Memos:

• The type of the array above is VARCHAR[](CHARACTER VARYING[]).

• You can set VARCHAR[][], VARCHAR[][][], etc to _2d_arr, then the type of _2d_arr is automatically converted to VARCHAR[](CHARACTER VARYING[]) but you cannot set VARCHAR to _2d_arr otherwise there is the error.

• The last 3 RAISE INFO ... are tricky.

• If the number of the elements in each 1D array in _2d_arr is different, there is error.

• Don't create the 2D array which has numbers and strings otherwise there is the error.

Or:

DO $$
DECLARE
  _2d_arr VARCHAR[] := '{
    {a,b,c,d},
    {e,f,g,h},
    {i,j,k,l}
  }';
BEGIN
  RAISE INFO '%', _2d_arr; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}}
  RAISE INFO '%', _2d_arr[0][2]; -- NULL
  RAISE INFO '%', _2d_arr[2]; -- NULL
  RAISE INFO '%', _2d_arr[2][0]; -- NULL
  RAISE INFO '%', _2d_arr[2][3]; -- g
  RAISE INFO '%', _2d_arr[2:2]; -- {{e,f,g,h}}
  RAISE INFO '%', _2d_arr[1:1][2:3]; -- {{b,c}}
  RAISE INFO '%', _2d_arr[2:2][2:3]; -- {{f,g}}
  RAISE INFO '%', _2d_arr[3:3][2:3]; -- {{J,k}}
  RAISE INFO '%', _2d_arr[1:3][2:3]; -- {{b,c},{f,g},{J,k}}
  RAISE INFO '%', _2d_arr[:][:]; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}}
  RAISE INFO '%', _2d_arr[1][2:3]; -- {{b,c}} -- Tricky
  RAISE INFO '%', _2d_arr[2][2:3]; -- {{b,c},{f,g}} -- Tricky
  RAISE INFO '%', _2d_arr[3][2:3]; -- {{b,c},{f,g},{J,k}} -- Tricky
END
$$;

*Memos:

• The type of the array above is VARCHAR[](CHARACTER VARYING[]).

• You can set VARCHAR[][], VARCHAR[][][], etc to _2d_arr, then the type of _2d_arr is automatically converted to VARCHAR[](CHARACTER VARYING[]) but you cannot set VARCHAR to _2d_arr otherwise there is the error.

• The last 3 RAISE INFO ... are tricky.

• If the number of the elements in each 1D array in _2d_arr is different, there is error.

In addition, even if you set VARCHAR(2)[2] to the array, the result is the same and the type of the 2D array is VARCHAR[](CHARACTER VARYING[]) as shown below:

DO $$
DECLARE -- ↓ ↓ ↓ ↓ ↓ ↓
  _2d_arr VARCHAR(2)[2] := ARRAY[
    ['a','b','c','d'],
    ['e','f','g','h'],
    ['i','J','k','l']
  ];
BEGIN
  RAISE INFO '%', _2d_arr; -- {{a,b,c,d},{e,f,g,h},{i,J,k,l}},
  RAISE INFO '%', pg_typeof(_2d_arr); -- character varying[]
END
$$;

And, even if you set ::TEXT to 'a', the type of 'a' is VARCHAR(CHARACTER VARYING) rather than TEXT as shown below because the type VARCHAR[] set to _2d_arr is prioritized. *You cannot set ::TEXT[] to each 1D array otherwise there is error but you can set ::TEXT[] to each 1D array if you set the keyword ARRAY just before each 1D array but the type of each row is VARCHAR[](CHARACTER VARYING[]) rather than TEXT[] because the type VARCHAR[] set to _2d_arr is prioritized as well:

DO $$
DECLARE
  _2d_arr VARCHAR[] := ARRAY[
    ['a'::TEXT,'b','c','d'],
    ['e','f','g','h'],
    ['i','J','k','l']
  ];
BEGIN
  RAISE INFO '%', _2d_arr[1][1]; -- a
  RAISE INFO '%', pg_typeof(_2d_arr[1][1]); -- character varying
END
$$;