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.

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
– joop, Commented Sep 4, 2018 at 14:26

Vincent · Accepted Answer · 2018-11-11 20:14:34Z

7

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, 2018 at 20:14

Vincent

9,01613 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

reyalino Over a year ago

Hi @Vincent any ideas how to use it when working with multiples of gzip files?

Daniel Hernández Over a year ago

@relayino, as @kemin-zhou pointed in another answer, you can use zcat, which supports multiple files.

Kemin Zhou · Accepted Answer · 2019-08-29 17:14:02Z

4

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, 2019 at 17:14

Kemin Zhou

6,9913 gold badges54 silver badges60 bronze badges

Comments

hmadinei · Accepted Answer · 2024-04-28 10:43:16Z

0

It works for me:
7z e -so file.csv.gz | psql -U username -d databasename -c "COPY tablename FROM STDIN WITH (FORMAT csv, HEADER true);"

I use 7zip to do that. For example, You can use gzip.

answered Apr 28, 2024 at 10:43

hmadinei

614 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related