postgresql PG COPY 错误：导入没有任何整数的带引号的 CSV 文件时，“整数的输入语法无效”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36368496/
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' when importing quoted CSV file without any integers
提问by Basil Bourque
When trying to use the COPYcommand via SQL in Postgres 9.5.1 in a simple example database…
COPY在一个简单的示例数据库中尝试通过 Postgres 9.5.1 中的 SQL使用该命令时......
I am getting this error:
我收到此错误：
ERROR: invalid input syntax for integer: "Sally"
CONTEXT: COPY customer_, line 2, column id_: "Sally"
********** Error **********
ERROR: invalid input syntax for integer: "Sally"
SQL state: 22P02
Context: COPY customer_, line 2, column id_: "Sally"
…when importing this data in CSV (comma-separated value):
...当以 CSV 格式（逗号分隔值）导入此数据时：
"first_name_","last_name_","phone_","email_"
"Sally","Jones","425.555.1324","[email protected]"
"Jarrod","Barkley","206.555.3454","[email protected]"
"Wendy","Melvin","415.555.2343","[email protected]"
"Lisa","Coleman","425.555.7282","[email protected]"
"Jesse","Johnson","507.555.7865","[email protected]"
"Jean-Luc","Martin","212.555.2244","[email protected]"
…being imported via the following SQL executed in pgAdmin:
...通过在 pgAdmin 中执行的以下 SQL 导入：
COPY customer_
FROM '/home/parallels/Downloads/customer_.csv'
CSV
HEADER
;
…into this table:
...进入这张表：
-- Table: public.customer_
-- DROP TABLE public.customer_;
CREATE TABLE public.customer_
(
id_ integer NOT NULL DEFAULT nextval('customer__id__seq'::regclass),
first_name_ text NOT NULL,
last_name_ text NOT NULL,
phone_ text NOT NULL DEFAULT ''::text,
email_ text NOT NULL DEFAULT ''::text,
CONSTRAINT pkey_customer_ PRIMARY KEY (id_)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.customer_
OWNER TO postgres;
COMMENT ON TABLE public.customer_
IS 'Represents a person whose pets visit our clinic.';
So it seems the first row containing the names of the columns is being processed successfully. The failure point is with the first data value in the first data line of the CSV. None of my imported data is of integer type, so the I am befuddled by the error message. The only integer is the id_primary key, auto-incrementing SERIAL.
因此，似乎正在成功处理包含列名称的第一行。故障点是 CSV 的第一个数据行中的第一个数据值。我导入的数据都不是整数类型，所以我对错误消息感到困惑。唯一的整数是id_主键，自动递增SERIAL。
I did read the Question page on PG COPY error: invalid input syntax for integer. But that question did involve integer values, and the lack thereof in an empty quoted string being interpreted as a NULL. In my case here we have no integer values in the data; the only integer is the primary key SERIALcolumn with a DEFAULTgenerated value (not in the data being imported).
我确实阅读了关于PG COPY error: invalid input syntax for integer的问题页面。但是这个问题确实涉及整数值，并且在被解释为 NULL 的空引用字符串中缺少整数值。就我而言，这里的数据中没有整数值；唯一的整数是SERIAL具有DEFAULT生成值的主键列（不在导入的数据中）。
I also found the Question, PostgreSQL ERROR: invalid input syntax for integer. But it seems irrelevant.
我还发现了问题，PostgreSQL ERROR: invalid input syntax for integer。但这似乎无关紧要。
回答by Gordon Linoff
Try specifying the columns . . . without the primary key:
尝试指定列。. . 没有主键：
COPY customer_ (first_name_ text, last_name_ text, phone_ text, email_ text)
FROM '/home/parallels/Downloads/customer_.csv'
CSV
HEADER
;
Without the column list, it is looking for a value for id_.
在没有列列表的情况下，它正在寻找 的值id_。
The import data file's first row of column names are notused for mapping to the table columns. The HEADER flag merely tells Postgres to skip over that first line, as documented:
导入数据文件的第一行列名不用于映射到表列。HEADER 标志只是告诉 Postgres 跳过第一行，如文档所示：
HEADER
Specifies that… on input, the first line is ignored. …
标题
指定...在输入时，第一行被忽略。…
回答by Amar
COPY table_name FROM 'C:\path\file.csv' DELIMITERS ',' CSV header;
