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 https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table — zealous, Apr 10 '20 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, Apr 10 '20 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, Apr 10 '20 at 23:36
@Abelisto You're right. Returns only the last row of the temporary table. Can you tell me how to make sure that all lines pass, not just the last? — JuniorLittle, Apr 10 '20 at 23:43
@Abelisto As far as I understand, I need to insert a certain number of rows, and I insert only one row. I still don't understand how to solve this problem — JuniorLittle, Apr 10 '20 at 23:52
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, Apr 10 '20 at 23:54

score 1 · Accepted Answer · answered Apr 11 '20 at 09:11

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 '20 at 09:11

wildplasser

43,142
8
66
109

Hi, everything works, thank you. But why is the first line of the file ignored? I will give links to screenshots: File contents: [link](https://ibb.co/ZKZq8ZJ) In request: [link](https://ibb.co/CtSmXCP) Look at the dates (not to get confused) – JuniorLittle Apr 11 '20 at 16:58
Maybe because the `HEADER` option suppresses the first line? – wildplasser Apr 11 '20 at 17:18
Checked the temporary table. All 5 lines that were in the file are filled. But in request gives 4 – JuniorLittle Apr 11 '20 at 17:29
Sorry, this is my mistake. I had a mistake in one word in the file. Thank you very much for helping. – JuniorLittle Apr 11 '20 at 17:45
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. – JuniorLittle Apr 11 '20 at 18:18
That is a different question. You could ask a new question. (including usable code, please!) – wildplasser Apr 11 '20 at 20:25
I have - https://stackoverflow.com/questions/61162852/postgresql-pass-file-name-as-argument – JuniorLittle Apr 11 '20 at 20:30

PostgreSQL insert multiple lines from csv

1 Answers1