MS-SQL迁PostgreSQL视图带LIMIT报错：整数输入语法无效

问题现象 #

将数据库从MS-SQL迁移至PostgreSQL后，部分视图出现异常：

• 执行带LIMIT子句的视图查询时，抛出Invalid Input Syntax for Integer: SQL state: 22P02错误
• 移除LIMIT子句则查询正常，MS-SQL中使用TOP(1)可正常运行
• 涉事视图SQL代码如下：

SELECT ag."Codice" AS "Codice_anagen",
    ac."Codice" AS "Codice_anacli",
    ad."Codice" AS "Codice_anaditte",
    ag."AUID" AS "Codice_au",
    ag."Ragione_sociale_att",
    ac."Cliente_disatt" AS "Anacli_disattivato",
    COALESCE(ad."Ditta_disabilitata", 'X'::character varying) AS "Ditta_disabilitata",
        CASE
            WHEN COALESCE(ad."Data_Disabilitazione", '01/01/1900'::character varying)::text = '00/00/0000'::text THEN '01/01/1900'::character varying
            ELSE COALESCE(ad."Data_Disabilitazione", '01/01/1900'::character varying)
        END AS "Data_cessazione_come_ditta",
    ag."Partita_iva" AS ag_partita_iva,
    ag."Codice_fiscale" AS ag_codice_fiscale,
    "ANA_UN"."Partita_IVA" AS au_partita_iva,
    "ANA_UN"."Codice_Fiscale" AS au_codice_fiscale,
    COALESCE(( SELECT 'M'::text AS "Tipo"
           FROM dbo."MULTI_PRIVATI" mpp
          WHERE mpp."Anacli_disattivato"::text = 'N'::text AND mpp."Ditta_disabilitata"::text <> 'X'::text AND mpp."Codice_au"::text = ag."AUID"::text AND mpp."Codice_anagen"::text = ag."Codice"::text AND mpp."Codice_anacli"::text = ac."Codice"::text), 'P'::text)::character varying(2) AS tipocli,
    COALESCE(( SELECT COALESCE("ANAGEN_CRM".note, ''::character varying) AS "Note"
           FROM dbo."ANAGEN_CRM"
          WHERE "ANAGEN_CRM"."Codice"::text = ag."Codice"::text), ''::character varying) AS note
   FROM dbo."ANAGEN" ag
     JOIN dbo."ANACLI" ac ON ac."Codice_anagrafica_anagen"::integer = ag."Codice"::integer
     LEFT JOIN ( SELECT *
           FROM dbo."ANADITTE"
             JOIN dbo."ANADITTE_SINGLE_AUID" ON "ANADITTE"."AUID"::integer = "ANADITTE_SINGLE_AUID"."AUID"::integer AND "ANADITTE"."Codice"::integer = "ANADITTE_SINGLE_AUID".codice::integer) ad ON ad."AUID"::integer = ag."AUID"::integer
     JOIN dbo."ANA_UN" ON "ANA_UN"."ID"::integer = ag."AUID"::integer;

原因分析 #

错误核心是字符串转整数时遇到非数字内容，PostgreSQL与MS-SQL的查询优化器行为差异导致：

• MS-SQL优化器会优先执行过滤逻辑，再处理类型转换；而PostgreSQL在带LIMIT时，可能调整执行计划，提前尝试将字符串字段转为整数，当字段中存在非数字值时触发转换失败。
• 视图代码中大量使用::integer强制转换（如ac."Codice_anagrafica_anagen"::integer = ag."Codice"::integer），若这些字段实际存储了非数字字符串，就会触发该错误。

解决方案 #

1. 修正字段类型（最优方案） #

如果Codice、Codice_anagrafica_anagen、AUID等字段本应是整数类型，直接修改表结构，彻底消除转换需求：

-- 示例：修改ANAGEN表的Codice字段为整数类型
ALTER TABLE dbo."ANAGEN" ALTER COLUMN "Codice" TYPE integer USING "Codice"::integer;
-- 同理修改其他涉及强制转换的字段

注意：执行前需验证字段所有值均可正常转为整数，可先执行以下查询检查异常值：

SELECT "Codice" FROM dbo."ANAGEN" WHERE "Codice" !~ '^[0-9]+$';

2. 安全类型转换（兼容方案） #

若暂时无法修改表结构，使用安全转换方式避免转换失败：

方法一：使用TRY_CAST（PostgreSQL 12+支持）

将所有::integer替换为TRY_CAST(字段 AS integer)，转换失败时返回NULL，同步调整JOIN条件：

-- 示例JOIN条件修改
JOIN dbo."ANACLI" ac ON TRY_CAST(ac."Codice_anagrafica_anagen" AS integer) = TRY_CAST(ag."Codice" AS integer)

方法二：自定义安全转换函数（低版本PostgreSQL适用）

创建自定义函数处理转换失败场景：

CREATE OR REPLACE FUNCTION safe_to_int(text) RETURNS integer AS $$
BEGIN
    RETURN $1::integer;
EXCEPTION
    WHEN invalid_text_representation THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

之后在视图中用safe_to_int(字段)替代::integer即可。

3. 强制执行计划顺序（临时 workaround） #

通过添加OFFSET 0强制PostgreSQL优先执行过滤逻辑再处理转换，仅作为临时解决方案，不推荐长期使用：

SELECT * FROM 你的视图名 LIMIT 1 OFFSET 0;

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