Работа с временными таблицами в PostgreSQL

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

Зачем они нужны?

• Временное хранение данных: Например, у вас есть сложные вычисления над данными, которые нужно выполнять в несколько этапов. Вместо того чтобы нагружать основную таблицу, можно использовать временные таблицы.
• Анализ данных: Вы можете собрать данные из нескольких источников, обработать их, а затем удалить временную таблицу после выполнения анализа.
• Оптимизация сложных запросов: Иногда лучше разделить запрос на несколько этапов с использованием временных таблиц, чтобы ускорить его выполнение.
• Минимизация рисков: Нет риска случайно испортить реальные таблицы — временные таблицы полностью изолированы.

Магия временных таблиц заключается в том, что их данные видны только вам! Другие пользователи базы данных не смогут заглянуть в них, что делает временные таблицы безопасными для экспериментов.

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

Создание временной таблицы похоже на создание обычной таблицы, только добавляется ключевое слово TEMP или TEMPORARY.

Синтаксис:

CREATE TEMP TABLE table_name (
    column_name data_type constraints,
    ...
);

Всё просто, правда? Временная таблица исчезнет автоматически по завершении вашей сессии.

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

CREATE TEMP TABLE temp_students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK (age > 0)
);

Теперь у нас есть временная таблица temp_students, в которую можно вставлять данные и работать с ними, как с обычной таблицей. Только не забудьте — она исчезнет, как только вы закроете сессию.

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

Иногда вам не хочется описывать структуру временной таблицы вручную — особенно если вы просто хотите сохранить результат какого-то запроса и поработать с ним. В этом случае на помощь приходит форма:

CREATE TEMP TABLE имя_таблицы AS
SELECT ...;

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

Предположим, вы хотите сохранить студентов, не записанных ни на один курс:

CREATE TEMP TABLE temp_unregistered_students AS
    SELECT s.id, s.name
    FROM students s
    LEFT JOIN enrollments e ON s.id = e.student_id
    WHERE e.student_id IS NULL;

Теперь у вас есть таблица temp_unregistered_students, которую можно использовать в других частях скрипта — без необходимости повторять запрос.

Зачем это?

• Не нужно явно указывать столбцы и типы — PostgreSQL сам определит их из результата SELECT.
• Можно многократно использовать временные данные без повторных вычислений.
• Это особенно полезно в отчётах, ETL-операциях и аналитике.

Временные таблицы внутри транзакций

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

Пример:

BEGIN;

CREATE TEMP TABLE temp_transactions (
    transaction_id SERIAL PRIMARY KEY,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(50)
);

INSERT INTO temp_transactions (amount, status)
VALUES (100.50, 'Pending');

-- Посмотрим данные
SELECT * FROM temp_transactions;

COMMIT;
-- Теперь таблица temp_transactions исчезла!

Если вместо COMMIT вы выполните ROLLBACK, данные и таблица также будут удалены.

Использование временных таблиц в реальных задачах

Пример 1: Временное объединение данных

Предположим, у нас есть две таблицы: students и courses. Нужно решить, кто из студентов ещё не записан на курсы. Мы можем сначала собрать данные во временную таблицу, а уже потом выполнить анализ.

CREATE TEMP TABLE temp_unregistered_students AS
	SELECT s.id, s.name
	FROM students s
	LEFT JOIN enrollments e ON s.id = e.student_id
	WHERE e.student_id IS NULL;

-- Теперь мы можем работать с временной таблицей temp_unregistered_students.
SELECT * FROM temp_unregistered_students;

Пример 2: Подготовка данных для отчёта

Иногда нужно собрать данные из нескольких таблиц, преобразовать их и подготовить отчёт. Временные таблицы отлично подходят для этого.

CREATE TEMP TABLE temp_sales AS
	SELECT p.product_id, p.name, SUM(s.quantity) AS total_quantity
	FROM products p
	JOIN sales s ON p.product_id = s.product_id
	GROUP BY p.product_id, p.name;

-- Создали временную таблицу с результатом. Теперь можно построить на её основе отчёт:
SELECT name, total_quantity FROM temp_sales WHERE total_quantity > 50;

Хитрости и особенности временных таблиц

Именование временных таблиц: если у вас уже есть обычная таблица с именем students, вы всё равно можете создать временную таблицу с таким же названием! Временная таблица будет иметь приоритет в рамках вашей сессии. Однако это может запутать, если вы забудете, что работаете с временной таблицей.

Оптимизация временных таблиц: PostgreSQL автоматически создаёт индексы для столбцов с ограничением PRIMARY KEY или UNIQUE даже в временных таблицах. Если вам нужно ускорить работу с данными, вы можете вручную добавить индексы:

CREATE INDEX idx_temp_students_age ON temp_students (age);

Удаление временной таблицы: если вы хотите удалить временную таблицу до завершения сессии, используйте команду DROP TABLE:

DROP TABLE temp_students;

Объем данных в временной таблице: временные таблицы хранятся в памяти (если хватает ресурсов), что делает их намного быстрее. Однако помните, что слишком большие данные могут начать использовать диск.

Частые ошибки и как их избежать

Ошибка 1: "Таблица уже существует"

Если вы попробуете создать временную таблицу с именем, которое уже занято другой временной таблицей, получите ошибку. Используйте CREATE TEMP TABLE IF NOT EXISTS или удалите таблицу перед созданием:

DROP TABLE IF EXISTS temp_students;
CREATE TEMP TABLE temp_students (...);

Ошибка 2: "Таблица исчезает слишком рано"

Если вы создаёте временную таблицу внутри транзакции, не забывайте, что она исчезнет, когда транзакция завершится. Планируйте свои действия заранее!