psql Command Reference: Essential PostgreSQL Command-Line Commands

The psql command-line tool is your primary way to interact with PostgreSQL. Whether you’re querying data, managing databases, or troubleshooting issues, you’ll rely on psql commands daily. This guide covers the essential commands you need to work efficiently.

Understanding psql Commands

psql commands start with a backslash (\) and don’t require a semicolon. SQL statements, by contrast, are typed without a backslash and must end with a semicolon.

-- This is an SQL statement (needs semicolon)
SELECT * FROM employees;

-- This is a psql command (no semicolon, starts with \)
\dt

When you type \d or \l, you’re using psql’s built-in commands. When you type SELECT or CREATE, you’re writing SQL that PostgreSQL executes.

Navigation & Connection Commands

List Databases: \l or \list

Shows all databases on your PostgreSQL server:

learning_db=# \l
                    List of databases
     Name      | Owner    | Encoding |   Collate   | Ctype
----------------+----------+----------+-------------+----------
 learning_db    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8
 postgres       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8
 template0      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8
 template1      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8
(4 rows)

The postgres database is PostgreSQL’s system database. template0 and template1 are templates used when creating new databases.

Connect to a Database: \c or \connect

Switch to a different database:

learning_db=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#

You can also specify a user:

postgres=# \c learning_db myusername
You are now connected to database "learning_db" as user "myusername".
learning_db=>

Notice the prompt changed from # to => because you’re no longer a superuser.

List Users/Roles: \du or \dg

View all users and roles in your PostgreSQL instance:

postgres=# \du
                        List of roles
 Role name |                         Attributes
-----------+----------------------------------------------------
 admin     | Superuser
 analyst   | Cannot login
 postgres  | Superuser, Create role, Create DB, Replication
 sarah     |

The Attributes column shows permissions:

  • Superuser: Unrestricted access
  • Cannot login: A role used for permission grouping only
  • Create role: Can create other roles
  • Create DB: Can create new databases

Information & Description Commands

Describe a Table: \d or \describe

View the structure of a specific table:

postgres=# \d employees
              Table "public.employees"
   Column   |         Type          | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
 emp_id     | integer               |           | not null |
 first_name | character varying(50) |           | not null |
 last_name  | character varying(50) |           | not null |
 email      | character varying(100)|           | not null |
 hire_date  | date                  |           | not null |
 dept_id    | integer               |           | not null |
 salary     | numeric(10,2)         |           | not null |
Indexes:
    "employees_pkey" PRIMARY KEY, btree (emp_id)
    "employees_email_key" UNIQUE, btree (email)
Constraints:
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)

This shows:

  • Column names and data types
  • Whether columns allow NULL values
  • Default values
  • Indexes and constraints

List All Tables: \dt

Show all tables in the current database:

postgres=# \dt
         List of relations
 Schema |     Name      | Type  | Owner
--------+---------------+-------+----------
 public | departments   | table | postgres
 public | employees     | table | postgres
 public | projects      | table | postgres
 public | salaries      | table | postgres
(4 rows)

List Views: \dv

Show all views (virtual tables based on queries):

postgres=# \dv
                List of relations
 Schema |        Name         | Type | Owner
--------+---------------------+------+----------
 public | employee_salaries   | view | postgres
 public | active_projects     | view | postgres
(2 rows)

List Functions: \df

View all functions available in the current database:

postgres=# \df get_*
                    List of functions
 Schema | Name      | Result data type | Argument data types
--------+-----------+------------------+---------------------
 public | get_emp   | integer          | character varying
 public | get_dept  | integer          | character varying
(2 rows)

View Index Information: \di

List all indexes in the current database:

postgres=# \di
         List of relations
 Schema |       Name        | Type  | Owner | Table
--------+-------------------+-------+-------+-----------
 public | departments_pkey  | index | postgres | departments
 public | employees_pkey    | index | postgres | employees
 public | employees_email   | index | postgres | employees
(3 rows)

Indexes speed up queries on frequently-searched columns.

Query & Formatting Commands

Get Help: \h or \help

View syntax for any SQL command:

postgres=# \h SELECT
Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    ...

This shows the exact syntax for SELECT, including all optional clauses.

Toggle Query Timing: \timing

Enable or disable display of execution time for queries:

postgres=# \timing
Timing is on.
postgres=# SELECT COUNT(*) FROM employees;
 count
-------
    30
(1 row)

Time: 1.234 ms

The 1.234 ms shows how long the query took. Very useful for understanding performance.

Toggle Expanded Output: \x

Switch between regular and expanded (vertical) output:

-- Regular output (default)
postgres=# SELECT * FROM employees LIMIT 1;
 emp_id | first_name | last_name |       email       | hire_date  | dept_id | salary
--------+------------+-----------+-------------------+------------+---------+----------
      1 | James      | Wilson    | james.wilson@...  | 2020-01-15 |       1 | 75000.00
(1 row)

-- Expanded output (after \x)
postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM employees LIMIT 1;
-[ RECORD 1 ]
emp_id     | 1
first_name | James
last_name  | Wilson
email      | [email protected]
hire_date  | 2020-01-15
dept_id    | 1
salary     | 75000.00

Expanded output is great for tables with many columns because it prevents line wrapping.

Set Output Format: \a, \t, \H

