Робота з тимчасовими таблицями в 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-операціях та аналітиці.

Тимчасові таблиці всередині транзакцій

Важливо: дефолтна поведінка TEMP TABLE — ON COMMIT PRESERVE ROWS, тобто таблиця зберігається до кінця сесії, а не до COMMIT. Щоб її дійсно видалити на COMMIT, треба явно вказати ON COMMIT DROP:

BEGIN;

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

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

-- Podivymosya danni
SELECT * FROM temp_transactions;

COMMIT;
-- Teper tablicya temp_transactions znykla!

Три опції ON COMMIT:

• PRESERVE ROWS — дефолт, нічого не робити.
• DELETE ROWS — TRUNCATE при COMMIT (таблиця зберігається).
• DROP — DROP TABLE при COMMIT.

Якщо замість 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;

-- Teper mozhna pratsyuvaty z tymchasovoyu tablytseyu 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;

-- Stvoreno tymchasovu tablytsyu z rezultatom. Teper mozhna pobuduvaty na yii osnovi zvit:
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;

Обсяг даних у тимчасовій таблиці: тимчасові таблиці фізично зберігаються так само, як і звичайні — у файлах PostgreSQL (у спеціальному тимчасовому tablespace). Але вони буферизуються в session-local пул (temp_buffers, за замовчуванням 8MB), що робить роботу з невеликими TEMP TABLE значно швидшою за звичайні.

Часті помилки та як їх уникнути

Помилка 1: "Таблиця вже існує"

Якщо ти спробуєш створити тимчасову таблицю з іменем, яке вже зайняте іншою тимчасовою таблицею, отримаєш помилку. Використовуй CREATE TEMP TABLE IF NOT EXISTS або видали таблицю перед створенням:

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

Помилка 2: "Таблиця зникає занадто рано"

Якщо ти створюєш тимчасову таблицю всередині транзакції, не забувай, що вона зникне, коли транзакція завершиться. Плануй свої дії заздалегідь!