In PostgreSQL, we can create database objects within a database. Each database created in PostgreSQL has tables, schemas, tablespaces, functions, etc. that are created within them. To perform specific operations on a database, first, we need to switch to that particular existing database and then perform the desired operations.
How to Switch a Database in Postgres?
We can switch between the two existing databases in Postgres. By default, the database created by PostgreSQL is “postgres”. However, we can switch the Database in PostgreSQL:
- Using SQL Shell (psql)
- Using pgAdmin
Switch Databases in PostgreSQL Using psql
We can switch the database from the default database to our specified database. Before doing this, we can see the list of all the existing databases in the system by executing the “\l” command in psql like this:
This meta-command will return all the databases existing.
We can now switch to any of these databases using the two methods illustrated below.
Method 1: Switch a Database Using \c Command
One way to switch to an existing database is by using the \c command. The syntax used for switching the database in PostgreSQL using psql is:
In the above syntax, we will have to provide the database name to which we want to switch after the “\c” meta-command.
Now if we want to switch our default “postgres” database to the “database_1”, we will write the following query:
The execution of the above query will return the following output:
The “database_1=#” shows that the database has been successfully switched. We have another command to do the same.
Method 2: Switch a Database Using \connect Command
We can also switch the database in PostgreSQL using the \connect command in psql with the following syntax:
This is another syntax to switch the databases in PostgreSQL using psql. The database name in which we desire to switch into is written after the “\connect” command in psql. In our case the query will be:
This query will result in the database switch from “postgres” to the “database_1” database like this:
So, these were the two ways we can use to switch between the two databases in PostgreSQL using psql.
How to Switch Databases in PostgreSQL Using pgAdmin?
In pgAdmin, we can simply switch the database we want by selecting that database from the side panel and querying the object in it. For example, to connect to the database_1, we need to select the database_1 option from the databases section. After that, we can write queries for the database objects by expanding the query tool from the icon at the top, or by right-clicking on the database_1 you will see the query tool option.
This will open the query tool for us where we can write the queries to perform specific operations within that database.
This is how we can switch the database using psql and pgAdmin in PostgreSQL.
To switch to a database, the database should already exist. This means that we can switch only to the existing database. This can be done in psql by using the “\c” and “\connect” commands and these are followed by the database name to which we wish to switch. Switching the database can be done manually by simply navigating to the Databases section and selecting the desired database from the left pane of pgAdmin. This tutorial thoroughly illustrated the methods to switch the database in psql and pgAdmin.