postgresql - 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 '''';

我尝试了各种方法(虽然我理解它们并且认为我有正确的组合):

• 编码
• 标题/无标题
• 不同的引用/转义组合
• 我从列列表中删除了 id 字段，因为它不在 csv 中

下载的数据是一个很大的样本，所以我可以想象我需要调整一些东西，但是如果错误消息中没有提供任何详细信息，我不知道会发生什么。

除 id 外，每个字段都允许为 null。

在下面创建语句。

 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 来调试它，因为它会向您隐藏错误消息。您需要单击“更多详细信息...”，然后通过拖动一个角来展开对话框，然后您将看到错误消息。

使用 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。