PostgreSQL Array
Store multiple values in a single column
📦 What are PostgreSQL Arrays?
PostgreSQL arrays allow you to store multiple values of the same data type in a single column. This powerful feature helps organize related data efficiently without creating separate tables.
-- Creating a table with an array column
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
grades INTEGER[]
);
Key Array Concepts
Declaration
Define array columns with [] syntax
column_name datatype[]Insertion
Insert arrays using curly braces
'{value1, value2, value3}'Access
Access elements by index (1-based)
array_column[1]Functions
Built-in functions for array operations
array_length(), unnest()🔹 Creating Arrays
Arrays in PostgreSQL store multiple values of the same type. You can create arrays using different methods for organizing related data efficiently.
-- Create table with array columns
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
tags TEXT[],
prices DECIMAL[]
);
-- Insert data with arrays
INSERT INTO products (name, tags, prices)
VALUES
('Laptop', '{"electronics", "computers", "tech"}', '{999.99, 899.99}'),
('Book', '{"education", "reading"}', '{29.99, 19.99}');Result:
id | name | tags | prices
---+--------+-----------------------------------+------------------
1 | Laptop | {electronics,computers,tech} | {999.99,899.99}
2 | Book | {education,reading} | {29.99,19.99}
🔹 Accessing Array Elements
PostgreSQL uses 1-based indexing to access array elements. You can retrieve specific elements or slices from arrays using bracket notation.
-- Access first element (index starts at 1)
SELECT name, tags[1] AS first_tag
FROM products;
-- Access multiple elements (slice)
SELECT name, tags[1:2] AS first_two_tags
FROM products;Result:
name | first_tag -------+----------- Laptop | electronics Book | education
🔹 Array Functions
PostgreSQL provides powerful built-in functions for working with arrays. These functions help you manipulate, search, and analyze array data effectively.
-- Get array length
SELECT name, array_length(tags, 1) AS tag_count
FROM products;
-- Unnest array (convert to rows)
SELECT name, unnest(tags) AS individual_tag
FROM products;
-- Check if array contains value
SELECT name
FROM products
WHERE 'electronics' = ANY(tags);Result (array_length):
name | tag_count -------+----------- Laptop | 3 Book | 2
🔹 Modifying Arrays
You can update array values by appending elements, removing items, or replacing entire arrays using PostgreSQL's array operators and functions.
-- Append element to array
UPDATE products
SET tags = array_append(tags, 'bestseller')
WHERE name = 'Book';
-- Prepend element to array
UPDATE products
SET tags = array_prepend('new', tags)
WHERE name = 'Laptop';
-- Concatenate arrays
UPDATE products
SET prices = prices || ARRAY[799.99]
WHERE name = 'Laptop';🔹 Searching in Arrays
PostgreSQL offers multiple operators to search within arrays. You can check for element existence, overlaps, or containment using intuitive operators.
-- Find products with specific tag
SELECT name
FROM products
WHERE 'tech' = ANY(tags);
-- Find products with all specified tags
SELECT name
FROM products
WHERE tags @> ARRAY['electronics', 'computers'];
-- Find products with overlapping tags
SELECT name
FROM products
WHERE tags && ARRAY['reading', 'tech'];