Using pg_dump to only get insert statements from one table within database

just in case you are using a remote access and want to dump all database data, you can use:

pg_dump -a -h your_host -U your_user -W -Fc your_database > DATA.dump

it will create a dump with all database data and use

pg_restore -a -h your_host -U your_user -W -Fc your_database < DATA.dump

to insert the same data in your data base considering you have the same structure


if version < 8.4.0

pg_dump -D -t <table> <database>

Add -a before the -t if you only want the INSERTs, without the CREATE TABLE etc to set up the table in the first place.

version >= 8.4.0

pg_dump --column-inserts --data-only --table=<table> <database>

If you want to DUMP your inserts into an .sql file:

  1. cd to the location which you want to .sql file to be located
  2. pg_dump --column-inserts --data-only --table=<table> <database> > my_dump.sql

Note the > my_dump.sql command. This will put everything into a sql file named my_dump

Tags:

Postgresql