PostgreSQL COPY导入Epoch格式时间戳失败，报错日期时间值越界

使用PostgreSQL的COPY功能导入CSV文件时，因时间戳字段为Epoch格式（如1680894075000）触发如下错误：

org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "1680894075000"
Hint: Perhaps you need a different "datestyle" setting.
Where: COPY source_pr_tbl, line 1, column processing_time: "1680894075000"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1211)
at org.postgresql.core.v3.QueryExecutorImpl.endCopy(QueryExecutorImpl.java:1016)

能否无需转换CSV文件直接将Epoch值插入PostgreSQL？求可行建议。

可以直接在PostgreSQL内部处理，无需预先修改CSV文件，核心是解决毫秒级Epoch与PostgreSQL默认秒级Epoch不兼容的问题，以下是具体方案：

方案一：COPY导入临时字段后转换 #

先导入原始毫秒值到临时字段，再转换为timestamp类型：

-- 创建表时新增临时字段存储原始毫秒值
CREATE TABLE source_pr_tbl (
  col1 text,
  col2 int,
  processing_time_raw bigint,
  processing_time timestamp
);

-- 用COPY导入CSV
COPY source_pr_tbl (col1, col2, processing_time_raw)
FROM '/path/to/your/file.csv'
WITH (FORMAT csv, HEADER);

-- 转换为timestamp并写入目标字段
UPDATE source_pr_tbl
SET processing_time = to_timestamp(processing_time_raw / 1000)
WHERE processing_time_raw IS NOT NULL;

-- 可选：删除临时字段
ALTER TABLE source_pr_tbl DROP COLUMN processing_time_raw;

方案二：直接用bigint存储Epoch，查询时转换 #

如果不需要实时将字段作为timestamp使用，可以将字段类型设为bigint，直接导入毫秒值，查询时再做转换：

-- 创建表时指定字段类型为bigint
CREATE TABLE source_pr_tbl (
  col1 text,
  col2 int,
  processing_time bigint
);

-- 直接COPY导入
COPY source_pr_tbl FROM '/path/to/your/file.csv' WITH (FORMAT csv, HEADER);

-- 查询时转换为timestamp
SELECT 
  col1, 
  col2, 
  to_timestamp(processing_time / 1000) AS processing_time 
FROM source_pr_tbl;

方案三：借助file_fdw扩展直接转换导入 #

如果已经安装file_fdw扩展，可以直接在COPY时完成转换：

-- 先创建外部表映射CSV
CREATE FOREIGN TABLE csv_source (
  col1 text,
  col2 int,
  processing_time_raw bigint
)
SERVER file_server
OPTIONS (filename '/path/to/your/file.csv', format 'csv', header 'on');

-- 导入时直接转换
INSERT INTO source_pr_tbl (col1, col2, processing_time)
SELECT col1, col2, to_timestamp(processing_time_raw / 1000)
FROM csv_source;

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