postgres

#
# Dumping db (local or remote into file (schema only))
#
pg_dump --host [HOST] --port [PORT] --username "[USERNAME]" --schema-only --file "e:\[DB_NAME].schema" "[DB_NAME]"\


#
#killing process
#
select * from pg_stat_activity;
select pg_cancel_backend([PID]);


#
#dumping query data to csv file
#
copy (
[query]
) To '/path/to/file/file.csv' with csv delimiter ',' header;


#
# loading data from CSV with temporary table in the middle to be able to choose columns
#
TRUNCATE temp;
DROP TABLE temp;

CREATE temporary table temp (
  h_id bigint NOT NULL,
  p_id integer NOT NULL,
  pc_id1 smallint NOT NULL,
  pc_name1 varchar(3),
  pc_id2 smallint NOT NULL,
  pc_name2 varchar(3),
  bc_id1 smallint NOT NULL,
  bc_name1 varchar(3),
  bc_id2 smallint NOT NULL,
  bc_name2 varchar(3),
  bc_id3 smallint NOT NULL,
  bc_name3 varchar(3),
  bc_id4 smallint NOT NULL,
  bc_name4 varchar(3),
  bc_id5 smallint NOT NULL,
  bc_name5 varchar(3),
  winphase1 numeric(17,14) NOT NULL,
  tiephase1 numeric(17,14) NOT NULL,
  winphase2 numeric(17,14) NOT NULL,
  tiephase2 numeric(17,14) NOT NULL
);

COPY temp 
FROM '/data/query_res/kalk_csv/SPIN100_4.csv' 
WITH csv delimiter ',' header;

INSERT INTO hands_players_ev 
SELECT p_id, h_id, pc_id1, pc_id2, bc_id1, bc_id2, bc_id3, bc_id4, bc_id5, winphase1, tiephase1, winphase2, tiephase2
FROM temp;


#
# load data from csv with automatically generated table
#
pip install csvkit
csvsql --db postgresql://[user]:[hasło]@localhost:[port]/[baza] --insert --encoding [kodowanie] --delimiter \[separator] --quotechar \[quoting_char] --blanks --no-constraints --no-inference [plik.csv]