SQL OptimizatioN
  • 3D моделирование
    • ✦ 3D-модели букв алфавита
    • ✦ Генератор надписи на подставке
    • ✦ Статистика генерации
    • ✦ Генератор 3D из картинки
  • Авторизация
Оптимизация SQL
  • SQL-оптимизация и скрипты PostgreSQL для аналитики
  • СУБД PostgreSQL
    • Оптимизация построения запросов с несколькими JOIN
    • EXPLAIN ANALYZE
      • Seq Scan
Полезные скрипты
  • СУБД PostgreSQL - основные операции
    • Добавление данных в PostgreSQL: полное руководство по оператору INSERT
    • Оператор UPDATE в PostgreSQL: полное руководство по изменению данных
    • Оператор DELETE в PostgreSQL: безопасное удаление данных из таблиц
    • Создание триггеров в PostgreSQL: система аудита изменений
    • Система архивации данных в PostgreSQL: автоматическое сохранение удаленных записей с помощью триггеров
    • Представления (Views) в PostgreSQL: инструмент для работы с данными
    • Хранимые функции в PostgreSQL: инкапсуляция бизнес-логики на уровне базы данных
    • Хранимые процедуры в PostgreSQL: реализация бизнес-логики на уровне базы данных
    • Партиционирование таблицы в PostgreSQL: ускорение запросов и управление данными
    • Работа с иерархическими данными: рекурсивные запросы в PostgreSQL
    • GROUP BY в PostgreSQL: От подсчета до разбора массивов и HAVING
    • Временные таблицы и функции в PostgreSQL
    • Как отлаживать функции в PostgreSQL — RAISE NOTICE
  • СУБД PostgreSQL - полезные скрипты
    • UNION vs UNION ALL
    • Генерация серии
    • Задание последовательности SERIAL
    • Извлечение текста между скобками
    • Извлечение числовых значений из текста
    • Множественный атрибут в строку
    • Создание и использование DB LINK между базами
    • Использование LEFT JOIN в PostgreSQL
    • Массивы в PostgreSQL: добавляем теги и учимся эффективному поиску
    • Оконные функции в PostgreSQL
    • GROUP BY в PostgreSQL на реальных данных
    • CASE WHEN THEN в PostgreSQL: от простой категоризации до подзапросов
    • Группировка по произвольным промежуткам времени в PostgreSQL
    • JSONB в PostgreSQL: зачем он нужен в реляционной базе и как с ним работать
Использование PGAdmin
  • PostgreSQL pgAdmin 4: полный обзор графического интерфейса для работы с базами данных
  • Создание базы данных в PostgreSQL через pgAdmin: подробное руководство для начинающих
  • Таблицы и индексы в PostgreSQL: полное руководство по созданию и оптимизации
  • Импорт данных в PostgreSQL через pgAdmin: полное руководство для эффективной загрузки данных
Сквозной пример в PostgreSQL
  • Описание сквозного примера
  • Создание таблицы с перечнем товаров
  • Создание таблицы категорий товаров
  • Наполнение таблиц категорий и перечня товаров
  • Выбор средства визуализации и построения дашбордов
Видеомонтаж
  • Аниме-сага «Тени Памяти»
  • Профессиональная озвучка текста с Yandex SpeechKit: пошаговое руководство
  • OBS Studio - легкая профессиональная запись экрана
  • Как извлечь звук из видео с помощью VLC Media Player: подробное руководство
  • Как озвучить любой текст с помощью Алисы от Яндекса
  • Как обрезать видеоролик без установки сторонних программ
Интерактивные материалы
  • Все интерактивные видео и мини-уроки
    • Интерактивные уроки по SQL/PostgreSql
    • Интерактивные уроки по AI/ИИ/Нейросети
    • Интерактивные уроки по MS Excel
    • Интерактивные уроки по MS Word
    • Интерактивные уроки по MS PowerPoint
    • Инструменты для монтажа видео
    • Сборники видео с лайфхаками и советами
      • MS Excel
      • MS Word
      • Сборники по PostgreSQL
      • Видеомонтаж
Офисные приложения
  • MS Excel
    • Основы Microsoft Excel: ключевые инструменты для эффективной работы
    • Краткое руководство по условному форматированию в Excel
    • Как переключить стиль ссылок в Excel: R1C1 против A1
    • Как создать интерактивные флажки (чекбоксы) в Excel: пошаговая инструкция
    • Как создать интерактивное содержание со ссылками в Excel: навигация по листам и внешним ресурсам
    • Умная фильтрация данных в Excel: создаем интерактивный фильтр по параметрам с автообновлением
    • Генерация случайных чисел и дат в Excel: полное руководство по функциям СЛЧИС и СЛУЧМЕЖДУ
    • Создать поисковую строку в Excel: быстрая фильтрация данных без сложных макросов
    • Расчет рабочих дней в Excel: функции для точного планирования
  • MS Word
    • Базовое руководство по Microsoft Word: первые шаги для начинающих
    • Колонтитулы в Microsoft Word: полное руководство по созданию профессиональных документов
    • Как конвертировать PDF в DOC (файл формата PDF в Word)?
    • Эффективная работа с текстом: скрытые возможности для повышения продуктивности
    • Расширенный буфер обмена Windows: перестаньте копировать заново
    • Форматирование текста в MS Word по образцу, скрытые символы, копирование формата
    • Профессиональное оформление технической документации в MS Word: базовые правила и неочевидные нюансы
  • MS PowerPoint
    • Как создать интерактивную презентацию с анимацией в PowerPoint
