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
我的 /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,第3行,栏目年龄:
DROP TABLE
琐事:
- PostgreSQL 9.2.4
错误:整数的输入语法无效: 并且由于同样的原因而失败。 如果你想处理的CSV有null的整数的空字符串引用,你需要通过一个预处理器,它可以改进它一点,它提供给PostgreSQL。 PostgreSQL的CSV输入不能理解所有的奇怪和奇妙的可能滥用CSV。 选项包括: Running My My Output: Trivia:
ERROR: invalid input syntax for 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: 这篇关于PG COPY错误:整数的输入语法无效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
不是有效的整数。 PostgreSQL在CSV中默认接受无引号的空白字段为空,但就像写成:
SELECT'':: integer;
csv
模块,Perl Text :: CSV
等预处理它;
COPY
results in ERROR: invalid input syntax for integer: ""
error message for me. What am I missing?/tmp/people.csv
file:"age","first_name","last_name"
"23","Ivan","Poupkine"
"","Eugene","Pirogov"
/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;
$ 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
""
isn't a valid integer. PostgreSQL accepts unquoted blank fields as null by default in CSV, but ""
would be like writing:SELECT ''::integer;
csv
module, Perl Text::CSV
, etc to pre-process it;