PostgreSQL insert multiple lines from csv

Question

When I read my CSV file into a temporary table and then try to write this data to my table, only the last row is written, and all the rest are skipped. How can I write all the data (can I queue them up?). I can’t figure out how to do this.

Sorry for the big code, I have a lot of checks

CREATE OR REPLACE FUNCTION test2(teach integer)
RETURNS void
LANGUAGE 'plpgsql'

AS $BODY$
BEGIN

DROP TABLE  IF EXISTS temp_table;
CREATE TEMP TABLE IF NOT EXISTS temp_table (
num varchar,
let varchar,
dis varchar,
last_n varchar,
oc varchar,
dt date
) on commit drop;

COPY temp_table(num,let,dis,last_n,oc,dt) 
FROM 'D:\bd2.csv' DELIMITER ',' CSV HEADER encoding 'windows-1251';

insert into sch.ocenka (id_pupil,ocenk,id_discteacher,date)
select 
sch.pupil.id_pupil,temp_table.oc,sch.discipline_teacher.id_discteacher,temp_table.dt 
from sch.discipline_teacher,sch.discipline,sch.teacher,sch.class,sch.pupil,temp_table 
where  sch.discipline_teacher.id_class = sch.class.id_class and sch.pupil.id_class = 
sch.class.id_class and 
sch.discipline_teacher.id_discipline = sch.discipline.id_discipline and 
sch.discipline_teacher.id_teacher = sch.teacher.id_teacher and

EXISTS (select * from sch.discipline_teacher 
where sch.discipline_teacher.id_class = sch.class.id_class and
sch.discipline_teacher.id_discipline = sch.discipline.id_discipline and 
sch.discipline_teacher.id_teacher = sch.teacher.id_teacher and sch.discipline.title 
=temp_table.dis and sch.teacher.phone=teach and sch.class.title =temp_table.num and 
sch.class.kurs =temp_table.let) 
and sch.discipline.title =temp_table.dis and sch.teacher.phone=teach and 
sch.class.title =temp_table.num and sch.class.kurs =temp_table.let and 
sch.pupil.last_name =temp_table.last_n;

DROP TABLE  IF EXISTS temp_table;
END;
$BODY$;

This may help you to resolve your issue stackoverflow.com/questions/2987433/… — zealous
– zealous, Commented Apr 10, 2020 at 23:21
Does temp table contains all needed rows? Does select used to insert rows returns all needed data? You provides the final result but not concern on intermediate stages. — Abelisto
– Abelisto, Commented Apr 10, 2020 at 23:24
@Abelisto Just checked. Temporary table returns all data that was loaded from file. But the select does not return all the necessary data — JuniorLittle
– JuniorLittle, Commented Apr 10, 2020 at 23:36
Start for example from select * from temp_table join sch.discipline on (sch.discipline.title = temp_table.dis) then add tables/conditions to the query one by one and see which one caused the problem. It is called "debugging" :) — Abelisto
– Abelisto, Commented Apr 10, 2020 at 23:54

wildplasser · Accepted Answer · 2020-04-11 09:11:08Z

1

I tried to rewrite the query:

insert into sch.ocenka (id_pupil,ocenk,id_discteacher,date)
select pu.id_pupil
        , temp_table.oc
        , dt.id_discteacher
        , temp_table.dt
from sch.discipline_teacher dt
JOIN sch.discipline dc
        ON dt.id_discipline = dc.id_discipline
JOIN sch.teacher tr 
        ON dt.id_teacher = tr.id_teacher
JOIN sch.class cl 
        ON dt.id_class = cl.id_class
JOIN sch.pupil pu 
        ON pu.id_class = cl.id_class
JOIN temp_table tt 
        ON pu.last_name = tt.last_n
        and dc.title = tt.dis
        and cl.title = tt.num
        and cl.kurs = tt.let
where 1=1  
and EXISTS (
        select * from sch.discipline_teacher x
        where x.id_class = cl.id_class 
        and x.id_discipline = dc.id_discipline
        and x.id_teacher = tr.id_teacher
        and dc.title = tt.dis   -- <<-- Huh?
        and tr.phone=teach 
        and cl.title = tt.num   -- <<-- Huh?
        and cl.kurs = tt.let
        )
and tr.phone = teach -- <<-- Argument
        ;

answered Apr 11, 2020 at 9:11

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

JuniorLittle Over a year ago

Hi, everything works, thank you. But why is the first line of the file ignored? I will give links to screenshots: File contents: link In request: link Look at the dates (not to get confused)

wildplasser Over a year ago

Maybe because the HEADER option suppresses the first line?

JuniorLittle Over a year ago

Checked the temporary table. All 5 lines that were in the file are filled. But in request gives 4

JuniorLittle Over a year ago

Sorry, this is my mistake. I had a mistake in one word in the file. Thank you very much for helping.

JuniorLittle Over a year ago

You do not know how to pass the path to the file name through the function argument? CREATE OR REPLACE FUNCTION sch.test11(teach integer, file_n text) COPY temp_table(num,let,dis,last_n,oc,dt) FROM 'file_n' DELIMITER ',' CSV HEADER encoding 'windows-1251'; And I call this function like that select * from sch.test11(064424422,'D:\bd2.csv') But I have a error.

|

Collectives™ on Stack Overflow

PostgreSQL insert multiple lines from csv

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related