How to Switch Databases in PostgreSQL psql Command Line

Switching Between Databases in PostgreSQL psql.

In PostgreSQL's command-line interface psql, switching between databases is straightforward. The \c (or \connect) command allows you to change the active database within the same psql session. Here’s how to switch databases in PostgreSQL using psql.

1. Using the \c Command

The \c (or \connect) command is the primary way to switch databases in psql.

Syntax:

\c database_name [username]
database_name: The name of the database you want to switch to.
username (optional): Specifies the username if you want to connect with a different user.

Example Code:

-- Switch to a different database
\c new_database               -- Connects to 'new_database' as the current user
-- Optionally, connect to a database with a specific user
\c new_database another_user  -- Connects to 'new_database' as 'another_user'

Explanation:

• \c new_database: Changes the connection to new_database while keeping the same user.
• \c new_database another_user: Switches to new_database and connects as another_user if permissions allow.

2. Connecting to a Database with psql Command-Line Options

When initially starting psql, you can specify the database you want to connect to using the -d option.

Syntax:

psql -U username -d database_name
-U username: Specifies the username to connect with.
-d database_name: Specifies the database to connect to.

Example Code:

# Start psql and connect directly to 'new_database' as 'my_user'
psql -U my_user -d new_database

Explanation:

• psql -U my_user -d new_database: Opens psql and connects directly to new_database as my_user.

3. Connecting to a Database After Logging in as Superuser

If you’re already connected to a database as a superuser, you can switch to any other database with \c without having to exit psql.

Example Code:

-- Connect to another database as a superuser
\c target_database

Explanation:

• \c target_database: Switches to target_database as a superuser without leaving the psql session.

Important Notes

• User Permissions: You need proper permissions to access other databases. Ensure the user has the necessary access rights.
• Exiting psql: If you need to switch to another database that does not allow switching directly, you can exit psql with \q and reconnect using the -d option.