PostgreSQL中Varchar转Timestamp报错：YYYY值超出范围

这个问题我之前帮同事排查过，咱们先搞清楚错误根源，再一步步解决：

错误原因分析 #

你遇到的ERROR: value for "YYYY" in source string is out of range错误，大概率是这两种情况之一：

1. 字段内容格式不匹配：你的lastmodifiedon字段里存在不符合'YYYY-MM-DD HH24:MI:SS:MS'格式的脏数据（比如年份不是4位、毫秒不是3位，或者包含非数字字符），导致TO_TIMESTAMP解析时把无效内容当成年份，触发了范围溢出。
2. 年份数值真的超出范围：虽然比较少见，但如果你的数据里有远超出常规年份的数值（比如99999或者-100000），也会触发这个错误——PostgreSQL的timestamp类型对年份有合法范围限制，而错误提示里的整数范围是解析时的中间值溢出导致的。

解决方案步骤 #

1. 先找出异常数据

首先运行下面的查询，定位所有不符合目标格式的记录，这是最关键的第一步：

SELECT lastmodifiedon 
FROM table1 
WHERE lastmodifiedon !~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}:\d{3}$';

这个正则表达式会匹配标准的YYYY-MM-DD HH24:MI:SS:MS格式（4位年份、2位月份/日期、2位时分秒、3位毫秒），返回的就是导致转换失败的脏数据。你可以根据结果清理这些数据（比如修正格式、删除无效记录）。

2. 使用安全转换函数（PostgreSQL 12+）

如果不想因为个别脏数据导致整个查询失败，PostgreSQL 12及以上版本提供了TRY_TO_TIMESTAMP函数，它会在转换失败时返回NULL而不是抛出错误：

SELECT TRY_TO_TIMESTAMP(lastmodifiedon, 'YYYY-MM-DD HH24:MI:SS:MS') AS lastmodifiedon 
FROM table1;

3. 自定义安全转换函数（低版本PostgreSQL）

如果你用的是PostgreSQL 11及以下版本，可以自己写一个PL/pgSQL函数来捕获转换异常：

CREATE OR REPLACE FUNCTION safe_to_timestamp(input_str text, format_str text)
RETURNS timestamp AS $$
BEGIN
    RETURN TO_TIMESTAMP(input_str, format_str);
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL; -- 转换失败时返回NULL，也可以根据需求返回默认值
END;
$$ LANGUAGE plpgsql;

然后调用这个函数即可：

SELECT safe_to_timestamp(lastmodifiedon, 'YYYY-MM-DD HH24:MI:SS:MS') AS lastmodifiedon 
FROM table1;

4. 检查格式符是否正确

另外再确认一下：你用的MS是匹配毫秒的格式符，对应的是3位数字。如果你的数据里毫秒是1位或2位（比如12:34:56:7而不是12:34:56:007），可以保留MS（它会自动补零）或者换成FF1/FF2来匹配对应位数的毫秒。

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