Redshift外部PostgreSQL Schema数值类型不匹配查询报错求助

问题说明 #

使用以下SQL在Redshift中创建外部PostgreSQL Schema：

create external schema if not exists external_psql_db
from POSTGRES
DATABASE 'production' SCHEMA 'public'
URI 'xxx.us-east-1.rds.amazonaws.com'
IAM_ROLE 'arn:aws:iam::xxx:role/xxx'
SECRET_ARN 'arn:aws:secretsmanager:us-east-1:xxx:secret:xxx/xxx'
;

但查询或关联部分包含数值类型列的表时失败：

• PostgreSQL中该列类型为numeric(65,2)，Redshift中显示为numeric
• 查询时触发错误：

SQL Error [XX000]: ERROR: #

error: Assert
code: 1000
context: precision <= 38 - precision=65, 38=38.
query: 0
location: pg_utils.cpp:4716
process: padbmaster [pid=14415] #

问题根源：Redshift的numeric类型最大支持精度为38，而PostgreSQL的numeric(65,2)超出了这个范围，导致类型映射时触发断言错误。

解决方法 #

方法1：在PostgreSQL端创建适配视图 #

在PostgreSQL中为目标表创建视图，将高精度数值列转换为Redshift支持的类型（如numeric(38,2)或文本类型），之后在Redshift中查询该视图：

-- PostgreSQL端执行
CREATE OR REPLACE VIEW public.your_table_adapted AS
SELECT
    id,
    -- 若业务允许精度压缩，转换为Redshift支持的最大精度数值类型
    CAST(high_precision_col AS NUMERIC(38,2)) AS high_precision_col,
    -- 若需要保留完整精度，转换为文本类型，后续在Redshift按需处理
    -- CAST(high_precision_col AS TEXT) AS high_precision_col,
    other_col1,
    other_col2
FROM public.your_table;

在Redshift中直接查询视图：

SELECT * FROM external_psql_db.your_table_adapted;

方法2：使用Redshift联邦查询实时转换 #

如果无法修改PostgreSQL端，可通过Redshift的联邦查询函数postgres_query在查询时实时转换类型（适合小数据量场景）：

SELECT
    t.id,
    CAST(
        postgres_query(
            'external_psql_db',
            'SELECT high_precision_col FROM public.your_table WHERE id = ' || t.id
        ) AS NUMERIC(38,2)
    ) AS high_precision_col,
    t.other_col
FROM external_psql_db.other_table t;

方法3：修改PostgreSQL原表列精度（业务允许时） #

如果业务场景不需要65位精度，直接修改PostgreSQL表的列类型为Redshift兼容的精度：

-- PostgreSQL端执行
ALTER TABLE public.your_table ALTER COLUMN high_precision_col TYPE NUMERIC(38,2);

修改后重新在Redshift中刷新外部schema（或重新创建）即可正常查询。

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