Пошук уроків, статей та іншого контенту
Навчитеся створювати масиви, звертатися до їхніх елементів, фільтрувати та змінювати значення.
Масив — це значення, яке містить кілька елементів одного типу. Масиви зручно використовувати, коли запис має невелику та логічно пов’язану колекцію значень:
теги статті;
ролі користувача;
підтримувані мови;
ідентифікатори пов’язаних об’єктів.
Масиви PostgreSQL можуть містити значення базових типів, наприклад text[], integer[], boolean[] або date[].
CREATE TABLE articles (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
tags text[],
related_ids integer[]
);Тип text[] означає масив текстових значень, а integer[] — масив цілих чисел.
ARRAYНайзрозуміліший спосіб створити масив — використати конструктор ARRAY:
SELECT ARRAY['postgresql', 'sql', 'database'] AS tags;Для числових значень:
SELECT ARRAY[10, 20, 30] AS numbers;PostgreSQL визначає тип масиву за його елементами:
SELECT
ARRAY['a', 'b'] AS text_array,
ARRAY[1, 2, 3] AS integer_array,
ARRAY[true, false] AS boolean_array;Усі елементи одного масиву повинні бути сумісними з одним типом.
Масив також можна записати як рядок у фігурних дужках:
SELECT '{postgresql,sql,database}'::text[] AS tags;
SELECT '{10,20,30}'::integer[] AS numbers;Для нового коду зазвичай зручніше використовувати ARRAY[...], оскільки цей синтаксис краще передає структуру значення.
INSERT INTO articles (title, tags, related_ids)
VALUES (
'Масиви PostgreSQL',
ARRAY['postgresql', 'sql'],
ARRAY[2, 5, 8]
);Якщо масив порожній, потрібно явно вказати його тип:
INSERT INTO articles (title, tags, related_ids)
VALUES (
'Порожня колекція',
ARRAY[]::text[],
ARRAY[]::integer[]
);Масив може бути NULL. Це означає, що значення масиву відсутнє, а не те, що він порожній:
INSERT INTO articles (title, tags)
VALUES ('Стаття без тегів', NULL);
INSERT INTO articles (title, tags)
VALUES ('Стаття з порожнім списком тегів', ARRAY[]::text[]);Індекси масивів у PostgreSQL починаються з 1, а не з 0.
SELECT
ARRAY['postgresql', 'sql', 'database'][1] AS first_tag,
ARRAY['postgresql', 'sql', 'database'][2] AS second_tag;Результат:
перший елемент — postgresql;
другий елемент — sql.
Для стовпця таблиці синтаксис такий:
SELECT
title,
tags[1] AS first_tag,
tags[2] AS second_tag
FROM articles;Якщо індекс виходить за межі масиву, PostgreSQL повертає NULL:
SELECT ARRAY['postgresql', 'sql'][10] AS missing_element;За допомогою зрізу можна отримати частину масиву:
SELECT ARRAY['postgresql', 'sql', 'database', 'backend'][2:3] AS selected_tags;Результат:
{sql,database}Зріз має формат [початок:кінець], причому обидві межі включаються.
SELECT
tags[1:2] AS first_two_tags
FROM articles;Для підрахунку кількості елементів використовуйте cardinality:
SELECT
title,
cardinality(tags) AS tag_count
FROM articles;cardinality повертає кількість усіх елементів масиву. Для NULL результатом буде NULL, а для порожнього масиву — 0.
Щоб отримати довжину конкретного виміру, можна використати array_length:
SELECT array_length(ARRAY['a', 'b', 'c'], 1) AS length;Другий аргумент 1 означає перший вимір масиву.
PostgreSQL підтримує багатовимірні масиви. Наприклад, можна створити матрицю:
SELECT ARRAY[
[1, 2, 3],
[4, 5, 6]
] AS matrix;Звертання до елемента багатовимірного масиву виконується за індексом кожного виміру:
SELECT (ARRAY[
[1, 2, 3],
[4, 5, 6]
])[2][3] AS selected_value;Результатом буде 6: другий рядок, третій стовпець.
На практиці найчастіше використовують одновимірні масиви. Багатовимірні масиви мають бути прямокутними: усі рядки повинні мати однакову довжину.
ANYОператор ANY перевіряє, чи дорівнює значення хоча б одному елементу масиву:
SELECT title
FROM articles
WHERE 'postgresql' = ANY(tags);Цей запит повертає статті, у яких серед тегів є postgresql.
Також можна використовувати оператори порівняння:
SELECT title
FROM articles
WHERE 5 < ANY(related_ids);Запит поверне рядки, де хоча б один ідентифікатор більший за 5.
ALLALL порівнює значення з кожним елементом масиву:
SELECT title
FROM articles
WHERE 0 < ALL(related_ids);Рядок буде вибрано, якщо всі значення в related_ids більші за 0.
@>Оператор @> перевіряє, чи містить лівий масив усі елементи правого:
SELECT title
FROM articles
WHERE tags @> ARRAY['postgresql'];Пошук за кількома тегами:
SELECT title
FROM articles
WHERE tags @> ARRAY['postgresql', 'sql'];Такий запит вибирає рядки, у яких присутні обидва теги.
Правий операнд має бути сумісного типу. За потреби тип можна вказати явно:
WHERE tags @> ARRAY['postgresql', 'sql']::text[]&&Оператор && перевіряє, чи мають два масиви хоча б один спільний елемент:
SELECT title
FROM articles
WHERE tags && ARRAY['postgresql', 'mysql'];Цей запит вибирає статті, які мають тег postgresql або mysql.
Якщо потрібно знайти конкретний текстовий елемент, можна використати ANY:
SELECT title
FROM articles
WHERE 'backend' = ANY(tags);Не слід порівнювати масив із рядком через LIKE, якщо потрібно перевірити саме окремий елемент. Перевірка за допомогою ANY, @> або && точніше описує умову.
Елемент можна змінити за його індексом:
UPDATE articles
SET tags[1] = 'postgres'
WHERE id = 1;Масив tags після цього матиме нове перше значення.
Можна змінити одразу зріз:
UPDATE articles
SET tags[1:2] = ARRAY['postgres', 'database']
WHERE id = 1;Кількість елементів у правій частині має відповідати діапазону, який змінюється.
Функція array_append додає елемент у кінець масиву:
UPDATE articles
SET tags = array_append(tags, 'backend')
WHERE id = 1;Функція array_prepend додає елемент на початок:
UPDATE articles
SET tags = array_prepend('programming', tags)
WHERE id = 1;Якщо масив може бути NULL, ці функції також можуть повернути NULL. Для обробки NULL використовуйте coalesce:
UPDATE articles
SET tags = array_append(coalesce(tags, ARRAY[]::text[]), 'backend')
WHERE id = 2;Оператор || об’єднує масиви:
UPDATE articles
SET tags = tags || ARRAY['backend', 'web']
WHERE id = 1;Можна додати один елемент, якщо PostgreSQL може однозначно визначити тип:
UPDATE articles
SET tags = tags || 'api'
WHERE id = 1;Явне створення масиву зазвичай робить запит зрозумілішим:
UPDATE articles
SET tags = tags || ARRAY['api']::text[]
WHERE id = 1;Функція array_remove видаляє всі входження вказаного значення:
UPDATE articles
SET tags = array_remove(tags, 'sql')
WHERE id = 1;Функція array_replace замінює всі входження одного значення іншим:
UPDATE articles
SET tags = array_replace(tags, 'postgres', 'postgresql')
WHERE id = 1;Нижче наведено приклад, який можна виконати в PostgreSQL послідовно.
DROP TABLE IF EXISTS courses;
CREATE TABLE courses (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
technologies text[] NOT NULL DEFAULT ARRAY[]::text[],
student_ids integer[] NOT NULL DEFAULT ARRAY[]::integer[]
);
INSERT INTO courses (title, technologies, student_ids)
VALUES
(
'PostgreSQL для розробників',
ARRAY['postgresql', 'sql', 'backend'],
ARRAY[101, 102, 103]
),
(
'Вебзастосунки з Node.js',
ARRAY['node.js', 'javascript', 'backend'],
ARRAY[102, 104]
),
(
'Основи тестування',
ARRAY['javascript', 'testing'],
ARRAY[105]
);
-- Отримання першої технології кожного курсу.
SELECT
title,
technologies[1] AS first_technology
FROM courses;
-- Пошук курсів, які містять PostgreSQL.
SELECT title
FROM courses
WHERE 'postgresql' = ANY(technologies);
-- Пошук курсів, які містять одночасно backend і sql.
SELECT title
FROM courses
WHERE technologies @> ARRAY['backend', 'sql']::text[];
-- Пошук курсів, які мають хоча б одну з указаних технологій.
SELECT title
FROM courses
WHERE technologies && ARRAY['postgresql', 'testing']::text[];
-- Додавання технології в кінець масиву.
UPDATE courses
SET technologies = array_append(technologies, 'database')
WHERE title = 'PostgreSQL для розробників';
-- Заміна першої технології.
UPDATE courses
SET technologies[1] = 'PostgreSQL'
WHERE title = 'PostgreSQL для розробників';
-- Видалення технології з масиву.
UPDATE courses
SET technologies = array_remove(technologies, 'sql')
WHERE title = 'PostgreSQL для розробників';
-- Підрахунок технологій після змін.
SELECT
title,
technologies,
cardinality(technologies) AS technology_count
FROM courses
ORDER BY id;Іноді потрібно обробити кожен елемент масиву як окремий рядок. Для цього використовується unnest:
SELECT
c.title,
technology
FROM courses AS c
CROSS JOIN LATERAL unnest(c.technologies) AS technology;Результатом буде по одному рядку для кожної пари «курс — технологія».
Наприклад, один курс із трьома технологіями перетвориться на три рядки. Це корисно, коли потрібно виконати агрегацію або окремо перевірити кожне значення.
Можна також відфільтрувати розгорнуті елементи:
SELECT
c.title,
technology
FROM courses AS c
CROSS JOIN LATERAL unnest(c.technologies) AS technology
WHERE technology LIKE 'java%';У PostgreSQL перший елемент має індекс 1:
SELECT ARRAY['first', 'second'][1];Звертання до [0] не дає першого елемента.
NULL і порожнім масивомNULL означає відсутність значення, а ARRAY[]::text[] — масив без елементів:
SELECT
cardinality(NULL::text[]) AS null_array,
cardinality(ARRAY[]::text[]) AS empty_array;Результати будуть відповідно NULL і 0.
PostgreSQL не завжди може визначити тип ARRAY[] самостійно:
SELECT ARRAY[]::text[];
SELECT ARRAY[]::integer[];Під час вставлення або оновлення вказуйте тип, якщо він не визначається з контексту.
Умова:
WHERE tags = ARRAY['postgresql', 'sql']перевіряє повну рівність масивів: порядок і набір елементів мають відповідати.
Якщо потрібно перевірити наявність одного або кількох елементів, використовуйте:
WHERE 'postgresql' = ANY(tags);
WHERE tags @> ARRAY['postgresql']::text[];NULLОперації над NULL зазвичай повертають NULL:
SELECT array_append(NULL::text[], 'postgresql');Щоб працювати з NULL як із порожнім масивом, застосовуйте coalesce:
SELECT array_append(
coalesce(NULL::text[], ARRAY[]::text[]),
'postgresql'
);Масиви оголошуються за допомогою типу на кшталт text[] або integer[].
Створювати значення масивів можна через ARRAY[...].
Індексація в PostgreSQL починається з 1.
Оператор ANY перевіряє значення серед елементів масиву.
@> перевіряє, чи містить масив задані елементи.
&& перевіряє наявність спільних елементів у двох масивах.
Елементи змінюються через індекс, а додавання та видалення виконуються за допомогою array_append, array_prepend і array_remove.
cardinality допомагає визначити кількість елементів.
unnest перетворює елементи масиву на окремі рядки.