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.
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.