Использование нейросетей
  • AI DeepSeek
    • Особенности уточнения запросов SQL у нейросети
    • Создаём расширенный поиск по товарам с помощью нейросети DeepSeek: пошаговое руководство
  • Алиса от Яндекс и другие ИИ Яндекс
    • Как озвучить любой текст с помощью Яндекс Алисы
    • Тонкая настройка речи Яндекс Алисы: управление ударениями и паузами
    • ИИ-ассистент на Яндекс Маркете: как нейросеть помогает выбирать товары
  • GigaChat
    • Создание сложных сцен в Гигачате: от простого стула до "фантастической" композиции
  • character.ai
    • Нейросети и синтез голоса: как Character AI меняет общение с цифровыми персонажами
    • Character AI: как создавать виртуальных персонажей и общаться с историческими личностями
  • Magic Eraser
    • Изменение цвета волос на фото: используем нейросеть MagicEraser и меняемся на глазах
  1. Вы здесь:  
  2. Главная
  3. СУБД PostgreSQL - полезные скрипты
  4. Извлечение текста между скобками
  5. Scripts

Статистика генерации 3D фраз

Информация о материале
Автор: Андрей К.
Категория: Scripts
Опубликовано: 30 сентября 2026
Просмотров: 253

Загрузка статистики…

JSONB в PostgreSQL: зачем он нужен в реляционной базе и как с ним работать

Информация о материале
Автор: Андрей К.
Категория: Scripts
Опубликовано: 27 апреля 2026
Просмотров: 395
  • PostgreSQL
  • JSONB

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

Сегодня на примере тестовых таблиц интернет-магазина мы разберём:

  • где JSONB выигрывает у обычных колонок и массивов;

  • как с ним работать эффективно;

  • основные операции: извлечение, фильтрация, обновление.


Исходная структура: категории и товары

У нас есть две таблицы.

Категории — с иерархической структурой (например, «Электроника» → «Смартфоны» → «Apple»).

Товары — с базовыми полями и массивом tags для хранения простых меток:

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    category_id INT,
    tags TEXT[]
);

Массив tags удобен для хранения простых меток: {'смартфон', 'apple', 'флагман'}.


Когда массивов достаточно (и даже хорошо)

Запрос 1: найти все товары с тегом «apple»

sql
SELECT * FROM products WHERE 'apple' = ANY(tags);

Запрос 2: найти товары с тегами «смартфон» и «apple» одновременно

sql
SELECT * FROM products WHERE tags @> ARRAY['смартфон', 'apple'];

Благодаря GIN-индексу такие запросы выполняются быстро даже на больших объёмах:

sql
CREATE INDEX idx_products_tags ON products USING GIN(tags);

📌 Массивы — отличный инструмент для простых списков строк.


Когда массивов становится недостаточно

Представьте, что для тега «популярный» мы хотим хранить:

  • дату, когда товар стал популярным;

  • вес тега для поисковой выдачи.

Массив строк этого не умеет. Пришлось бы создавать отдельную таблицу product_tags с колонками tag, date, weight. Это усложняет схему и запросы.

Другой пример — характеристики товаров:

  • у смартфонов: процессор, память, объём батареи;

  • у наушников: время работы, тип подключения;

  • у одежды: размер, материал.

В классической реляционной модели это решается через:

  • таблицу атрибутов (EAV);

  • или множество колонок, большинство из которых будут NULL.

Оба подхода — громоздкие и медленные.


Решение: JSONB

Добавим в таблицу товаров колонку properties типа JSONB:

sql
ALTER TABLE products ADD COLUMN properties JSONB;

Теперь заполним её характеристиками в зависимости от категории.

Для смартфонов

sql
UPDATE products SET properties = '{
    "processor": "A17 Bionic",
    "ram": 8,
    "storage": 256,
    "battery": 4000,
    "water_resistance": "IP68"
}' WHERE category_id = 1;

Для наушников

sql
UPDATE products SET properties = '{
    "battery_life": 30,
    "connection_type": "Bluetooth 5.3",
    "noise_cancelling": true
}' WHERE category_id = 2;

Для книг

sql
UPDATE products SET properties = '{
    "author": "Лев Толстой",
    "pages": 1225,
    "genres": ["роман", "классика"]
}' WHERE category_id = 3;

Основные операции с JSONB

1. Извлечение значения (оператор ->>)

sql
SELECT
    name,
    properties ->> 'processor' AS cpu,
    properties ->> 'ram' AS memory
FROM products
WHERE properties ? 'processor';

2. Фильтрация по вложенному значению

Найти все товары с влагозащитой IP68:

sql
SELECT * FROM products
WHERE properties ->> 'water_resistance' = 'IP68';

3. Фильтрация по диапазону чисел

Найти смартфоны с батареей выше 4500 мАч:

sql
SELECT * FROM products
WHERE (properties ->> 'battery')::int > 4500;

📌 Обратите внимание на приведение типов: ->> возвращает текст.

Более эффективный вариант для фильтрации — использовать @> или создать индекс по выражению.

4. Поиск внутри массива, хранящегося в JSONB

Найти товары, у которых в жанрах указано «роман»:

sql
SELECT * FROM products
WHERE properties -> 'genres' ? 'роман';

Или любой из жанров:

sql
SELECT * FROM products
WHERE properties -> 'genres' ?| ARRAY['роман', 'основы'];

