如何将科学计数法格式的数字字符串导入PostgreSQL表？

核心思路 #

PostgreSQL的integer类型无法直接解析科学计数法格式的字符串，但numeric类型可以识别这类格式。通过先将科学计数字符串转为numeric，再转为integer，就能实现需求，无需提前用Python展开科学计数法。

方案1：临时表中转（适合首次导入） #

1. 创建临时表，用text类型接收科学计数字段：

CREATE TEMP TABLE temp_import (
    time_col timestamp,
    value text
);

2. 导入CSV/TXT数据到临时表：

COPY temp_import FROM '/your/file/path.csv' WITH (FORMAT CSV, HEADER);

3. 将数据转换后插入目标表：

INSERT INTO target_table (time_col, value)
SELECT
    time_col,
    -- 先转numeric解析科学计数法，再转integer
    value::numeric::integer
FROM temp_import;

注意：如果数据包含非整数的科学计数法（如3.14e+01），可改用ROUND(value::numeric)::integer取整，或提前校验数据确保为整数。

方案2：直接修改已导入的text字段（适合已导入text类型表的情况） #

如果已经把数据导入到text类型字段的表中，直接用ALTER TABLE配合USING子句完成类型转换：

ALTER TABLE table6 ALTER COLUMN value TYPE integer USING (value::numeric::integer);

这条命令会自动将所有科学计数字符串转换为整数，无需重新导入数据。

方案3：导入时直接处理（进阶） #

如果不想用临时表，也可以在COPY时通过PROGRAM调用轻量工具（如awk）实时转换，但SQL内处理更直观：

COPY target_table (time_col, value)
FROM PROGRAM 'awk ''BEGIN{FS=","} {gsub(/e\+/,"*10^",$2); print $1","$2}'' /your/file/path.csv'
WITH (FORMAT CSV, HEADER);

不过这种方式依赖外部工具，不如前两种方案通用。

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