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 中默认接受 未加引号的空白字段为 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
模块、PerlText::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, PerlText::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屋!