Contents

psql

PostgreSQL's standard command line client

psql is PostgreSQL's standard command line client application and is a standard part of most PostgreSQL installations. It can be used both for terminal-based text-only interaction with a PostgreSQL server, and also for facilitating automated script-based tasks.

psql has been a core part of the PostgreSQL project since its inception and is usually present in PostgreSQL installations. It can also be installed as a standalone application, and many operating systems/distributions provide separate packages for the PostgreSQL server itself and for the core client applications. psql has of course been ported to Windows, but with certain limitations, particularly readline support.

psql can connect to a PostgreSQL instance on the same server via a Unix domain socket if available, or connect TCP/IP to PostgreSQL running either on the local or a remote server.

The most basic interaction with psql is connecting to a PostgreSQL server and issuing SQL commands, e.g.:

postgres$ psql
psql (13.2)
Type "help" for help.
postgres=# SELECT 'hello world';
  ?column? 
------------- 
 hello world 
(1 row)

psql also provides a wide selection of meta-commands (so-called slash commands) and other features which turn it into a powerful tool for manipulating SQL and make working directly with the PostgreSQL backend much easier.

psql can also be used to pipe raw SQL from another process, and to execute scripts consisting both of SQL and psql meta-commands.

psql's default configuration file is .psqlrc; if the PostgreSQL minor version or major version (PostgreSQL 9.2 and later) numbers are appended (e.g. .psqlrc-9.6.6 or .psqlrc-11), that file will be interpreted if it matches the psql version.

Backwards compatibility

psql is intended to function with PostgreSQL versions as far back as PostgreSQL 7.4; see comment in src/bin/psql/describe.c.

psql can connect to a server which is running a newer PostgreSQL version and execute basic queries, however this should be avoided where possible as some advanced functionality may not be available or may not function correctly.

Change history

