selecting enum columns in union all postgres

Question

i have problem figuring out how to set up my query. what i want to do select two tables without relations in one query. after researching i've found out about union all but the problem is in some cases i have type or status columns which is enum and i'm getting error while i'm trying to select them:

ERROR: UNION could not convert type money_transfer_type_enum to invoice_type_enum

i think it's not the best practice to select two totally different tables in a query but for some reasons i need it so another question is: if union all is not going to help me handling this is there anyway else? thx in advance

score 2 · Accepted Answer · answered Sep 26 '21 at 22:05

Not sure what the types of the columns are, but you can simply cast both columns to the same type, something like

SELECT money_transfer_type_enum::VARCHAR
FROM table1

UNION ALL

SELECT invoice_type_enum::VARCHAR
FROM table2

Varchar is a catch all for this type of incompatibility, but it comes with some costs, tho. You might wanna see if casting both columns to INTEGER might solve the issue as well

score 1 · Answer 2 · answered Sep 26 '21 at 22:03

The issue appears to be that you cannot union together two different enums. What you can potentially do is union the two sets together, but put them into separate columns.

select money_transfer_type_enum, 
       NULL as invoice_type_enum
from tblA

union all

select NULL as money_transfer_type_enum,
       invoice_type_enum
from tblB

Not sure if this solves your problem, but this should allow you to combine the two tables together without error.

selecting enum columns in union all postgres

2 Answers2