Using the Equivalent of "USE database_name" in PostgreSQL

The Equivalent of "USE database_name" in PostgreSQL

In PostgreSQL, switching between databases within a single session is different from other database systems like MySQL, which uses the command USE database_name. PostgreSQL does not support this command due to its design. Instead, each database connection in PostgreSQL is tied to a specific database, meaning you need to reconnect to switch databases.

To "switch" databases in PostgreSQL, you must either:

• Exit the current session and connect to a new database, or
• Use \c database_name within the psql command-line interface.

Syntax to Connect to a Database in PostgreSQL

1. Reconnecting from the Command Line

To connect to a PostgreSQL database, use the following command directly from your terminal:

# Connect to a PostgreSQL database from the terminal
psql -U username -d database_name

Explanation:

• -U username: Specifies the PostgreSQL user.
• -d database_name: Specifies the database you wish to connect to.

2. Switching Databases with \c in psql

Within the psql environment, use the \c command to change to another database:

-- Switch to another database within psql
\c database_name username

Explanation:

• \c database_name: Reconnects to database_name.
• username (optional): Specifies a username, if different from the current session.

Example and Explanation

1. Connecting to a Database from the Command Line

Code:

# Connect to the "sales" database as user "admin"
psql -U admin -d sales

Explanation:

• This command initiates a new connection to the sales database as the admin user from the terminal.

2. Switching Databases Within psql

Code:

-- Inside psql, switch to the "inventory" database
\c inventory admin

Explanation:

• \c inventory: Switches to the inventory database.
• admin: Connects using the admin user, if necessary.

Important Notes:

• Single-Database Session: PostgreSQL enforces a single database per session connection.
• Reconnection Requirement: Switching databases requires reconnecting within psql or restarting with psql -d.
• User Privileges: Ensure the user has the required privileges to access the database you are switching to.

All PostgreSQL Questions, Answers, and Code Snippets Collection.