Слияние с возвращением в временную таблицу без CTE

Содержание

Вопрос или проблема
Ответ или решение
Проблема и Ограничения
Причина:
Решение с использованием CTE
Дополнительные аспекты
Заключение

Вопрос или проблема

Возможно ли сделать что-то подобное без использования WITH?
Можно ли использовать CREATE TEMP TABLE temp AS вместо того, чтобы создавать временную таблицу вручную?

CREATE TEMP TABLE temp
(   action    text,
    winery_id int,
    brand     text );

MERGE INTO wines w
USING wines_updates u
ON u.winery_id = w.winery_id
WHEN MATCHED THEN
    UPDATE
    SET stock = u.stock
RETURNING merge_action() action, w.winery_id, w.brand 
INTO temp;

Пример DDL:

create table wines(winery_id int,brand text,stock int);
insert into wines values
 (1,'Cheapaux',10)
,(2,'Mediocret Sauvignon',20)
,(3,'Expensivau Blanc',30);
create table wines_updates(winery_id int,stock int);
insert into wines_updates values
 (1,11)
,(2,22)
,(3,33);

К сожалению, нет. Как оператор DML, MERGE не может использоваться напрямую в подзапросе, он не работает как прямой источник для create table as, и он не предлагает дополнительный into клауза, кроме той, что вверху.

Это не связано с тем, как работает MERGE, а скорее с тем, где в общем допустимы любые операторы изменения данных, с или без клаузы returning.

Вам действительно нужно использовать CTE:

CREATE TEMP TABLE tmp AS
WITH cte AS(
    MERGE INTO wines w
    USING wines_updates u
    ON u.winery_id = w.winery_id
    WHEN MATCHED THEN
        UPDATE
        SET stock = u.stock
    RETURNING merge_action() action, w.winery_id, w.brand)
SELECT * FROM cte;

CREATE TABLE AS явно указывает, какой тип запроса должен следовать за ним:

query
Команда SELECT, TABLE или VALUES, или команда EXECUTE, которая выполняет подготовленный запрос SELECT, TABLE или VALUES.

SELECT INTO также должен включать CTE:

WITH cte AS(
    MERGE INTO wines w
    USING wines_updates u
    ON u.winery_id = w.winery_id
    WHEN MATCHED THEN
        UPDATE
        SET stock = u.stock
    RETURNING merge_action() action, w.winery_id, w.brand)
SELECT * INTO TEMP TABLE tmp2 FROM cte;

Другие DML, такие как insert, update или delete, также не могут использоваться как прямой источник для create table as. Вы также не можете следовать за ними с into, подражая синтаксису select into, чтобы создать таблицу.

Ответ или решение

К сожалению, вы не можете напрямую использовать оператор MERGE для создания временной таблицы с помощью CREATE TEMP TABLE ... AS без использования общей таблицы выражений (CTE) в PostgreSQL. Это связано с тем, что операция MERGE, которая относится к модификации данных, не может служить источником данных для операторов создания таблиц. Рассмотрим более подробно эту ситуацию и возможные стратегии решения.

Проблема и Ограничения

Оператор MERGE используется для объединения данных из одной таблицы в другую и может выполнять операции обновления, вставки или удаления в зависимости от определенных условий. Однако, несмотря на его функциональные возможности, использование MERGE как источника для CREATE TABLE AS или SELECT INTO не предусмотрено, так как эти операторы требуют, чтобы после них следовал оператор запроса, например, SELECT, который возвращает строки.

Причина:

Это ограничение обусловлено тем, что в SQL стандартах DML (Data Manipulation Language) операторов, такие как INSERT, UPDATE и DELETE, нельзя использовать как источники для создания новых таблиц. Таким образом, попытка провести MERGE без применения CTE будет невозможна.

Решение с использованием CTE

Для выполнения операции MERGE с последующим извлечением данных в временную таблицу, вам необходимо воспользоваться CTE. Пример выполнения этой операции с использованием CTE выглядит следующим образом:

CREATE TEMP TABLE tmp AS
WITH cte AS (
    MERGE INTO wines w
    USING wines_updates u
    ON u.winery_id = w.winery_id
    WHEN MATCHED THEN
        UPDATE
    SET stock = u.stock
    RETURNING merge_action() AS action, w.winery_id, w.brand
)
SELECT * FROM cte;

В этом запросе:

Создаётся временная таблица tmp.
Внутри CTE выполняется MERGE, который обновляет записи в таблице wines на основе данных из wines_updates.
Оператор RETURNING позволяет вернуть изменённые данные, такие как действия слияния, идентификатор винодельни и марку.
Затем с CTE создаётся выборка SELECT *, которая заполняет временную таблицу tmp.

Дополнительные аспекты

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

WITH cte AS (
    MERGE INTO wines w
    USING wines_updates u
    ON u.winery_id = w.winery_id
    WHEN MATCHED THEN
        UPDATE
    SET stock = u.stock
    RETURNING merge_action() AS action, w.winery_id, w.brand
)
SELECT * INTO TEMP TABLE tmp2 FROM cte;

Заключение

Чтобы суммировать, главное ограничение использования MERGE непосредственно в контексте создания временной таблицы заключается в том, что данный оператор не может быть источником данных для CREATE TABLE AS или SELECT INTO. Однако использование CTE предоставляет эффективное решение этой проблемы, позволяя вам объединять данные и немедленно извлекать их в новую таблицу.

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