Postgresql – How to import ZIpped file into Postgres Table

postgresqlzip

I would like to important a file into my Postgresql system(specificly RedShift). I have found a arguement for copy that allows importing a gzip file. But the provider for the data I am trying to include in my system only produces the data in a .zip. Any built in postgres commands for opening a .zip?

Best Answer

From within Postgres:

COPY table_name FROM PROGRAM 'unzip -p input.csv.zip' DELIMITER ',';

From the man page for unzip -p:

-p     extract files to pipe (stdout).  Nothing but the file data is sent to stdout, and the files are always extracted  in  binary
       format, just as they are stored (no conversions).

Related Solutions

Postgresql – How to import CSV file data into a PostgreSQL table

Take a look at this short article.

Solution paraphrased here:

Create your table:

CREATE TABLE zip_codes 
(ZIP char(5), LATITUDE double precision, LONGITUDE double precision, 
CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar);

Copy data from your CSV file to the table:

COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' WITH (FORMAT csv);

Postgresql – Import SQL dump into PostgreSQL database

psql databasename < data_base_dump

That's the command you are looking for.

Beware: databasename must be created before importing. Have a look at the PostgreSQL Docs Chapter 23. Backup and Restore.

Best Answer

Related Solutions

Postgresql – How to import CSV file data into a PostgreSQL table

Postgresql – Import SQL dump into PostgreSQL database

Related Topic