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?
asked Jul 19, 2013 at 16:08
Dan Ciborowski - MSFT
7,32710 gold badges61 silver badges90 bronze badges
Add a comment
|
4 Answers
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).
Comments
Can you just do something like
unzip -c myfile.zip | gzip myfile.gz
Easy enough to automate if you have enough files.
2 Comments
Dan Ciborowski - MSFT
File is not being stored locally and database is not local.
Neil Neyman
Ug. Well that makes it tough because I don't think amzn has any option for that. Can you get access to exec psql on the server where file is stored and connect that way? Otherwise might have to send the file through a middle-man server where you can.
This might only work when loading redshift from S3, but you can actually just include a "gzip" flag when copying data to redshift tables, as described here:
This is the format that works for me if my s3 bucket contains a gzipped .csv.
copy <table> from 's3://mybucket/<foldername> '<aws-auth-args>' delimiter ',' gzip;
Comments
unzip -c /path/to/.zip | psql -U user
The 'user' must be have super user right else you will get a
ERROR: must be superuser to COPY to or from a file
To learn more about this see here.
Basically this command is used in handling large databases
1 Comment
Cadoiz
Alternatively, you can also pipe into
unzip (or gunzip in my case). Consider cat /path/to/.zip | unzip -c | psql -U user (simplified for your case)