PostgreSQL 14如何兼容导入含逗号小数点的实数数据？

针对CSV中同时存在.和,作为小数点的导入问题，以下是几种无需修改原始数据的可行方案：

1. 确保lc_numeric设置生效（单格式兼容） #

如果你的CSV主要是其中一种格式，之前设置en_DK没生效，大概率是设置范围或生效方式不对：

• 全局生效：编辑postgresql.conf，修改lc_numeric = 'en_DK.UTF-8'，保存后重启PostgreSQL服务：

  sudo systemctl restart postgresql
  

• 数据库级生效：仅对目标数据库修改，无需全局重启：

  ALTER DATABASE your_db SET lc_numeric = 'en_DK.UTF-8';
  

  修改后必须重新连接数据库才会生效。注意：en_DK locale仅认,为小数点，若CSV同时存在.格式，此方法无法兼容，需用下面的混合处理方案。

2. 临时表+字段转换（混合格式通用） #

这是最稳妥的混合格式处理方式，先导入文本再转换：

1. 创建临时表，将数值字段定义为文本类型：

   CREATE TEMP TABLE temp_csv_import (
     -- 根据你的CSV结构定义字段，数值字段用TEXT
     id INT,
     amount TEXT,
     other_column VARCHAR(100)
   );
   

2. 导入原始CSV到临时表：

   COPY temp_csv_import FROM '/path/to/your/data.csv' WITH (FORMAT CSV, HEADER, DELIMITER ',');
   

   注意：DELIMITER是你的CSV字段分隔符，若用分号则改为';'。
3. 转换数值并插入正式表：

   INSERT INTO your_target_table (id, amount, other_column)
   SELECT 
     id,
     CAST(REPLACE(amount, ',', '.') AS NUMERIC),
     other_column
   FROM temp_csv_import;
   

   此方法会把所有,替换为.，不管原始是哪种小数点格式，都能正常转为数值。

3. COPY时直接预处理大文件（高效方案） #

如果CSV文件极大，不想占用临时表空间，可在导入前用shell命令直接处理指定列的逗号：
比如你的数值在第2列，CSV分隔符是,，用awk只替换第2列的逗号为点：

COPY your_target_table FROM PROGRAM 'awk ''BEGIN{FS=",";OFS=","} {gsub(/,/, ".", $2)}1'' /path/to/large_data.csv' WITH (FORMAT CSV, HEADER);

如果CSV分隔符是;，可直接用sed全局替换逗号为点：

COPY your_target_table FROM PROGRAM 'sed ''s/,/./g'' /path/to/large_data.csv' WITH (FORMAT CSV, HEADER, DELIMITER ';');

4. 自定义转换函数（复用性强） #

如果需要频繁处理这类数据，可创建一个通用转换函数：

CREATE OR REPLACE FUNCTION str_to_numeric(text) RETURNS NUMERIC AS $$
BEGIN
  RETURN CAST(REPLACE($1, ',', '.') AS NUMERIC);
EXCEPTION
  WHEN invalid_text_representation THEN
    RETURN NULL; -- 也可根据需求抛出错误，比如 RAISE NOTICE 'Invalid numeric value: %', $1;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

之后导入时直接调用函数：

INSERT INTO your_target_table (amount)
SELECT str_to_numeric(amount_text) FROM temp_csv_import;

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