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?
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> 
-d, -a, and -t short versions are still present, though. Checked with PG11.
--inserts is another option; it restores slightly faster but can't tolerate column order changes
SELECT version();If you want to DUMP your inserts into an .sql file:
cd to the location where you want the .sql file to be locatedpg_dump --column-inserts --data-only --table=<table> <database> > my_dump.sqlNote that the > my_dump.sql part of this command will put everything into an .sql file named my_dump
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
for postgres 12, this worked for me:
pg_dump -d <database> -t <table> > DATA.dump 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="${BASE_DIR}/myApp_$(date +%Y%m%d_%H%M%S)" FNAME="${LOCATION}_${DB_NAME}_${TABLE}.sql" # 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 > $FNAME sudo gzip $FNAME For example, you can export only the data of apple database of the user(role) john to backup.sql as shown below. *My answer explains how to export both schema and data:
pg_dump -U john -a apple > backup.sql Or:
pg_dump -U john --data-only apple > backup.sql And, you can export only the data of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below:
pg_dump -U john -a --column-inserts apple > backup.sql And, you can export only the data of apple database of the user(role) john to backup.sql with only INSERT statement which doesn't have column names as shown below:
pg_dump -U john -a --inserts apple > backup.sql And, you can export only the data of the specific tables person and animal of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below. *Specifying multiple tables are available in one command:
pg_dump -U john -a --column-inserts -t person -t animal apple > backup.sql Or:
pg_dump -U john -a --column-inserts --table=person --table=animal apple > backup.sql Or:
pg_dump -U john -a --column-inserts --table person --table animal apple > backup.sql Lastly for example, you export only the data of the specific tables person and animal of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below:
pg_dump -U john -a --column-inserts -t person -t animal apple > backup.sql Then, you can import backup.sql to orange database of the user(role) john with psql which must be used to import non-archive files as shown below. *You have to create orange database and the schema otherwise there is error and my answer explains how to create a database and my answer explains how to import schema and data:
psql -U john -f backup.sql orange And, for example, you export and archive(-Fc) only the data of the specific tables person and animal of apple database of the user(role) john to backup.sql with only INSERT statement which has column names as shown below. *My answer explains how to export and archive a database:
pg_dump -U john -Fc -a --column-inserts -t person -t animal apple > backup.sql Then, you can import archive backup.sql to orange database of the user(role) john with pg_restore which must be used to import archive files as shown below. *You have to create orange database and the schema otherwise there is error and my answer explains how to create a database and my answer explains how to import archive files:
pg_restore -U john -d orange < backup.sql 
To get the data set as insert commands without root user - using different user
pg_dump -U -a --column-inserts --data-only > backup.sql
data will be backed up into a backup.sql file
