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:

\l

Or the expanded form:

\list

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:

SELECT datname FROM pg_database WHERE datistemplate = false;

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

🛠️ Method 3: Command-Line Shell

Outside of psql, from your terminal:

psql -U postgres -c "\l"

You can also use psql -l:

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:

\q -- Quit current psql session

Then:

psql -U username -d target_database

Or directly:

psql -U username -d target_database -h hostname -p port

Example:

psql -U admin -d salesdb

4. Tips for Efficient Multi-Database Workflow

a. Use .pgpass for Passwordless Switching

Create a .pgpass file to automate authentication:

hostname:port:database:username:password

Make sure it has correct permissions:

chmod 600 ~/.pgpass

 b. Automate with Shell Scripts

Create a script to list and switch:

#!/bin/bash
echo "Available Databases:"
psql -U postgres -c "\l"
read -p "Enter target DB: " db
psql -U postgres -d "$db"

 c. Use Environment Variables

Export default DB and user for faster switching:

export PGDATABASE=mydb
export PGUSER=myuser
psql

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:

SELECT current_database();

Or use:

\conninfo

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.