postgresql PG COPY 错误：整数的无效输入语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18297980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PG COPY error: invalid input syntax for integer
提问by gmile
Running COPYresults in ERROR: invalid input syntax for integer: ""error message for me. What am I missing?
运行COPY结果ERROR: invalid input syntax for integer: ""对我来说是错误消息。我错过了什么？
My /tmp/people.csvfile:
我的/tmp/people.csv文件：
"age","first_name","last_name"
"23","Ivan","Poupkine"
"","Eugene","Pirogov"
My /tmp/csv_test.sqlfile:
我的/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;
Output:
输出：
$ 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
Trivia:
琐事：
- PostgreSQL 9.2.4
- PostgreSQL 9.2.4
采纳答案by Craig Ringer
ERROR: invalid input syntax for integer: ""
错误：整数的无效输入语法：“”
""isn't a valid integer. PostgreSQL accepts unquotedblank fields as null by default in CSV, but ""would be like writing:
""不是有效整数。默认情况下，PostgreSQL在 CSV 中接受未加引号的空白字段为 null，但""会像这样写：
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.
如果要处理包含空整数引用空字符串之类的 CSV，则需要通过预处理器将其提供给 PostgreSQL，该预处理器可以稍微整理一下。PostgreSQL 的 CSV 输入不了解 CSV 的所有奇怪和美妙的可能滥用。
Options include:
选项包括：
- Loading it in a spreadsheet and exporting sane CSV;
- Using the Python
csvmodule, 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
- 将其加载到电子表格中并导出正常的 CSV；
- 使用 Python
csv模块、 PerlText::CSV等对其进行预处理； - 使用 Perl/Python/whatever 加载 CSV 并将其直接插入到数据库中
- 使用 CloverETL、Talend Studio 或 Pentaho Kettle 等 ETL 工具
回答by Roman Pekar
I think it's better to change your csv file like:
我认为最好更改您的 csv 文件，例如：
"age","first_name","last_name"
23,Ivan,Poupkine
,Eugene,Pirogov
It's also possible to define your table like
也可以像这样定义你的表
CREATE TABLE people (
age varchar(20),
first_name varchar(20),
last_name varchar(20)
);
and after copy, you can convert empty strings:
复制后，您可以转换空字符串：
select nullif(age, '')::int as age, first_name, last_name
from people
回答by Slobodan Savkovic
I got this error when loading '|' separated CSV file although there were no '"' characters in my input file. It turned out that I forgot to specify FORMAT:
加载“|”时出现此错误 尽管我的输入文件中没有 '"' 字符，但我将 CSV 文件分开。结果是我忘记指定 FORMAT：
COPY ... FROM ... WITH (FORMAT CSV, DELIMITER '|').
COPY ... FROM ... WITH ( FORMAT CSV, DELIMITER '|').
回答by zwippie
I had this same error on a postgres .sqlfile with a COPYstatement, but my file was tab-separatedinstead of comma-separated and quoted.
我在.sql带有COPY语句的 postgres文件上遇到了同样的错误，但我的文件是制表符分隔而不是逗号分隔和引号。
My mistake was that I eagerly copy/pasted the file contents from github, but in that process all the tabs were converted to spaces, hence the error. I had to download and save the raw file to get a good copy.
我的错误是我急切地从 github 复制/粘贴文件内容，但在此过程中所有选项卡都转换为空格，因此出现错误。我必须下载并保存原始文件才能得到一个好的副本。
回答by soyayix
this ought to work without you modifying the source csv file:
这应该可以在不修改源 csv 文件的情况下工作：
alter table people alter column age type text;
copy people from '/tmp/people.csv' with csv;
回答by u5827450
There is a way to solve "", the quoted null string as null in integer column, use FORCE_NULL option :
有一种方法可以解决“”，引用的空字符串在整数列中为空，使用 FORCE_NULL 选项：
\copy table_name FROM 'file.csv' with (FORMAT CSV, FORCE_NULL(column_name));
see postgresql document, https://www.postgresql.org/docs/current/static/sql-copy.html
参见 postgresql 文档， https://www.postgresql.org/docs/current/static/sql-copy.html
回答by gmile
Ended up doing this using csvfix:
最终使用csvfix：
csvfix map -fv '' -tv '0' /tmp/people.csv > /tmp/people_fixed.csv
In case you know for sure which columns were meant to be integeror float, you can specify just them:
如果您确定哪些列是integer或float，您可以仅指定它们：
csvfix map -f 1 -fv '' -tv '0' /tmp/people.csv > /tmp/people_fixed.csv
Without specifying the exact columns, one may experience an obvious side-effect, where a blank string will be turned into a string with a 0character.
如果不指定确切的列，可能会遇到明显的副作用，即空白字符串将变成带有0字符的字符串。
回答by helderreis
Just came across this while looking for a solution and wanted to add I was able to solve the issue by adding the "null" parameter to the copy_from call:
刚刚在寻找解决方案时遇到了这个问题，并想添加我能够通过将“null”参数添加到 copy_from 调用来解决该问题：
cur.copy_from(f, tablename, sep=',', null='')
回答by Anil
Use the below command to copy data from CSV in a single line without casting and changing your datatype. Please replace "NULL" by your string which creating error in copy data
使用以下命令在一行中从 CSV 复制数据，而无需强制转换和更改数据类型。请用您的字符串替换“NULL”，这会在复制数据中产生错误
copy table_name from 'path to csv file' (format csv, null "NULL", DELIMITER ',', HEADER);
回答by WSchnuble
CREATE TABLE people (
first_name varchar(20),
age integer,
last_name varchar(20)
);
"first_name","age","last_name" Ivan,23,Poupkine Eugene,,Pirogov
"first_name","age","last_name" Ivan,23,Poupkine Eugene,,Pirogov
copy people from 'file.csv'with (delimiter ';', null '');
从'file.csv'with (delimiter ';', null '');复制人
select * from people;
Just in first column.....
就在第一列.....
