How to Copy a Table from One Database to Another in PostgreSQL

When working with PostgreSQL, the data is stored in tables. Thus, when you create a database to store a given entity, the entity is represented as a table in which you insert the values. You can have different tables in a given database but you can’t open two databases simultaneously.

Hence, when you want to access a table in another database, the best option is to copy it and all its data into the current database. We are mainly used to copying tables in the same database. However, our focus today is on how to copy a table from one database to another in PostgreSQL. Let’s dig in!

Step-by-Step Guide to Copy a Table from One Database to Another in PostgreSQL

PostgreSQL relies on the “pg_dump” tool to copy the tables across different databases. The copied table will have all its data moved to the new database. That way, you will have a quick way to duplicate the table when working with different databases.

Different steps are involved in copying a table from one database to another in PostgreSQL. For this example, we have the PostgreSQL installed on Ubuntu, and we will access it from its shell. The steps are similar when working with another environment such as Windows, with only a minor difference.

1. Open Your PostgreSQL Shell

You must first access the PostgreSQL shell to access the table that you wish to copy from one database to another. The following commands bring up the PostgreSQL database. You can enter your password to access the database with the specified user account:

$ sudo -iu postgres
$ psql

2. List the Available Databases

You must check the available database to determine the source and destination database for the table that you wish to copy.

# \l

You can quickly create one if you don’t have a target database.

For our example, we use “postgres” as our source database and have the “linuxdemo” as the target database where we copy the table in the “postgres” database.

3. Find the Target Table

From the terminal shell, we can already note that we are connected to the “postgres” database. In that case, we can list the available tables using the \dt command to locate the table that we wish to copy to our new database.

# \dt

Here, we only have one table named “floatdemo”. We can open it to check its contents before copying it.

4. Verify If the Table Doesn’t Exist in the Target Database

Before you copy your table, you must ensure that the target database has no table which shares the same name as the one that you wish to copy. Similarly, don’t create a table that would raise an error when copying the target table.

Replace the following command with your target database:

# \c linuxdemo;

Next, list the tables in the database to check if there is any match to the one that you want to copy.

# \dt

5. Execute the Copy Command

The command to copy the table depends on whether you use Windows or Ubuntu. In the case of Windows, you must find the path to the PostgreSQL files on your system. For instance, if you are running PostgreSQL v14, your path will likely be C:\program files\postgresql\14\bin. Once you get the path, open CMD and navigate to that directory. You then execute the copy command on your CMD.

In our case, we are using Ubuntu, and how we execute the command is different. Here, open the PostgreSQL shell on your terminal. From there, use the following syntax to copy the table from the source database to the target database. Note that the command is the same for Windows and Ubuntu.

pg_dump -U postgres -t target-table source-database | psql -U postgres target-database

In the following example, “floatdemo” is the target table and the “postgres” following it is our source database. The “linuxdemo” is the target database. So, replace the same on your case. Then, run the command.

Enter your PostgreSQL password for the logged-in user to authenticate the action. You will get an output message confirming that the copy action is completed successfully.

6. Verify the Copied Table

After executing the copy command, the last step is to check if the action is completed as expected. Open your PostgreSQL shell and connect to the target database. Once connected, list the available tables to check if a new entry is created for the copied table. If so, check its contents using the SELECT * command.

We confirm that our duplicate table is successfully copied from the source database to the target database in PostgreSQL.

Conclusion

PostgreSQL lets you quickly copy a table from one database to another using the “pg_dump” command. We explained the step-by-step process while providing a practical example. You now understand how to copy tables across PostgreSQL databases.