Published at Feb 12, 2025

Understanding PostgreSQL Array Functions

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.

PostgreSQL Array Basics and Creation

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']);

Essential Array Functions

Let's explore some of the most useful array functions that PostgreSQL provides:

array_length() and array_dims()

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]

array_append(), array_prepend(), and array_cat()

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}

array_remove() and array_replace()

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}

Working With Array Aggregation

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}

Array Operators and Comparisons

PostgreSQL provides several operators for working with arrays:

Contains (@>) and Contained by (<@)

-- 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'];

Overlaps (&&)

-- 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;

Working With Multi-dimensional Arrays

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;

Performance Considerations

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.

Conclusion

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.