pgadmin 4，导入csv，错误码1，无错误提示

Question

我正在尝试导入csv到pgadmin4.

我一直得到错误代码1，但没有详细信息。

命令日志:

2021-02-19 01:47:08.718 UTC [88] STATEMENT:  COPY  public.flights ( year, quarter, month, dayofmonth, dayofweek, flightdate, reporting_airline, dot_id_reporting_airline, iata_code_reporting_airline, tail_number, flight_number_reporting_airline, originairportid, originairportseqid, origincitymarketid, origin, origincityname, originstate, originstatefips, originstatename, originwac, destairportid, destairportseqid, destcitymarketid, dest, destcityname, deststate, deststatefips, deststatename, destwac, crsdeptime, deptime, depdelay, depdelayminutes, depdel15, departuredelaygroups, deptimeblk, taxiout, wheelsoff, wheelson, taxiin, crsarrtime, arrtime, arrdelay, arrdelayminutes, arrdel15, arrivaldelaygroups, arrtimeblk, cancelled, cancellationcode, diverted, crselapsedtime, actualelapsedtime, airtime, flights, distance, distancegroup, carrierdelay, weatherdelay, nasdelay, securitydelay, lateaircraftdelay, firstdeptime, totaladdgtime, longestaddgtime, divairportlandings, divreacheddest, divactualelapsedtime, divarrdelay, divdistance, div1airport, div1airportid, div1airportseqid, div1wheelson, div1totalgtime, div1longestgtime, div1wheelsoff, div1tailnum, div2airport, div2airportid, div2airportseqid, div2wheelson, div2totalgtime, div2longestgtime, div2wheelsoff, div2tailnum, div3airport, div3airportid, div3airportseqid, div3wheelson, div3totalgtime, div3longestgtime, div3wheelsoff, div3tailnum, div4airport, div4airportid, div4airportseqid, div4wheelson, div4totalgtime, div4longestgtime, div4wheelsoff, div4tailnum, div5airport, div5airportid, div5airportseqid, div5wheelson, div5totalgtime, div5longestgtime, div5wheelsoff, div5tailnum ) FROM STDIN  DELIMITER ',' CSV   HEADER  ENCODING 'UTF8' QUOTE '"' ESCAPE '''';

我已经尝试了各种(虽然我理解他们，认为我有正确的组合):

<<ul>

编码/gh>

header/no header

不同的引号/转义组合

我从列列表中删除id字段，因为它不在csv

下载的数据是一个大样本，所以我可以想象我需要调整一些东西，但是我不知道什么时候在错误消息中没有给出任何细节。

每个字段都允许为空，除了id。

创建语句

CREATE TABLE FLIGHTS(
ID INT PRIMARY KEY NOT NULL,
Year INT,
Quarter INT,
Month INT,
DayofMonth INT,
DayOfWeek INT,
FlightDate CHAR(50),
Reporting_Airline CHAR(50),
DOT_ID_Reporting_Airline CHAR(50),
IATA_CODE_Reporting_Airline CHAR(50),
Tail_Number CHAR(50),
Flight_Number_Reporting_Airline CHAR(50),
OriginAirportID CHAR(50),
OriginAirportSeqID CHAR(50),
OriginCityMarketID CHAR(50),
Origin CHAR(50),
OriginCityName CHAR(50),
OriginState CHAR(50),
OriginStateFips CHAR(50),
OriginStateName CHAR(50),
OriginWac CHAR(50),
DestAirportID CHAR(50),
DestAirportSeqID CHAR(50),
DestCityMarketID CHAR(50),
Dest CHAR(50),
DestCityName CHAR(50),
DestState CHAR(50),
DestStateFips CHAR(50),
DestStateName CHAR(50),
DestWac CHAR(50),
CRSDepTime CHAR(50),
DepTime CHAR(50),
DepDelay CHAR(50),
DepDelayMinutes CHAR(50),
DepDel15 CHAR(50),
DepartureDelayGroups CHAR(50),
DepTimeBlk CHAR(50),
TaxiOut CHAR(50),
WheelsOff CHAR(50),
WheelsOn CHAR(50),
TaxiIn INT,
CRSArrTime INT,
ArrTime INT,
ArrDelay INT,
ArrDelayMinutes INT,
ArrDel15 INT,
ArrivalDelayGroups INT,
ArrTimeBlk INT,
Cancelled INT,
CancellationCode CHAR(50),
Diverted INT,
CRSElapsedTime INT,
ActualElapsedTime INT,
AirTime INT,
Flights INT,
Distance INT,
DistanceGroup INT,
CarrierDelay INT,
WeatherDelay INT,
NASDelay INT,
SecurityDelay INT,
LateAircraftDelay INT,
FirstDepTime CHAR(50),
TotalAddGTime CHAR(50),
LongestAddGTime CHAR(50),
DivAirportLandings INT,
DivReachedDest INT,
DivActualElapsedTime INT,
DivArrDelay INT,
DivDistance INT,
Div1Airport CHAR(50),
Div1AirportID CHAR(50),
Div1AirportSeqID CHAR(50),
Div1WheelsOn CHAR(50),
Div1TotalGTime CHAR(50),
Div1LongestGTime CHAR(50),
Div1WheelsOff CHAR(50),
Div1TailNum CHAR(50),
Div2Airport CHAR(50),
Div2AirportID CHAR(50),
Div2AirportSeqID CHAR(50),
Div2WheelsOn CHAR(50),
Div2TotalGTime CHAR(50),
Div2LongestGTime CHAR(50),
Div2WheelsOff CHAR(50),
Div2TailNum CHAR(50),
Div3Airport CHAR(50),
Div3AirportID CHAR(50),
Div3AirportSeqID CHAR(50),
Div3WheelsOn CHAR(50),
Div3TotalGTime CHAR(50),
Div3LongestGTime CHAR(50),
Div3WheelsOff CHAR(50),
Div3TailNum CHAR(50),
Div4Airport CHAR(50),
Div4AirportID CHAR(50),
Div4AirportSeqID CHAR(50),
Div4WheelsOn CHAR(50),
Div4TotalGTime CHAR(50),
Div4LongestGTime CHAR(50),
Div4WheelsOff CHAR(50),
Div4TailNum CHAR(50),
Div5Airport CHAR(50),
Div5AirportID CHAR(50),
Div5AirportSeqID CHAR(50),
Div5WheelsOn CHAR(50),
Div5TotalGTime CHAR(50),
Div5LongestGTime CHAR(50),
Div5WheelsOff CHAR(50),
Div5TailNum CHAR(50));

样本数据:

"Year","Quarter","Month","DayofMonth","DayOfWeek","FlightDate","Reporting_Airline","DOT_ID_Reporting_Airline","IATA_CODE_Reporting_Airline","Tail_Number","Flight_Number_Reporting_Airline","OriginAirportID","OriginAirportSeqID","OriginCityMarketID","Origin","OriginCityName","OriginState","OriginStateFips","OriginStateName","OriginWac","DestAirportID","DestAirportSeqID","DestCityMarketID","Dest","DestCityName","DestState","DestStateFips","DestStateName","DestWac","CRSDepTime","DepTime","DepDelay","DepDelayMinutes","DepDel15","DepartureDelayGroups","DepTimeBlk","TaxiOut","WheelsOff","WheelsOn","TaxiIn","CRSArrTime","ArrTime","ArrDelay","ArrDelayMinutes","ArrDel15","ArrivalDelayGroups","ArrTimeBlk","Cancelled","CancellationCode","Diverted","CRSElapsedTime","ActualElapsedTime","AirTime","Flights","Distance","DistanceGroup","CarrierDelay","WeatherDelay","NASDelay","SecurityDelay","LateAircraftDelay","FirstDepTime","TotalAddGTime","LongestAddGTime","DivAirportLandings","DivReachedDest","DivActualElapsedTime","DivArrDelay","DivDistance","Div1Airport","Div1AirportID","Div1AirportSeqID","Div1WheelsOn","Div1TotalGTime","Div1LongestGTime","Div1WheelsOff","Div1TailNum","Div2Airport","Div2AirportID","Div2AirportSeqID","Div2WheelsOn","Div2TotalGTime","Div2LongestGTime","Div2WheelsOff","Div2TailNum","Div3Airport","Div3AirportID","Div3AirportSeqID","Div3WheelsOn","Div3TotalGTime","Div3LongestGTime","Div3WheelsOff","Div3TailNum","Div4Airport","Div4AirportID","Div4AirportSeqID","Div4WheelsOn","Div4TotalGTime","Div4LongestGTime","Div4WheelsOff","Div4TailNum","Div5Airport","Div5AirportID","Div5AirportSeqID","Div5WheelsOn","Div5TotalGTime","Div5LongestGTime","Div5WheelsOff","Div5TailNum",
2020,1,1,17,5,2020-01-17,"B6",20409,"B6","N661JB","942",14027,1402702,34027,"PBI","West Palm Beach/Palm Beach, FL","FL","12","Florida",33,10529,1052906,30529,"BDL","Hartford, CT","CT","09","Connecticut",11,"1940","1926",-14.00,0.00,0.00,-1,"1900-1959",15.00,"1941","2212",6.00,"2229","2218",-11.00,0.00,0.00,-1,"2200-2259",0.00,"",0.00,169.00,172.00,151.00,1.00,1133.00,5,,,,,,"",,,0,,,,,"",,,"",,,"","","",,,"",,,"","","",,,"",,,"","","",,,"",,,"","","",,,"",,,"","",
2020,1,1,18,6,2020-01-18,"B6",20409,"B6","N584JB","942",14027,1402702,34027,"PBI","West Palm Beach/Palm Beach, FL","FL","12","Florida",33,10529,1052906,30529,"BDL","Hartford, CT","CT","09","Connecticut",11,"1940","1926",-14.00,0.00,0.00,-1,"1900-1959",14.00,"1940","2207",13.00,"2229","2220",-9.00,0.00,0.00,-1,"2200-2259",0.00,"",0.00,169.00,174.00,147.00,1.00,1133.00,5,,,,,,"",,,0,,,,,"",,,"",,,"","","",,,"",,,"","","",,,"",,,"","","",,,"",,,"","","",,,"",,,"","",

Answer 1

不要使用pgAdmin来调试这个，因为它隐藏了错误消息。您需要点击"More Details…"然后通过拖动一个角来展开对话框，然后你会看到错误信息。

对于psql，我得到:

ERROR:  extra data after last expected column
CONTEXT:  COPY flights, line 2: "2020,1,1,17,5,2020-01-17,"B6",20409,"B6","N661JB","942",14027,1402702,34027,"PBI","West Palm Beach/P..."

确实，每行的末尾都有一个逗号。如果去掉逗号，就会得到不同的错误信息:

ERROR:  invalid input syntax for type integer: "6.00"
CONTEXT:  COPY flights, line 2, column taxiin: "6.00"

所以你看，错误信息是有用的。使用psql调试问题。一旦一切正常，您可以恢复到pgAdmin。