How to Restore a Single Table in PostgreSQL Quickly

To restore a single table in PostgreSQL, first export the table using pg_dump with the -t option, then import it back using psql or pg_restore. This method allows you to backup and restore just one table without affecting the entire database.

Syntax

Use pg_dump to export a single table and psql or pg_restore to restore it.

• pg_dump -t table_name database_name > table_backup.sql: Dumps the specified table.
• psql database_name < table_backup.sql: Restores the table from the SQL file.

bash

pg_dump -t table_name database_name > table_backup.sql
psql database_name < table_backup.sql

Example

This example shows how to backup and restore a table named employees from the database companydb.

bash

pg_dump -t employees companydb > employees_backup.sql

psql companydb < employees_backup.sql

Output

SET
SET
SET
SET
SET
CREATE TABLE
COPY 5
ALTER TABLE
SET

Common Pitfalls

• Forgetting to specify the exact table name with -t can cause the entire database to be dumped.
• Restoring a table that already exists without dropping it first may cause errors.
• Dependencies like foreign keys or sequences might need manual handling.

Always check if the table exists before restoring or use DROP TABLE IF EXISTS in your dump.

bash

pg_dump -t employees companydb > employees_backup.sql

-- Before restoring, drop the table if it exists
psql companydb -c "DROP TABLE IF EXISTS employees CASCADE;"

psql companydb < employees_backup.sql

Output

DROP TABLE
SET
SET
SET
SET
CREATE TABLE
COPY 5
ALTER TABLE
SET

Quick Reference

Command	Description
pg_dump -t table_name db_name > file.sql	Backup a single table to a file
psql db_name < file.sql	Restore the table from the backup file
psql db_name -c "DROP TABLE IF EXISTS table_name CASCADE;"	Drop table before restoring to avoid conflicts
pg_restore	Use for restoring from custom-format dumps (not shown here)

Key Takeaways

Use pg_dump with -t option to export a single table.

Restore the table using psql with the backup SQL file.

Drop the existing table before restoring to avoid errors.

Check for dependencies like foreign keys and sequences.

Always verify the table name to avoid dumping the whole database.