Alright, let’s talk about multi-dimensional arrays. You’ve probably built a simple one-dimensional array, a nice, tidy list of elements. It’s the database equivalent of a shopping list. PostgreSQL, in its infinite wisdom, lets you create arrays of those arrays. This is where we get into matrices, grids, and… well, a fair bit of pain if you’re not careful. It’s a powerful feature, but one that comes with more caveats than a medieval manuscript.

The syntax is straightforward. You just add more square brackets to the data type declaration. Want a 2D array of integers? That’s integer[][]. A 3D array of text? text[][][]. You get the idea.

-- A 2x3 matrix of integers
SELECT ARRAY[ [1, 2, 3], [4, 5, 6] ] AS matrix;

-- Result:
--    matrix
-- ----------------
--  {{1,2,3},{4,5,6}}

Notice how the output uses double curly braces? That’s your first clue this is a different beast. The inner braces represent each inner array.

The Jagged Array Problem (And Why It’s Not)

Here’s the first thing you need to know, and it’s a biggie: PostgreSQL officially does not support “jagged” arrays. You know, where one inner array is longer than another? Yeah, forget it. The system will initially let you create one, but it will immediately and passive-aggressively coerce it into a rectangular form by padding the shorter arrays with NULL values.

It’s the database equivalent of a perfectionist friend who sees your crooked picture frame and “fixes” it without asking. Let’s watch it happen.

-- Try to create a jagged array
SELECT ARRAY[ [1, 2], [3, 4, 5] ] AS jagged_attempt;

-- Result (wait for it...):
--    jagged_attempt
-- ---------------------
--  {{1,2,NULL},{3,4,5}}

See that? A NULL just magically appeared in the first sub-array. This isn’t a warning; it’s a silent, forced correction. This “feature” is the source of countless subtle bugs. You assume your data is what you put in, but PostgreSQL has… helpfully rearranged it. Always check your dimensions.

Accessing Elements and Slicing

Accessing elements builds on what you know. You just use multiple indexes. A crucial detail: you access them as [index_of_outer_array][index_of_inner_array]. It’s “row” then “column” if you think in matrix terms.

-- Let's grab a specific element
WITH sample_matrix AS (
  SELECT ARRAY[ ['A', 'B', 'C'], ['D', 'E', 'F'] ] AS letters
)
SELECT
  letters[1][1] as top_left, -- 'A'
  letters[2][3] as bottom_right -- 'F'
FROM sample_matrix;

Slicing works similarly, but the syntax can make your eyes glaze over. To get a slice from the first inner array, you’d use [1:1][1:2] (get the first array, then elements 1 to 2 of it). It’s powerful, but I honestly find it easier to often unpack the array first with unnest.

The Cardinal Sin of unnest()

This brings us to the most important, “read-the-fine-manual-twice” pitfall with multi-dimensional arrays and unnest(). What happens when you unnest a 2D array?

SELECT unnest( ARRAY[ ['A', 'B'], ['C', 'D'] ] );

-- Result:
--  unnest
-- --------
--  A
--  B
--  C
--  D

It flattens the whole thing! It doesn’t return a set of arrays; it returns a set of the base elements. This is almost never what you want when you’re working with multi-dimensional data. You typically want to process one inner array at a time.

To do it right, you need to lean on the generate_subscripts() function. It’s a bit more verbose, but it’s the only way to get sane, predictable results.

-- How to properly process each inner array one by one
WITH twod_array AS (
  SELECT ARRAY[ ['A', 'B'], ['C', 'D'] ] AS data
)
SELECT
  i AS outer_index,
  data[i] AS inner_array
FROM twod_array, generate_subscripts(data, 1) AS i;

-- Result:
--  outer_index | inner_array
-- -------------+-------------
--            1 | {A,B}
--            2 | {C,D}

This is the golden ticket. The 1 in generate_subscripts(data, 1) refers to the first dimension of the array. For a 3D array, you’d need to nest these calls. It’s not pretty, but it’s correct.

So, Should You Even Use Them?

This is the million-dollar question. Multi-dimensional arrays are fantastic for storing rigid, fixed-dimension data that you want to treat as a single unit—think a game board, a mathematical matrix, or a small lookup grid that’s specific to a single row. They save you from creating a barrage of related tables for very tightly coupled data.

However, if you find yourself constantly fighting to unpack and manipulate them using generate_subscripts, that’s the database’s not-so-subtle way of telling you you’re wrong. The relational model is built on sets of rows. If your data wants to be a set, let it be a set. Normalize it into a proper table. You’ll thank yourself later when you need to write a readable query instead of an unmaintainable mess of array indexes.

Use them as a sharp tool in a specific compartment of your toolbox, not as a replacement for proper database design. They’re brilliant until they’re not.