How to Use @> Operator for Array Contains in PostgreSQL

In PostgreSQL, the @> operator checks if the left array contains all elements of the right array. Use it in a WHERE clause like array_column @> ARRAY['value'] to filter rows where the array includes specific elements.

Syntax

The @> operator tests if the left array contains the right array completely. It returns true if every element in the right array is found in the left array.

• left_array @> right_array: Checks if left_array contains all elements of right_array.
• Both sides must be arrays of the same data type.

sql

left_array @> right_array

Example

This example shows how to find rows where the tags array contains the element 'red'. It also shows checking for multiple elements.

sql

CREATE TABLE products (id SERIAL PRIMARY KEY, tags TEXT[]);

INSERT INTO products (tags) VALUES
  (ARRAY['red', 'blue', 'green']),
  (ARRAY['yellow', 'red']),
  (ARRAY['blue', 'black']),
  (ARRAY['red']);

-- Find products containing 'red'
SELECT id, tags FROM products WHERE tags @> ARRAY['red'];

-- Find products containing both 'red' and 'blue'
SELECT id, tags FROM products WHERE tags @> ARRAY['red', 'blue'];

Output

 id |         tags         
----+----------------------
  1 | {red,blue,green}
  2 | {yellow,red}
  4 | {red}
(3 rows)

 id |         tags         
----+----------------------
  1 | {red,blue,green}
(1 row)

Common Pitfalls

Common mistakes when using @> include:

• Using scalar values instead of arrays on the right side. The right side must be an array, e.g., ARRAY['value'], not just 'value'.
• Confusing @> with <@ which checks if the left array is contained by the right array.
• Not matching data types on both sides, which causes errors.

sql

/* Wrong: scalar on right side */
SELECT ARRAY['a', 'b'] @> 'a'; -- ERROR

/* Right: array on right side */
SELECT ARRAY['a', 'b'] @> ARRAY['a']; -- TRUE

Quick Reference

Operator	Meaning	Example	Result
@>	Left array contains right array	ARRAY['a','b','c'] @> ARRAY['b','c']	true
<@	Left array is contained by right array	ARRAY['b','c'] <@ ARRAY['a','b','c']	true
&&	Arrays overlap (any common elements)	ARRAY['a','b'] && ARRAY['b','c']	true

Key Takeaways

Use @> to check if one array contains all elements of another array in PostgreSQL.

Always provide the right side as an array using ARRAY[...] syntax.

Ensure both arrays have the same data type to avoid errors.

Remember @> is different from <@ which checks containment in the opposite direction.

Use @> in WHERE clauses to filter rows based on array contents efficiently.