DBT运行tempo.sql模型报错：PostgreSQL列Id不存在

问题背景 #

• 运行tempo.sql增量模型时触发错误：column "id" does not exist，提示需引用tempo__dbt_tmpxxx.Id或tempo.Id
• 表public.tempo的列均为带双引号的大小写敏感命名（如"Id"）
• dbt生成的SQL中，delete语句里的Id未加双引号，手动修改为"Id"后重新运行会被自动覆盖

相关代码与信息 #

原模型代码

{{ config(
    materialized='incremental',
    unique_key='Id'
) }}
SELECT 40 AS "Id", 
       'Moe Eggert' AS "Contact", 
       'M' AS "Sex", 
       35 AS "Age", 
       'PA' AS "State", 
       'I3593' AS "Product_ID", 
       'Desktop' AS "Product_Type", 
       399.99 AS "Sale Price", 
       72.09 AS "Profit", 
       'Website' AS "Lead", 
       'May' AS "Month", 
       2020 AS "Year"
FROM public.tempo;

错误提示

column "id" does not exist
  LINE 8:                 select (Id)
                                  ^
  HINT:  Perhaps you meant to reference the column "tempo__dbt_tmp135333094832.Id" or the column "tempo.Id".

表结构

CREATE TABLE public.tempo (
    "Id" int4 NULL,
    "Age" int4 NULL,
    "Sale Price" float4 NULL,
    "Profit" float4 NULL,
    "Year" int4 NULL,
    "Contact" varchar(256) NULL,
    "Sex" varchar(256) NULL,
    "State" varchar(256) NULL,
    "Product_ID" varchar(256) NULL,
    "Product_Type" varchar(256) NULL,
    "Lead" varchar(256) NULL,
    "Month" varchar(256) NULL
);

dbt生成的错误SQL片段

delete from "postgres"."public"."tempo"
where (
    Id) in (
    select (Id)
    from "tempo__dbt_tmp140716157689"
);

解决方案 #

要让dbt生成的增量逻辑正确引用大小写敏感的列，需修改模型配置中的unique_key，确保生成的SQL包含双引号：

方法1：直接使用带双引号的字符串作为unique_key #

修改模型配置部分，给Id加上双引号并转义：

{{ config(
    materialized='incremental',
    unique_key='"Id"'
) }}

方法2：使用dbt内置的quote宏 #

通过quote宏自动适配数据库的引号规则，更通用：

{{ config(
    materialized='incremental',
    unique_key=quote('Id')
) }}

验证效果 #

修改后重新执行dbt run，dbt生成的delete语句会自动添加双引号：

delete from "postgres"."public"."tempo"
where (
    "Id") in (
    select ("Id")
    from "tempo__dbt_tmp140716157689"
);

此时PostgreSQL能正确识别"Id"列，错误即可消除。

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