This list enumerates the significant functionality changes in psql in each release; for full details see the linked release notes for each version.

  • PostgreSQL 14
    • \dX command added to display extended statistics objects added (commit ad600bba)
    • \di, \dm and \dt commands now display access method name (commit 07f386ed)
    • all query results are displayed by default if a command string contains multiple queries (commit 3a513067)
    • SHOW_ALL_RESULTS variable added (commit 3a513067)
    • \df and \do commands now accept specification of argument types (commit a3027e1e)
  • PostgreSQL 13 (psql release notes)
    • \warn command added (commit 02e95a50)
    • following operator-related slash commands added (commit b0b5e20c):
      • \dAc - list operator classes
      • \dAf - list operator families
      • \dAo - list operators of operator families
      • \dAp - list procedures of operator families
    • \d+ now shows whether an object is permanent, temporary or unlogged (commit 9a2ea618)
    • \d output for TOAST tables improved (commit eb5472da)
    • \e now inserts an unterminated query into psql's query buffer, making it possible to view or further edit the query before submission  (commit d1c866e5)
    • \g and \gx can accept additional formatting options, e.g. \g (format=csv csv_fieldsep=',') (commit b63c293b)
    • "invisible" PROMPT2 prompt configuration option %w added (commit 7f338369)
    • %x included by default in PROMPT1 and PROMPT2 to show transaction status (commit dcdbb5a5)
  • PostgreSQL 12 (psql release notes)
    • \dP command to list partitioned tables and indexes added (commit 1c5d9270)
    • CSV table output mode added (commit aa2ba50c)
    • link to online manual page displayed in psql's \help output (commit 27f3dea6)
  • PostgreSQL 11 (psql release notes)
    • \gdesc command added (commit 49ca462e)
    • variables showing success/failure of SQL queries added (commit 69835bc8; variables added: ERROR, SQLSTATE, ROW_COUNT, LAST_ERROR_MESSAGE, and LAST_ERROR_SQLSTATE)
    • add test for the existence of a variable (commit d57c7a7c)
    • environment variable PSQL_PAGER added, to control psql's pager (commit 5e8304fd)
    • tab completion functionality made server-independent (commit 722408bc)
    • quit and exit commands added, similar to MySQL's command line client (commit df9f599b)
  •  PostgreSQL 10 (psql release notes)
    •  variables showing psql version and server version added (commit 9ae9d8c1; variables added: VERSION_NAME, VERSION_NUM, SERVER_VERSION_NAME and SERVER_VERSION_NUM)
    • meta-commands \if, \elif, \else, and \endif added (commit e984ef58)
    • \gx command added to execute a query in expanded mode (commit b2678efd)
  • PostgreSQL 9.6 (psql release notes)
    • support for multiple and mixed -c and -f options added (commit d5563d7d)
    • \crosstabview added which prints the results of a query in a cross-tabulated display (initial commit c09b18f2)
    • \errverbose command added which shows the last server error at full verbosity (commit 3cc38ca7)
    • \ev and \sv commands for editing/showing view definitions (commit 8eb6407a)
    • \gexec command added which executes a query and re-submits the result(s) as new queries (commit 2bbe9112)
    • tab completion now considers the entire query, not just the current line (commit d854118c)
    • prompt option %p added, which displays the PID of the connected backend (commit 275f05c9)
    • display of the CONTEXT field of messages can be controlled by the new variable SHOW_CONTEXT (commit 0426f349)
  • PostgreSQL 9.5 (psql release notes)
    • AsciiDoc output mode added (commit 9d9991c8)
    • prompt option %l added, which displays the current statement line number (commit 51bb7956)
    • \pset option pager_min_lines added (commit 7655f4cc)
    • unicode column, header, and border linestyle controls added (commit a2dabf0e)
    • basic tab completion support for search_path (commit bd409519)
    • \db+ shows tablespace size in PostgreSQL 9.2 and later (commit ee80f043)
  • PostgreSQL 9.4 (psql release notes)
    • (no significant changes were made to psql in this release)
  • PostgreSQL 9.3 (psql release notes)
    • only emit warning when connecting to a newer server version (commit 5cad0245)
  • PostgreSQL 9.2 (psql release notes)
    • auto option added to \x command (commit 95d2af16)
    • \ir command added (commit c7f23494)
    • \setenv command added (commit e6d9e210)
    • .psqlrc-X.X (where X.X represents a PostgreSQL major version) processing added (commit 2795592e)
    • PSQL_HISTORY and PSQLRC environment variables now recognised (commit 34c97844)
    • COMP_KEYWORD_CASE variable added to control keyword case in tab completion (commit db84ba65)
  • PostgreSQL 9.1 (psql release notes)
    • \conninfo command to display current connection info added (commit 013ed0bd)
    • \sf command to display definition of a function added (commit b6e06942)
    • \dL command to list available languages added (commit 9c5e2c12)
    • \dn now ignores system schemas by default, \dnS includes them (commit e43fb604)
    • \dO command to list available collations added (commit b313bca0)
  • PostgreSQL 8.0
    •  able to handle tilde user expansion for filenames (commit 55a92063)
  • PostgreSQL 7.4
    • prompt option %x added to show transaction status (initial commit f9ebf369 as %T, later changed to %x in commit 9951474c)
  • PostgreSQL 7.3
  • PostgreSQL 7.0
    • tab completion support added (commit 78bc83fe)
  • PostgreSQL 6.3
    • \da command to list available aggregates added (commit 4cbc06cf)
    • \do command to list available operators added (commit 4cbc06cf)
    • \dT command to list available data types added (commit 4cbc06cf)
  • PostgreSQL 6.1
    • \ds slash command to list available sequences added (commit 735bf0b7)
    • \dt and \di slash commands to list available tables and indexes added (commit ff038a5b)
    • \z slash command added (commit f986173e)
  • PostgreSQL documentation: psql
  • psql tips - 100 psql tips by Lætitia AVROT

Categories

Client application, Core utility, psql

See also

.psqlrc, slash command, psql hacks