A multi-dimensional array in PostgreSQL
Tags: postgres, array, multidimensional, arrays
Published: 2024-02-02 20:03:57

*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](https://stackoverflow.com/questions/13809547/convert-integer-to-string-in-postgresql#77723276) explains type conversion in detail.

- [The doc](https://www.postgresql.org/docs/current/arrays.html) explains a multi-dimensional array in detail.

- [My answer](https://stackoverflow.com/questions/71406989/declare-an-array-in-postgresql#77723752) explains how to create and use the 1D(one-dimensional) array with `VARCHAR[]` in detail.

- [My answer](https://stackoverflow.com/questions/11831555/get-nth-element-of-an-array-that-returns-from-string-to-array-function/#77723802) explains how to create and use the 1D array with `INT[]` in detail.

- [My answer](https://stackoverflow.com/questions/23924815/create-an-empty-array-in-an-sql-query-using-postgresql-instead-of-an-array-with#77932610) explains how to create an use an empty array in detail.

- [My post](https://dev.to/hyperkai/foreach-and-for-statements-with-arrays-in-postgresql-2bnc) explains how to iterate a 1D and 2D array with a [FOREACH](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY) and [FOR](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-INTEGER-FOR) statement.

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

```sql
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](https://stackoverflow.com/questions/71397289/apply-subscripting-to-text-data-type-in-postgresql#77924037).

- 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](https://stackoverflow.com/questions/72975653/why-do-i-get-an-invalid-input-syntax-for-type-integer-error#77919995).

Or:

```sql
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](https://stackoverflow.com/questions/71397289/apply-subscripting-to-text-data-type-in-postgresql#77924037).

- 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:

```sql
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:

```sql
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
$$;
```