CODEWITHSUNDEEP
postgresqlbackupinsert-intopg-dump
Rudi
Rudi

Reputation: 1169

Create PostgreSQL dump with ONE INSERT statement instead of INSERT per row

I try to do a table data dump using pg_dump, something like this:

pg96\bin\pg_dump ... --format plain --section data --column-inserts --file obj.account.backup --table obj.account database_xyz

Instead of getting 

INSERT INTO obj.account(name, email, password) VALUES ('user1','email1','password1');
INSERT INTO obj.account(name, email, password) VALUES ('user2','email2','password2');

I would like to get

INSERT INTO obj.account (name, email, password) VALUES 
('user1','email1','password1'),
('user2','email2','password2');

Is there a way for this without any Non-PostgreSQL postprocessing?

Upvotes: 7

Views: 2091

Answers (2)

Alexander Watzinger
Alexander Watzinger

Reputation: 73

Since PostgreSQL 12 you can use pg_dump with --rows-per-insert=nrows, see https://www.postgresql.org/docs/12/app-pgdump.html

I'm aware that this is an old question but I wanted to mention it in case somebody else (like me) finds this while searching for a solution. There are cases where COPY can't be used and for bigger data sets using a single INSERT statement is much faster when importing.

Upvotes: 6

Laurenz Albe
Laurenz Albe

Reputation: 246053

There is no way to get INSERT statements like that with pg_dump.

Upvotes: 3

Related Questions