Listing and Switching Databases in PostgreSQL

Listing and Switching Databases in PostgreSQL

PostgreSQL, a powerful open-source object-relational database system, offers a rich set of tools and features for managing databases efficiently. Whether you’re a database administrator or a backend developer, knowing how to list and switch between databases is foundational for navigating multi-database environments.

1. Prerequisites

Before you begin, ensure that:

• PostgreSQL is installed on your system (psql is accessible).

• You have appropriate permissions (e.g., superuser or role with connection rights).

• You can authenticate with the PostgreSQL server using a valid user.

2. Listing Databases in PostgreSQL

🔍 Method 1: Using psql Meta-command

Launch the psql interactive terminal and run:

Or the expanded form:

This will return a list of all databases:

• Name

• Owner

• Encoding

• Collation

• Ctype

• Access privileges

 Method 2: Using SQL Query

Alternatively, run this SQL statement:

This query excludes template databases (template0, template1) and shows user-created databases.

🛠️ Method 3: Command-Line Shell

Outside of psql, from your terminal:

You can also use psql -l:

3. Switching Databases

Unlike some other RDBMSs (e.g., MySQL), PostgreSQL does not support switching databases within the same session using a command like USE dbname;.

 Why?

PostgreSQL establishes a connection to a specific database at login time. To access another database, you must disconnect and reconnect.

 Recommended Approach:

Exit the current session and reconnect to the desired database:

Then:

Or directly:

Example:

4. Tips for Efficient Multi-Database Workflow

a. Use .pgpass for Passwordless Switching

Create a .pgpass file to automate authentication:

Make sure it has correct permissions:

 b. Automate with Shell Scripts

Create a script to list and switch:

 c. Use Environment Variables

Export default DB and user for faster switching:

5. Working with PgAdmin and Other GUIs

In PgAdmin:

1. Click on the server group.

2. Right-click → Connect to a database.

3. Use the Query Tool dropdown to switch databases (creates a new tab per DB).

Many tools (DBeaver, DataGrip) allow multiple connections with tabbed database views.

6. Bonus: Check Current Database

Within psql, find your current database:

Or use:

Conclusion

While PostgreSQL doesn’t allow in-session database switching like some other SQL engines, its robust connection model ensures clean, consistent access control and resource management. By mastering listing techniques and adopting smart reconnection practices, you can efficiently manage and switch between PostgreSQL databases in any environment—whether via CLI, GUI, or scripts.