How to Query Array in PostgreSQL: Syntax and Examples

In PostgreSQL, you can query arrays using the ANY and ALL operators, or array-specific functions like unnest(). Use WHERE column_name @> ARRAY[value] to check if an array contains a value, and unnest() to expand array elements into rows for detailed queries.

Syntax

PostgreSQL provides several ways to query arrays:

• column_name @> ARRAY[value]: Checks if the array contains the specified value(s).
• value = ANY(column_name): Checks if the value is present in the array.
• unnest(column_name): Expands array elements into individual rows.

These allow flexible filtering and searching inside array columns.

sql

SELECT * FROM table_name WHERE column_name @> ARRAY['value'];

SELECT * FROM table_name WHERE 'value' = ANY(column_name);

SELECT id, unnest(column_name) AS element FROM table_name;

Example

This example shows how to find rows where the array contains a specific value and how to list each element of the array in separate rows.

sql

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

INSERT INTO users (name, tags) VALUES
('Alice', ARRAY['sports', 'music']),
('Bob', ARRAY['music', 'travel']),
('Carol', ARRAY['travel', 'cooking']);

-- Find users with 'music' tag
SELECT * FROM users WHERE tags @> ARRAY['music'];

-- List each tag per user
SELECT id, name, unnest(tags) AS tag FROM users ORDER BY id;

Output

 id | name  |      tags       
----+-------+-----------------
  1 | Alice | {sports,music}
  2 | Bob   | {music,travel}
  3 | Carol | {travel,cooking}

 id | name  |   tag   
----+-------+---------
  1 | Alice | sports  
  1 | Alice | music   
  2 | Bob   | music   
  2 | Bob   | travel  
  3 | Carol | travel  
  3 | Carol | cooking 

Common Pitfalls

Common mistakes when querying arrays include:

• Using = instead of @> or ANY() to check for values inside arrays, which will not work as expected.
• Forgetting to use ARRAY[] syntax when comparing arrays.
• Not using unnest() when you want to work with individual array elements as rows.

sql

/* Wrong: This looks for exact array match, not element */
SELECT * FROM users WHERE tags = 'music';

/* Right: Use ANY() to check if 'music' is in tags */
SELECT * FROM users WHERE 'music' = ANY(tags);

Quick Reference

Operator/Function	Description	Example Usage
@>	Checks if array contains specified elements	tags @> ARRAY['music']
ANY	Checks if a value is in the array	'music' = ANY(tags)
unnest()	Expands array elements into rows	SELECT unnest(tags) FROM users

Key Takeaways

Use @> to check if an array contains specific values in PostgreSQL.

Use ANY() to test if a single value exists inside an array column.

Use unnest() to convert array elements into individual rows for detailed queries.

Avoid using = to compare arrays unless you want exact matches.

Always use ARRAY[] syntax when working with array literals.