Postgresql – How to load 100k + Records into progres database from a CSV file

bulkloadpostgresql

Our project needs to load 100k+ rows into a Postgres table. We have a huge .CSV file with all these 100K+ rows and we need to load this data into a table effectively and quicker (may be in the form of batches so that unloaded data/batch can be revisited, corrected and loaded again).

Our project involves a Java application and we initially thought of using JDBC, but reading each row and committing to database slows down the application.

Please suggest if you know an approach to get this job done.

Best Answer

You can use the COPY command for this.

See: http://www.postgresql.org/docs/9.3/static/sql-copy.html https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgres-table

Related Solutions

Sql-server – How to load them as varbinary(MAX) format into SQL Database

Yes, use OPENROWSET with BULK. You need a format file though.

Assuming you want to attach blobs to existing records, something like:

INSERT SomeTable (id, blob)
select
    X.SomeID, B.Blob
from
    SomeKeyTable X
    JOIN
    OPENROWSET (
             BULK 'c:\myfile.txt',
             FORMATFILE = 'c:\myfileformat.txt'
    ) B ON X.AKey  = B.AKey

Mysql – How to import a specific line from a text file into MySQL

If you are using Unix/Linux you could try using a fifo buffer:

mkfifo /tmp/mysql.pipe 
# Select rows 101 to 110
sed -n 101,110p filename > /tmp/mysql.pipe & 
mysql –uusername -p -Ddatabasename -e "LOAD DATA LOCAL INFILE ' /tmp/mysql.pipe ' INTO TABLE tablename"

rm -f /tmp/mysql.pipe

Best Answer

Related Solutions

Sql-server – How to load them as varbinary(MAX) format into SQL Database

Mysql – How to import a specific line from a text file into MySQL

Related Question