PostgreSQL – SELECT Database

PostgreSQL – SELECT Database or Connect to a Database

PostgreSQL does not provide a SQL statement named USE database for switching databases inside an existing session. To work with a different database, you connect to that database directly. In the PostgreSQL command-line client, psql, you can change the current connection with the \connect command or its shorter form, \c.

This tutorial explains how to select a PostgreSQL database in psql, connect with a specific user or host, confirm the active database, and open the selected database in pgAdmin 4.

How PostgreSQL Selects the Current Database

A PostgreSQL server can contain multiple databases. Each client session is connected to one database at a time. SQL queries in that session can directly access objects in the connected database, but they cannot directly query tables in another database through ordinary schema-qualified names.

To use another database, establish a new connection to it. In psql, the \c command closes the current connection and reconnects using the requested database and connection settings.

Select a PostgreSQL Database using psql

To select a database or make a connection to the database, run the select/connect command as shown below.

 \c databasename

where databasename is the name of your database.

For example, to connect to a database named mydb, enter the following command at the psql prompt:

\c mydb

When the connection succeeds, psql displays a confirmation message containing the database name and user name.

You are now connected to database "mydb" as user "postgres".

A connection has been made to the database mydb. The database name also appears in the psql prompt. Depending on whether a statement is complete, the prompt may appear as mydb=# or as a continuation prompt such as mydb-#.

Connect to a PostgreSQL Database with a Different User

You can include the PostgreSQL role name when changing the connection. Use the database name first and the user name second.

\c mydb app_user

If password authentication is required, psql prompts for the password of the specified role.

Connect to a PostgreSQL Database on Another Host or Port

The full \connect form can include a database name, user, host, and port. This is useful when the target database is on another PostgreSQL server or listens on a non-default port.

\connect database_name user_name host_name port_number

For example:

\connect mydb app_user localhost 5432

You can also start psql and connect to a database in one command from the terminal:

psql -h localhost -p 5432 -U app_user -d mydb

Check the Current PostgreSQL Database Connection

After connecting, verify the active database before running schema changes or data-modification statements.

Run \conninfo in psql to display the current database, user, host, port, and connection details:

\conninfo

You can also use PostgreSQL functions in an SQL query:

SELECT
    current_database() AS database_name,
    current_user AS user_name;

The result confirms which database and role are active in the current session.

List PostgreSQL Databases Before Connecting

If you do not know the exact database name, list the available databases from psql before using \c.

\l

The longer equivalent is:

\list

The output includes database names, owners, encodings, locale settings, and access privileges. You can connect only if the PostgreSQL role has permission to access the target database.

Select a PostgreSQL Database using pgAdmin 4

You can also select the database and open SQL Query window in pgAdmin UI.

Step 1: In the Browser panel, expand the registered server and then expand Databases. Select the database by clicking its name.

Step 2: Click the Tools drop-down menu and select Query Tool. You can also right-click the database and open the Query Tool from the context menu when that option is available.

Step 3: The Query Tool opens with a connection to the selected database. Check the displayed database name before executing SQL statements.

You can confirm the pgAdmin Query Tool connection by running:

SELECT current_database();

Why PostgreSQL Does Not Support USE Database

Some database systems provide a USE database_name SQL command. PostgreSQL uses a different connection model: the database is chosen when the client connects. Therefore, the following statement is not valid PostgreSQL syntax:

USE mydb;

Use \c mydb inside psql, open a Query Tool for mydb in pgAdmin 4, or establish a new application connection whose database parameter is set to mydb.

Troubleshoot PostgreSQL Database Connection Errors

Database Does Not Exist

If PostgreSQL reports that the database does not exist, check its spelling and letter case. Unquoted PostgreSQL identifiers are normally folded to lowercase. Use \l to verify the exact available database names.

Permission Denied for Database

A role needs the CONNECT privilege on a database. If the database exists but access is denied, connect with an authorized role or ask the database administrator to review the database privileges.

Password Authentication Failed

Confirm the PostgreSQL user name, password, host, and port. Also verify that the server authentication configuration permits the requested connection method.

Connection Refused

A refused connection commonly means that PostgreSQL is not running, is not listening on the requested address or port, or is blocked by network or firewall settings. Confirm the server status and connection parameters before retrying.

PostgreSQL SELECT Database FAQs

How do I switch databases in PostgreSQL?

In psql, run \c database_name. In pgAdmin 4, select the database and open its Query Tool. Applications must open a new connection configured for the target database.

Can I use USE database_name in PostgreSQL?

No. PostgreSQL does not support the USE statement. The database is selected as part of the client connection.

How do I find the current PostgreSQL database?

Run SELECT current_database(); in SQL. In psql, you can also run \conninfo.

Can one PostgreSQL query access tables in another database?

Not through ordinary database and schema qualification. A session connects to one database at a time. Cross-database access requires an additional mechanism, such as a foreign data wrapper or another explicitly configured connection method.

PostgreSQL Database Connection QA Checklist

• Confirm that every psql example distinguishes meta-commands such as \c from SQL statements.
• Verify that database names, role names, hosts, and ports in examples are clearly identified as placeholders or sample values.
• Check that the tutorial does not imply PostgreSQL supports the USE database statement.
• Confirm that the pgAdmin 4 steps open the Query Tool from the intended database rather than only selecting the server.
• Test current_database() and \conninfo examples against a working PostgreSQL connection.
• Review connection-error guidance for database existence, CONNECT privilege, authentication, server status, host, and port.

Connect to the Required PostgreSQL Database

To work with a specific PostgreSQL database, connect to it with \c database_name in psql or open the Query Tool for that database in pgAdmin 4. Before executing queries that change data or database objects, verify the connection with current_database() or \conninfo.

Continue with the PostgreSQL Tutorial to learn database creation, table management, SQL queries, constraints, backup, and restore.