09.08.2023 в 16:22

Временные таблицы Postgres: руководство по работе с данными

Давайте сначала разберем понятие временные таблицы в PostgreSQL.

При работе с базами данных бывают ситуации, когда вам нужно хранить временные данные, которые нужны только на время сеанса или конкретной транзакции. Postgres (PostgreSQL) предоставляет мощную функцию, называемую временными таблицами, для обработки таких сценариев. В этом сообщении блога мы рассмотрим временные таблицы в Postgres, поймем их преимущества и предоставим несколько примеров кода, иллюстрирующих их использование.

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

Временная таблица в 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 могут иметь разные области видимости, в зависимости от того, где они созданы. Существует три типа областей временной таблицы:

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

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

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

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

Предположим, у нас есть ресторан быстрого питания, и у нас есть таблица заказов со столбцами 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 могут помочь вам более эффективно управлять данными и манипулировать ими, сохраняя промежуточные результаты для сложных запросов, выполняя операции с подмножеством данных, не затрагивая исходные данные, и работая с данными таким образом, который изолирован от других сеансов.

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

Источник:

Selected Sort Option

#PostgreSQL

1360

Войти на сайт