PLSQL Cheat Sheet

General

\copyright show PostgreSQL usage and distribution terms

\g [FILE] or ; execute query (and send results to file or |pipe)

\h [NAME] help on syntax of SQL commands, * for all commands

\q quit psql

Query Buffer

\e [FILE] edit the query buffer (or file) with external editor

\ef [FUNCNAME] edit function definition with external editor

\p show the contents of the query buffer

\r reset (clear) the query buffer

\s [FILE] display history or save it to file

\w FILE write query buffer to file

Input/Output

\copy ... perform SQL COPY with data stream to the client host

\echo [STRING] write string to standard output

\i FILE execute commands from file

\o [FILE] send all query results to file or |pipe

\qecho [STRING] write string to query output stream (see \o) Informational  (options: S = show system objects, + = additional detail)

\d[S+] list tables, views, and sequences

\d[S+] NAME describe table, view, sequence, or index

\da[S] [PATTERN] list aggregates

\db[+] [PATTERN] list tablespaces

\dc[S] [PATTERN] list conversions

\dC [PATTERN] list casts

\dd[S] [PATTERN] show comments on objects

\ddp [PATTERN] list default privileges

\dD[S] [PATTERN] list domains

\des[+] [PATTERN] list foreign servers

\deu[+] [PATTERN] list user mappings

\dew[+] [PATTERN] list foreign-data wrappers

\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions

\dF[+] [PATTERN] list text search configurations

\dFd[+] [PATTERN] list text search dictionaries

\dFp[+] [PATTERN] list text search parsers

\dg[+] [PATTERN] list roles (groups)

\dFt[+] [PATTERN] list text search templates

\di[S+] [PATTERN] list indexes

\dl list large objects, same as \lo_list

\do[S] [PATTERN] list operators

\dp [PATTERN] list table, view, and sequence access privileges

\dr[S+] [PATTERN] list foreign tables

\drds [PATRN1 [PATRN2]] list per-database role settings

\ds[S+] [PATTERN] list sequences

\dt[S+] [PATTERN] list tables

\dT[S+] [PATTERN] list data types

\du[+] [PATTERN] list roles (users)

\dv[S+] [PATTERN] list views

\dx [PATTERN] list external tables

\l[+] list all databases

\z [PATTERN] same as \dp

Formatting

\a toggle between unaligned and aligned output mode

\C [STRING] set table title, or unset if none

\f [STRING] show or set field separator for unaligned query output

\H toggle HTML output mode

\pset NAME [VALUE] set table output option (NAME := {format | border | expanded | fieldsep | footer | null | numericlocale | recordsep | tuples_only | title | tableattr | pager})

\t [on|off] show only rows 

\T [STRING] set HTML <table> tag attributes, or unset if none

\x [on|off] toggle expanded output

Connection

\c[onnect] [DBNAME|- 

USER|- HOST|- PORT|-]

connect to new database

\encoding [ENCODING] show or set client encoding

\password [USERNAME] securely change the password for a user

\conninfo display information about current connection

Operating System

\cd [DIR] change the current working directory

\timing [on|off] toggle timing of commands

\! [COMMAND] execute command in shell or start interactive shell Variables

\prompt [TEXT] NAME prompt user to set internal variable

\set [NAME [VALUE]] set internal variable, or list all if no parameters

\unset NAME unset (delete) internal variable

Large Objects

\lo_export LOBOID FILE

\lo_import FILE [COMMENT]

\lo_list

\lo_unlink LOBOID large object operations