PostgreSQL – SQL Shell – psql

PostgreSQL SQL Shell (psql)

psql is PostgreSQL’s interactive command-line client. It lets you connect to a PostgreSQL server, run SQL statements, inspect databases and tables, execute SQL files, and use psql-specific commands.

On Windows, PostgreSQL installations commonly include a shortcut named SQL Shell (psql). On Linux and macOS, you usually start psql from a terminal.

Open PostgreSQL SQL Shell on Windows

Open the Start menu, locate the PostgreSQL program group, and select SQL Shell (psql). The shell prompts for the server, database, port, username, and password needed for the connection.

Enter the PostgreSQL Server Host in psql

At the Server prompt, enter the hostname or IP address of the machine running PostgreSQL.

• Press Enter to accept localhost when PostgreSQL runs on the same computer.
• Enter a hostname such as db.example.com when connecting to another server.
• Enter an IP address when the server is identified by its network address.

A remote PostgreSQL server must be configured to accept network connections from your computer. Its firewall and PostgreSQL authentication rules must also permit the connection.

Select the PostgreSQL Database in SQL Shell

At the Database prompt, enter the name of the database to open. A standard PostgreSQL installation normally includes a database named postgres, which is commonly used for administrative connections.

You may press Enter to accept the displayed default, or type the name of another existing database. In this example, the database is named mydb.

Enter the PostgreSQL Port Number

At the Port prompt, specify the TCP port used by the PostgreSQL server. The default PostgreSQL port is 5432.

Press Enter to use the displayed default. Enter a different port only when the server was configured to listen on another port.

Enter the PostgreSQL Username

At the Username prompt, enter the PostgreSQL role used for authentication. Many local installations create an administrative role named postgres.

For regular application work, connect with a role that has only the required permissions instead of using the administrative postgres role.

Enter the PostgreSQL Password

Enter the password assigned to the selected PostgreSQL role and press Enter. The password is normally not displayed while you type.

If the host, database, port, username, and password are valid, psql opens an interactive session connected to the selected database.

The prompt usually contains the current database name. A prompt such as mydb=# indicates that the session is connected to mydb. The exact prompt symbol can vary according to the connected role and command state.

Connect to PostgreSQL with the psql Command

You can provide the connection settings directly in a terminal instead of answering the interactive SQL Shell prompts.

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

The command options are:

• -h: PostgreSQL server host
• -p: PostgreSQL server port
• -U: PostgreSQL username or role
• -d: database name

For a local server using default connection settings, a shorter command may be sufficient.

psql -U postgres -d mydb

Run SQL Statements in PostgreSQL psql

After connecting, type a SQL statement and end it with a semicolon. The following query displays the PostgreSQL server version.

SELECT version();

You can also confirm the current database and connected user.

SELECT current_database(), current_user;

If a SQL statement spans multiple lines, psql waits until it reaches a terminating semicolon. Press Ctrl+C to cancel an unfinished command.

Useful PostgreSQL psql Meta-Commands

Commands beginning with a backslash are psql meta-commands. They are processed by psql rather than by the PostgreSQL server and do not require a semicolon.

\l
\c mydb
\dt
\d table_name
\du
\conninfo
\?

• \l lists available databases.
• \c mydb connects to the database named mydb.
• \dt lists visible tables.
• \d table_name displays the structure of a table or another relation.
• \du lists PostgreSQL roles.
• \conninfo displays information about the current connection.
• \? opens help for psql meta-commands.

Run a PostgreSQL SQL File with psql

Use the -f option to execute SQL statements stored in a file.

psql -U postgres -d mydb -f schema.sql

From inside an active psql session, use \i to read and execute a file.

\i schema.sql

Use an absolute path or start psql from the directory containing the file when the file cannot be found.

Exit PostgreSQL SQL Shell

Enter the following psql meta-command to close the current session.

\q

You can also use the terminal’s end-of-input shortcut, although \q is the clearest way to exit psql.

Fix Common PostgreSQL psql Connection Errors

psql Is Not Recognized as a Command

This message usually means that the PostgreSQL bin directory is not included in the operating system’s PATH. Run psql from the PostgreSQL bin directory or add that directory to PATH.

Connection Refused on Port 5432

Confirm that the PostgreSQL service is running and that the host and port are correct. For remote connections, also check the firewall, the server’s listening configuration, and its client authentication rules.

Password Authentication Failed for the PostgreSQL User

Verify the username and password. PostgreSQL role names and operating-system account names are separate unless the server’s authentication setup intentionally maps them.

PostgreSQL Database Does Not Exist

Connect to an existing database, such as postgres, and use \l to list the databases available to the current role. Then reconnect with the correct database name.

PostgreSQL SQL Shell Questions

What is the difference between PostgreSQL and psql?

PostgreSQL is the database server. psql is a command-line client used to connect to that server and submit SQL statements or psql commands.

What is the default PostgreSQL psql port?

The default PostgreSQL TCP port is 5432. Use the port configured on the server when it differs from the default.

How do I see which PostgreSQL database psql is connected to?

Run \conninfo to display the current connection details, or execute SELECT current_database();.

Do psql backslash commands need a semicolon?

No. psql meta-commands such as \l, \dt, and \q are entered without a semicolon. SQL statements normally end with a semicolon.

PostgreSQL psql Connection Summary

To open PostgreSQL SQL Shell, provide the server host, database name, port, username, and password. After connecting, use SQL statements for database operations and backslash commands for psql tasks such as listing databases, changing connections, inspecting tables, and exiting the shell. In this PostgreSQL Tutorial, we learned how to open SQL Shell, connect to PostgreSQL, run commands, execute SQL files, and troubleshoot common psql connection problems.