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);
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
ASELECT
,TABLE
, orVALUES
command, or anEXECUTE
command that runs a preparedSELECT
,TABLE
, orVALUES
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.