PostgreSQL psql Backslash Commands: A Practical Guide

When working with PostgreSQL from the terminal, psql provides special commands that begin with a backslash, such as \l, \dt, and \d. These are called meta-commands or backslash commands; they are processed by the psql client rather than sent to the PostgreSQL server as SQL. postgresql

This guide explains the most useful commands for viewing databases, tables, schemas, users, permissions, query results, and configuration.

Getting started

Connect to PostgreSQL from your terminal:

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

Where:

• -U postgres specifies the PostgreSQL user.
• -d postgres specifies the database.
• -h localhost specifies the server host.
• -p 5432 specifies the port.

On Linux, you may also connect as the operating-system postgres user:

sudo -u postgres psql

A successful connection displays a prompt similar to:

postgres=#

The prompt changes when you connect to another database:

myapp_db=#

Help and exiting

Show all psql commands

\?

This displays the complete list of available psql meta-commands.

You can also display help for a category:

\? commands
\? options
\? variables

Get help for SQL commands

\h

Show the syntax for a specific SQL command:

\h CREATE TABLE
\h ALTER USER
\h GRANT

Exit psql

\q

You can also press Ctrl+D on Linux and macOS.

Databases and connections

List all databases

\l

Example output:

   Name    |  Owner   | Encoding | Collate |  Ctype  | Access privileges
-----------+----------+----------+---------+---------+-------------------
 myapp_db  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |

Show additional information such as database size:

\l+

Connect to another database

\c myapp_db

You can specify both a database and a user:

\c myapp_db app_user

For a remote database:

\c myapp_db app_user db.example.com 5432

Show the current connection

\conninfo

Example:

You are connected to database "myapp_db" as user "app_user" on host "localhost" at port "5432".

Show or change client encoding

\encoding

Set UTF-8 encoding:

\encoding UTF8

Schemas and tables

List schemas

\dn

Show schemas with descriptions and access privileges:

\dn+

List tables

\dt

This lists tables visible through the current search_path.

List tables in all schemas:

\dt *.*

List tables in a particular schema:

\dt myapp.*

Show extra information, such as table size:

\dt+

Describe a table

\d users

This usually shows:

• Column names.
• Data types.
• Nullable status.
• Default values.
• Indexes.
• Primary keys.
• Foreign keys.
• Constraints.

For a table in a specific schema:

\d myapp.users

Show more detailed information:

\d+ myapp.users

Example output:

                                      Table "myapp.users"
 Column |           Type           | Collation | Nullable |              Default
--------+--------------------------+-----------+----------+-----------------------------------
 id     | bigint                   |           | not null | generated by default as identity
 email  | text                     |           | not null |
 name   | text                     |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

List all relations

\d

This can show tables, views, sequences, and other relation-like objects.

Views, indexes, sequences, and functions

List views

\dv
\dv+

Describe a view:

\d myapp.active_users

Show the SQL definition of a view:

\sv myapp.active_users

List materialized views

\dm
\dm+

List indexes

\di
\di+

Describe indexes for a table:

\d myapp.users

List sequences

\ds
\ds+

List functions

\df
\df+

List functions matching a pattern:

\df *email*

Describe a particular function:

\df myapp.calculate_total

Users, roles, and permissions

List users and roles

\du

Show additional role information:

\du+

Example output:

 Role name  | Attributes | Member of
------------+------------+-----------
 app_user   |            | {}
 postgres   | Superuser  | {}

Show table privileges

\dp

You can also use:

\z

Show privileges for a specific table:

\dp myapp.users

Example:

                              Access privileges
 Schema | Name  | Type  | Access privileges
--------+-------+-------+-------------------
 myapp  | users | table | app_user=arwdDxt/app_owner

The privilege letters generally represent:

• r: SELECT
• a: INSERT
• w: UPDATE
• d: DELETE
• D: TRUNCATE
• x: REFERENCES
• t: TRIGGER

Show object descriptions

\dd

Show the description of a specific object:

\dd myapp.users

Viewing table data

Backslash commands show metadata. To view actual rows, use SQL.

Display all rows:

SELECT * FROM myapp.users;

Display selected columns:

SELECT id, email, name
FROM myapp.users;

Limit the result:

SELECT *
FROM myapp.users
LIMIT 10;

Sort the result:

SELECT id, email, created_at
FROM myapp.users
ORDER BY created_at DESC
LIMIT 10;

Count rows:

SELECT COUNT(*)
FROM myapp.users;

The SQL statement must end with a semicolon:

SELECT * FROM myapp.users;

A psql meta-command such as \dt normally does not require a semicolon:

\dt

Improving query output

Toggle expanded output

Normal output is displayed horizontally:

SELECT * FROM myapp.users LIMIT 1;

For wide rows, use expanded output:

\x
SELECT * FROM myapp.users LIMIT 1;

Toggle it back:

\x

You can let psql decide automatically:

\x auto

Show query execution time

\timing on

Now each query displays its execution time.

Disable it with:

\timing off

Show only rows without headers

