Array Comparison and Filtering: =, @>, <@, &&

Sometimes you run into situations where you need to figure out if one set of values is inside another, if they overlap, if they're equal, or if one is a subset of the other. For example:

• Does a user have access to certain categories?
• Which products share tags?
• Which arrays contain all the listed values?

You can solve these tasks using PostgreSQL's array comparison operators: =, @>, <@, and &&. Let's dive into each of these operators and break them down with examples.

= — Array Equality Comparison

This operator checks if two arrays are identical. The condition returns TRUE if both arrays have the same length and the exact same set of elements in the exact same order.

Example:

SELECT ARRAY[1, 2, 3] = ARRAY[1, 2, 3] AS are_equal;  -- TRUE
SELECT ARRAY[1, 2, 3] = ARRAY[3, 2, 1] AS are_equal;  -- FALSE

Heads up: order matters. The array [1, 2, 3] is not equal to [3, 2, 1].

@> — Array Contains Another Array

This operator checks if an array contains all the elements of another array (order doesn't matter). It's often used when you need to make sure all the required elements are present.

Example:

SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2] AS contains;  -- TRUE
SELECT ARRAY[1, 2, 3] @> ARRAY[4] AS contains;     -- FALSE

Handy use case: checking access rights. For example, you want to make sure a certain role includes all the necessary permissions.

<@ — Array Is a Subset of Another Array

This is the opposite of the @> operator. It checks if an array is fully contained within another array.

Example:

SELECT ARRAY[1, 2] <@ ARRAY[1, 2, 3] AS is_subset;  -- TRUE
SELECT ARRAY[4] <@ ARRAY[1, 2, 3] AS is_subset;     -- FALSE

Handy use case: when you need to check if a specific row in a table has all the required labels or categories.

&& — Array Intersection

This operator checks if two arrays overlap, meaning they have at least one element in common.

Example:

SELECT ARRAY[1, 2, 3] && ARRAY[3, 4, 5] AS intersects;  -- TRUE
SELECT ARRAY[1, 2, 3] && ARRAY[6, 7, 8] AS intersects;  -- FALSE

Handy use case: finding products or records that share categories or features.

Using Operators to Filter Data

Let's use these operators to filter data in a table. For example, say we have a products table with a tags column that stores an array of tags for each product.

Example products table

Let's find all products that have the electronics tag.

SELECT *
FROM products
WHERE tags @> ARRAY['electronics'];

Result:

Now let's find products whose tags are exactly {home, sale}.

SELECT *
FROM products
WHERE tags = ARRAY['home', 'sale'];

Result:

Now let's

find products that have at least one of the tags {sale, new}.

SELECT *
FROM products
WHERE tags && ARRAY['sale', 'new'];

Result:

Let's look for products where the tags are exactly limited to {home, garden}.

SELECT *
FROM products
WHERE tags <@ ARRAY['home', 'garden'];

Result:

Example: Checking Array Contents with CASE

We can combine array comparison with conditional expressions. For example, let's write a query that marks products containing the sale tag as "Discount", and the rest as "No Discount":

SELECT name,
       CASE
         WHEN tags @> ARRAY['sale'] THEN 'Discount'
         ELSE 'No Discount'
       END AS discount_status
FROM products;

Result:

Common Mistakes and How to Avoid Them

Mistake: using the wrong order with the = operator. Remember, = requires the exact order of elements to match. If order doesn't matter, use @> or && instead.

Mistake: data type mismatch. Arrays must be of the same type. For example, ARRAY['1', '2'] is not equal to ARRAY[1, 2] because the first is a string array and the second is numeric. Always check types before comparing.

Mistake: missing indexes. If you filter rows by arrays a lot, make sure to create indexes (GIN) to boost performance.

These operators let you build powerful filtering and comparison logic that comes up all the time in real-world projects: from analyzing product tags to managing access rights. Use them smartly and you'll make complex tasks easier and your queries faster.