Loading zipped CSV(filename.csv.gz) file into PostgreSQL table

Question

How to load larger amount of csv.gz file into Postgresql without unzipping to csv file ,since I tried pipeline command (mkfifo pipelinename) but it doesn't work for me. Is there any other solution to solve this issue ?

I have try to load it from local to postgresql using following command command : zcat file.csv.gz | psql -U username -d database;

Result : out of memory

Need : I want to load a big size csv.gz (around 15+ GB) file from centos to postgresql database.

https://stackoverflow.com/a/41741644/2235885 (`didn't work for me` is not a very useful description. What exactly did you try? What went wrong? What was the error massage?) — joop, Sep 04 '18 at 14:26

score 6 · Answer 1 · answered Nov 11 '18 at 20:14

6

Note that this should also work from inside psql:

\copy TABLE_NAME FROM PROGRAM 'gzip -dc FILENAME.csv.gz' DELIMITER ',' CSV HEADER NULL ''

answered Nov 11 '18 at 20:14

Vincent

7,808
13
49
63

Hi @Vincent any ideas how to use it when working with multiples of gzip files? – reyalino Dec 17 '19 at 06:50
@relayino, as @kemin-zhou pointed in another answer, you can use `zcat`, which supports multiple files. – Daniel Hernández May 12 '21 at 06:42

score 3 · Answer 2 · answered Aug 29 '19 at 17:14

3

Just to share my simple examples using zcat instead of gzip. Simply less typing. I am using zcat to expand the gzipped file.

\copy tmptable from program 'zcat O1variant.tab.gz' with (format csv, delimiter E'\t', header TRUE)

answered Aug 29 '19 at 17:14

Kemin Zhou

6,264
2
48
56

Loading zipped CSV(filename.csv.gz) file into PostgreSQL table

2 Answers2

Linked