CODEWITHSUNDEEP
sqldatabasepostgresql
JuniorLittle
JuniorLittle

Reputation: 161

PostgreSQL insert multiple lines from csv

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

Upvotes: 1

Views: 899

Answers (1)

wildplasser
wildplasser

Reputation: 44240

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
        ;

Upvotes: 1

Related Questions