Postgresql COPY CSV ERROR: extra data after last expected column

csv, database, postgresql

Solution

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 ?)

Problem

I'm trying to import the data from http://www.unitedstateszipcodes.org/zip-code-database. A subset of the data looks like this: ``` "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: ``` 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?

Original source