CODEWITHSUNDEEP
postgresqlcsv
Hristian Yordanov
Hristian Yordanov

Reputation: 668

How to auto populate data from CSV files to PostgreSQL?

i`m using this SQL query to populate a database:

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

COPY tmp_x FROM 'D:\water_utility.csv' (header, FORMAT CSV, ENCODING 'WIN1251');

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

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

Can you help me by giving advice how to get all files from folder named like this and populate the table with data:

sample_1.csv sample_2.csv ... sample_12.csv

The numbers are the months that are created the files. How to create auto populate script?

Upvotes: 1

Views: 600

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51406

try smth like:

do
$$
begin
 execute format ($f$COPY tmp_x FROM 'D:\water_utility%s.csv' (header, FORMAT CSV, ENCODING 'WIN1251');$f$,extract(month from now()));
end;
$$
;

instead of your copy line.

update it is often called dynamic sql - I format the statement before calling it. here I append the current month number to the name of the file, so the line execute would be:

COPY tmp_x FROM 'D:\water_utility8.csv' (header, FORMAT CSV, ENCODING 'WIN1251');

because August is 8th month (which I get with extract(month from now()))

Upvotes: 1

Related Questions