Union types integer and text cannot be matched

Question

After migrating database data from Postgresql 9 to GCP cloud sql PostgreSql 12, the query shown here is failing with this error:

“UNION types integer and text cannot be matched”

Query:

Select c1, c2, c3 
From table t1 
Union all 
Select x_.* 
From 
    (Select c1, c2, c3 From table t2) as x_ 
Inner Join t3 as t on t.c33 = x_.c3 
Where c2 in (‘a’, ’b’, ’d’)

This type of query was working flawlessly in Postgresql 9, but is failing in Postgresql 12.

I'm not able to understand how to fix this issue.

Individually when I run both queries, the first part returns data but 2nd part of union all query is not returning any data

Answer 1

Your query looks a little bit complicated, try using:

Select c1,c2,c3 from table t1 
Union all 
select c1,c2,c3 from table t2 inner join t3 as t on t.c33=t2.c3 where c2 in ('a','b','d')

As per my understanding, the temp table which is created with (select c1,c2,c3 from table t2) as x_ stores the newly created values as string, not as integer