Importing zipped CSV file into PostgreSQL

postgresql csv import named-pipes postgresql-copy
Arezoo picture Arezoo · Jan 19, 2017 · Viewed 9k times · Source

I have a big compressed csv file (25gb) and I want to import it into PostgreSQL 9.5 version. Is there any fast way to import zip or qzip file into postgres without extracting the file?

Answer

joop picture joop · Jan 19, 2017

There is an old trick to use a named pipe (works on Unix, don't know about Windows)

  • create a named pipe: mkfifo /tmp/omyfifo
  • write the file contents to it: zcat mycsv.csv.z > /tmp/omyfifo &
  • [from psql] copy mytable(col1,...) from '/tmp/omyfifo'
  • [when finished] : rm /tmp/omyfifo

The zcat in the backgound will block until a reader (here: the COPY command) will start reading, and it will finish at EOF. (or if the reader closes the pipe)

You could even start multiple pipes+zcat pairs, which will be picked up by multiple COPY statements in your sql script.

This will work from pgadmin, but the fifo (+zcat process) should be present on the machine where the DBMS server runs.

BTW: a similar trick using netcat can be used to read a file from a remote machine (which of course should write the file to the network socket)

Related questions

Postgres COPY FROM csv file- No such file or directory

I'm trying to import a (rather large) .txt file into a table geonames in PostgreSQL 9.1. I'm in the /~ directory of my server, with a file named US.txt placed in that directory. I set the search_path variable to geochat, …

Read More
Importing CSV file PostgreSQL using pgAdmin 4

I'm trying to import a CSV file to my PostgreSQL but I get this error ERROR: invalid input syntax for integer: "id;date;time;latitude;longitude" CONTEXT: COPY test, line 1, column id: "id;date;time;latitude;longitude" my csv file …

Read More
How to import CSV file data into a PostgreSQL table?

How can I write a stored procedure that imports data from a CSV file and populates the table?

Read More