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

Question

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?

Davidadavidde

using COPY is not an option?.. – Hereld

No, so far I know, with copy I get a csv or tsv file. It's not what I want. Sure I could edit it afterwards, but that's what I would like to avoid. – Davidadavidde

COPY has binary format, and you can re-load the data directly into the table (also using COPY). No need for post processing. See COPY TO and COPY FROM. – Patronymic

Right, but this format is not useful if you would like to edit this data frequently in a text oriented programming IDE, especially if you have more then 10 columns. I'm not looking for workarounds .. if it is not possible no problem, then I know enough workarounds. The question was simply: is it possible or not? If NOT, it's ok. – Davidadavidde

Answer 1

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

Davenport

Answer 2

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.

Schertz