Cheatsheet - psql

Connecting to psql

Basic connection from your command line:

# Connect to a database with the same name as your current user
psql <database_name>

# Connect as a specific user
psql -U <username> -d <database_name>

# Connect to a database on a specific host and port
psql -h <host> -p <port> -U <username> -d <database_name>

# The -W flag will prompt for a password
psql -U <username> -d <database_name> -W

Meta-Commands (The "Backslash" Commands)

These commands are shortcuts for administrative tasks, executed directly within the psql prompt.

Help & Information

Command	Description
\?	Show help for all psql meta-commands.
\h [SQL_COMMAND]	Get help for a specific SQL command (e.g., \h CREATE TABLE).
\q	Quit the psql session.
\g	Execute the current query in the buffer.
\crosstabview	Pivot wide query results into a crosstab format.

Connection & Session

Command	Description
\c <dbname> [user]	Connect to a new database, optionally as a different user.
\conninfo	Display information about the current connection.
\l or \list	List all available databases on the server.
\password [user]	Change the password for the specified user (or current user).
\timing	Toggle the display of how long each query takes to execute.

Inspecting Database Objects (\d commands)

The \d family of commands is essential for exploring your database schema. Add a + for more detailed output (e.g., \dt+).

Command	Description
\dt [pattern]	List tables (use * as a wildcard for pattern).
\dv [pattern]	List views.
\di [pattern]	List indexes.
\ds [pattern]	List sequences.
\df [pattern]	List functions.
\dn [pattern]	List schemas.
\du [pattern]	List users and roles.
\d <object_name>	Describe a table, view, sequence, or index (shows columns, types, etc.).
\d+ <object_name>	Show a more detailed description of an object.

Query Buffer & History

Command	Description
\s [filename]	Display command history. If a filename is provided, saves it to that file.
\e	Edit the current query buffer in an external editor ($EDITOR).
\p	Print the current query buffer to the screen.
\r	Reset (clear) the query buffer.
\i <filename>	Execute psql commands from a file.
\watch [seconds]	Execute the current query buffer repeatedly (default is every 2 seconds).

Output Formatting (\pset commands)

Command	Description
\x	Toggle expanded table formatting mode (useful for wide results).
\a	Toggle between aligned and unaligned column output.
\H	Toggle HTML output format.
\pset format <format>	Set output format to unaligned, aligned, html, latex, csv, etc.
\pset border <num>	Set the table border style (0 = none, 1 = inner lines, 2 = full).
\pset title <text>	Set a title for any subsequent tables that are printed.
\pset null '<text>'	Set the string to be printed in place of a null value.

In-Terminal Shortcuts (Readline)

psql uses the Readline library, so standard Bash-like shortcuts work.

Shortcut	Description
Up Arrow / Ctrl+P	Go to the previous command in history.
Down Arrow / Ctrl+N	Go to the next command in history.
Ctrl+R	Reverse-search through command history.
Ctrl+A	Move cursor to the beginning of the line.
Ctrl+E	Move cursor to the end of the line.
Ctrl+W	Delete the word to the left of the cursor.
Ctrl+U	Delete from the cursor to the beginning of the line.
Ctrl+K	Delete from the cursor to the end of the line.
Ctrl+L	Clear the screen.
Tab	Autocomplete SQL keywords, table names, column names, etc.

Customizing psql

You can customize your psql environment by creating a .psqlrc file in your home directory (~/.psqlrc). This file is executed every time you start psql.

Example ~/.psqlrc file:

-- Set the prompt to show user, host, and database name
\set PROMPT1 '%n@%m:%> %/ '

-- Turn on expanded display for better readability of wide tables
\x

-- Show how long each query takes to run
\timing

-- A friendly message on startup
\echo 'psql connected. Happy querying!'