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?

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. — Rudi
– Rudi, Commented Jun 11, 2017 at 13:48
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. — pbuck
– pbuck, Commented Jun 11, 2017 at 16:31
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. — Rudi
– Rudi, Commented Jun 11, 2017 at 20:06

Alexander Watzinger · Accepted Answer · 2022-01-20 09:57:55Z

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.

Laurenz Albe · Accepted Answer · 2017-06-12 07:48:00Z

4

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

answered Jun 12, 2017 at 7:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related