Error message when using the function "Union" twice

Question

Hi,

Using pgAdmin4, PostgreSQL12

I wrote this SQL query to get a table with 3 columns, and the first one should be created with text= 'Type':

select customer.last_name
, customer.first_name
from customer
where customer.first_name = 'Susan'

union

select actor.last_name
, actor.first_name
from actor
where actor.first_name = 'Susan'

union

select 'Actor' as Type
, actor.last_name
, actor.first_name
from actor
where actor.first_name= 'Tom'

I get this error message:
"Each union query must have the same number of columns"

I understand that, in the third part of the query, 'Actor' as Type is considered as a third column.
But if I remove the line 'Actor' as Type, I get a table with 2 columns.

Can you please help me?

Answer 1

Try adding an empty or null value to first two queries:

select '' as Type, customer.last_name, customer.first_name
. . .
union
select '', actor.last_name, actor.first_name
. . .

Answer 2

"Each union query must have the same number of columns"

As the error message clearly says, the first two queries returns 2 column, the third 3 columns; that's not allowed in a UNION query.
Return some dummy values for the first two queries, like

select NULL as Type
     ,customer.last_name
     , customer.first_name
from customer
where customer.first_name = 'Susan'

union

select NULL as Type
     , actor.last_name
     , actor.first_name
from actor
where actor.first_name = 'Susan'

union

select 'Actor' as Type
, actor.last_name
, actor.first_name
from actor
where actor.first_name= 'Tom'