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

运行 COPY会导致 ERROR: invalid input syntax for integer: ""错误消息。我遗漏了什么？

我的 /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
244250 次浏览
小开
最佳答案

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

""不是有效的整数。PostgreSQL 在 CSV 中默认接受 未被引用空字段为 null，但是 ""就像写:

SELECT ''::integer;

因为同样的原因而失败。

如果您想要处理 CSV，比如空整数的引号空字符串，那么您需要通过预处理程序将其提供给 PostgreSQL，这样可以稍微整理一下。PostgreSQL 的 CSV 输入不能理解 CSV 可能存在的所有怪异和奇妙的弊端。

备选方案包括:

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

我认为最好改变你的 csv 文件，比如:

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

还可以像下面这样定义表

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

复制后，可以转换空字符串:

select nullif(age, '')::int as age, first_name, last_name
from people
小开

最后使用 csvfix来完成这项工作:

csvfix map -fv '' -tv '0' /tmp/people.csv > /tmp/people_fixed.csv

为了确定哪些列应该是 integer或 float，可以只指定它们:

csvfix map -f 1 -fv '' -tv '0' /tmp/people.csv > /tmp/people_fixed.csv

如果不指定确切的列，则可能会产生明显的副作用，空白字符串将转换为具有 0字符的字符串。

小开

我在使用 COPY语句的 postgres .sql文件中也有同样的错误，但是我的文件是 标签分隔而不是 逗号-分隔并引用。

我的错误在于我急切地从 github 复制/粘贴文件内容，但在这个过程中所有的制表符都被转换为空格，因此出现了错误。我必须下载并保存原始文件才能得到一个好的副本。

小开

这应该可以在不修改源 csv 文件的情况下工作:

alter table people alter column age type text;
copy people from '/tmp/people.csv' with csv;
小开

当加载’|’分隔的 CSV 文件时，我得到了这个错误，尽管在我的输入文件中没有’”’字符。结果我忘记指定 FORMAT:

用(格式 CSV，DELIMITER’|’)复制..。

小开

有一种方法可以解决“”，在整数列中引号为空的字符串, 使用 FORCE _ NULL 选项:

\copy table_name FROM 'file.csv' with (FORMAT CSV, FORCE_NULL(column_name));

参见 postgreql 文档 https://www.postgresql.org/docs/current/static/sql-copy.html

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

“ first _ name”，“ age”，“ last _ name” Ivan 23岁 Poupkine 尤金，皮罗戈夫

使用(分隔符’;’，null”)从 'file.csv'复制人;

select * from people;

就在第一栏。

小开

只是在寻找解决方案时偶然发现了这个问题，并且想要添加，我可以通过在 copy _ from 调用中添加“ null”参数来解决这个问题:

cur.copy_from(f, tablename, sep=',', null='')
小开

使用下面的命令在一行中从 CSV 复制数据，而不需要强制转换和更改数据类型。 请将“ NULL”替换为在复制数据中创建错误的字符串

copy table_name from 'path to csv file' (format csv, null "NULL", DELIMITER ',', HEADER);
小开

令人难以置信的是，我对同一错误的解决方案是重新排列列。对于任何其他人做上述解决方案，仍然没有得到过去的错误。

显然，我必须将 CSV 文件中的列排列成与 PGADmin 中的表清单中的相同序列相匹配。

小开

在 python 中(使用 psycopg2) ，首先创建空表，然后使用 copy_expert将 csv 加载到空表中。它应该处理空值。

import psycopg2
conn = psycopg2.connect(host="hosturl", database="db_name", user="username", password="password")
cur = conn.cursor()
cur.execute("CREATE TABLE schema.destination_table ("
"age integer, "
"first_name varchar(20), "
"last_name varchar(20)"
");")


with open(r'C:/tmp/people.csv', 'r') as f:
next(f)  # Skip the header row. Or remove this line if csv has no header.
conn.cursor.copy_expert("""COPY schema.destination_table FROM STDIN WITH (FORMAT CSV)""", f)

提交答案