UNION操作报错：character varying与date类型不匹配，请求排查帮助

核心问题：UNION ALL的列顺序不匹配 #

你的activity_retailers CTE的列顺序和其他几个CTE（earn_txn、burn_txn等）不一致，直接导致类型冲突：

• 其他CTE的列顺序：shop_id, state, date, year, month, transactiontype, transactionid, countryname
• activity_retailers的列顺序：shop_id, date, year, month, transactiontype, state, transactionid, countryname

这就造成第二个位置的列，在其他CTE中是字符类型的state，在activity_retailers中是日期类型的date，正好触发了UNION types character varying and date cannot be matched错误。

修复步骤 #

调整activity_retailers的列顺序，让它和其他CTE完全对齐：

activity_retailers as (
    select 
        shop_id::varchar(513) AS shop_id, 
        state, -- 将state移到第二位置，与其他CTE保持一致
        final_created_at as "date",
        extract(year from final_created_at)  as "year",
        extract(month from final_created_at) as "month", 
        'in_app_activity' as transactiontype,
        row_number() over (order by shop_id) as transactionid, 
        country::varchar(513) as countryname
    from (
        with f1 as (
            select *, to_char(to_date(created_date, 'MM-DD-YYYY'), 'YYYY-MM-DD')::date as final_created_at,
            coalesce (nullif(trim(userid), ''), nullif(trim(json_extract_path_text(properties, 'userId', true)), '')) as user_id_final,
            (case when coalesce(nullif(trim(userid), ''), nullif(trim(json_extract_path_text(properties, 'userId', true)), '')) is not null then 'not null'
                else 'null' end) as user_id_availability,
            nullif(trim(json_extract_path_text(properties, 'country', true)), '') as country
            from all_events.rudder_events
            where event like 'a_%'
        and event not like 'a_Onboarding%'
        and event not like 'a_Splash%'
        and user_id_availability = 'not null'
        and country notnull 
        )
        select country::varchar(513), shop_id::varchar(513), final_created_at, state
        from f1 as u
        join dw_global_app.in_shop_user s
        on u.user_id_final = s.user_id
        join dw_yc_in.india_users_migrated f
        on f.retailer_id = s.user_id)
)

建议后续所有CTE都严格遵循统一的列顺序编写，避免因字段位置变动再次引发类型匹配错误。

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