Listing and switching databases are core concepts in any DBMS. For example, if you want to perform some tasks on the existing databases, you must know the names of the respective databases. However, remembering all databases' names is impossible for a human being. Therefore different databases adopt different approaches to reveal the list of available databases.
For instance, Postgres offers various built-in commands for listing databases, such as “\l”, “\list”, “pg_database”, etc. Once you get the list of databases, you can switch to any database of your choice using the “\c” or “\connect” command.
This blog post illustrates how to list and switch databases in SQL Shell (psql) via practical demonstration.
PostgreSQL: List Databases in SQL Shell(psql)
The “pg_database” catalog, “\List”, and “\l” commands are used in Postgres to get the list of available databases. You can also use the “\list+” and “\l+” commands to get a more detailed database list, including size, tablespace, etc.
Example 1: List Databases in SQL Shell(psql) Using “\l” or “\list” Command
Launch the SQL Shell, provide the login details, and execute the “\l” command to get the databases’ list:
\l
The “\l” command retrieves seven databases, three of which(i.e., postgres, template0, and template1) are default databases, while the other four are user-defined databases. Alternatively, you can use the “\list” command to get the same functionality.
Example 2: Expand Output
To get the psql output in a more readable format, you can execute the “\x” command:
The expanded display is on. Now, execute the “\l” or “\list” command to get the list of databases in the expanded display:
\list
Example 3: List Databases in SQL Shell(psql) Using “pg_database” Catalog
Alternatively, you can use the “pg_database” catalog along with the SELECT statement to show the list of available databases:
SELECT datname FROM pg_database;
Example 3: List Databases in SQL Shell(psql) Using “\l+” or “\list+” Command
To get more details regarding available databases, you need to execute the “\l+” or “\list+” command:
\l+
PostgreSQL: Switch Databases in SQL Shell(psql)
To switch databases in SQL Shell, the “\c” and “\connect” commands are used with the respective database name.
Example 1: Switching Database Using “\c” Command
Specify the “\c” command followed by the database name to which you want to establish a connection:
\c sample_db;
The output snippet shows that you are successfully switched to a database named “smaple_db”.
Example 2: Switching Database Using “\connect” Command
Alternatively, you can use the “\connect” command followed by the database name to switch the databases in psql:
\connect postgres;
The output signifies that the “\connect” command successfully switched you to the “postgres” database.
Conclusion
Different built-in Postgres commands are used for listing and switching databases in SQL Shell. For instance, the “\List”, “\l” commands, and “pg_database” catalog are used in SQL Shell to get the list of available databases. The “\list+” and “\l+” commands are used to get a more detailed database list, including size, tablespace, etc. To switch databases in SQL Shell, the “\c” and “\connect” commands are used with the respective database name. This blog post presented a detailed guide on how to list and switch databases in SQL Shell (psql) via practical demonstration.
