In database management systems, copying a table provides numerous features, such as time-saving, error-free data manipulation, etc. Postgres lets us copy/duplicate a table within the same or different database. To copy a table within the same database, various commands are used, such as the “CREATE TABLE AS”, “CREATE TABLE LIKE”, etc. However, to copy a table to a different database the “pg_dump” utility is used in Postgres.
This write-up explains how to copy a Postgres table from one database to another using practical examples.
How to Copy/Duplicate a Postgres Table From One Database to Another?
Use the following “pg_dump” command to duplicate a table from one database to another:
pg_dump –U user_name –t table_name source_db | psql –U user_name targeted_db
Here in the above snippet:
- “pg_dump” is a utility that is used to back up a database.
- “-U” is a parameter specifying the user name.
- “table_name” represents a table to be duplicated.
- “source_db” represents the source database(where the selected table is placed).
- “targeted_ db” represents the targeted database(where the selected table will be copied).
To learn how to copy a table from one database to another, follow the steps below:
Step 1: Populating the Sample Databases
Let’s execute the “\l” command to see the available databases:
\l
Use the “\dt” command to populate the tables available in “postgres” database:
\dt
To see all the tables of the “example_db” database, first, we need to access that particular database. For this, execute the “\c” command along with the database name:
\c example_db;
Once you are connected to the “example_db” database, then you can populate all of its tables using the “\dt” command:
\dt
The output snippet shows that the “example_db” database didn’t have any table.
Step 2: Sample Table
The “postgres” database has a table named “author_info”, whose content is displayed in the following snippet:
SELECT * FROM author_info;
In the following step, we will copy the “author_info” table from the “postgres” database to the “example_db” database.
Step 3: Access the Postgres Bin Directory
Type “CMD” in the Windows search bar, and open it as an administrator:
Once the “CMD” is opened, use the “cd” command to navigate to the Postgres’ bin directory:
cd C:\program files\postgresql\15\bin
Replace "15" with the Postgres version installed on your system:
The output shows that the Postgres bin directory has been accessed successfully.
Step 4: Copying Table From One Database to Another
Execute the following “pg_dump” command to copy the “author_info” table from “postgres” to “example_db”:
pg_dump –U postgres –t author_info postgres | psql –U postgres example_db
The output shows that the selected table has been successfully copied to the “example_db”.
Step 5: Confirm the Table Duplication
Now navigate back to the “example_db” and execute the following command to populate the copied table:
\dt
The “author_info” table of the “postgres” database has been successfully duplicated to the “example_db” database. Utilize the below-provided query to fetch the content of copied table:
SELECT * FROM author_info;
This is how a specific table can be copied from one database to another.
Conclusion
To duplicate a table from one database to another, PostgreSQL uses the "pg_dump" command with the name of the targeted table, source database, and destination database. For more clarity, specify the user name in the “pg_dump” command using the -U parameter. This post presented a practical guide on how to copy a specific table from one(source) database to another(destination).
