Right, let’s talk about actually doing things with arrays. You’ve got this collection of stuff, but it’s useless if you can’t interrogate it, modify it, or generally boss it around. That’s where these functions come in. They’re your basic toolkit for array manipulation, and while they seem simple, a few of them have quirks that will absolutely bite you if you’re not paying attention.

array_length: The “How Much Stuff Is In Here?” Function

This one is straightforward. array_length(your_array, dimension) tells you how many elements are in a specified dimension of your array. For 99.9% of your work, you’ll be dealing with one-dimensional arrays, so you’ll just use 1 for the dimension.

SELECT array_length(ARRAY['a', 'b', 'c'], 1); -- Returns 3
SELECT array_length(ARRAY[]::int[], 1);       -- Returns NULL
SELECT array_length(NULL::int[], 1);          -- Also returns NULL

See that last one? This is your first pitfall. If the array is NULL, array_length returns NULL. If it’s an empty array, it also returns NULL. This is one of those design choices that feels a bit… questionable. It means you can’t distinguish between “I have no elements” (empty array) and “I have no array at all” (NULL). For most checks, you’ll want to use cardinality() instead, which returns 0 for an empty array. But more on that in a bit.

array_append: Sticking Things on the End

The name says it all: array_append(anyarray, anyelement) takes an array and an element, and returns a new array with that element tacked onto the end. It does not modify the original array—a crucial point. In SQL, almost nothing modifies data in place; everything returns a new value.

SELECT array_append(ARRAY[1, 2, 3], 4); -- Returns {1,2,3,4}

Simple, right? Here’s the absurd part. What if you want to prepend an element to the beginning? You might look for an array_prepend function. And you’d be right, it exists. But the designers, in their infinite wisdom, decided to put the arguments in the opposite order. I’m not kidding.

SELECT array_prepend(0, ARRAY[1, 2, 3]); -- Returns {0,1,2,3}

Why is array_append(array, element) but array_prepend(element, array)? Your guess is as good as mine. It’s inconsistent and a guaranteed source of momentary confusion. I just remember it as “the new thing goes on the outside.”

array_cat: Smashing Arrays Together

When array_append isn’t enough and you need to combine two whole arrays, you reach for array_cat(anyarray, anyarray). It concatenates them.

SELECT array_cat(ARRAY[1, 2], ARRAY[3, 4, 5]); -- Returns {1,2,3,4,5}

This is where you start to see the power. You can build complex arrays dynamically. But be warned: this isn’t a “flatten” operation. If you concatenate multidimensional arrays, it behaves exactly as you’d expect from a linear algebra perspective, which might not be what a naive user expects.

SELECT array_cat(ARRAY[[1, 2]], ARRAY[[3, 4]]); -- Returns {{1,2},{3,4}}

It didn’t create {1,2,3,4}; it created a new two-dimensional array with two elements (each of which is an array itself). This is correct and powerful, but you must be intentional about your dimensions.

array_remove: The Subtle Art of Deletion

array_remove(anyarray, anyelement) removes all elements equal to the given value from the array. Sounds simple.

SELECT array_remove(ARRAY[1, 2, 3, 2, 1], 2); -- Returns {1,3,1}

Now, for the edge cases. What if the element isn’t there? You just get the original array back. No harm, no foul. What if the array is NULL? You get NULL back. What if the element is NULL? Ah, here’s the fun one.

SELECT array_remove(ARRAY[1, NULL, 3], NULL); -- Returns {1,3}

Wait, it worked? But I thought you couldn’t compare NULL with =? You’re right, you can’t. This is a rare exception baked directly into the array_remove function. It uses IS NOT DISTINCT FROM semantics internally, which treats NULL as comparable to NULL. So it can indeed find and remove NULL elements. This is a lifesaver and a piece of design I actually applaud.

Why cardinality() is Often Your Best Friend

I told you I’d come back to this. The cardinality() function is a more robust way to check the size of an array in many cases. While array_length returns NULL for empty arrays, cardinality() returns 0.

SELECT array_length(ARRAY[]::int[], 1); -- NULL
SELECT cardinality(ARRAY[]::int[]);     -- 0

This makes it much easier to use in WHERE clauses or conditional logic without having to constantly guard against NULL. Want to find all rows with non-empty arrays? WHERE cardinality(my_array) > 0 is clear and correct. Using array_length would require a NULL check too. Use cardinality() unless you specifically need the dimension-aware functionality of array_length.

The key takeaway with all of these functions is to think in terms of expressions and new values, not in-place mutation. You use these functions to create a new array value based on an old one, which you can then assign back to a column or variable. It’s a functional programming approach that, once you get used to it, is incredibly powerful and composable.