Изменение JSONB-данных без перезаписи всего объекта

PostgreSQL позволяет точечно обновлять JSONB.

Добавить новый цвет в массив colors

sql
UPDATE products
SET properties = jsonb_set(
    properties,
    '{colors}',
    COALESCE(properties -> 'colors', '[]'::jsonb) || '["черный"]'::jsonb
)
WHERE id = 1;

Добавить новое поле

sql
UPDATE products
SET properties = properties || '{"warranty_months": 12}'::jsonb
WHERE id = 1;

Удалить поле

sql
UPDATE products
SET properties = properties - 'warranty_months'
WHERE id = 1;

Индексы для JSONB

Для ускорения запросов используйте GIN-индексы:

sql
-- индекс для операторов ?, ?|, ?&, @>
CREATE INDEX idx_products_properties ON products USING GIN(properties);

-- индекс для конкретного пути (например, battery)
CREATE INDEX idx_products_battery ON products ((properties ->> 'battery'));

Итог: когда использовать JSONB, а когда — нет

Задача Решение
Простой список строк (теги, метки) TEXT[] + GIN
Ключ-значение с разными типами значений JSONB
Динамические атрибуты (характеристики товаров) JSONB
Вложенные структуры (объекты в объектах) JSONB
Данные, которые нужно часто фильтровать и индексировать JSONB + GIN
Строгая схема с фиксированными колонками Обычные колонки INT, TEXT и т.д.

Главное правило: JSONB не заменяет реляционную модель, а дополняет её там, где гибкость важнее строгости схемы.


Что мы сегодня разобрали

  • ✅ Чем JSONB отличается от массивов и когда его применять.

  • ✅ Как добавить JSONB в существующую таблицу и заполнить данными.

  • ✅ Основные операции: извлечение, фильтрация, обновление.

  • ✅ Индексация для быстрых запросов.


 

Статья подготовлена по материалам обучающего видео для вашего удобства.

Группировка по произвольным промежуткам времени в PostgreSQL: знакомимся с date_bin

Информация о материале
Автор: Андрей К.
Категория: Scripts
Опубликовано: 27 апреля 2026
Просмотров: 598
  • PostgreSQL
  • date_bin
  • Группировка

Группировка данных по временным интервалам — задача, с которой сталкивается каждый разработчик SQL. Будь то «заказы по 15-минуткам», «активность пользователей по часам» или «средний чек за 10 минут» — рано или поздно это нужно сделать.

Раньше, в версиях PostgreSQL до 14, это требовало изворотливости: date_trunc, extract, деление с округлением, снова умножение… А если нужно было сменить шаг с 15 минут на 10 — переписывать половину запроса.

Но начиная с PostgreSQL 14, у нас есть элегантное решение — функция date_bin.

Давайте разберёмся, как она работает и почему она лучше.


Как раньше делали группировку по интервалам

Предположим, у нас есть таблица заказов с полем created_at (тип timestamp). Нужно посчитать количество заказов за каждые 15 минут.

Пример запроса «до date_bin»:

sql
WITH orders AS (
  SELECT generate_series(
    '2024-01-01 00:00:00',
    '2024-01-01 23:59:59',
    interval '1 minute'
  ) AS created_at
  LIMIT 1000  -- генерируем 1000 случайных заказов
)
SELECT
  date_trunc('hour', created_at) +
    (extract(minute FROM created_at)::int / 15) * interval '15 minutes' AS bin_start,
  count(*)
FROM orders
GROUP BY bin_start
ORDER BY bin_start;

Проблемы такого подхода:

  • Код сложно читать.

  • Вычисления происходят для каждой строки.

  • Индексы по полю created_at используются неэффективно.

  • Смена шага (например, на 10 минут) требует правки нескольких мест.


Знакомьтесь: date_bin

В PostgreSQL 14 появилась функция, которая делает то же самое в одну строку.

Синтаксис:

sql
date_bin(stride, source, origin)

Где:

  • stride — шаг (интервал). Например: interval '15 minutes'.

  • source — исходная временная метка (timestamp).

  • origin — точка отсчёта, от которой начинаются интервалы.

Функция возвращает начало того интервала, в который попадает source.

📌 Простыми словами: date_bin отвечает на вопрос:

«В каком интервале длины N, если считать от точки O, лежит момент времени T?»


Переписываем пример с date_bin

Возьмём тот же набор данных и ту же задачу:

sql
WITH orders AS (
  SELECT generate_series(
    '2024-01-01 00:00:00',
    '2024-01-01 23:59:59',
    interval '1 minute'
  ) AS created_at
  LIMIT 1000
)
SELECT
  date_bin(interval '15 minutes', created_at, '2024-01-01 00:00:00') AS bin_start,
  count(*)
FROM orders
GROUP BY bin_start
ORDER BY bin_start;

✅ Код стал короче, понятнее и быстрее.


Наглядный пример работы

Давайте посмотрим, как date_bin округляет события по 15-минутным интервалам.

sql
WITH events(time) AS (VALUES
  ('09:03'::time),
  ('09:07'::time),
  ('09:12'::time),
  ('09:19'::time),
  ('09:27'::time),
  ('09:33'::time)
)
SELECT
  time,
  date_bin(interval '15 minutes', time, '09:00'::time) AS bin_start
FROM events;

Результат:

time bin_start
09:03 09:00
09:07 09:00
09:12 09:00
09:19 09:15
09:27 09:15
09:33 09:30

