Therefore, whenever you need to entry a desk in one other database, the best choice is to repeat it and all its information into the present database. We’re primarily used to copying tables in the identical database. Nevertheless, our focus as we speak is on learn how to copy a desk from one database to a different in PostgreSQL. Let’s dig in!
Step-by-Step Information to Copy a Desk from One Database to One other in PostgreSQL
PostgreSQL depends on the “pg_dump” device to repeat the tables throughout totally different databases. The copied desk can have all its information moved to the brand new database. That approach, you’ll have a fast strategy to duplicate the desk when working with totally different databases.
Totally different steps are concerned in copying a desk from one database to a different in PostgreSQL. For this instance, we now have the PostgreSQL put in on Ubuntu, and we are going to entry it from its shell. The steps are comparable when working with one other setting equivalent to Home windows, with solely a minor distinction.
1. Open Your PostgreSQL Shell
You have to first entry the PostgreSQL shell to entry the desk that you simply want to copy from one database to a different. The next instructions convey up the PostgreSQL database. You’ll be able to enter your password to entry the database with the desired person account:
$ sudo -iu postgres
$ psql
2. Checklist the Out there Databases
You have to examine the accessible database to find out the supply and vacation spot database for the desk that you simply want to copy.
You’ll be able to shortly create one if you happen to don’t have a goal database.
For our instance, we use “postgres” as our supply database and have the “linuxdemo” because the goal database the place we copy the desk within the “postgres” database.
3. Discover the Goal Desk
From the terminal shell, we are able to already be aware that we’re linked to the “postgres” database. In that case, we are able to checklist the accessible tables utilizing the dt command to find the desk that we want to copy to our new database.
Right here, we solely have one desk named “floatdemo”. We will open it to examine its contents earlier than copying it.
4. Confirm If the Desk Doesn’t Exist within the Goal Database
Earlier than you copy your desk, you need to make sure that the goal database has no desk which shares the identical title because the one that you simply want to copy. Equally, don’t create a desk that will increase an error when copying the goal desk.
Substitute the next command along with your goal database:
Subsequent, checklist the tables within the database to examine if there’s any match to the one that you simply need to copy.
5. Execute the Copy Command
The command to repeat the desk relies on whether or not you utilize Home windows or Ubuntu. Within the case of Home windows, you need to discover the trail to the PostgreSQL recordsdata in your system. As an illustration, in case you are working PostgreSQL v14, your path will seemingly be C:program filespostgresql14bin. When you get the trail, open CMD and navigate to that listing. You then execute the copy command in your CMD.
In our case, we’re utilizing Ubuntu, and the way we execute the command is totally different. Right here, open the PostgreSQL shell in your terminal. From there, use the next syntax to repeat the desk from the supply database to the goal database. Observe that the command is identical for Home windows and Ubuntu.
pg_dump -U postgres -t target-table source-database | psql -U postgres target-database
Within the following instance, “floatdemo” is the goal desk and the “postgres” following it’s our supply database. The “linuxdemo” is the goal database. So, change the identical in your case. Then, run the command.
Enter your PostgreSQL password for the logged-in person to authenticate the motion. You’ll get an output message confirming that the copy motion is accomplished efficiently.
6. Confirm the Copied Desk
After executing the copy command, the final step is to examine if the motion is accomplished as anticipated. Open your PostgreSQL shell and connect with the goal database. As soon as linked, checklist the accessible tables to examine if a brand new entry is created for the copied desk. In that case, examine its contents utilizing the SELECT * command.
We verify that our duplicate desk is efficiently copied from the supply database to the goal database in PostgreSQL.
Conclusion
PostgreSQL enables you to shortly copy a desk from one database to a different utilizing the “pg_dump” command. We defined the step-by-step course of whereas offering a sensible instance. You now perceive learn how to copy tables throughout PostgreSQL databases.
