sqlpostgresqldbt

Safe selecting non-existent column in SQL (or dbt)


Is there a way of safe selecting a column that doesn't exists in a table without geting "invalid identifier" error? Maybe returning null instead?

Something like this:

select 
    id, 
    non_existant_column as col_name -- return null here instead of rising an error

from table

I tried already a number of SQL (coalesce, ifnull, ...) and dbt (dbt_utils.star, jinja adapter) functions.

I would like to see a simple solution returning null in the column values if the column doesn't exists.


Solution

  • There is a PostgreSQL way using JSON, not very nice however.

    select 
        jr['id']::integer as id, 
        jr['non_existant_column'] as col_name
    from (select to_jsonb(t) jr from the_table t) t;
    

    A typecast would be needed for col_name expression.
    In your particular case it would be something like

    select
      id, existent_column_a, existent_column_b, ...,
      jr['non_existent_column_a'] as col_name_a,
      jr['non_existent_column_b'] as col_name_b,
      ...
    from (select *, to_jsonb(t) jr from the_table t) t;
    

    column_name_x expressions would need to be typecast. You may also prefer JSONB arrow syntax (jr -> 'non_existent_column_a') rather than subscripting.