0

I'm trying to import .txt file in my table. The only problem is that its gives me an error message when importing

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

The SQL query is this:

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

The .txt file have this data inside:

|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

And the table:

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

Any idea how to fix this issue. Maybe the problem is in the temporary table? I have searched the web but i don't see anything with this case.

Hristian Yordanov
  • 650
  • 1
  • 6
  • 25
  • 1
    I believe the duplicate link will let you answer your question. Basically, you may define a temp table containing only those columns in the CSV input. Then, copy from that temp table into the actual target table. – Tim Biegeleisen Jun 28 '18 at 07:14
  • The linked question seems to be the inverse of this question (copy only some columns of the CSV file into the table vs. copy the CSV file data into a subset of the columns of the table). – melpomene Jun 28 '18 at 07:17
  • @Tim Biegeleisen thank you. That was my case :) All works now! – Hristian Yordanov Jun 28 '18 at 07:41

0 Answers0