Postgresql COPY CSV 错误：最后一个预期列之后的额外数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20301646/
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
Postgresql COPY CSV ERROR: extra data after last expected column
提问by Devin Dixon
I'm trying to import the data from http://www.unitedstateszipcodes.org/zip-code-database. A subset of the data looks like this:
我正在尝试从http://www.unitedstateszipcodes.org/zip-code-database导入数据。数据的子集如下所示：
"zip","type","primary_city","acceptable_cities","unacceptable_cities","state","county","timezone","area_codes","latitude","longitude","world_reg$
"00501","UNIQUE","Holtsville",,"I R S Service Center","NY","Suffolk County","America/New_York","631","40.81","-73.04","NA","US","0","384",
"00544","UNIQUE","Holtsville",,"Irs Service Center","NY","Suffolk County","America/New_York","631","40.81","-73.04","NA","US","0","0"
The postgresql command I running is this:
我运行的 postgresql 命令是这样的：
copy development.zip_codes FROM '/tmp/zip_code_database.csv' WITH DELIMITER ',' CSV HEADER;
And the result is this:
结果是这样的：
ERROR: extra data after last expected column
SQL state: 22P04
Context: COPY zip_codes, line 2: ""00501","UNIQUE","Holtsville",,"I R S Service Center","NY","Suffolk County","America/New_York","631"..."
What am I doing wrong with the import?
我在导入时做错了什么？
采纳答案by wildplasser
Works like a charm, here ...
就像一个魅力，在这里......
DROP TABLE zipcodes CASCADE;
CREATE TABLE zipcodes
( id serial NOT NULL PRIMARY KEY
, zzip varchar NOT NULL UNIQUE
, ztype varchar
, primary_city varchar
, acceptable_cities varchar
, unacceptable_cities varchar
, state varchar
, county varchar
, ztimezone varchar
, area_codes varchar
, latitude varchar
, longitude varchar
, world_region varchar
, country varchar
, decommissioned varchar
, estimated_population varchar
, notes varchar
);
COPY zipcodes (zzip,ztype,primary_city
, acceptable_cities,unacceptable_cities
, state,county,ztimezone,area_codes
, latitude,longitude,world_region,country
, decommissioned,estimated_population,notes )
FROM '/tmp/zip_code_database.csv'
WITH CSV HEADER delimiter ','
;
Result:
结果：
DROP TABLE
CREATE TABLE
COPY 42522
(maybe the OP has CR/CRLF problems ?)
（也许 OP 有 CR/CRLF 问题？）
