To specify a database in psql, the primary method is using the -d or --dbname flag followed by the database name, such as psql -d my_database. Alternatively, you can provide the database name as a final positional argument, like psql my_database, or use a connection URI string.
The psql utility is the interactive terminal for PostgreSQL, serving as the primary gateway for database administrators and developers to manage their data. While its basic usage seems straightforward, mastering the various ways to specify a database—whether through command-line flags, environment variables, or internal commands—is essential for efficient workflow management in 2026. As PostgreSQL environments move toward increasingly complex cloud-native architectures, understanding how to target specific logical databases within a cluster becomes a foundational skill.
The Architecture of a psql Connection
Before executing a command, it is vital to understand what happens when you initiate a psql session. Unlike some database systems that dump you into a general "server" shell, psql always attempts to connect to a specific logical database from the moment of initialization. If you do not specify a database, psql defaults to a database name that matches your current operating system username. If that database does not exist, the connection will fail with a FATAL error message.
This behavior highlights why specifying the database is not just a preference but a requirement for most production workflows. You are not just "logging into PostgreSQL"; you are establishing a session with a specific set of schemas, tables, and permissions tied to a distinct database entity.
Primary Methods to Specify a Database from the Command Line
The most frequent way to interact with psql is via the terminal. PostgreSQL provides several flexible syntax options to ensure that users can connect to the correct environment regardless of their scripting requirements or manual entry preferences.
Using the -d or --dbname Flag
The -d flag is the most explicit way to tell psql which database you intend to use. In 2026, this remains the standard for shell scripts and automated deployment pipelines because of its clarity.
When you use psql -d sales_records, you are explicitly identifying the target. This flag is versatile; it does not only accept a simple name. It can also accept a "connection string," which allows you to bundle the database name with other parameters like the host, port, and user. For example, psql -d "dbname=sales_records host=localhost user=admin" is a valid way to use the flag to encapsulate the entire connection context.
The Positional Argument Shortcut
If you prefer brevity, psql allows you to specify the database name as a positional argument. If you type psql sales_records, the utility assumes that the final argument provided (which is not attached to another flag) is the database name.
While convenient for manual typing, this method carries risks in complex commands. If you accidentally misplace other flags or parameters, the shell might misinterpret the database name as a different argument. For this reason, senior technical editors recommend using the explicit -d flag in any script that will be executed in a production or shared environment.
Connection URIs (PostgreSQL URLs)
As of 2026, the use of Connection URIs has become the industry favorite for cloud-based PostgreSQL instances, such as those hosted on AWS, GCP, or Azure. Instead of separate flags for host, port, and database, you can provide a single string that follows the standard URI format:
psql postgresql://username:password@hostname:port/database_name
In this format, the database name is the path element following the final forward slash. This method is highly effective because it is the same format used by most application drivers (Node.js, Python, Go, etc.), making it easy to copy connection strings from application configuration files directly into the terminal for troubleshooting.
Specifying and Switching Databases Within the psql Shell
Often, you may already be inside an active psql session and realize you need to move to a different database within the same cluster. You do not need to exit the program and restart it to change your context.
The \c or \connect Meta-Command
The internal command \c (short for \connect) allows you to switch databases instantly. The syntax is:
\c database_name
When you execute this, psql terminates the current connection and establishes a new one. This is particularly useful when you are performing administrative tasks across multiple databases, such as checking table sizes in marketing_db and then immediately comparing them to sales_db.
The \connect command is more powerful than it initially appears. You can specify a new user, host, or port as well:
\c database_name username hostname port
If you wish to remain on the same database but change your user identity (for example, to test permissions), you can use a hyphen as a placeholder for the database name: \c - new_user.
Utilizing Environment Variables for Default Specification
For developers who work predominantly on a single project, typing the database name repeatedly is inefficient. PostgreSQL respects several environment variables that can "pre-specify" your database.
The PGDATABASE Variable
If the PGDATABASE environment variable is set in your shell profile (e.g., .bashrc or .zshrc), psql will use its value as the default database whenever you run the command without flags.
Setting export PGDATABASE=dev_workspace allows you to simply type psql and be automatically dropped into the dev_workspace environment. This is a common practice in local development to reduce cognitive load, though it should be used cautiously when moving between different projects to avoid running commands on the wrong database by mistake.
The .pgpass File for Seamless Authentication
While not directly used to "name" the database in the command line, the .pgpass file is essential for specifying database-specific credentials. This file allows you to store passwords securely so that when you specify a database via -d, psql can automatically retrieve the correct password without prompting you.
The format for the .pgpass file is: hostname:port:database:username:password
By including the database name in this file, you ensure that your connections to high-security databases are both specific and automated.
Comparison of Methods to Specify a Database
| Method | Syntax Example | Best Use Case |
|---|---|---|
| Command Flag | psql -d my_db | Shell scripts and explicit documentation. |
| Positional Argument | psql my_db | Fast, manual entry during local development. |
| Connection URI | psql postgresql://host/my_db | Cloud environments and copy-pasting from app configs. |
| Internal Meta-command | \c my_db | Switching contexts without exiting the current session. |
| Env Variable | export PGDATABASE=my_db | Long-term projects with a single primary database. |
| Connection Service File | psql service=my_app_service | Complex enterprise environments with many parameters. |
Handling Special Characters and Case Sensitivity
One of the most common pitfalls when specifying a database name in psql is dealing with case sensitivity or special characters. By default, PostgreSQL folds unquoted identifiers to lowercase.
If your database is named MyDatabase (with capital letters), simply typing psql -d MyDatabase may result in an error because psql will look for mydatabase. To resolve this, you must use double quotes around the database name within the shell command:
psql -d "MyDatabase"
Note that depending on your operating system's shell (Bash, Zsh, or PowerShell), you might need to escape these quotes to ensure they are passed literally to the psql utility: psql -d ' "MyDatabase" '.
Common Errors When Specifying a Database
When attempting to specify a database, you may encounter several standard errors. Understanding these is the key to rapid troubleshooting in 2026 environments.
- FATAL: database "xyz" does not exist: This indicates a typo or that the database was dropped. It can also occur if you are connecting to the wrong host where that specific database hasn't been created yet.
- FATAL: role "user" does not exist: This often happens when you specify a database correctly but fail to specify a valid user, causing psql to default to your OS username, which might not be a recognized role in the database.
- Connection Refused: This usually means you have specified the database correctly, but the host or port parameters are pointing to a location where PostgreSQL is not listening, or a firewall is blocking the request.
- FATAL: no pg_hba.conf entry for host: This is a security error. Even if you specify the database name correctly, the server's configuration file must explicitly allow your user to connect to that specific database from your current IP address.
Frequently Asked Questions
What happens if I don't specify a database in psql?
If no database is specified, psql looks for an environment variable named PGDATABASE. If that is not set, it attempts to connect to a database with the same name as your current operating system login name. If neither exists, the connection will fail unless you are connecting to a server where a default postgres database is available and you specifically target it.
Can I specify multiple databases in a single psql command?
No, a single psql process can only be connected to one database at a time. To interact with multiple databases simultaneously, you must either open multiple terminal windows or use the \c command within a single session to switch between them sequentially.
How do I specify a database when using psql to run a SQL file?
You use the same flags. To run a script against a specific database, the syntax is psql -d database_name -f script.sql. This ensures that all commands within script.sql are executed in the context of the specified database.
Is there a limit to the length of the database name I can specify?
PostgreSQL typically has a limit of 63 bytes for identifiers, which includes database names. When specifying the name in psql, ensure it falls within this limit, or it may be truncated, leading to "database not found" errors.
How do I specify a database on a different port?
The database name is specified with -d, and the port is specified with -p. For example: psql -h localhost -p 5433 -d my_database. You can also combine them in a URI: psql postgresql://localhost:5433/my_database.
Practical Recommendations for 2026
When working with PostgreSQL in 2026, the recommended best practice for specifying a database is to use Connection URIs for all remote or cloud-based work. This approach reduces the chance of mismatching host and database parameters. For local development, setting the PGDATABASE environment variable for your active project is the most efficient way to work, provided you have a visual indicator in your terminal prompt (like a git-style branch indicator) to remind you which database context is currently active.
Always verify your current connection context after entering the shell by looking at the prompt—which usually displays database_name=#—or by typing the \conninfo command to see the current database, user, and connection method.
Image Gallery
Select an image to open it in a new tab.
Felix Mercer
Transnational Learning Reporter
