postgresql – PG COPY错误:导入带有任何整数的引用CSV文件时,
在一个简单的示例数据库中尝试通过Postgres 9.5.1中的SQL使用
COPY 命令时…
我收到此错误: 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_,column id_: "Sally" …以CSV格式导入此数据时(逗号分隔值): "first_name_","last_name_","phone_","email_" "Sally","Jones","425.555.1324","s.jones@acme.com" "Jarrod","Barkley","206.555.3454","j.barkley@example.com" "Wendy","Melvin","415.555.2343","wendy@wendyandlisa.com" "Lisa","Coleman","425.555.7282","lisa@wendyandlisa.com" "Jesse","Johnson","507.555.7865","j.j@guitar.com" "Jean-Luc","Martin","212.555.2244","jean-luc.martin@example.com" …通过pgAdmin中执行的以下SQL导入: COPY customer_ FROM '/home/parallels/Downloads/customer_.csv' CSV HEADER ; …进入此表: -- 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.'; 因此,似乎包含列名称的第一行正在成功处理.失败点是CSV的第一个数据行中的第一个数据值.我导入的所有数据都不是整数类型,所以我对错误信息感到困惑.唯一的整数是id_主键,自动递增SERIAL. 我确实在PG COPY error: invalid input syntax for integer阅读了问题页面.但是这个问题确实涉及整数值,并且缺少空引用字符串被解释为NULL.就我而言,我们在数据中没有整数值;唯一的整数是具有DEFAULT生成值的主键SERIAL列(不在要导入的数据中). 我也找到了问题,PostgreSQL ERROR: invalid input syntax for integer.但似乎无关紧要.
尝试指定列. . .没有主键:
COPY customer_ (first_name_ text,last_name_ text,phone_ text,email_ text) FROM '/home/parallels/Downloads/customer_.csv' CSV HEADER ; 没有列列表,它正在寻找id_的值. 导入数据文件的第一行列名不用于映射到表列. HEADER标志只是告诉Postgres跳过第一行as documented:
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |