Right, let’s talk about the weird and wonderful symbols PostgreSQL uses to make arrays actually useful. You’ve got your array full of data, great. Now what? You need to do something with it. You need to ask questions like “does this array contain my favorite number?” or “do these two arrays have anything in common?” This is where the array operators come in. They look a bit like someone fell asleep on the keyboard, but once you learn them, you’ll wonder how you lived without them.

Forget simple = for a second; comparing arrays is a more nuanced game. Let’s break down the key players.

The Containment Operators (@> and <@)

These two are the workhorses. The @> operator checks if the array on the left contains all the elements of the array on the right. Its sibling <@ checks if the left array is contained by the right one. I know, the direction of the symbol feels backwards. Just remember: the “mouth” always opens to the bigger, containing array.

-- Does our superhero's arsenal contain both a grapnel and shurikens?
SELECT ARRAY['grapnel', 'batarang', 'shuriken'] @> ARRAY['grapnel', 'shuriken'];
-- returns true

-- Is our list of suspects contained by the list of known criminals?
SELECT ARRAY['Red Claw', 'Professor Pyg'] <@ ARRAY['Joker', 'Professor Pyg', 'Red Claw', 'Bane'];
-- returns true

-- Order does NOT matter. These are existence checks.
SELECT ARRAY[1, 2, 3, 4] @> ARRAY[3, 1];
-- returns true

-- But duplicates do matter. The contained array must not have *more* of any element.
SELECT ARRAY[1, 2, 2, 3] @> ARRAY[2, 2, 2]; -- Does the left array contain three 2s?
-- returns false (it only has two)

The most common pitfall here is forgetting that you’re almost always comparing an array column to an array literal. You’ll see this all the time in WHERE clauses.

-- Find all products that have both 'electronics' and 'smart-home' tags.
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'smart-home'];

The Overlap Operator (&&)

This one is beautifully simple. It answers the question: “Do these two arrays share any elements at all?” It’s a straight-up intersection check. If they have even one element in common, it returns true.

-- Do these two friends have any favorite movies in common?
SELECT ARRAY['Casablanca', 'The Godfather', 'Airplane!'] && ARRAY['Fargo', 'Airplane!', 'Die Hard'];
-- returns true (thanks to 'Airplane!')

-- Do these allergy lists overlap? If true, maybe don't serve this meal.
SELECT ARRAY['peanuts', 'shellfish'] && ARRAY['gluten', 'soy', 'peanuts'];
-- returns true

This operator is incredibly useful for negative checks too. “Find all users who have none of these banned keywords in their bio.”

SELECT * FROM users WHERE NOT (bio_keywords && ARRAY['spam', 'bot', 'malware']);

The Concatenation Operator (||)

This one does exactly what you’d expect: it slams two arrays together. It’s the array version of + for strings.

SELECT ARRAY[1, 2, 3] || ARRAY[4, 5, 6];
-- result: {1,2,3,4,5,6}

-- You can also use it to append a single element to an array.
-- This is where it subtly but importantly differs from array_append().
SELECT ARRAY[1, 2, 3] || 4;
-- result: {1,2,3,4}

Now, a major gotcha. The concatenation operator does not remove duplicates. It’s a dumb, mechanical operation. If you concatenate {1,2} and {2,3}, you get {1,2,2,3}. If you want a true “union” that removes dupes, you need to look elsewhere, like the UNION statement within a SELECT or the array_agg functions after an UNNEST.

The Equality Operator (=)

This is the strict schoolteacher of the bunch. For = to return true, the two arrays must be identical: same elements, same order, and same multiplicity (number of occurrences).

SELECT ARRAY[1, 2, 3] = ARRAY[1, 2, 3];
-- returns true

SELECT ARRAY[1, 2, 3] = ARRAY[3, 2, 1];
-- returns false (wrong order)

SELECT ARRAY[1, 2, 2] = ARRAY[1, 2];
-- returns false (different multiplicity)

This strictness is why you’ll use @> and <@ for membership checks far more often than you’ll use = for direct comparison. The real world is often messy, and your queries should be able to handle that.

So there you have it. These operators are what transform arrays from a passive storage format into something you can actively query against. Use them to ask smart, set-based questions of your data, and you’ll unlock a ton of power that would be clunky and inefficient to handle any other way.