Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Export to CSV and Compress with GZIP in postgres

I need to export a big table to csv file and compress it.

I can export it using COPY command from postgres like -

COPY foo_table to '/tmp/foo_table.csv' delimiters',' CSV HEADER;

And then can compress it using gzip like -

gzip -c foo_table.csv > foo.gz

The problem with this approach is, I need to create this intermediate csv file, which itself is huge, before I get my final compressed file.

Is there a way of export table in csv and compressing the file in one step?

Regards, Sujit

like image 310
Sujit Avatar asked Oct 20 '10 19:10

Sujit


2 Answers

The trick is to make COPY send its output to stdout, then pipe the output through gzip:

psql -c "COPY foo_table TO stdout DELIMITER ',' CSV HEADER" \
    | gzip > foo_table.csv.gz
like image 195
Joey Adams Avatar answered Oct 24 '22 15:10

Joey Adams


You can use directly, as per docs, https://www.postgresql.org/docs/9.4/sql-copy.html

COPY foo_table to PROGRAM 'gzip > /tmp/foo_table.csv' delimiter ',' CSV HEADER;
like image 24
abhi shukla Avatar answered Oct 24 '22 13:10

abhi shukla