Set operators like UNION with SELECT INTO or INSERT SELECT

[VolkerBarth]

Combining query blocks with UNION, INTERSECT or EXCEPT is rather common.

How can you put these combined results into a table?

It seems that both SELECT..INTO and INSERT..SELECT and do work with UNIONs and the like:

drop table if exists LT_TestResult;
select 1 as ID, 'Query block 1' as Description
   into local temporary table LT_TestResult
   from dummy
union all
select 2, 'Query block 2'
   from dummy
union all
select 3, 'Query block 3'
   from dummy
order by 1;

select * from LT_TestResult order by 1;

This returns 3 rows, i.e. the SELECT...INTO has apparently used the combined result set although the INTO clause is just specfied for the first query block (and is not allowed for the further query blocks).

The same does work with INSERT SELECT:

truncate table LT_TestResult;

insert into LT_TestResult
select 1 as ID, 'Query block 1' as Description
from dummy
union all
select 2, 'Query block 2'
from dummy
union all
select 3, 'Query block 3'
from dummy
order by 1;

select * from LT_TestResult order by 1;

This also returns 3 rows although according to the docs INSERT expects a (single) SELECT statement and not a combined query.

---

So, in a nutshell, this works as desired as of V17.0.10.6315 but I think it is not really documented that way.

If this is expected behaviour, I'd suggest to add this to the docs.

[VolkerBarth]

(Of course one could use the combined query within a derived query and use the derived query as base for SELECT..INTO and INSERT..SELECT, as other DBMSes seems to require - but if that's not necessary, why should one?)

[Breck_Carter]

The INSERT SELECT UNION syntax seems natural, but the SELECT INTO UNION syntax is a surprise... thank you for telling us!

IMO the problem with the docs is that UNION is described as a "statement" when it fact it is an operator like JOIN.

UNION may be a funky operator, but so are many other operators such as LATERAL.

...and then there's the "recursive union" which is something else altogether, neither statement nor operator, perhaps a "staterator" 🙂