sqlpostgresqlsql-mergesql-returningpostgresql-17

MERGE RETURNING into temp table without a CTE


Is something like this possible without having to use WITH?
Can CREATE TEMP TABLE temp AS be used instead of creating the temp table manually?

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;

Example 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);

Solution

  • Unfortunately, no. As a DML statement, MERGE can't be used directly in a subquery, it doesn't work as a direct source for create table as and it doesn't offer an additional into clause beyond the one at the top.

    It's not related to how MERGE works but rather to where any data-modifying statements are allowed in general, with or without a returning clause.

    You do have to involve a 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 lists explicitly what type of query has to follow it:

    query
    A SELECT, TABLE, or VALUES command, or an EXECUTE command that runs a prepared SELECT, TABLE, or VALUES query.

    SELECT INTO also has to involve a 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;
    

    Other DML like insert, update or delete can't be used as a direct source to create table as either. You also can't follow them with an into mimicking the syntax of select into to create a table.