SQL Academy · Lesson

Searching Inside Arrays

ANY, contains and overlap operators.

Arrays Store Multiple Values

In PostgreSQL, an array column holds multiple values in a single cell. Before you can search inside arrays, it helps to see what they look like.

The query below creates a table where each product can belong to several categories stored as a text array.

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

INSERT INTO products (name, tags) VALUES
  ('Laptop',     ARRAY['electronics', 'computers', 'sale']),
  ('T-Shirt',    ARRAY['clothing', 'summer', 'sale']),
  ('Coffee Mug', ARRAY['kitchen', 'gifts']),
  ('Headphones', ARRAY['electronics', 'audio']),
  ('Notebook',   ARRAY['stationery', 'office']);

Checking Membership with ANY

The ANY operator lets you test whether a value appears anywhere in an array. The syntax is: value = ANY(array_column).

This query finds every product tagged with electronics.

SELECT name, tags
FROM products
WHERE 'electronics' = ANY(tags);

All lessons in this course

← Back to SQL Academy