PG COPY 错误:整数的输入语法无效 [英] PG COPY error: invalid input syntax for integer

问题描述

运行 COPY 结果在 ERROR: invalid input syntax for integer: "" 给我的错误信息.我错过了什么?

Running COPY results in ERROR: invalid input syntax for integer: "" error message for me. What am I missing?

我的 /tmp/people.csv 文件:

"age","first_name","last_name"
"23","Ivan","Poupkine"
"","Eugene","Pirogov"

我的 /tmp/csv_test.sql 文件:

CREATE TABLE people (
  age        integer,
  first_name varchar(20),
  last_name  varchar(20)
);

COPY people
FROM '/tmp/people.csv'
WITH (
  FORMAT CSV,
  HEADER true,
  NULL ''
);

DROP TABLE people;

输出:

$ psql postgres -f /tmp/sql_test.sql
CREATE TABLE
psql:sql_test.sql:13: ERROR:  invalid input syntax for integer: ""
CONTEXT:  COPY people, line 3, column age: ""
DROP TABLE

琐事:

• PostgreSQL 9.2.4

推荐答案

错误:整数的无效输入语法:"

ERROR: invalid input syntax for integer: ""

"" 不是有效整数.PostgreSQL 在 CSV 中默认接受 unquoted 空白字段为 null，但 "" 就像这样写:

"" isn't a valid integer. PostgreSQL accepts unquoted blank fields as null by default in CSV, but "" would be like writing:

SELECT ''::integer;

因为同样的原因而失败.

and fail for the same reason.

如果您想处理包含空整数引用的空字符串之类的 CSV，您需要通过预处理器将其提供给 PostgreSQL，该预处理器可以对其进行整理.PostgreSQL 的 CSV 输入无法理解 CSV 的所有奇怪而奇妙的可能滥用.

If you want to deal with CSV that has things like quoted empty strings for null integers, you'll need to feed it to PostgreSQL via a pre-processor that can neaten it up a bit. PostgreSQL's CSV input doesn't understand all the weird and wonderful possible abuses of CSV.

选项包括:

• 将其加载到电子表格中并导出 CSV；
• 使用 Python csv 模块、Perl Text::CSV 等对其进行预处理；
• 使用 Perl/Python/whatever 加载 CSV 并将其直接插入数据库
• 使用 CloverETL、Talend Studio 或 Pentaho Kettle 等 ETL 工具

• Loading it in a spreadsheet and exporting sane CSV;
• Using the Python csv module, Perl Text::CSV, etc to pre-process it;
• Using Perl/Python/whatever to load the CSV and insert it directly into the DB
• Using an ETL tool like CloverETL, Talend Studio, or Pentaho Kettle

这篇关于PG COPY 错误:整数的输入语法无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！