PostgreSQL Question posted in
The official documentation can be found here.

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

Tags:

3

Answers


  1. 0

    This is dependent upon the database you’re using, but:

    In PostgreSQL:

    In short, no, when you run a query against a non-existant resource, it will throw an error. However, there are ways you can work around this.

    For example you can run queries against your database to check your schema, however:

    A) This has to be run before you run your queries, and

    B) I would recommend using a different database engine such as a NoSQL db if you need schema flexibility – schema flexibility is not what relational databases are exactly intended for.

    Getting schema information:

    SELECT column_name 
FROM information_schema.columns 
WHERE table_name='data_table'

    An example query using schema information:

    DO $$
DECLARE
    column_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1 
        FROM information_schema.columns 
        WHERE table_name='data_table' AND column_name='value_column'
    ) INTO column_exists;

    IF column_exists THEN
        EXECUTE 'SELECT name, value_column FROM data_table';
    ELSE
        EXECUTE 'SELECT name, NULL::INT AS value_column FROM data_table';
    END IF;
END $$;
    Login or Signup to reply.
  2. 0

    This is easy to do in DBT:

    {%- set columns = dbt_utils.get_filtered_columns_in_relation(from=source('source_name', 'table_name') -%}

select 
    id,
    {% if 'non_existant_column' in columns -%}
      non_existant_column as col_name
    {% else %}
      NULL as col_name
    {% endif %}
from table

    Under the hood, this is doing the same thing as Gerik’s answer above. It queries the underlying DB’s information schema at compile time to retrieve the column list which you can use to generate your final SQL.

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search