Работа с массивами в 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? Это тема, которую мы подробно рассмотрим в следующей лекции, но сама идея проста: массивы дают нам гибкость и позволяют искать значения внутри массивов.

2

Задача

SQL SELF, 35 уровень, 1 лекция

Недоступна

Создание таблицы с массивами и выборка данных

Создание таблицы с массивами и выборка данных

Открыть