Right, let’s talk about getting arrays into your database. You’ve got two main ways to do it, and while they look similar, they have a few quirks that’ll bite you if you aren’t paying attention. I’m here to make sure that doesn’t happen.

The Quick and Dirty: Array Literals

This is the way you’d probably guess if you just started typing. An array literal is a comma-separated list of elements, all enclosed in curly braces {}.

SELECT '{apple, banana, cherry}' AS fruit_basket;

Simple, right? But here’s the first ‘gotcha’ that makes database newcomers sigh: the literal string 'null' inside an array literal and the actual SQL NULL value are two completely different things. The designers, in their infinite wisdom, decided that an unquoted NULL inside the literal means the actual null value, while a quoted 'null' is just a string. I don’t love it, but it’s what we have to work with.

-- This array contains two actual NULL values and the string 'null'
SELECT '{NULL, NULL, "null"}' AS confusing_array;

Also, if your elements contain commas, whitespace, or those pesky curly braces themselves, you need to double-quote the little buggers. It gets messy fast.

-- This will fail because of the comma in the element.
SELECT '{Ben & Jerry's, Cherry Garcia}' AS ice_cream;

-- This is the correct, albeit uglier, way.
SELECT '{"Ben & Jerry's, Cherry Garcia"}' AS ice_cream;

See what I mean? It works, but writing all those escape sequences feels like you’re appeasing a particularly pedantic ancient god.

The Explicit and Robust: ARRAY[] Constructor

This is my preferred method for anything non-trivial. It feels more like SQL and less like a cryptic incantation. Instead of curly braces, you use the ARRAY keyword followed by a square-bracketed list of values [].

SELECT ARRAY['apple', 'banana', 'cherry'] AS fruit_basket;

The huge advantage here is that each element in the square brackets is a standard SQL expression. This means you can use other functions, subqueries, and, most importantly, you can use the actual NULL keyword without the ambiguity of the literal approach.

-- Much clearer: This array contains two NULL values and a string.
SELECT ARRAY[NULL, NULL, 'null'] AS clear_array;

-- You can even build an array from a subquery. Powerful stuff.
SELECT ARRAY(SELECT id FROM users WHERE active = true) AS active_user_ids;

The ARRAY[] constructor is also blissfully unaware of the quoting nightmares of the literal syntax. Commas, braces, and spaces? No problem, because each element is already a properly delimited value.

-- A breeze with the constructor.
SELECT ARRAY['Ben & Jerry''s, Cherry Garcia'] AS ice_cream;

So, Which One Should You Use?

Here’s the rule of thumb I’ve developed after being burned more than once:

  • Use the Array Literal ('{}') for hand-typed, simple, static arrays where the elements are basic numbers or short strings without special characters. It’s quicker to type for a quick SELECT test.
  • Use the ARRAY[] constructor for everything else. Especially for:
    • Any programmatically generated SQL (from your application code).
    • Arrays containing NULL values.
    • Arrays with elements that have commas, quotes, or braces.
    • Building arrays from subqueries (this is literally its superpower).

The constructor is just more explicit, more predictable, and integrates with the rest of the SQL language far more cleanly. It’s the choice of someone who knows they’ll be coming back to this code in six months and doesn’t want to hate their past self. Be that person.