PG COPY错误:整数的输入语法无效 [英] PG COPY error: invalid input syntax for integer
问题描述
正在运行 COPY
会导致 ERROR:无效的整数输入语法:
错误消息。我缺少什么?
我的 /tmp/people.csv
档案:
age,first_name,last_name
23,Ivan,Poupkine
Eugene,Pirogov
My /tmp/csv_test.sql
档案:
CREATE TABLE people(
age integer,
first_name varchar ),
last_name varchar(20)
);
COPY people
FROM'/tmp/people.csv'
WITH(
FORMAT CSV,
HEADER true,
NULL'
);
DROP TABLE people;
输出:
code> $ psql postgres -f sql_test.sql
CREATE TABLE
psql:sql_test.sql:13:ERROR:整数输入语法无效:
CONTEXT:COPY people, line 3,column age:
DROP TABLE
琐事:
- PostgreSQL 9.2.4
错误:整数的输入语法无效:
不是有效的整数。 PostgreSQL在CSV中默认接受无引号的空白字段为空,但就像写成:
SELECT'':: integer;
并且由于相同的原因而失败。
如果你想处理的CSV有空的整数的空字符串的引用,你需要通过一个预处理器,它可以把它一点提供给PostgreSQL。 PostgreSQL的CSV输入不能理解所有的奇怪和奇妙的可能滥用CSV。
选项包括:
- 将它载入电子表格并导出完整的CSV文件;
- 使用Python
csv
模块,PerlText :: CSV
等预处理它; - 使用Perl / Python /任何加载CSV并将其直接插入DB
- 使用ETL工具(例如CloverETL,Talend Studio或Pentaho Kettle)
Running COPY
results in ERROR: invalid input syntax for integer: ""
error message for me. What am I missing?
My /tmp/people.csv
file:
"age","first_name","last_name"
"23","Ivan","Poupkine"
"","Eugene","Pirogov"
My /tmp/csv_test.sql
file:
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;
Output:
$ psql postgres -f 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
Trivia:
- PostgreSQL 9.2.4
ERROR: invalid input syntax for integer: ""
""
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.
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.
Options include:
- 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屋!