Redshift regression: failed to find conversion function from "unknown" to numeric

The error you're encountering in Redshift, ERROR: failed to find conversion function from "unknown" to numeric, suggests that the query engine is having difficulty inferring the correct data type for the null value in your UNION query.

In your query, you have two Common Table Expressions (CTEs), a and b. a returns a column test of type decimal(15, 4), while b returns a column test with null values but doesn't explicitly define the data type for null. When you try to UNION these two CTEs in c, Redshift struggles to reconcile the unknown data type of the null in b with the decimal(15, 4) type in a.

To resolve this, you should explicitly cast the null in CTE b to the same data type as the corresponding column in CTE a.

with
    a as (select cast(0 as decimal(15, 4)) as test),
    b as (select cast(null as decimal(15, 4)) as test),
    c as (
        select * from a
        union all
        select * from b
    )
select * from c;

Always ensure that your queries explicitly define data types, especially when dealing with null values and set operations like UNION