\t

Run a query:

SELECT email FROM myapp.users;

Toggle tuple-only output off:

\t

Change the output format

Use expanded output:

\x on

Use unaligned output, which is useful for scripts:

\a

Set a particular output format:

\pset format aligned
\pset format unaligned
\pset format csv

Set a display value for NULL:

\pset null '(none)'

Running SQL files

Execute a SQL file

\i /path/to/schema.sql

Example:

\i /home/admin/migrations/001_create_users.sql

For a file relative to the current script:

\ir migrations/001_create_users.sql

This is useful for running database setup scripts and migrations.

Redirect output to a file

\o query_output.txt
SELECT * FROM myapp.users;
\o

The first \o starts writing output to the file. The second \o returns output to the terminal.

Export data with \copy

Export a query result to CSV:

\copy (
  SELECT id, email, name
  FROM myapp.users
) TO '/tmp/users.csv' WITH CSV HEADER

Import CSV data into a table:

\copy myapp.users(email, name)
FROM '/tmp/users.csv'
WITH CSV HEADER

\copy reads and writes files on the machine running the psql client, which is different from server-side SQL COPY.

Query history and editing

Show command history

\history

You can also use:

\s

Save history to a file:

\s /tmp/psql-history.txt

Clear the current query buffer

\r

This is useful if you started writing a SQL command but no longer want to execute it.

Edit the current query

\e

This opens the current query buffer in your configured text editor.

You can set an editor before launching psql:

export EDITOR=nano
psql -U postgres -d myapp_db

Execute the query buffer

\g

This runs the SQL currently stored in the query buffer.

Shell and file-system commands

Run an operating-system command

On Linux or macOS:

\! pwd
\! ls

On Windows:

\! cd
\! dir

Change the client working directory

\cd /tmp

Check the current client working directory:

\! pwd

This affects local file commands such as \i and \copy.

Variables and prompts

List psql variables

\set

Set a variable:

\set environment 'development'

Display it:

\echo :environment

Unset it:

\unset environment

Variables can be used in SQL:

\set user_id 10

SELECT *
FROM myapp.users
WHERE id = :user_id;

For string values, quote the value appropriately:

\set email '''[e-mail]'''

SELECT *
FROM myapp.users
WHERE email = :email;

For application scripts, use parameterized queries rather than manually constructing SQL strings.

Automatic query execution

Repeat a query

Run a query every five seconds:

SELECT COUNT(*) FROM myapp.users
\watch 5

Stop the repeating query with:

Ctrl+C

Execute query output as SQL

SELECT 'CREATE TABLE test(id integer);'
\gexec

\gexec executes each value returned by the query as SQL. Use it carefully, especially in production.

A complete inspection workflow

The following sequence is useful when investigating a database:

-- Show the current connection
\conninfo

-- List all databases
\l

-- Connect to the application database
\c myapp_db

-- List schemas
\dn+

-- List tables in the application schema
\dt myapp.*

-- Describe a table
\d+ myapp.users

-- List views
\dv myapp.*

-- List indexes
\di myapp.*

-- List sequences
\ds myapp.*

-- List users and roles
\du+

-- Show table privileges
\dp myapp.users

-- Show query execution time
\timing on

-- Inspect some rows
SELECT *
FROM myapp.users
LIMIT 10;

Common mistakes

Using a backslash command outside psql

This will not work directly in a normal shell:

\dt

Start psql first:

psql -U app_user -d myapp_db

Then run:

\dt

Adding a semicolon incorrectly

Use:

\dt

Not:

\dt;

SQL commands, however, require a semicolon:

SELECT * FROM myapp.users;

Expecting tables from another database

Tables belong to a specific database. First connect to the correct database:

\c myapp_db
\dt

Not seeing a table

The table may be in another schema. Try:

\dt *.*

Or describe it with its full name:

\d myapp.users

You may also lack the privileges required to see or access it.

Quick reference

Command	Purpose	Example
\l	List databases	\l
\c	Connect to a database	\c myapp_db
\conninfo	Show connection details	\conninfo
\dn	List schemas	\dn
\dt	List tables	\dt myapp.*
\d	Describe an object	\d myapp.users
\dv	List views	\dv
\di	List indexes	\di
\ds	List sequences	\ds
\df	List functions	\df
\du	List users and roles	\du
\dp or \z	Show privileges	\dp myapp.users
\x	Toggle expanded output	\x auto
\timing	Toggle query timing	\timing on
\i	Execute a SQL file	\i setup.sql
\copy	Import or export CSV	\copy users TO 'users.csv' CSV
\e	Edit the query buffer	\e
\r	Clear the query buffer	\r
\?	Show psql help	\?
\h	Show SQL help	\h CREATE TABLE
\q	Exit psql	\q

The most important commands to remember are:

\l
\c database_name
\dn
\dt
\d table_name
\du
\dp
\conninfo
\?
\q

For the authoritative and version-specific list, run \? inside psql; available commands can vary slightly between PostgreSQL client versions. postgresql