PostgreSQL Array Operations

What are PostgreSQL Arrays?

PostgreSQL supports array data types for any built-in or user-defined type. A column can store multiple values of the same type as an array.

Syntax

-- Create a column with an array type
tags TEXT[]

-- Insert array values
INSERT INTO products (tags) VALUES (ARRAY['electronics', 'sale', 'featured']);

-- Access elements (1-indexed)
SELECT tags[1] FROM products;

-- Check if array contains a value
SELECT * FROM products WHERE 'sale' = ANY(tags);

Key Points

1. 1-Indexed — PostgreSQL arrays start at index 1, not 0.
2. ANY and ALL — value = ANY(array) checks membership. value = ALL(array) checks all elements.
3. Unnest — unnest(array) expands an array into rows.
4. Array Functions — array_length, array_append, array_remove, array_cat.
5. GIN Index — Create a GIN index on array columns for fast ANY and @> queries.
6. Array Containment — array1 @> array2 checks if array1 contains all elements of array2.