psql command

General

• \bind [PARAM]... set query parameters
• \copyright show PostgreSQL usage and distribution terms
• \crosstabview [COLUMNS] execute query and display result in crosstab
• \errverbose show most recent error message at maximum verbosity
• \g [(OPTIONS)] [FILE] execute query (and send result to file or |pipe); \g with no arguments is equivalent to a semicolon
• \gdesc describe result of query, without executing it
• \gexec execute query, then execute each value in its result
• \gset [PREFIX] execute query and store result in psql variables
• \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
• \q quit psql
• \watch [[i=]SEC] [c=N] [m=MIN] execute query every SEC seconds, up to N times, stop if less than MIN rows are returned

Help

• \? [commands] show help on backslash commands
• \? options show help on psql command-line options
• \? variables show help on special variables
• \h [NAME] help on syntax of SQL commands, * for all commands

Query Buffer

• \e [FILE] [LINE] edit the query buffer (or file) with external editor
• \ef [FUNCNAME [LINE]] edit function definition with external editor
• \ev [VIEWNAME [LINE]] edit view 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 [-n] [STRING] write string to standard output (-n for no newline)
• \i FILE execute commands from file
• \ir FILE as \i, but relative to location of current script
• \o [FILE] send all query results to file or |pipe
• \qecho [-n] [STRING] write string to \o output stream (-n for no newline)
• \warn [-n] [STRING] write string to standard error (-n for no newline)

Conditional

• \if EXPR begin conditional block
• \elif EXPR alternative within current conditional block
• \else final alternative within current conditional block
• \endif end conditional block

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
• \dA[+] [PATTERN] list access methods
• \dAc[+] [AMPTRN [TYPEPTRN]] list operator classes
• \dAf[+] [AMPTRN [TYPEPTRN]] list operator families
• \dAo[+] [AMPTRN [OPFPTRN]] list operators of operator families
• \dAp[+] [AMPTRN [OPFPTRN]] list support functions of operator families
• \db[+] [PATTERN] list tablespaces
• \dc[S+] [PATTERN] list conversions
• \dconfig[+] [PATTERN] list configuration parameters
• \dC[+] [PATTERN] list casts
• \dd[S] [PATTERN] show object descriptions not displayed elsewhere
• \dD[S+] [PATTERN] list domains
• \ddp [PATTERN] list default privileges
• \dE[S+] [PATTERN] list foreign tables
• \des[+] [PATTERN] list foreign servers
• \det[+] [PATTERN] list foreign tables
• \deu[+] [PATTERN] list user mappings
• \dew[+] [PATTERN] list foreign-data wrappers
• \df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]] list [only agg/normal/procedure/trigger/window] functions
• \dF[+] [PATTERN] list text search configurations
• \dFd[+] [PATTERN] list text search dictionaries
• \dFp[+] [PATTERN] list text search parsers
• \dFt[+] [PATTERN] list text search templates
• \dg[S+] [PATTERN] list roles
• \di[S+] [PATTERN] list indexes
• \dl[+] list large objects, same as \lo_list
• \dL[S+] [PATTERN] list procedural languages
• \dm[S+] [PATTERN] list materialized views
• \dn[S+] [PATTERN] list schemas
• \do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]] list operators
• \dO[S+] [PATTERN] list collations
• \dp[S] [PATTERN] list table, view, and sequence access privileges
• \dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]
• \drds [ROLEPTRN [DBPTRN]] list per-database role settings
• \drg[S] [PATTERN] list role grants
• \dRp[+] [PATTERN] list replication publications
• \dRs[+] [PATTERN] list replication subscriptions
• \ds[S+] [PATTERN] list sequences
• \dt[S+] [PATTERN] list tables
• \dT[S+] [PATTERN] list data types
• \du[S+] [PATTERN] list roles
• \dv[S+] [PATTERN] list views
• \dx[+] [PATTERN] list extensions
• \dX [PATTERN] list extended statistics
• \dy[+] [PATTERN] list event triggers
• \l[+] [PATTERN] list databases
• \sf[+] FUNCNAME show a function's definition
• \sv[+] VIEWNAME show a view's definition
• \z[S] [PATTERN] same as \dp

Large Objects

• \lo_export LOBOID FILE write large object to file
• \lo_import FILE [COMMENT] read large object from file
• \lo_list[+] list large objects
• \lo_unlink LOBOID delete a large object

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 (currently off)
• \pset [NAME [VALUE]] set table output option (border|columns|csv_fieldsep|expanded|fieldsep| fieldsep_zero|footer|format|linestyle|null| numericlocale|pager|pager_min_lines|recordsep| recordsep_zero|tableattr|title|tuples_only| unicode_border_linestyle|unicode_column_linestyle| unicode_header_linestyle|xheader_width)
• \t [on|off] show only rows (currently off)
• \T [STRING] set HTML tag attributes, or unset if none
  \x [on|off|auto] toggle expanded output (currently off)

  Connection

  • \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo} connect to new database (currently "fadev_infra")
  • \conninfo display information about current connection
  • \encoding [ENCODING] show or set client encoding
  • \password [USERNAME] securely change the password for a user

  Operating System

  • \cd [DIR] change the current working directory
  • \getenv PSQLVAR ENVVAR fetch environment variable
  • \setenv NAME [VALUE] set or unset environment variable
  • \timing [on|off] toggle timing of commands (currently off)
  • ! [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