---
title: "Understanding PostgreSQL Array Functions"
description: "Learn how to effectively use PostgreSQL array functions and operators for data manipulation, including array_agg(), multi-dimensional arrays."
section: "Postgres basics"
---

> **TimescaleDB is now Tiger Data.**

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](https://docs.timescale.com/#:~:text=What%20is%20TimescaleDB%3F).



## 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](https://www.timescale.com/learn/database-indexes-in-postgres) 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](https://www.timescale.com/learn/understanding-postgresql-user-defined-functions), including those that work with arrays.