Fast mass inserts into PostgreSQL

published Sep 04, 2015 03:32 by admin ( last modified Sep 04, 2015 03:32 )

The fastest way seems to be with a special tool:

Other than that, one can use COPY which is almost as fast and it means you have a data file that postgres can suck in: PostgreSQL: Documentation: 9.4: Populating a Database

This guy has tested it all and concludes that if for whatever reason you cannot use COPY you can make multirow INSERTS: select * from depesz; » Blog Archive » how to insert data to database – as fast as possible

Here is an example of how a multirow insert looks like in PostgreSQL

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

There is an alternative to using COPY, which is the multirow values syntax that Postgres supports. From the documentation: