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;
          CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;

	  DROP FOREIGN TABLE IF EXISTS tmpcsv_br_state_codes CASCADE;
	  CREATE FOREIGN TABLE tmpcsv_br_state_codes (
		subdivision text,
		region 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,
		km2 int,
		borders text,
		centroid_geohash text,
		utm_zones text,
		bounds_geohash text,
		bounds_lat text,
		bounds_long text,
		notes text
	  ) SERVER files OPTIONS ( 
	     filename '/tmp/br-state-codes.csv', -- windows transforma '-' em '_'?
	     format 'csv', 
	     header 'true'
	  );

REGIONS:

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

	  DROP FOREIGN TABLE IF EXISTS tmpcsv_br_region_codes CASCADE;
	  CREATE FOREIGN TABLE tmpcsv_br_region_codes (
		region text,
		"wdId" text,
		name text,
		fullname text,
		creation integer,
		extinction integer,
		"postalCode_ranges" text,
		notes text
	  ) SERVER files OPTIONS ( 
	     filename '/tmp/br-region-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;