PostgreSQL | Connecting to PostgreSQL with psql | List of psql Meta-Commands and How to Execute Them

Tags:

This page explains how to use the psql meta-commands that can be executed after connecting to PostgreSQL with psql, and lists the available commands.

How to Execute psql Commands

psql provides many commands that can be executed after connecting to PostgreSQL. To check the command list, run the following command. The list of psql commands and their usage is displayed. The command list is also included at the end of this page.

\?

postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in a crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

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

On Windows, psql commands begin with a backslash (\). For example, \q, which is used to exit psql and disconnect from PostgreSQL, is also one of the psql commands.

\q

C:\>psql -U postgres
Password for user postgres:
psql (12.2)
Type "help" for help.

postgres=# \q

C:\>

You can retrieve the list of created databases with a psql command.

\l

postgres=# \l
postgres=# \l
                                      List of databases
   Name    |  Owner   | Encoding |     Collate      |      Ctype       |   Access privileges
-----------+----------+----------+------------------+------------------+-----------------------
 devkuma   | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 |
 postgres  | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 |
 template0 | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |          |                  |                  | postgres=CTc/postgres
 template1 | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |          |                  |                  | postgres=CTc/postgres
(4 rows)

You can also connect to a database other than the currently connected database.

\c devkuma

postgres=# \c devkuma
You are now connected to database "devkuma" as user "postgres".
devkuma=#

psql Command List

General:

Command	Description
\copyright	Show PostgreSQL usage and copyright information
\crosstabview [columns]	Execute a query and display the results as a pivot table
\errverbose	Show the most recent error in maximum verbosity
\g [FILE] or ;	Execute query and send results to a file or \| pipe
\gdesc	Show result columns and data types without executing the query
\gexec	Execute a query and then execute each result
\gset [PREFIX]	Execute a query and store the result in psql variables
\gx [FILE]	Same as \g, but force expanded output mode
\q	Exit psql
\watch [SEC]	Execute query every second

Help:

Command	Description
? [commands]	Help for psql backslash commands
? options	Help for psql command-line options
? variables	Help for psql environment setting variables
\h [NAME]	SQL command syntax help; enter * to show all commands

Query Buffer:

Command	Description
\e [FILE] [LINE]	Edit the query buffer or file with an external editor
\ef [FUNCNAME [LINE]]	Edit the function body with an external editor
\ev [VIEWNAME [LINE]]	Edit the view definition with an external editor
\p	Show the contents of the query buffer
\r	Reset the query buffer by clearing it
\w FILE	Write the query buffer to a file

Input/Output:

Command	Description
\copy …	Execute an SQL COPY command using data on the client host
\echo [STRING]	Write a string to standard output
\i FILE	Execute commands from a file
\ir FILE	Same as \i, but paths are relative to the current location
\o [FILE]	Send all query results to a file or \| pipe
\qecho [STRING]	Write a string to the query output stream; see \o

Conditional:

Command	Description
\if EXPR	Start a conditional block
\elif EXPR	Start an else-if branch
\else	Alternative branch of a conditional block
\endif	End a conditional block

Informational:

(Options: S = show system objects, + = additional details)

Command	Description
\d[S+]	List tables, views, and sequences
\d[S+] NAME	Describe a table, view, sequence, or index
\da[S] [PATTERN]	List aggregate functions
\dA[+] [PATTERN]	List access methods
\db[+] [PATTERN]	List tablespaces
\dc[S+] [PATTERN]	List character set conversions
\dC[+] [PATTERN]	List type casts
\dd[S] [PATTERN]	Show object descriptions not visible elsewhere
\dD[S+] [PATTERN]	List domains
\ddp [PATTERN]	List default access privileges
\dE[S+] [PATTERN]	List foreign tables
\det[+] [PATTERN]	List foreign tables
\des[+] [PATTERN]	List foreign servers
\deu[+] [PATTERN]	List user mappings
\dew[+] [PATTERN]	List foreign-data wrappers
\df[anptw][S+] [PATRN]	List functions [aggregate/normal/procedure/trigger/window]
\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] [PATTERN]	List operators
\dO[S+] [PATTERN]	List collations
\dp [PATTERN]	List table, view, and sequence access privileges
\dP[itn+] [PATTERN]	List partitioned relations [indexes/tables only] [n=nested]
\drds [PATRN1 [PATRN2]]	List per-database role settings
\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
\dy [PATTERN]	List event triggers
\l[+] [PATTERN]	List databases
\sf[+] function_name	Show function definition
\sv[+] view_name	Show view definition

Output Format:

Command	Description
\a	Toggle between unaligned and aligned output modes
\C [STRING]	Set table title, or unset it if no value is supplied
\f [STRING]	Show or set the field separator for unaligned output
\H	Toggle HTML output mode, currently off
\pset [name [value]]	Set table output options
	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
\t [on\|off]	Show rows only, currently off
\T [STRING]	Set HTML `<table>` tag attributes, or unset them if empty
\x [on\|off\|auto]	Toggle expanded output, currently off

Connection:

Command	Description
\c[onnect] {[DBNAME\|- USER\|- HOST\|- PORT\|-] \| conninfo}	Connect to a new database, currently “postgres”
\conninfo	Show current database connection information
\encoding [ENCODING]	Show or set client encoding
\password [USERNAME]	Change a user’s password securely

Operating System:

Command	Description
\cd [DIR]	Change the current working directory
\setenv NAME [VALUE]	Set or unset an environment variable
\timing [on\|off]	Toggle command execution timing, currently off
! [COMMAND]	Execute a shell command or start an interactive shell

Variables:

Command	Description
\prompt [TEXT] NAME	Prompt the user to set an internal variable
\set [NAME [VALUE]]	Set an internal variable, or list all variables if unspecified
\unset NAME	Unset an internal variable

Large Objects:

Command	Description
\lo_export LOBOID FILE	Large object operation
\lo_import FILE [COMMENT]	Large object operation
\lo_list	Large object operation
\lo_unlink LOBOID	Large object operation