postgresql - 使用 COPY 导入超过 1,000 个格式化数字时出现问题

Question

我有一个 Postgres 数据库，并且一直在尝试使用下面的代码将 CSV 文件导入到表中。我不断收到错误

ERROR:  invalid input syntax for type numeric: " 1,183.26 "

我认为问题是值中有一个 , 但当我进入 CSV 并尝试编辑该值时，似乎添加了 ,由 Excel 自动执行。

COPY invtest5
FROM 'C:\Users\Hank\Downloads\SampleData\SampleDataCSV.csv' 
DELIMITER ',' 
CSV HEADER;

表定义:

CREATE TABLE invtest5 (
    OrderDate date,
    Region varchar(255),
    Rep varchar(255),
    Item varchar(255),
    Units int,
    Unit_Cost numeric(15,3),
    Total numeric(15,3)
);

我正在寻找一种导入数据的方法，无论号码中是否有 , 。

Answer 1

“1,183.26”不是有效的 numeric literal 。 COPY快速且简单，但不具有容错性。需要有效的输入。

一些要修复的选项:

1. 在 Excel 中设置数字格式而不使用“组分隔符”(这就是噪音 , 的来源)。

2. 编辑 CSV 以删除组分隔符。 (但不要删除其他逗号!)

3. 如果你有能力ALTER目标表中的列类型(即数据库上没有并发负载，您拥有必要的权限，并且没有会阻塞的依赖对象)，您可以:

ALTER TABLE invtest5
  ALTER unit_cost TYPE text
, ALTER total     TYPE text;     -- both columns?

COPY ...

ALTER TABLE invtest5
  ALTER unit_cost TYPE numeric(15,3) USING (replace(unit_cost, ',', '')::numeric)
, ALTER total     TYPE numeric(15,3) USING (replace(total    , ',', '')::numeric);

表达式 (replace(unit_cost, ',', '')::numeric) 在转换为 numeric 之前删除所有逗号。

在转换中自动修剪前导和尾随空格。

如果表中已经有一些行，现有值也会来回转换，这会触发整个表重写并使表膨胀。 对于大表效率不高。

如果您无法轻松修复 CSV，并且无法修改目标表(或者只是不想使其膨胀)，请使用临时暂存表作为COPY 目标，然后从那里 INSERT:

CREATE TEMP tmp_invtest5 AS TABLE invtest5 LIMIT 0;  -- copy basic structure

ALTER TABLE tmp_invtest5
  ALTER unit_cost TYPE text
, ALTER total     TYPE text;     -- both columns?

COPY TO tmp_invtest5 ...

INSERT INTO invtest5 
      (orderdate, region, rep, item, units, unit_cost, total)
SELECT orderdate, region, rep, item, units, replace(unit_cost, ',', '')::numeric
                                          , replace(total    , ',', '')::numeric
FROM   tmp_invtest5
-- ORDER BY ??? -- while being at it?

临时表会在 session 结束时自动删除。如果您需要在此之前删除它，DROP TABLE tmp_invtest5;。

相关:

• How to update selected rows with values from a CSV file in Postgres?
• How to ignore errors with psql \copy meta-command