Временные таблицы PostgreSQL

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

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

Временные таблицы могут быть полезны в различных сценариях, в том числе:

• Хранение промежуточных результатов при сложных запросах или преобразованиях данных.
• Разбиение сложной задачи на более мелкие, выполнимые шаги.
• Кэширование данных для более быстрого доступа в рамках сеанса.
• Изоляция данных для разных одновременных сеансов или транзакций.

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

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

Теперь давайте рассмотрим, как создавать и использовать временные таблицы в Postgres.

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

Временные таблицы создаются с помощью CREATE TEMPORARY TABLE оператора. Синтаксис следующий:

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
);

-- TEMPORARY can be shorthanded to TEMP

CREATE TEMP TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
);

Представьте, что вы выполняете (или должны были выполнить) анализ своих продаж.

Возможно, вы захотите сохранить временные данные для анализа в течение сеанса, и для этого вы решаете создать временную таблицу с именем со sales_analysis столбцами product_name и sales_amount.

-- Create a temporary table
raiden=# CREATE TEMPORARY TABLE sales_analysis(
    product_name VARCHAR(50),
    sales_amount NUMERIC(10, 2)
);

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

Вставка данных во временные таблицы

После того как вы создали временную таблицу, вы можете вставить в нее данные с помощью этого INSERT INTO оператора. Для этого примера мы просто будем использовать фиктивные данные.

-- Insert data into the temporary table
raiden=# INSERT INTO sales_analysis (product_name, sales_amount)
VALUES ('Product A', 100.50), ('Product B', 75.20), ('Product C', 150.00);

Убедитесь, что данные добавлены

INSERT 0 3

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

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

-- Retrieve data from the temporary table
raiden=# SELECT * FROM sales_analysis;

raiden=# SELECT * FROM sales_analysis;
 product_name | sales_amount
--------------+--------------
 Product A    |       100.50
 Product B    |        75.20
 Product C    |       150.00
(3 rows)

К временным таблицам можно применять различные операции фильтрации, сортировки и агрегирования, как и к обычным таблицам. Давайте использовать SUMв этом случае.

raiden=# SELECT SUM(sales_amount) as total_sales FROM sales_analysis;
 total_sales
-------------
      325.70
(1 row)

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

Удаление временных таблиц

Временные таблицы автоматически удаляются в конце сеанса или транзакции, в зависимости от их области действия. Однако вы можете явно удалить временную таблицу до окончания сеанса, используя DROP TABLE оператор.

-- Drop the temporary table
raiden=# DROP TABLE sales_analysis;

raiden=# SELECT * FROM sales_analysis;
ERROR:  relation "sales_analysis" does not exist
LINE 1: SELECT * FROM sales_analysis;

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

Область действия временных таблиц

Временные таблицы в Postgres могут иметь разные области действия в зависимости от того, где они созданы. Существует три типа областей временных таблиц:

1. Временные таблицы сеанса : эти таблицы видны только в рамках текущего сеанса и автоматически удаляются в конце сеанса или при его завершении.
2. Временные таблицы транзакций : эти таблицы видны только в рамках текущей транзакции и автоматически удаляются в конце транзакции или при ее откате.
3. Непосредственные временные таблицы : эти таблицы видны в текущем сеансе и во всех последующих сеансах до тех пор, пока таблица не будет явно удалена или сеанс не завершится.

Область действия временной таблицы зависит от контекста, в котором она создана. Чтобы явно создать временную таблицу сеанса, вы можете использовать CREATE TEMPORARY TABLE оператор.

Временные таблицы транзакций создаются с использованием одного и того же оператора в блоке транзакций. Непосредственные временные таблицы создаются с использованием CREATE TEMPORARY TABLE оператора вне блока транзакции.

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

Предположим, у нас есть ресторан быстрого питания и orders таблица со столбцами order_id, product_id и quantity. Мы хотим рассчитать общий объем продаж для каждого продукта, суммируя проданные количества. Мы можем использовать временную таблицу для хранения промежуточных результатов и упрощения запроса.

-- Create a temporary table to store intermediate results
raiden=# CREATE TEMPORARY TABLE temp_product_sales (
    product_id INT,
    total_sales INT
);

-- Insert data into the temporary table
raiden=# INSERT INTO temp_product_sales (product_id, total_sales)
SELECT product_id, SUM(quantity) AS total_sales
FROM orders
GROUP BY product_id;

-- Retrieve the total sales for each product
raiden=# SELECT p.product_name, t.total_sales
FROM temp_product_sales t
JOIN products p ON p.product_id = t.product_id;

В приведенном выше примере мы создаем временную таблицу temp_product_sales для хранения промежуточных результатов расчета общего объема продаж. Мы вставляем агрегированные данные с помощью SELECT оператора с предложением GROUP BY.

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

Временные таблицы улучшают читаемость и удобство обслуживания наших запросов.

Заключение

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

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