MERGE RETURNING into temp table without a CTE

Question

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

Most likely Yes but impossible to answer since you didn't share the DDL for the other tables and/or any data. — Frank Heikens
– Frank Heikens, Commented Oct 18, 2024 at 19:16

Zegarek · Accepted Answer · 2024-10-18 19:53:43Z

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.

Thank you for this explanation. I am switching some of our SQL from MS to PostgreSQL and your response has been extremely helpful.

Collectives™ on Stack Overflow

MERGE RETURNING into temp table without a CTE

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related