Change how results are displayed:

  • \a: Toggle between aligned (formatted) and unaligned output
  • \t: Toggle tuples only (removes headers and footers)
  • \H: Toggle HTML output (useful for piping to web tools)
-- Regular aligned output
postgres=# SELECT emp_id, first_name, salary FROM employees LIMIT 2;
 emp_id | first_name | salary
--------+------------+----------
      1 | James      | 75000.00
      2 | Mary       | 78000.00

-- After \t (tuples only)
postgres=# \t
Tuples only is on.
postgres=# SELECT emp_id, first_name, salary FROM employees LIMIT 2;
1|James|75000.00
2|Mary|78000.00

Tuples-only output is useful for scripting or importing data.

Change Pager: \pset

Control pagination and formatting:

postgres=# \pset border 2
Border style is 2.
postgres=# SELECT * FROM employees LIMIT 1;
+--------+------------+-----------+-------+------------+---------+----------+
| emp_id | first_name | last_name | email | hire_date  | dept_id | salary   |
+--------+------------+-----------+-------+------------+---------+----------+
|      1 | James      | Wilson    | ...   | 2020-01-15 |       1 | 75000.00 |
+--------+------------+-----------+-------+------------+---------+----------+

border 2 creates box drawing characters around your output.

File & Output Commands

View Query History: \s

Display your command history:

postgres=# \s
...
SELECT * FROM employees;
SELECT COUNT(*) FROM employees;
SELECT first_name, salary FROM employees WHERE salary > 80000;
\q

Save Output to File: \o

Redirect query output to a file:

postgres=# \o /tmp/employee_list.txt
postgres=# SELECT * FROM employees;
postgres=# \o

The first \o specifies the file. The second \o (without a filename) returns output to the terminal.

Execute a SQL File: \i or \include

Run SQL statements from a file:

postgres=# \i /path/to/script.sql

This is how you’d run the employee database setup script we created earlier:

postgres=# \i script/postgresql-employee-database.sql

Edit Queries in an External Editor: \e

Open your default text editor to write a multi-line query:

postgres=# \e
-- Opens your editor (vim, nano, VS Code depending on $EDITOR env variable)
-- Write your query, save, and exit
-- The query executes automatically

This is useful for complex, multi-line queries.

Database & Metadata Commands

Show Current Database: \c (without arguments)

Display the current database and user:

postgres=# \c
You are currently connected to database "postgres" as user "postgres" on host "localhost" port "5432".

List All Schema Objects: \d+

Detailed description of all tables, views, and other objects:

postgres=# \d+

Create an Emergency Database Backup: \g

Execute the last query again:

postgres=# SELECT COUNT(*) FROM employees;
 count
-------
    30
(1 row)

postgres=# \g
 count
-------
    30
(1 row)

This is quick shorthand for repeating recent queries.

Common Errors & Solutions

Error: “psql: command not found”

Problem: psql isn’t recognized as a command.

Solution: Add PostgreSQL’s bin directory to your PATH:

  • Windows: Add C:\Program Files\PostgreSQL\15\bin to environment variables
  • Mac: Run brew link postgresql@15
  • Linux: Reinstall PostgreSQL with your package manager

Verify: psql --version should work.

Error: “FATAL: role ‘postgres’ does not exist”

Problem: You’re trying to connect as a user that doesn’t exist.

Solution: You’ve likely lost database permission. Reinstall PostgreSQL or use the PostgreSQL super-installer recovery mode.

Error: “could not translate host name…”

Problem: Can’t connect to the server (wrong hostname or server is down).

Solution:

# Use explicit localhost
psql -h localhost -U postgres
# Or IP address
psql -h 127.0.0.1 -U postgres

Performance Tips

Use Expanded Display for Wide Tables When you have many columns, use \x to switch to expanded output. This prevents ugly line wrapping and makes results more readable.

\x
SELECT * FROM employees;

Enable Timing Early Use \timing to measure query performance as you learn. This builds intuition about what makes queries fast or slow.

Batch Multiple Statements Instead of running queries one by one, write them in a file and execute with \i. This is faster and helps you organize your work.

Use Keyboard Shortcuts

  • Ctrl+A: Move to start of line
  • Ctrl+E: Move to end of line
  • Ctrl+R: Search history backwards (like bash)
  • ↑/↓: Navigate command history

FAQ

Q: How do I clear the psql screen? A: Type clear (not a psql command, but works in your terminal):

postgres=# clear

Q: How do I count rows without seeing all the data? A: Use SELECT COUNT(*):

postgres=# SELECT COUNT(*) FROM employees;
 count
-------
    30

Q: How do I exit psql? A: Type \q:

postgres=# \q

Q: Can I run SQL files without entering psql? A: Yes, from your terminal:

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

Q: How do I see the exact column names and types for a table? A: Use \d tablename:

postgres=# \d employees

Q: What’s the difference between \l and \list? A: They’re identical shortcuts. PostgreSQL accepts both.

Next Steps

Now that you understand psql commands, you’re ready to learn about managing users and permissions. Check out the Creating Users & Roles guide to understand how PostgreSQL’s permission system works.

For authentication configuration, see PostgreSQL Basic Authentication.

Ready to write SQL queries? Head to the PostgreSQL SELECT Statement Complete Guide.