psql
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 older PostgreSQL versions; as of PostgreSQL 15, support is available for versions as far back as PostgreSQL 9.2; 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 functionality - particularly metadata queries - 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 16
\pset
optionxheader_width
added (commit a45388d6)\d+
on a partitioned table now indicates foreign partitions (commit bd95816f)\bind
command added for setting query parameters and using the extended query protocol (commit 5b66de34)\dpS
and\zS
commands added (commit d913928c)
- PostgreSQL 15
- new environment variable
PSQL_WATCH_PAGER
for setting a pager forpsql
's\watch
command (commit 7c09d279) \getenv
slash command added (commit 33d3eead)\lo_list
/\dl
now display large objects' ACLs when the+
option is provided (commit 328dfbda)- results for multiple queries are displayed by default; variable
SHOW_ALL_RESULTS
added to control this behaviour (commit 7844c991) \dconfig
slash command added (commit 3e707fbb)\copy
performance improved (commit eec57115)- support for versions before PostgreSQL 9.2 removed (commit cf0cab86)
- new environment variable
- PostgreSQL 14
\dX
command added to display extended statistics objects added (commit ad600bba)\di
,\dm
and\dt
commands now displayaccess 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 inPROMPT1
andPROMPT2
to show transaction status (commit dcdbb5a5)
- PostgreSQL 12 (psql release notes)
- 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
, andLAST_ERROR_SQLSTATE
) - add test for the existence of a variable (commit d57c7a7c)
- environment variable
PSQL_PAGER
added, to controlpsql
's pager (commit 5e8304fd) - tab completion functionality made server-independent (commit 722408bc)
quit
andexit
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
andSERVER_VERSION_NUM
) - meta-commands
\if
,\elif
,\else
, and\endif
added (commit e984ef58) \gx
command added to execute a query in expanded mode (commit b2678efd)
- variables showing
- 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 variableSHOW_CONTEXT
(commit 0426f349) --reuse-previous
option added to the\connect
command (commit 9d924e9a)
- support for multiple and mixed
- PostgreSQL 9.5 (psql release notes)
-b
command line option and newECHO
modeerrors
which displays only failed commands (commit 5b214c5d)- AsciiDoc output mode added (commit 9d9991c8)
- prompt option
%l
added, which displays the current statement line number (commit 51bb7956) \pset
optionpager_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)
- PostgreSQL 9.3 (psql release notes)
- 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
(whereX.X
represents a PostgreSQL major version) processing added (commit 2795592e)PSQL_HISTORY
andPSQLRC
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.4
\ef
command added (commit 2c863ca8)
- PostgreSQL 8.0
- able to handle tilde user expansion for filenames (commit 55a92063)
- PostgreSQL 7.4
- PostgreSQL 7.3
- PostgreSQL 7.0
- tab completion support added (commit 78bc83fe)
- PostgreSQL 6.3
- PostgreSQL 6.1
- PostgreSQL 6.0
\copy
command added (commit 7870c5a0)
References
- PostgreSQL documentation: psql
Useful links
- psql tips - 100 psql tips by Lætitia AVROT