Using pg dump to only get insert statements from one table within database

Question

I m looking for a way to get all rows as INSERT statements from one specific table within a database using pg dump in PostgreSQL   E g   I have table A and all rows in table A I need as INSERT statements  it should also dump those statements to a file   Is this possible

User · Accepted Answer

if version  lt  8 4 0      pg dump -D -t  lt table gt   lt database gt    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  lt table gt   lt database gt

User · Answer

Put into a script I like something like that     bin bash set -o xtrace   remove me after debug TABLE some table name DB NAME prod database  BASE DIR  var backups someDir LOCATION  quot   BASE DIR  myApp   date   Y m d  H M S  quot  FNAME  quot   LOCATION    DB NAME    TABLE  sql quot     Create backups directory if not exists if      -e  BASE DIR    then        mkdir  BASE DIR        chown -R postgres postgres  BASE DIR fi  sudo -H -u postgres pg dump --column-inserts --data-only --table  TABLE  DB NAME  gt   FNAME sudo gzip  FNAME

User · Answer

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  gt  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  lt  DATA dump  to insert the same data in your data base considering you have the same structure

User · Answer

If you want to DUMP your inserts into an  sql file    cd to the location which you want to  sql file to be located pg dump --column-inserts --data-only --table  lt table gt   lt database gt   gt  my dump sql   Note the  gt  my dump sql command  This will put everything into a sql file named my dump

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

Examples related to postgresql