Redshift - CASE statement checking column EXISTS or no

Question

I am querying dynamically tables where some of the tables might not have specific column. My intention is check the existence of the column and dynamically assign a value. Basically if all the tables would contain the field I would just write simply :

select name, count(k_val) from tbl GROUP by 1

But in my case I need to do something like this:

select name, 
    SUM( (CASE when (select EXISTS( SELECT * FROM pg_table_def WHERE tablename = 'tbl'
                                            and "column" = 'k_val')) 
                then 1 else 0 end) ) as val  
    from tbl GROUP by 1

I am getting the error:

SQL Error [500310] [0A000]: Amazon Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;

Your sample is equivalent to COUNT(*) or 0. Sample data and desired results would help. — Gordon Linoff

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-03-11T16:02:55

The following is a trick that works on most databases to handle missing columns.

select t.*,
       (select k_val  -- intentionally not qualified
        from tbl t2
        where t2.pk = t.pk
       ) new_k_val
from tbl t cross join
     (select NULL as k_val) k;

pk is the primary key column for the table. This uses scoping rules to find a value for k_val. If k_val is in the table, then the subquery will use the value from that row. If not, then the scope will "reach out" and take the value from k. There is no confusion in this case, because k_val is not in tbl.

If you don't want a constant subquery for some reason, you can always use:

     (select NULL as k_val from t limit 1) k

You can then use this as a subquery or CTE for your aggregation purposes.

Having said all that, I am wary of handling missing columns this way.

Redshift - CASE statement checking column EXISTS or no

1 Answers