postgresql - 导入 TXT 文件时在 Postgresql 中添加“自动递增主键”列

Question

我正在尝试在我的表中导入 .txt 文件。唯一的问题是它在导入时给了我一条错误消息

ERROR:  missing data for column "id"
CONTEXT:  COPY tmp_x, line 1: "15.06.18|1:00|11.6|11.6|10.8|96|11.0|9.7|SE|9.66|16.1|SE|10.6|11.8|10.8||753.9|0.20|0.0|0|0.00|0|0.0..."
SQL state: 22P04

SQL查询是这样的：

CREATE TEMP TABLE tmp_x AS SELECT * FROM weather LIMIT 0; -- but see below

COPY tmp_x FROM PROGRAM 'more +3 "D:\Users\h.yordanov\Desktop\downld08.txt"' (FORMAT csv, DELIMITER '|');


insert into weather select * from (
select x.* 
from tmp_x x
left outer join weather t on t. date1 = x. date1
where t. date1 is null
) as missing;

DROP TABLE tmp_x; -- else it is dropped at end of session automatically

.txt 文件中包含以下数据：

|Temp|Hi|Low|Out|Dew|Wind|Wind|Wind|Hi|Hi|Wind|Heat|THW|THSW|Rain|Solar|Solar|Hi|Solar|UV|UV|Hi|Heat|Cool|In|In|In|In|In|In|Air|Wind|Wind|ISS|Arc
|Date|Time|Out|Temp|Temp|Hum|Pt.|Speed|Dir|Run|Speed|Dir|Chill|Index|Index|Index|Bar|Rain|Rate|Rad.|Energy|Rad.|Index|Dose|UV|DD|DD|Temp|Hum|Dew|Heat|EMC|Density|ET|Samp|Tx|Recept|Int

15.06.18|1:00|11.6|11.6|10.8|96|11.0|9.7|SE|9.66|16.1|SE|10.6|11.8|10.8||753.9|0.20|0.0|0|0.00|0|0.0|0.00|0.0|0.280|0.000|13.1|52|3.4|12.1|9.84|1.2145|0.00|1393|1|100.0|60

和表：

CREATE TABLE weather
(
id SERIAL PRIMARY KEY,
Date1 date, 
Time1 time, 
"Temp Out" double precision,
"Hi Temp" double precision, 
"Low Temp" double precision, 
"Out Hum" double precision, 
"Dew Pt." double precision, 
"Wind Speed" double precision, 
"Wind Dir" character varying,
"Wind Run" double precision,
"Hi Speed" double precision,
"Hi Dir" character varying,
"Wind Chill" double precision,
"Heat Index" double precision,
"THW Index" double precision,
"THSW Index" double precision,
"Bar" double precision,
"Rain" double precision,
"Rain Rate" double precision,
"Solar Rad" integer,
"Solar Energy" double precision,
"Hi Solar Energy" double precision,
"UV Index" double precision,
"UV Dose" double precision,
"Hi UV" double precision,
"Heat DD" double precision,
"Cool DD" double precision,
"In temp" double precision,
"In Hum" double precision,
"In Dew" double precision,
"In Heat" double precision,
"In EMC" double precision,
"In Air Density" double precision,
"ET" double precision,
"Wind Stamp" double precision,
"Wind Tx" double precision,
"ISS Recept" double precision,
"Arc Int" double precision);

任何想法如何解决这个问题。也许问题出在临时表中？我已经在网上搜索过，但我没有看到任何关于这种情况的信息。

标签： postgresql