[PostgreSQL] how to copy SQL output to a compressed TSV file

Question

I want to copy the SQL query output to a compressed TSV file instead of a regular TSV file. What is the syntax of the COPY command for it?

Answer 1

If you want to copy the SQL output to a compressed file, you can pipe the output through an external compression program, such as gzip.

In the following example, I am creating a temporary table, "xyz," and then copy the table into a tab-separated file.

CREATE temporary table xyz as (

SELECT *

FROM customers

);

\copy xyz to PROGRAM 'gzip > person_drug_data.tsv.gz' WITH DELIMITER E'\t' CSV HEADER QUOTE E'\b';