如何在SQL或dbt中安全查询不存在的列？返回Null而非报错

一、SQL层面（动态SQL实现） #

静态SQL无法直接实现“列不存在返回NULL”——因为SQL解析阶段就会校验标识符有效性，必须借助动态SQL结合元数据查询生成逻辑：

PostgreSQL 示例 #

DO $$
DECLARE
    col_exists BOOLEAN;
    query TEXT;
BEGIN
    -- 检查目标列是否存在
    SELECT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'your_table' AND column_name = 'non_existant_column'
    ) INTO col_exists;

    -- 动态生成查询语句
    query := 'SELECT id, ' || 
             CASE WHEN col_exists THEN 'non_existant_column' ELSE 'NULL' END || 
             ' AS col_name FROM your_table';
    
    EXECUTE query;
END $$;

MySQL 示例 #

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.columns 
    WHERE table_schema = DATABASE() AND table_name = 'your_table' AND column_name = 'non_existant_column'
);

SET @query = CONCAT(
    'SELECT id, ', 
    IF(@col_exists = 1, 'non_existant_column', 'NULL'), 
    ' AS col_name FROM your_table'
);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

BigQuery 示例 #

DECLARE col_exists BOOL;
DECLARE query STRING;

SET col_exists = EXISTS (
    SELECT 1 FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
    WHERE table_name = 'your_table' AND column_name = 'non_existant_column'
);

SET query = CONCAT(
    'SELECT id, ', 
    IF(col_exists, 'non_existant_column', 'NULL'), 
    ' AS col_name FROM `your_project.your_dataset.your_table`'
);

EXECUTE IMMEDIATE query;

二、dbt专属解决方案（Jinja模板实现） #

dbt的Jinja环境可通过adapter对象获取表元数据，编译阶段就完成列存在性判断，生成合法SQL：

基础实现 #

在dbt模型文件中添加以下逻辑：

{% set target_table = ref('your_table') %}
{% set table_columns = adapter.get_columns_in_relation(target_table) | map(attribute='name') | list %}

SELECT
    id,
    {% if 'non_existant_column' in table_columns %}
        non_existant_column AS col_name
    {% else %}
        NULL AS col_name
    {% endif %}
FROM {{ target_table }}

批量处理多列（封装宏） #

如果需要批量处理多个可能不存在的列，可封装成复用宏：

{% macro safe_column(column_name, alias=None) %}
    {% set alias = alias if alias else column_name %}
    {% if column_name in table_columns %}
        {{ column_name }} AS {{ alias }}
    {% else %}
        NULL AS {{ alias }}
    {% endif %}
{% endmacro %}

-- 调用宏示例
{% set target_table = ref('your_table') %}
{% set table_columns = adapter.get_columns_in_relation(target_table) | map(attribute='name') | list %}

SELECT
    id,
    {{ safe_column('non_existant_column', 'col_name') }},
    {{ safe_column('another_missing_col', 'col2') }}
FROM {{ target_table }}

内容的提问来源于stack exchange，提问作者Damir