Видно, что все события «корректно» попадают в свои 15-минутные корзины.


Важный нюанс: параметр origin

Параметр origin становится критически важным, когда вы работаете не с «полуночным» отсчётом, а с бизнес-часами.

Пример: ваш рабочий день начинается в 9:00. Если в качестве origin указать '2024-01-01 09:00:00', интервалы будут строиться именно от этого момента.

Без этого группировка может «съехать» относительно реальных бизнес-процессов.


Бонус: строим гистограмму прямо в консоли

С помощью date_bin можно быстро построить примитивную гистограмму без выгрузки в Excel.

sql
WITH minutes AS (
  SELECT generate_series('09:00'::timestamp, '10:00'::timestamp, interval '1 minute') AS t
),
events AS (
  SELECT t FROM minutes WHERE random() < 0.3  -- примерно треть событий
)
SELECT
  date_bin(interval '15 minutes', t, '09:00') AS bin_start,
  count(*) AS cnt,
  repeat('#', count(*)) AS bar
FROM events
GROUP BY bin_start
ORDER BY bin_start;

Пример вывода:

bin_start cnt bar
09:00 5 #####
09:15 7 #######
09:30 3 ###
09:45 6 ######

Очень удобно для быстрого анализа на месте.


Итог

date_bin — это:

  • ✅ Читаемый код

  • ✅ Простота смены шага

  • ✅ Поддержка индексов

  • ✅ Контроль начала интервалов через origin

Если вы работаете с временными рядами в PostgreSQL, используйте date_bin — это современный, быстрый и правильный подход.


 

Статья подготовлена по материалам обучающего видео для вашего удобства.

Как отлаживать функции в PostgreSQL с помощью RAISE NOTICE

Информация о материале
Автор: Андрей К.
Категория: Scripts
Опубликовано: 09 апреля 2026
Просмотров: 1478
  • PostgreSQL
  • RAISE NOTICE
  • Отладка

Введение

Вы написали функцию в PostgreSQL. Она компилируется без ошибок. Вы запускаете её — и получаете результат. Но что-то не так. Средняя цена подозрительно высокая. Количество товаров — ноль, хотя вы точно знаете, что они есть. Или функция просто возвращает NULL, а вы теряетесь в догадках.

Где ошибка?

Многие разработчики начинают «гадать»: переписывать WHERE, менять JOIN, добавлять COALESCE наугад. Это долго и неэффективно.

Сегодня я покажу вам самый простой, встроенный и не требующий плагинов инструмент отладки в PostgreSQL — RAISE NOTICE.

Это ваш print() или console.log() в мире хранимых процедур. Мы разберем его на реальных данных нашего учебного магазина канцтоваров и электроники.


Что такое RAISE NOTICE?

RAISE NOTICE — это команда языка plpgsql, которая выводит сообщения в консоль клиента (например, вкладка «Messages» в DBeaver или pgAdmin) или в лог-файл сервера PostgreSQL.

Ключевые особенности:

  • Не прерывает выполнение: В отличие от RAISE EXCEPTION, функция продолжает работать дальше.

  • Подстановка значений: Поддерживает плейсхолдеры %, работающие аналогично printf в языке C или format в Python.

  • Автоматическое приведение типов: PostgreSQL сам преобразует числа, даты, булевы значения и даже массивы в читаемый текст.

Базовый синтаксис

sql
-- Простое сообщение
RAISE NOTICE 'Привет, мир!';

-- С подстановкой одного значения
RAISE NOTICE 'Цена товара: %', price;

-- С подстановкой нескольких значений
RAISE NOTICE 'Товар: %, Цена: %, Категория: %', name, price, category_name;

Просто, не правда ли? Но именно эта простота экономит часы отладки. Давайте посмотрим, как это работает на практике.


Пример 1: Базовая отладка функции с агрегацией

Представьте, что мы написали функцию для расчета средней цены в категории. Она принимает category_id и возвращает число. Но иногда возвращает NULL.

Где проблема? То ли в категории нет товаров, то ли все товары неактивны (is_active = false), то ли цены не заполнены?

Добавим RAISE NOTICE на каждом шаге.

Код функции

