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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/use-database-command-postgresql.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics