Load to SQL and save back to CSV

There are many options to load CSV into PostgreSQL without knowledge of the table structure, or using script-generator.

LOAD

Load the CSV

Using CSVkit

With previous CSVkit installation you can use

wget -c https://raw.githubusercontent.com/datasets-br/state-codes/master/data/br-state-codes.csv
csvsql --db postgresql:///database --insert br-state-codes.csv

Using pack2sql to FOREIGN TABLE

See code generator as pack2sql, it generates the sh and SQL scripts, something as

wget -O /tmp/br_state_codes.csv -c https://raw.githubusercontent.com/datasets-br/state-codes/master/data/br-state-codes.csv

          CREATE EXTENSION file_fdw;

	  DROP FOREIGN TABLE IF EXISTS tmpcsv_br_state_codes CASCADE;
	  CREATE FOREIGN TABLE tmpcsv_br_state_codes (
		subdivision text,
		name_prefix text,
		name text,
		id integer,
		idibge text,
		wdid text,
		lexlabel text,
		creation integer,
		extinction integer,
		category text,
		timezone text,
		utcoffset integer,
		utcoffset_dst integer,
		postalcode_ranges text,
		notes text
	  ) SERVER csv_files OPTIONS ( 
	     filename '/tmp/br_state_codes.csv', 
	     format 'csv', 
	     header 'true'
	  );

Relative-path copy

After create a table from the header and inferred datatypes as a table t,

psql -h remotehost -d remote_mydb -U myuser -c \
   "copy t from STDIN with delimiter as ','" < ./relative_path/file.csv

SAVE

Exemplo, gravando via psql para /tmp:

COPY (select * from tmpcsv_br_state_codes) TO '/tmp/test_ibge.csv' CSV HEADER;