#8 – Расширенные возможности PostgreSQL

Современные базы данных уже давно вышли за рамки простых таблиц. PostgreSQL — одно из тех решений, которое позволяет хранить сложные структуры данных прямо внутри таблицы. Два мощных инструмента для этого — JSON/JSONB и массивы.

Хранение JSON

Тип данных JSONB (более оптимизированная версия JSON) позволяет хранить настройки, параметры, объекты и даже целые документы в одном поле. Это удобно, когда структура данных может меняться или когда не хочется создавать десятки дополнительных колонок.

CREATE TABLE users_settings (
	id SERIAL PRIMARY KEY,
	user_id INTEGER REFERENCES users(id),
	settings JSONB
);

INSERT INTO users_settings (user_id, settings) VALUES
	(1, '{"theme": "dark", "notif": {"email": true, "sms": false}}');

Теперь мы можем обращаться к данным внутри JSON, используя стрелки:

// Достаем значение по ключу
SELECT settings->'theme' FROM users_settings;

// Извлекаем текстовое значение (без кавычек)
SELECT settings->>'theme' FROM users_settings;

// Получаем вложенные данные
SELECT settings->'notif'->>'email' FROM users_settings;

Также можно фильтровать по значениям внутри JSON:

SELECT * FROM users_settings
WHERE settings->>'theme' = 'dark';

Таким образом, JSONB идеально подходит для хранения пользовательских настроек, конфигураций и данных, которые не требуют фиксированной структуры.

Массивы в PostgreSQL

Ещё один мощный инструмент PostgreSQL — массивы. Они позволяют хранить несколько значений в одной ячейке таблицы.

CREATE TABLE contacts (
	id SERIAL PRIMARY KEY,
	name VARCHAR(50),
	phones TEXT[]
);

INSERT INTO contacts (name, phones) VALUES
	('Josh', ARRAY['123', '456', '789']);

Чтобы обратиться к элементу массива, используется индексация (начинается с 1):

SELECT phones[1], name FROM contacts; // Первый номер
SELECT phones[2], name FROM contacts; // Второй номер

А чтобы проверить наличие элемента в массиве — оператор ANY():

SELECT * FROM contacts
WHERE '789' = ANY(phones);