PostgreSQL - psql Shell Commands

PostgreSQL psql Shell Commands

psql is PostgreSQL’s interactive terminal. It lets you connect to a PostgreSQL server, run SQL statements, inspect databases and tables, manage query output, and execute psql-specific commands.

Commands that begin with a backslash, such as \d and \q, are psql meta-commands. They are processed by the psql client and do not require a terminating semicolon. Regular SQL statements, such as SELECT and CREATE TABLE, normally end with a semicolon.

Start the PostgreSQL psql Shell

When you start the interactive psql shell, you may be asked for the server address, database name, port, username, and password. You can accept the displayed defaults by pressing Enter where appropriate. A password may be required depending on the server’s authentication configuration.

You can also start psql from a terminal and provide the connection details as command-line options.

</>

Copy

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

In this command, -h specifies the host, -p specifies the port, -U specifies the PostgreSQL role, and -d specifies the database.

Essential psql Commands

Connect to a PostgreSQL Database with \c

</>

Copy

 \c databasename

The \c command, also available as \connect, connects the current psql session to another database. Provide the target database name after the command.

In the following example, the session is connected to a database named mydb. After the connection succeeds, SQL statements run against that database.

You may also specify a different user when changing the connection.

</>

Copy

\c database_name user_name

List PostgreSQL Databases with \l

Use \l or \list to display the databases available on the PostgreSQL server, along with details such as the owner, encoding, locale, and access privileges.

</>

Copy

\l

To display additional details, use the extended form:

</>

Copy

\l+

Show the Current psql Connection with \conninfo

The \conninfo command shows the current database, authenticated user, host, and port. It is useful when you work with multiple PostgreSQL servers or databases.

</>

Copy

\conninfo

List Available Relations with \d

</>

Copy

\d

The \d command lists visible relations in the current database. PostgreSQL relations include objects such as tables, views, materialized views, sequences, and indexes.

In the following example, \d is executed while connected to mydb. The result includes the schema, relation name, relation type, and owner.

psql command - describe available relations

For a more focused list, use the following psql commands:

\dt lists tables.
\dv lists views.
\ds lists sequences.
\di lists indexes.
\df lists functions.

Describe a PostgreSQL Table with \d table_name

</>

Copy

 \d relationname

While \d lists relations, \d relationname displays the structure of a specific relation. For a table, the result can include columns, data types, nullability, default values, indexes, constraints, and referenced relationships.

In the following example, the command is run for a table.

Use \d+ for additional information, such as table size, storage method, and descriptions when available.

</>

Copy

\d+ public.employees

List PostgreSQL Schemas with \dn

Use \dn to list schemas in the current database. Schemas organize database objects and allow objects with the same name to exist in separate namespaces.

</>

Copy

\dn

List PostgreSQL Roles and Users with \du

The \du command lists PostgreSQL roles and their attributes. PostgreSQL manages database users through roles, so this output can show login permission, superuser status, role creation privileges, and memberships.

</>

Copy

\du

Run SQL Statements in the psql Shell

You can enter regular PostgreSQL SQL statements directly at the psql prompt. Unlike backslash commands, SQL statements generally require a semicolon to execute.

</>

Copy

SELECT current_database(), current_user;

A statement can span multiple lines. psql waits until it receives a complete statement terminated by a semicolon.

</>

Copy

SELECT employee_id,
       employee_name
FROM employees
ORDER BY employee_name;

If you start typing an SQL statement and want to discard the unfinished input, use \r to reset the query buffer.

psql Help Commands for Meta-Commands and SQL Syntax

Display the psql Command List with \?

</>

Copy

\?

The \? command displays help for psql meta-commands. Use it when you need to find a command without leaving the psql session.

The help output may open in a pager when it is longer than the terminal window. Press Space to move forward by one page, Enter to move forward by one line, or q to exit the pager and return to the psql prompt.

Display PostgreSQL SQL Help with \h

</>

Copy

\h

The \h command lists SQL commands for which syntax help is available. To display help for one SQL statement, place its name after \h.

</>

Copy

\h CREATE TABLE

Control psql Query Output and Display

Enable Expanded Output with \x

The \x command toggles expanded display. Expanded output prints each column on a separate line, which is useful for wide tables or rows containing long values.

</>

Copy

\x

You can also ask psql to choose expanded output automatically when normal tabular output would not fit the screen.

</>

Copy

\x auto

Show Query Execution Time with \timing

The \timing command toggles reporting of the time taken by each SQL statement. This is useful for basic query testing, although detailed performance analysis should use tools such as EXPLAIN and EXPLAIN ANALYZE.

</>

Copy

\timing

Save psql Query Results to a File with \o

Use \o followed by a filename to redirect subsequent query output to a file. Run \o without a filename to send output back to the terminal.

</>

Copy

\o query-results.txt
SELECT * FROM employees;
\o

Execute SQL Files and Use Command History in psql

Execute a PostgreSQL SQL Script with \i

The \i command reads and executes commands from a file. It is useful for running schema definitions, seed data, reports, or migration scripts from an interactive psql session.

</>

Copy

\i /path/to/script.sql

From a system terminal, the same type of script can be executed with the -f option.

</>

Copy

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

Review and Reuse psql Command History

Use \s to display the current psql command history. You can also provide a filename to save the history.

</>

Copy

\s
\s psql-history.txt

Use \p to display the current query buffer and \g to send the current buffer to the PostgreSQL server for execution.

Quit the PostgreSQL psql Shell with \q

</>

Copy

\q

The \q command exits the PostgreSQL psql shell and returns control to the operating system terminal.

Common psql Commands Reference

psql command	Purpose
`\c database_name`	Connect to another PostgreSQL database.
`\conninfo`	Show current connection information.
`\l`	List databases.
`\d`	List visible relations.
`\dt`	List tables.
`\d table_name`	Describe a table or another relation.
`\dn`	List schemas.
`\du`	List PostgreSQL roles.
`\df`	List functions.
`\?`	Show help for psql meta-commands.
`\h SQL_COMMAND`	Show syntax help for an SQL command.
`\x`	Toggle expanded query output.
`\timing`	Toggle statement execution timing.
`\i file.sql`	Execute commands from an SQL file.
`\q`	Exit psql.

PostgreSQL psql Command FAQs

What is the difference between a psql command and an SQL command?

A psql meta-command begins with a backslash and is handled by the psql client. It does not normally use a semicolon. An SQL command is sent to the PostgreSQL server for execution and normally ends with a semicolon.

How do I list all tables in the current PostgreSQL database?

Run \dt to list tables visible through the current schema search path. To include tables from all schemas, use a pattern such as \dt *.*.

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

Run \conninfo to display the current database, user, host, and port. You can also execute SELECT current_database();.

Why does psql show a continuation prompt instead of running my query?

The statement may be incomplete or missing its terminating semicolon. Complete the statement and enter ;, or use \r to clear the current query buffer.

How do I exit a long psql help or query output screen?

If psql opened the output in a pager, press q to leave the pager. To exit the psql application itself, run \q.

Summary of PostgreSQL psql Shell Commands

The psql shell supports both SQL statements and backslash-prefixed meta-commands. Commands such as \c, \l, \d, \dt, \du, \?, \h, and \q cover common connection, inspection, help, and session-management tasks.

In this PostgreSQL Tutorial, we learned how to use useful psql commands, inspect PostgreSQL objects, run SQL scripts, control output, and exit the shell.