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

Question

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.

Answer 1

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.