Working with Arrays in PostgreSQL

Working with Arrays in PostgreSQL

Now that we know the basics, let’s dive into actually creating arrays in SQL queries. This is where things get really fun!

Using the ARRAY[] Constructor in SELECT

The ARRAY[] constructor is super handy in SELECT queries when you want to explicitly create an array. It’s like telling PostgreSQL, “Hey, here’s an array!”

-- Creating an array of numbers
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

-- Creating an array of strings
SELECT ARRAY['Monday', 'Tuesday', 'Wednesday'] AS weekdays;

Advantages of ARRAY[] over {} Syntax

1. Explicit type casting:

-- With ARRAY[] you can explicitly set the type
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS dates;

-- With {} you gotta be more careful
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS dates;

2. Better readability in complex queries:

SELECT
product_name,
ARRAY[category, subcategory, brand] AS product_hierarchy
FROM products;

Example: Creating an Array of Numbers

Let’s start with a classic. Say you need to create an array of numbers:

SELECT ARRAY[1, 2, 3, 4, 5] AS my_array;

The result will look like this:

Notice: PostgreSQL returns the array in {} format — that’s just its way of showing it’s an array. The style is kinda unique, but you get used to it fast.

Example: Creating an Array of Strings

If you want strings instead of numbers, just add quotes:

SELECT ARRAY['apple', 'banana', 'orange'] AS fruits;

Result:

By the way, PostgreSQL loves making life easier. Even if you use Cyrillic or any other alphabet, arrays will still work flawlessly.

Example: Arrays with Other Data Types (like Dates)

What if we want to put an array of dates? Easy as pie:

SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS important_dates;

Result:

Check out the ::DATE. We told PostgreSQL straight up that this is a DATE type. Without it, it might just take the strings as-is, which isn’t really what you want for dates.

Aggregating Data into Arrays with array_agg()

Now let’s get to the more advanced and interesting part. What if you already have a table with data, and you need to group it into arrays? That’s where the array_agg() function comes in.

One of the coolest features — turning a bunch of rows into arrays using array_agg().

Basic usage:

-- Let’s make a test table
CREATE TEMP TABLE students (
group_id INTEGER,
student_name TEXT
);

INSERT INTO students VALUES
(1, 'Anna'), (1, 'Otto'), (1, 'Maria'),
(2, 'Alex'), (2, 'Kira'),
(3, 'Elena');

-- Group students by group
SELECT
group_id,
array_agg(student_name) AS students
FROM students
GROUP BY group_id
ORDER BY group_id;

Sorting elements in the array:

SELECT
group_id,
array_agg(student_name ORDER BY student_name) AS students_sorted
FROM students
GROUP BY group_id;

Filtering while aggregating:

SELECT
group_id,
array_agg(student_name) FILTER (WHERE student_name LIKE 'A%') AS students_a
FROM students
GROUP BY group_id;

Practical Usage Examples

Arrays are super useful in lots of everyday scenarios: from storing tags and access rights to collecting user actions for the day. Here are some examples to help you get a better feel for how and where to use arrays in PostgreSQL.

Example 1: Tag System for a Blog

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    tags TEXT[]
);

-- Inserting with different syntaxes
INSERT INTO blog_posts (title, content, tags) VALUES
    ('Learning PostgreSQL', 'Article content...',
     ARRAY['PostgreSQL', 'SQL', 'Database']),
    ('Web Development in 2024', 'Article content...',
     '{"JavaScript", "React", "Node.js"}'),
    ('Machine Learning', 'Article content...',
     ARRAY['ML', 'Python', 'Data Science']);

-- Find articles by tag
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

Example 2: User Permissions System

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
permissions TEXT[]
);

INSERT INTO users (username, permissions) VALUES
('admin', ARRAY['read', 'write', 'delete', 'manage_users']),
('editor', ARRAY['read', 'write']),
('viewer', ARRAY['read']);

-- Aggregate all unique permissions in the system
SELECT array_agg(DISTINCT permission) AS all_permissions
FROM users, unnest(permissions) AS permission;

Example 3: User Action History

CREATE TABLE user_actions (
user_id INTEGER,
action TEXT,
action_date DATE
);

INSERT INTO user_actions VALUES
(1, 'login', '2024-01-01'),
(1, 'view_profile', '2024-01-01'),
(1, 'edit_settings', '2024-01-01'),
(2, 'login', '2024-01-01'),
(2, 'logout', '2024-01-01');

-- Group user actions by day
SELECT
user_id,
action_date,
array_agg(action ORDER BY action) AS daily_actions
FROM user_actions
GROUP BY user_id, action_date
ORDER BY user_id, action_date;

4. Queries with Arrays: Selection and Filtering

Once we have arrays, we need to know how to pull them out and analyze them. You can use a standard SELECT to get an array:

SELECT tags FROM articles WHERE id = 1;

This will return:

But what if you need to find an article that has a specific tag, like PostgreSQL? That’s a topic we’ll dig into in the next lecture, but the idea is simple: arrays give you flexibility and let you search for values inside arrays.