I have 2 files and want to import them to PostgreSql using pgAdmin4 so the only way I know is to create the tables and manually create the columns and every thing from the beginning, but this will take a lot of time hoping I didn't miss any thing, so any way to simply import them and PostgreSql consider the headers as the columns, creates the tables and saves me tons of time. I'm new to all of this so my question form maybe wrong but this is the best I can ask.
1 Answer
This is a approach, which I found here (from mmatt). Basically you call a function within Postgres (last argument specifies the number of columns).
CREATE OR REPLACE FUNCTION load_csv_file(
target_table text,
csv_path text,
col_count integer)
RETURNS void AS
$BODY$
declare
iter integer; -- dummy integer to iterate columns with
col text; -- variable to keep the column name at each iteration
col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet
begin
set schema 'public';
create table temp_table ();
-- add just enough number of columns
for iter in 1..col_count
loop
execute format('alter table temp_table add column col_%s text;', iter);
end loop;
-- copy the data from csv file
execute format('copy temp_table from %L with delimiter '','' quote ''"'' csv ', csv_path);
iter := 1;
col_first := (select col_1 from temp_table limit 1);
-- update the column names based on the first row which has the column names
for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
loop
execute format('alter table temp_table rename column col_%s to %s', iter, col);
iter := iter + 1;
end loop;
-- delete the columns row
execute format('delete from temp_table where %s = %L', col_first, col_first);
-- change the temp table name to the name given as parameter, if not blank
if length(target_table) > 0 then
execute format('alter table temp_table rename to %I', target_table);
end if;
end;
$BODY$
LANGUAGE plpgsql;
ALTER FUNCTION load_csv_file(text, text, integer)
OWNER TO postgres;
After use this function to create table
select load_csv_file('myTable','C:/MyPath/MyFile.csv',24)
consider the headers as the columns , creates the tables and saves me tons of time .<<-- which is nonsensical. Understanding the table's meaning, and using the tables in a correct way costs more time.