PostgreSQL返回大表函数报错：NULL值类型不匹配

问题原因 #

报错提示第4列返回text类型，与预期的boolean类型不匹配，根源是：直接使用null填充字段时，PostgreSQL会默认将null解析为text类型，而函数返回表中声明的col1是BOOLEAN类型，类型不兼容导致报错。

解决方法 #

对每个需要返回null的字段，根据其在RETURNS TABLE中声明的类型，显式转换null的类型，比如：

• BOOLEAN类型字段：null::BOOLEAN
• DATE类型字段：null::DATE
• BIGINT类型字段：null::BIGINT
• VARCHAR/TEXT类型字段：null::TEXT（或对应长度的VARCHAR，比如null::VARCHAR(50)）

修改后的函数代码 #

CREATE OR REPLACE FUNCTION get_letters(
    date_from                 DATE, 
    date_to                 DATE, 
    SystemState_ACTIVE             VARCHAR(50) default 'ACTIVE',
    SystemState_DELETED            VARCHAR(50) default 'DELETED',
    SystemState_INACTIVE           VARCHAR(50) default 'INACTIVE',
    LetterSource_PAPER             VARCHAR(50) default 'PAPER', 
    LetterSource_ELECTRONIC         VARCHAR(50) default 'ELECTRONIC', 
    LetterState_PRINTED           VARCHAR(50) default 'PRINTED', 
    LetterState_IN_TRASH           VARCHAR(50) default 'IN_TRASH', 
    LetterState_REALIZED           VARCHAR(50) default 'REALIZED', 
    LetterState_SENT             VARCHAR(50) default 'SENT', 
    Type_LETTER               VARCHAR(50) default 'LETTER', 
    Type_MOVE_TO_TRASH             VARCHAR(50) default 'MOVED_TO_TRASH', 
    Number_From               INT     default null, 
    Number_To                 INT     default null, 
    Param1_Id                 BIGINT     default null, 
    Param2_Id                 BIGINT     default null, 
    Param3_Number               VARCHAR(50)  default null, 
    Param4_Number               VARCHAR(50)  default null, 
    Param5                   VARCHAR(50)  default null, 
    Param6_Id                  VARCHAR(50)  default null, 
    Param7_Number               VARCHAR(50)  default null, 
    Param8_Number               VARCHAR(50)  default null, 
    Param9                   BOOLEAN   default null, 
    Param10                 BOOLEAN   default null, 
    Param11                 VARCHAR(50)  default null)
  RETURNS TABLE (
    Id             BIGINT,
    Number           VARCHAR(50),
    state           VARCHAR(50),
    col1           BOOLEAN,
    source           VARCHAR(50),
    type           VARCHAR(50),
    fullDate        DATE,
    officeDate         DATE,
    col2Date         DATE,
    col3Date         DATE,
    col4           BOOLEAN,
    col5Number         VARCHAR(50),
    col6InputNumber     VARCHAR(50),
    col7Date         DATE,
    col8           TEXT,
    col9Date         DATE,
    col10           BOOLEAN,
    frozen           BOOLEAN,
    col11           BOOLEAN,
    col12           BOOLEAN,
    col13           TEXT,
    col14           VARCHAR(150),
    col15           VARCHAR(150),
    col16           VARCHAR(150),
    col17           VARCHAR(150),
    col18           VARCHAR(150),
    col19           VARCHAR(150),
    col20           VARCHAR(150),
    col21           VARCHAR(150),
    col22           BIGINT,
    col23            VARCHAR(150),
    col24           VARCHAR(150),
    col25imestamp       timestamp without time zone, 
    col26           BIGINT,
    col27           VARCHAR(50),
    col28           TEXT,
        col29Count         BIGINT,
    col30Count         BIGINT,
        col31Count         BIGINT,
        col32Read         BIGINT,
        col33Count         BIGINT,
        col34Count         BIGINT,
        col35          BIGINT,
        --0l,          --new column soon
    col36           BIGINT,
    col37           BIGINT
  )
LANGUAGE 'plpgsql'
AS $$
BEGIN 
  RETURN QUERY SELECT 
    l.id letterId,
    l.case_number AS caseNumber,
    L.state AS state,
    null::BOOLEAN AS col1,
    l.source AS source,
    l.type AS type,
    l.full_date AS fullDate,
    l.office_date  AS officeDate,
    null::DATE  AS col2Date,
    null::DATE  AS col3Date,
    null::BOOLEAN  AS col4,
    null::VARCHAR(50)  AS col5Number,
    null::VARCHAR(50) AS col6InputNumber,
    null::DATE AS col7Date,
    null::TEXT AS col8,
    null::DATE AS col9Date,
    null::BOOLEAN AS col10,
    null::BOOLEAN AS frozen,
    null::BOOLEAN AS col11,
    null::BOOLEAN AS col12,
    null::TEXT AS col13,
    null::VARCHAR(150) AS col14,
    null::VARCHAR(150) AS col15,
    null::VARCHAR(150) AS col16,
    null::VARCHAR(150) AS col17,
    null::VARCHAR(150) AS col18,
    null::VARCHAR(150) AS col19,
    null::VARCHAR(150) AS col20,
    null::VARCHAR(150) AS col21,
    null::BIGINT AS col22,
    null::VARCHAR(150) AS col23,
    null::VARCHAR(150) AS col24,
    null::TIMESTAMP WITHOUT TIME ZONE AS col25imestamp, 
    null::BIGINT AS col26 ,
    null::VARCHAR(50) AS col27,
    null::TEXT AS col28,
        null::BIGINT AS col29Count,
    null::BIGINT AS col30Count,
        null::BIGINT AS col31Count,
        null::BIGINT AS col32Read,
        null::BIGINT AS col33Count,
        null::BIGINT AS col34Count,
        null::BIGINT AS col35,
        --0l,
    null::BIGINT AS col36,
    null::BIGINT AS col37
  FROM LETTER l
  LIMIT 1;
END; $$

补充说明 #

当你用具体值填充时能正常执行，是因为具体值的类型和函数返回表中声明的类型天然匹配；而null本身没有固定类型，PostgreSQL会默认赋予它text类型，所以必须显式转换才能和目标类型对齐。

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