copy command in PostgreSQL does not create proper gzip-compressed file

Question

I created a zipped TSV file using the following SQL:

\copy (select * from concept_relationship) to 'concept_relationship.tsv.gz'

But the following python code says bad zipped file. How to fix this issue.

with gzip.open('concept_relationship.tsv.gz','rt') as fin:

Answer 1

The issue is that the \copy command in PostgreSQL does not create a proper gzip-compressed file, even if you specify a .gz extension in the filename. Instead, it creates a plain text file with the .gz extension, which cannot be opened as a gzip-compressed file.

To fix this issue, you need to properly compress the file after exporting it with the \copy command. 

Here are the steps:

• Run your original \copy command without the .gz extension (export as a plain TSV file):

\copy (select * from concept_relationship) to 'concept_relationship.tsv'

• Compress the TSV file using the gzip command:

gzip concept_relationship.tsv

This will create the concept_relationship.tsv.gz file, which can be properly read by Python's gzip.open() function.