PostgreSQL导入含datetime2(7)列的CSV报错及类型转换求助

问题根源 #

报错提示invalid input syntax for type timestamp: "CREATE_S"，核心原因是CSV的表头字符串"CREATE_S"被当作timestamp类型的数据行处理了——不管是直接导入还是后续转换列类型，这个非日期格式的字符串都无法被解析为timestamp。

方案一：重新正确导入CSV（推荐） #

如果还没完成导入，直接通过COPY命令跳过表头并指定正确类型：

1. 确保CSV第一行是表头，导入时添加HEADER参数跳过表头：

   COPY table_s (列1, 列2, CREATE_S)
   FROM '/你的文件路径/xxx.csv'
   WITH (FORMAT csv, HEADER, DELIMITER ',', ENCODING 'UTF8');
   

   此时可以直接将CREATE_S列设为timestamp类型，PostgreSQL的timestamp兼容SQL Server的datetime2(7)格式。
2. 如果CSV日期是非标准格式，可指定解析格式：
   比如日期格式为yyyy-MM-dd HH:mm:ss.fffffff，用DATEFORMAT参数匹配：

   COPY table_s (列1, 列2, CREATE_S)
   FROM '/你的文件路径/xxx.csv'
   WITH (FORMAT csv, HEADER, DELIMITER ',', ENCODING 'UTF8', DATEFORMAT 'YYYY-MM-DD HH24:MI:SS.US');
   

方案二：修复已导入为varchar的表 #

如果已经将CREATE_S导入为character varying类型，按以下步骤修复：

1. 清理混入的表头数据：
   先查询确认表头行：

   SELECT * FROM table_s WHERE CREATE_S = 'CREATE_S';
   

   删除该行：

   DELETE FROM table_s WHERE CREATE_S = 'CREATE_S';
   

2. 转换列类型：
   • 标准日期格式直接转换：

     ALTER TABLE table_s ALTER COLUMN CREATE_S TYPE timestamp USING CREATE_S::timestamp;
     

   • 非标准日期格式需指定转换规则：

     ALTER TABLE table_s ALTER COLUMN CREATE_S TYPE timestamp USING to_timestamp(CREATE_S, 'YYYY-MM-DD HH24:MI:SS.US');
     

     注意：格式字符串需与CSV中的日期格式完全匹配，US表示微秒，PostgreSQL最多支持6位小数，datetime2(7)的第7位会被自动截断，不影响精度。

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