将游标记录存入数组时抛出日期时间范围错误求助

嘿，我来帮你排查这个问题！这个错误的核心是PostgreSQL没法把字符串"0"转换成合法的时间戳，而且问题出在游标查询的结果绑定到v_idetail的时候。咱们一步步拆解：

1. 先定位问题的根源 #

错误触发在FETCH c_dt_coursor INTO v_idetail;，说明游标返回的数组内容和你定义的stbschema.itdtls_v类型不兼容，或者查询里的时间字段藏着无效值。

先看你的游标查询：

select array(select row(id, mny, sdt, rat, coalesce(edt, p_dt)) from idtls where id = p_id and sdt <= p_dt and coalesce(edt, p_dt) > p_dtf order by sdt,mny);

这里有两个最可能的坑：

• 字段顺序不匹配：你定义的stbschema.itdtls类型字段顺序是ID NUMERIC, TD NUMERIC, SDT TIMESTAMP(0), RUP NUMERIC, EDT TIMESTAMP(0)，但游标里row()的字段顺序是否和这个完全对应？比如如果mny不是对应TD，或者rat不是对应RUP，就会把数值类型塞到时间字段里，导致解析出"0"这类无效值。
• edt字段有无效值：如果idtls表的edt字段里存了'0'、空字符串这类非合法时间值，当用coalesce(edt, p_dt)时，如果edt不是NULL而是'0'，就会直接返回这个无效值，PostgreSQL自然转不成时间戳。

2. 具体排查步骤 #

第一步：核对字段顺序和类型 #

先确认idtls表的字段和stbschema.itdtls类型的顺序、类型完全匹配：

• 类型的顺序是 ID → TD → SDT → RUP → EDT
• 游标里的row是 id → mny → sdt → rat → coalesce(edt,p_dt)
  如果mny或rat的类型不对，或者顺序错了，比如把某个数值字段放到了时间字段的位置，就会触发这个错误。

第二步：检查edt字段的无效值 #

执行下面的SQL，看看目标行里的edt有没有问题：

select edt from idtls where id = p_id and sdt <= p_dt and coalesce(edt, p_dt) > p_dtf;

重点看有没有edt的值是'0'、空字符串或者不符合YYYY-MM-DD HH:MI:SS格式的内容。

3. 修复方案 #

方案一：修正字段顺序（如果顺序错了） #

如果是字段顺序不匹配，调整游标里row()的字段顺序，和stbschema.itdtls严格对齐。比如如果TD应该对应另一个字段，就把位置换对。

方案二：清理或过滤edt的无效值 #

如果edt存在无效值，要么先清理表数据，要么在查询里提前处理：

-- 把无效的edt值转为NULL，再用p_dt代替
coalesce(case when edt::text ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$' then edt else null end, p_dt)

或者直接过滤掉edt无效的行：

select array(
    select row(id, mny, sdt, rat, coalesce(edt, p_dt)) 
    from idtls 
    where id = p_id 
      and sdt <= p_dt 
      and coalesce(edt, p_dt) > p_dtf
      and (edt is null or edt::text ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') -- 只保留合法时间
    order by sdt,mny
);

方案三：用明确的类型转换增强安全性 #

不要直接用row()构造数组，而是明确转成stbschema.itdtls类型，这样PostgreSQL会提前校验每个字段的类型，方便定位问题：

select array(
    select (id, mny, sdt, rat, coalesce(edt, p_dt))::stbschema.itdtls 
    from idtls 
    where id = p_id 
      and sdt <= p_dt 
      and coalesce(edt, p_dt) > p_dtf 
    order by sdt,mny
);

4. 调试小技巧 #

你可以先单独执行游标里的子查询，看看返回的每一行是否符合预期：

select row(id, mny, sdt, rat, coalesce(edt, p_dt)) 
from idtls 
where id = p_id 
  and sdt <= p_dt 
  and coalesce(edt, p_dt) > p_dtf 
order by sdt,mny;

重点检查第五个字段（对应EDT的值）有没有'0'或者其他奇怪的内容，这样能快速定位是哪条数据出了问题。

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