sql
CREATE OR REPLACE FUNCTION debug_avg_price(p_category_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    v_avg_price NUMERIC;
    v_count INTEGER;
BEGIN
    -- Отладка: выводим входной параметр
    RAISE NOTICE '=== НАЧАЛО ФУНКЦИИ ===';
    RAISE NOTICE 'Начинаем анализ категории: %', p_category_id;

    -- Основной запрос
    SELECT COUNT(*), AVG(price)
    INTO v_count, v_avg_price 
    FROM products
    WHERE category = p_category_id AND is_active = true;

    -- Отладка: выводим промежуточные результаты
    RAISE NOTICE 'Найдено товаров в категории: %', v_count;
    RAISE NOTICE 'Рассчитанная средняя цена: %', v_avg_price;

    -- Отладка: выводим предупреждение, если товаров нет
    IF v_count = 0 THEN
        RAISE NOTICE 'ВНИМАНИЕ: В категории % нет активных товаров!', p_category_id;
    END IF;

    RAISE NOTICE '=== ЗАВЕРШЕНИЕ ФУНКЦИИ ===';
    RETURN v_avg_price;
END;
$$ LANGUAGE plpgsql;

Тестирование

Сценарий А: Категория со смартфонами (ID = 6)

sql
SELECT debug_avg_price(6);

Вывод в консоли (Messages):

text
NOTICE: === НАЧАЛО ФУНКЦИИ ===
NOTICE: Начинаем анализ категории: 6
NOTICE: Найдено товаров в категории: 3
NOTICE: Рассчитанная средняя цена: 65323.333333333333
NOTICE: === ЗАВЕРШЕНИЕ ФУНКЦИИ ===

Сценарий Б: Пустая категория «Телевизоры» (ID = 8)

sql
SELECT debug_avg_price(8);

Вывод в консоли:

text
NOTICE: === НАЧАЛО ФУНКЦИИ ===
NOTICE: Начинаем анализ категории: 8
NOTICE: Найдено товаров в категории: 0
NOTICE: Рассчитанная средняя цена: <NULL>
NOTICE: ВНИМАНИЕ: В категории 8 нет активных товаров!
NOTICE: === ЗАВЕРШЕНИЕ ФУНКЦИИ ===

Анализ:
Без единого изменения в бизнес-логике мы получили полную картину происходящего внутри функции. Мы видим, что запрос отработал, но вернул ноль строк. Это сразу указывает на проблему с данными, а не с синтаксисом.


Пример 2: Отладка внутри циклов

Ошибки особенно коварны, когда они прячутся внутри циклов. Предположим, функция должна массово обновить цены (например, применить скидку или наценку ко всем товарам категории). Если на пятом товаре цена становится отрицательной или происходит сбой — отследить это без логирования невозможно.

Добавим RAISE NOTICE на каждую итерацию.

Код функции с циклом

sql
CREATE OR REPLACE FUNCTION debug_loop_example(p_category_id INTEGER, p_multiplier NUMERIC)
RETURNS INTEGER AS $$
DECLARE
    v_product RECORD;
    v_counter INTEGER := 0;
    v_new_price NUMERIC;
BEGIN
    RAISE NOTICE '=== МАССОВОЕ ИЗМЕНЕНИЕ ЦЕН ===';
    RAISE NOTICE 'Категория ID: %, Множитель: %', p_category_id, p_multiplier;

    FOR v_product IN
        SELECT product_id, product_name, price
        FROM products 
        WHERE category = p_category_id AND is_active = true
    LOOP
        v_counter := v_counter + 1;
        v_new_price := ROUND(v_product.price * p_multiplier, 2);

        -- Отладка: выводим информацию по каждому товару
        RAISE NOTICE 'Товар #%: %, старая цена=%, новая цена=%',
            v_counter, v_product.product_name, v_product.price, v_new_price;

        -- Выполняем обновление
        UPDATE products 
        SET price = v_new_price, updated_at = CURRENT_TIMESTAMP
        WHERE product_id = v_product.product_id;

        -- Отладка: проверяем, что обновилось
        IF FOUND THEN
            RAISE NOTICE ' -> Обновление успешно для товара #%', v_counter;
        ELSE
            RAISE NOTICE ' -> ОШИБКА: товар #% не обновился!', v_counter;
        END IF;
    END LOOP;

    RAISE NOTICE '=== ИТОГО ОБРАБОТАНО ТОВАРОВ: % ===', v_counter;
    RETURN v_counter;
END;
$$ LANGUAGE plpgsql;

Тестирование

Поднимем цены в категории «Смартфоны» на 10%:

sql
SELECT debug_loop_example(6, 1.1);

Вывод в консоли (фрагмент):

text
NOTICE: === МАССОВОЕ ИЗМЕНЕНИЕ ЦЕН ===
NOTICE: Категория ID: 6, Множитель: 1.1
NOTICE: Товар #1: iPhone 15 Pro, старая цена=89990, новая цена=98989.00
NOTICE:  -> Обновление успешно для товара #1
NOTICE: Товар #2: Samsung Galaxy S24, старая цена=79990, новая цена=87989.00
NOTICE:  -> Обновление успешно для товара #2
NOTICE: Товар #3: Xiaomi Redmi Note 13, старая цена=25990, новая цена=28589.00
NOTICE:  -> Обновление успешно для товара #3
NOTICE: === ИТОГО ОБРАБОТАНО ТОВАРОВ: 3 ===

Польза:
Теперь мы видим каждую итерацию. Если бы на втором товаре цена вдруг стала NULL или отрицательной (например, из-за ошибки в формуле), мы бы сразу заметили это в логе, не ломая голову над итоговым отчетом.


Пример 3: Сравнение «ДО» и «ПОСЛЕ»

Один из самых эффективных приемов отладки — логировать состояние данных до выполнения операции и сразу после неё. Это особенно полезно при обновлении сложных полей, таких как массивы (TEXT[]) или JSON.

Код функции сравнения тегов

sql
CREATE OR REPLACE FUNCTION debug_compare_before_after(p_product_id INTEGER, p_new_tags TEXT[])
RETURNS BOOLEAN AS $$
DECLARE
    v_old_tags TEXT[];
    v_new_tags TEXT[];
    v_product_name TEXT;
BEGIN
    -- Получаем текущее состояние
    SELECT product_name, tags INTO v_product_name, v_old_tags
    FROM products WHERE product_id = p_product_id;

    RAISE NOTICE '=== ОБНОВЛЕНИЕ ТЕГОВ ===';
    RAISE NOTICE 'Товар: % (ID: %)', v_product_name, p_product_id;
    RAISE NOTICE 'ТЕГИ ДО: %', v_old_tags;
    RAISE NOTICE 'ТЕГИ ПОСЛЕ (планируемые): %', p_new_tags;

    -- Выполняем обновление
    UPDATE products
    SET tags = p_new_tags, updated_at = CURRENT_TIMESTAMP
    WHERE product_id = p_product_id;

    -- Проверяем результат
    SELECT tags INTO v_new_tags
    FROM products WHERE product_id = p_product_id;

    RAISE NOTICE 'ТЕГИ ПОСЛЕ (фактические): %', v_new_tags;

    -- Сравниваем
    IF v_new_tags = p_new_tags THEN
        RAISE NOTICE '✅ Обновление успешно: теги совпадают с ожидаемыми';
        RETURN TRUE;
    ELSE
        RAISE NOTICE '❌ ОШИБКА: теги не совпадают! Ожидали: %, Получили: %', p_new_tags, v_new_tags;
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

Тестирование

Обновим теги для iPhone 15 Pro:

sql
SELECT debug_compare_before_after(1, ARRAY['смартфон', 'apple', 'флагман', 'хит']);

Вывод в консоли:

text
NOTICE: === ОБНОВЛЕНИЕ ТЕГОВ ===
NOTICE: Товар: iPhone 15 Pro (ID: 1)
NOTICE: ТЕГИ ДО: {смартфон,apple,флагман,популярный}
NOTICE: ТЕГИ ПОСЛЕ (планируемые): {смартфон,apple,флагман,хит}
NOTICE: ТЕГИ ПОСЛЕ (фактические): {смартфон,apple,флагман,хит}
NOTICE: ✅ Обновление успешно: теги совпадают с ожидаемыми

Три золотых правила отладки с RAISE NOTICE

Запомните эти простые правила, и любая функция перестанет быть «черным ящиком»:

  1. На входе: Всегда выводите входящие параметры. Это помогает понять, с какими данными функция была вызвана на самом деле.

    sql
    RAISE NOTICE 'Вызов функции my_func с параметрами: p1=%, p2=%', p1, p2;
  2. После важных запросов: Выводите количество найденных/измененных строк (ROW_COUNT, FOUND) или ключевые агрегаты (v_avg, v_sum).

    sql
    GET DIAGNOSTICS v_row_count = ROW_COUNT;
    RAISE NOTICE 'Запрос затронул строк: %', v_row_count;
  3. Внутри циклов: Выводите номер итерации и значения переменных, которые меняются. Это спасет вас при обработке 10 000 записей, если ошибка возникает только на 342-й строке.


Заключение

RAISE NOTICE — это не высокотехнологичный дебаггер с точками останова, но это самый надежный и доступный инструмент в арсенале разработчика PostgreSQL. Он не требует установки расширений, работает в любой среде и дает вам «рентгеновское зрение» внутрь ваших хранимых процедур.

В следующий раз, когда ваша функция поведет себя странно, не гадайте — просто добавьте RAISE NOTICE.


 

Статья подготовлена по материалам обучающего видео для вашего удобства.

Временные таблицы и функции в PostgreSQL: Упаковываем сложную логику в многоразовые конструкции

Информация о материале
Автор: Андрей К.
Категория: Scripts
Опубликовано: 09 апреля 2026
Просмотров: 529
  • PostgreSQL
  • TEMP TABLE
  • CREATE TEMP TABLE
  • FUNCTION

Введение

Вы уже освоили GROUP BY, оконные функции и сложные JOIN. Ваши запросы стали мощными, но... громоздкими. Огромные простыни SQL-кода с вложенными подзапросами становятся нечитаемыми, а при повторном использовании приходится копировать десятки строк.

Знакомая ситуация? Тогда пришло время познакомиться с временными таблицами и научиться упаковывать бизнес-логику в функции PostgreSQL.

Сегодня на примере всё той же базы интернет-магазина (products и categories) мы разберем:

  1. Как создавать временные таблицы и зачем они нужны.

  2. Как использовать их внутри пользовательских функций.

  3. Как анализировать массивы тегов через две временные таблицы.

  4. Как ускорять работу через индексы.

  5. Как строить иерархические отчеты с рекурсией.


Часть 1. Зачем нужны временные таблицы?

Представьте задачу: нужно собрать отчет по «витрине» — дорогие товары из электроники, но без учета самых дешевых позиций в каждой категории.

Обычный запрос может легко разрастись на 20-30 строк с вложенными WITH, двойными фильтрациями и самоприсоединениями. Читать и отлаживать такое — боль.

Временная таблица (CREATE TEMP TABLE) позволяет разбить задачу на два простых шага:

  1. Сначала положить промежуточный результат в «песочницу».

  2. Потом работать с ней как с обычной таблицей.

Пример 1: Создаем витрину дорогой электроники

sql
-- Шаг 1. Создаём временную таблицу с дорогой электроникой
CREATE TEMP TABLE temp_expensive_electronics AS
SELECT 
    p.product_name, 
    p.price, 
    c.categori_name
FROM products p
JOIN categories c ON p.category = c.categori_id
WHERE c.categori_name IN ('Смартфоны', 'Ноутбуки')
  AND p.price > 50000;

-- Шаг 2. Используем её для аналитики
SELECT
    LEFT(product_name, 20) as short_name,
    price,
    CASE
        WHEN price > 100000 THEN 'Флагман'
        ELSE 'Средний+'
    END as segment
FROM temp_expensive_electronics
ORDER BY price DESC;

Что важно знать о CREATE TEMP TABLE:

  • Область видимости: Таблица существует только в рамках вашей текущей сессии. Другие пользователи или даже вы сами из другого окна редактора её не увидят.

  • Время жизни: Автоматически удаляется при отключении от базы данных.

  • Удобство отладки: Если запрос упал с ошибкой, данные во временной таблице остаются. Вы можете спокойно сделать SELECT * FROM temp_... и посмотреть, на каком этапе данные «поломались».


Часть 2. Временные таблицы внутри функций

Теперь самое интересное — объединим мощь временных таблиц и пользовательских функций. Это позволяет создавать настоящие программные модули прямо в SQL.

Задача: найти ценовые аномалии — товары, которые отличаются от средней цены по своей категории более чем на 30%.

Пример 2: Функция поиска аномалий

Здесь мы не будем дважды сканировать таблицу products. Мы создадим временную таблицу, внутри которой оконной функцией сразу посчитаем среднее, а затем просто отфильтруем её.

sql
CREATE OR REPLACE FUNCTION get_price_anomalies(p_category_id INTEGER)
RETURNS TABLE(
    product_name VARCHAR(255),
    price NUMERIC,
    avg_price NUMERIC,
    deviation_percent NUMERIC
) AS $$
BEGIN
    -- Временная таблица со статистикой
    CREATE TEMP TABLE temp_products_with_stats AS
    SELECT
        p.product_name::VARCHAR(255) as product_name,
        p.price,
        AVG(p.price) OVER (PARTITION BY p.category) as avg_price
    FROM products p
    WHERE p.category = p_category_id
      AND p.is_active = true;

    -- Возвращаем аномалии
    RETURN QUERY
    SELECT
        t.product_name,
        t.price,
        ROUND(t.avg_price, 2),
        ROUND(ABS(t.price - t.avg_price) / t.avg_price * 100, 1)
    FROM temp_products_with_stats t
    WHERE ABS(t.price - t.avg_price) > t.avg_price * 0.3;

    -- Чистим за собой (хороший тон)
    DROP TABLE temp_products_with_stats;
END;
$$ LANGUAGE plpgsql;

-- Вызываем функцию для категории "Смартфоны" (ID = 6)
SELECT * FROM get_price_anomalies(6);

Ключевые моменты кода:

  • RETURNS TABLE: Позволяет функции возвращать набор строк, к которому можно применить SELECT * FROM.

  • OVER (PARTITION BY...): Мы использовали оконную функцию внутри создания временной таблицы. Это гениальный ход: средняя цена считается на лету, без дополнительного сканирования диска.

  • Явное приведение типов (::VARCHAR(255)): Важный нюанс для тех, кто использует строгую типизацию. Без этого PostgreSQL может ругаться на несоответствие TEXT и VARCHAR.

  • DROP TABLE в конце: Хотя таблица и так умрет после завершения сессии или транзакции, внутри функций, которые вызываются часто, лучше чистить мусор вручную, чтобы не забивать системные каталоги pg_temp.


Часть 3. Высший пилотаж: Две временные таблицы и массивы

У нас в таблице products есть поле tags типа TEXT[] (массив). Это прекрасный способ хранить характеристики, но анализировать массивы напрямую сложно.

Задача: Написать функцию, которая принимает два артикула (sku1 и sku2) и возвращает:

  • Количество общих тегов.

  • Уникальные теги первого товара.

  • Уникальные теги второго товара.

Пример 3: Сравнение тегов через нормализацию

Здесь мы создадим две временные таблицы, «развернув» массивы в строки с помощью unnest.

sql
CREATE OR REPLACE FUNCTION compare_tag_sets(sku1 TEXT, sku2 TEXT)
RETURNS TABLE(
    common_tags BIGINT,
    unique_tags_1 TEXT[],
    unique_tags_2 TEXT[]
) AS $$
DECLARE
    tags1 TEXT[];
    tags2 TEXT[];
BEGIN
    -- Получаем массивы тегов
    SELECT tags INTO tags1 FROM products WHERE sku = sku1;
    SELECT tags INTO tags2 FROM products WHERE sku = sku2;

    -- Создаем две независимые временные таблицы
    CREATE TEMP TABLE temp_tags1 AS SELECT unnest(tags1) as tag;
    CREATE TEMP TABLE temp_tags2 AS SELECT unnest(tags2) as tag;

    -- Считаем метрики
    RETURN QUERY
    SELECT
        (SELECT COUNT(*) FROM temp_tags1 t1 INNER JOIN temp_tags2 t2 ON t1.tag = t2.tag),
        ARRAY(SELECT tag FROM temp_tags1 EXCEPT SELECT tag FROM temp_tags2),
        ARRAY(SELECT tag FROM temp_tags2 EXCEPT SELECT tag FROM temp_tags1);

    DROP TABLE temp_tags1, temp_tags2;
END;
$$ LANGUAGE plpgsql;

-- Сравниваем iPhone 15 Pro и Samsung Galaxy S24
SELECT * FROM compare_tag_sets('IPH15PRO256', 'SGS24U512');

Что мы получили?
Мы превратили сложную задачу по работе с массивами в элементарные операции теории множеств (INNER JOIN и EXCEPT) над обычными таблицами.

Практическое применение: Такую функцию можно легко встроить в блок рекомендаций на сайте: «Товар X имеет 3 общих тега с товаром Y. Возможно, вас заинтересует?».


Часть 4. Производительность: Не забываем про индексы!

Существует опасное заблуждение: «Временная таблица маленькая, индексы ей не нужны». Это правда только если в ней 10 строк. Но что если в категории «Книги» 10 000 товаров, и мы кладем их во временную таблицу, а потом несколько раз фильтруем по цене?

По умолчанию временные таблицы не наследуют индексы исходных таблиц. Без индекса сканирование будет последовательным (Seq Scan).

Пример 4: Ускоряем работу явным индексом

sql
CREATE OR REPLACE FUNCTION fast_category_stats(category_id INTEGER)
RETURNS TABLE(price_segment TEXT, count INTEGER, avg_price NUMERIC) AS $$
BEGIN
    CREATE TEMP TABLE temp_filtered_products AS
    SELECT product_id, price, product_name
    FROM products
    WHERE category = category_id AND is_active = true;

    -- ВОТ ЭТО ВАЖНО! Создаём индекс для ускорения WHERE и GROUP BY
    CREATE INDEX idx_temp_price ON temp_filtered_products(price);

    RETURN QUERY
    SELECT
        CASE
            WHEN price < 10000 THEN 'Бюджет'
            WHEN price < 50000 THEN 'Средний'
            ELSE 'Премиум'
        END,
        COUNT(*),
        ROUND(AVG(price), 2)
    FROM temp_filtered_products
    GROUP BY 1
    ORDER BY MIN(price);

    DROP TABLE temp_filtered_products;
END;
$$ LANGUAGE plpgsql;

Совет: Если вы используете временную таблицу для JOIN с другой таблицей или для сложной фильтрации (WHERE price > 1000), всегда оценивайте объем данных. Для 500+ строк индекс на колонках фильтрации даст прирост скорости в 5-10 раз.


Часть 5. Финальный аккорд: Рекурсия + Временная таблица

Соберем все знания вместе и решим реальную бизнес-задачу. Категории у нас иерархические (Электроника -> Смартфоны -> ...). Нужно построить отчет по всей ветке каталога: сколько товаров и какова их суммарная стоимость на каждом уровне вложенности.

Пример 5: Отчет по иерархии

sql
CREATE OR REPLACE FUNCTION get_hierarchy_report(root_category_id INTEGER)
RETURNS TABLE(
    category_name TEXT, 
    level INT,
    products_count BIGINT, 
    total_value NUMERIC
) AS $$
BEGIN
    -- Шаг 1. Строим дерево через рекурсивный CTE и сохраняем во временную таблицу
    CREATE TEMP TABLE temp_category_tree AS
    WITH RECURSIVE cat_tree AS (
        SELECT
            c.categori_id, c.categori_name, c.level, c.parent_id
        FROM categories c
        WHERE c.categori_id = root_category_id
        
        UNION ALL
        
        SELECT
            c.categori_id,
            c.categori_name,
            c.level,
            c.parent_id
        FROM categories c
        INNER JOIN cat_tree ct ON c.parent_id = ct.categori_id
    )
    SELECT * FROM cat_tree;

    -- Шаг 2. Джойним временную таблицу с товарами и считаем агрегаты
    RETURN QUERY
    SELECT
        ct.categori_name::TEXT,
        ct.level,
        COUNT(p.product_id)::BIGINT,
        COALESCE(SUM(p.price), 0)::NUMERIC
    FROM temp_category_tree ct
    LEFT JOIN products p ON p.category = ct.categori_id
    GROUP BY ct.categori_name, ct.level
    ORDER BY ct.level, ct.categori_name;

    DROP TABLE temp_category_tree;
END;
$$ LANGUAGE plpgsql;

-- Получаем весь отчет по электронике (ID = 1)
SELECT * FROM get_hierarchy_report(1);

Разбор решения:

  1. WITH RECURSIVE: Строит дерево от корня (root_category_id) до последнего листа.

  2. CREATE TEMP TABLE ... AS: Мы сохраняем результат рекурсии. Это позволяет не повторять тяжелый рекурсивный обход при каждом обращении к данным.

  3. LEFT JOIN: Присоединяем товары. LEFT гарантирует, что мы увидим папку «Телевизоры», даже если там пусто.

На выходе — готовая сводка по всему дереву категорий одним вызовом функции.


Заключение

Временные таблицы в PostgreSQL — это не просто «костыль для новичков». Это мощный инструмент архитектуры данных, который позволяет:

  • Упрощать код: Убивать многоэтажные вложенные запросы.

  • Ускорять работу: За счет материализации промежуточных итогов и добавления индексов.

  • Инкапсулировать логику: Создавать чистые функции, которые удобно переиспользовать в отчетах, триггерах и ETL-процессах.

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


 

Статья подготовлена по материалам обучающего видео для вашего удобства.

  1. Погружение в GROUP BY PostgreSQL
  2. CASE WHEN THEN в PostgreSQL: от простой категоризации до подзапросов
  3. Гайд по GROUP BY в PostgreSQL: 6 запросов к реальным данным
  4. Оконные функции в PostgreSQL: практическое руководство по анализу товарного ассортимента

Страница 1 из 5

  • 1
  • 2
  • 3
  • 4
  • 5
  • Карта сайта
  • © Андрей К, 2025
Поиск по материалам сайта
Популярное в видео

БОЛЬШЕ В RUTUBE Picture background


Создание join запроса PostgreSQL

БОЛЬШЕ В YOUTUBE


К сожалению, YouTube недоступен в вашем регионе или компании

Все видео доступны на RUTUBE по ссылкам выше ↑

Создание join запроса PostgreSQL