Published at Feb 12, 2025
Start supercharging your PostgreSQL today.
PostgreSQL's array support provides powerful capabilities for storing and manipulating collections of values within a single column. In this article, we'll explore the essential array functions and operators that can help you work more effectively with array data types in PostgreSQL or TimescaleDB.
In PostgreSQL, you can create arrays using curly braces or the ARRAY constructor. Here's a simple example using a table of product tags:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
tags TEXT[]
);
-- Insert using curly brace notation
INSERT INTO products (name, tags) VALUES
('Laptop Pro', '{"electronics", "computers", "laptops"}');
-- Insert using ARRAY constructor
INSERT INTO products (name, tags) VALUES
('Gaming Mouse', ARRAY['electronics', 'gaming', 'accessories']);
Let's explore some of the most useful array functions that PostgreSQL provides:
These functions help you understand the size and dimensions of your arrays:
SELECT array_length(ARRAY[[1,2,3], [4,5,6]], 1); -- Returns 2 (number of rows)
SELECT array_length(ARRAY[[1,2,3], [4,5,6]], 2); -- Returns 3 (number of columns)
SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]); -- Returns [1:2][1:3]
These functions allow you to add elements to arrays:
-- Add a new tag to a product
UPDATE products
SET tags = array_append(tags, 'premium')
WHERE name = 'Laptop Pro';
-- Prepend a category
UPDATE products
SET tags = array_prepend('tech', tags)
WHERE name = 'Gaming Mouse';
-- Combine two arrays
SELECT array_cat(
ARRAY[1, 2, 3],
ARRAY[4, 5, 6]
); -- Returns {1,2,3,4,5,6}
These functions help modify array contents:
-- Remove a specific tag
UPDATE products
SET tags = array_remove(tags, 'electronics')
WHERE name = 'Laptop Pro';
-- Replace all occurrences of one value with another
SELECT array_replace(
ARRAY[1, 2, 2, 3],
2,
5
); -- Returns {1,5,5,3}
The array_agg() function is particularly useful for grouping related rows into arrays:
-- Create a table for order items
CREATE TABLE order_items (
order_id INTEGER,
product_name TEXT,
quantity INTEGER
);
-- Insert sample data
INSERT INTO order_items VALUES
(1, 'Laptop Pro', 1),
(1, 'Gaming Mouse', 2),
(2, 'Gaming Mouse', 1);
-- Group products by order
SELECT
order_id,
array_agg(product_name) as products,
array_agg(quantity) as quantities
FROM order_items
GROUP BY order_id;
This query might return:
order_id | products | quantities
----------+---------------------------------+--------------
1 | {Laptop Pro,Gaming Mouse} | {1,2}
2 | {Gaming Mouse} | {1}
PostgreSQL provides several operators for working with arrays:
-- Check if an array contains specific elements
SELECT ARRAY[1,2,3] @> ARRAY[2,3]; -- Returns true
SELECT ARRAY[2,3] <@ ARRAY[1,2,3]; -- Returns true
-- Find products with specific tags
SELECT name
FROM products
WHERE tags @> ARRAY['electronics', 'gaming'];
-- Check if arrays share any elements
SELECT ARRAY[1,2,3] && ARRAY[3,4,5]; -- Returns true
-- Find products that have any tags in common
SELECT p1.name, p2.name
FROM products p1, products p2
WHERE p1.id < p2.id AND p1.tags && p2.tags;
PostgreSQL supports multi-dimensional arrays, which can be useful for matrix operations or structured data:
-- Create a matrix
CREATE TABLE matrices (
id SERIAL PRIMARY KEY,
data INTEGER[][]
);
INSERT INTO matrices (data) VALUES
('{{1,2,3}, {4,5,6}, {7,8,9}}');
-- Access specific elements
SELECT
data[1][1] as top_left,
data[2][2] as center,
data[3][3] as bottom_right
FROM matrices
WHERE id = 1;
When working with arrays in PostgreSQL, keep these performance tips in mind:
1. Use GiST or GIN indexes for array columns when you frequently search using the @>, <@, or && operators:
CREATE INDEX idx_products_tags
ON products USING GIN (tags);
2. Arrays are stored in-line unless they are very large, which means accessing small arrays is typically fast.
3. Array operations can be memory-intensive, especially with large arrays or when using array_agg() on large result sets.
PostgreSQL's array functions and operators provide a robust toolkit for handling collections of values within your database. These tools can help you write more concise and maintainable code.
Some key takeaways:
Use array_agg() for grouping related data.
Leverage array operators for efficient searching and comparison.
While PostgreSQL's built-in array functions are powerful, you may need to create custom functions for your specific array processing needs. Learn more about how to build your own reusable functions, including those that work with arrays.