Робота з масивами в PostgreSQL

Робота з масивами в PostgreSQL

Тепер, коли ми знаємо базу, давай заглибимось у практичне створення масивів у SQL-запитах. Тут починається найцікавіше!

Використання конструктора ARRAY[] у SELECT

Конструктор ARRAY[] особливо зручний у SELECT-запитах, коли треба явно створити масив. Це як сказати PostgreSQL: «Гей, ось масив!».

-- Створення масиву чисел
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

-- Створення масиву рядків
SELECT ARRAY['Понеділок', 'Вівторок', 'Середа'] AS weekdays;

Переваги ARRAY[] над синтаксисом {}

1. Явне приведення типів:

-- З ARRAY[] можна явно вказати тип
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS dates;

-- З {} треба бути обережніше
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS dates;

2. Краща читабельність у складних запитах:

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

Приклад: створення масиву чисел

Почнемо з класики. Припустимо, тобі треба створити масив із чисел:

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

Результат буде таким:

Зверни увагу: PostgreSQL повертає масив у форматі {} — це просто його спосіб показати, що це масив. Стиль специфічний, але швидко звикаєш.

Приклад: створення масиву рядків

Якщо тобі потрібні рядки замість цифр, просто додай лапки:

SELECT ARRAY['яблуко', 'банан', 'апельсин'] AS fruits;

Результат:

До речі, PostgreSQL обожнює спрощувати життя. Навіть якщо ти використовуєш кирилицю чи будь-який інший алфавіт, масиви все одно працюватимуть бездоганно.

Приклад: масиви з даними інших типів (наприклад, дати)

А що, якщо ми захочемо покласти масив із дат? Легко:

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

Результат:

Зверни увагу на ::DATE. Ми явно вказали PostgreSQL, що це тип даних DATE. Без цього він міг би взяти рядки як є, а це було б не зовсім коректно для дат.

Агрегація даних у масиви з array_agg()

А тепер переходимо до складнішої і цікавішої частини. Що, якщо у нас вже є таблиця з даними, і треба згрупувати їх у масив? Тут на допомогу приходить функція array_agg().

Одна з найпотужніших можливостей — перетворення багатьох рядків у масиви за допомогою array_agg().

Базове використання:

-- Створимо тестову таблицю
CREATE TEMP TABLE students (
group_id INTEGER,
student_name TEXT
);

INSERT INTO students VALUES
(1, 'Анна'), (1, 'Отто'), (1, 'Марія'),
(2, 'Алекс'), (2, 'Кіра'),
(3, 'Єлена');

-- Групуємо студентів по групах
SELECT
group_id,
array_agg(student_name) AS students
FROM students
GROUP BY group_id
ORDER BY group_id;

Сортування елементів у масиві:

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

Фільтрація при агрегації:

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

Практичні приклади використання

Масиви корисні у багатьох повсякденних сценаріях: від зберігання тегів і прав доступу до збору дій користувача за день. Нижче наведені приклади, які допоможуть краще зрозуміти, як і де застосовувати масиви в PostgreSQL.

Приклад 1: Система тегів для блогу

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

-- Вставка з різними синтаксисами
INSERT INTO blog_posts (title, content, tags) VALUES
    ('Вивчаємо PostgreSQL', 'Зміст статті...',
     ARRAY['PostgreSQL', 'SQL', 'База даних']),
    ('Веб-розробка у 2024', 'Зміст статті...',
     '{"JavaScript", "React", "Node.js"}'),
    ('Машинне навчання', 'Зміст статті...',
     ARRAY['ML', 'Python', 'Data Science']);

-- Пошук статей за тегами
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

Приклад 2: Система дозволів користувачів

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

-- Агрегація всіх унікальних дозволів у системі
SELECT array_agg(DISTINCT permission) AS all_permissions
FROM users, unnest(permissions) AS permission;

Приклад 3: Історія дій користувача

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

-- Групуємо дії користувача по днях
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. Запити з масивами: вибірка і фільтрація

Коли у нас є масиви, треба вміти їх витягати й аналізувати. Ти можеш використовувати стандартний SELECT, щоб отримати масив:

SELECT tags FROM articles WHERE id = 1;

Це поверне:

Але що робити, якщо треба знайти статтю, у якої є певний тег, наприклад PostgreSQL? Це тема, яку ми детально розглянемо в наступній лекції, але сама ідея проста: масиви дають нам гнучкість і дозволяють шукати значення всередині масивів.