Wiki / psql Commands
psql Commands
Compare versionsBackslash commands with exact syntax, modifiers, examples and client-version history.
Reading PostgreSQL 18.6.
130 entries; 130 recorded in PostgreSQL 18.6.
-
\c Connections and sessions
Establishes a new connection to a PostgreSQL server. The connection parameters to use can be specified either using a positional syntax (one or more of database name, user, host, and port), or using a conninfo connection string as detailed in Sectio…
-
\connect Connections and sessions
Establishes a new connection to a PostgreSQL server. The connection parameters to use can be specified either using a positional syntax (one or more of database name, user, host, and port), or using a conninfo connection string as detailed in Sectio…
-
\conninfo Connections and sessions
Outputs information about the current database connection, including SSL-related information if SSL is in use.
-
\encoding Connections and sessions
Sets the client character set encoding. Without an argument, this command shows the current encoding.
-
\password Connections and sessions
Changes the password of the specified user (by default, the current user). This command prompts for the new password, encrypts it, and sends it to the server as an ALTER ROLE command. This makes sure that the new password does not appear in cleartex…
-
\copy Data transfer and large objects
Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. T…
-
\lo_export Data transfer and large objects
Reads the large object with OID loid from the database and writes it to filename . Note that this is subtly different from the server function lo_export , which acts with the permissions of the user that the database server runs as and on the server…
-
\lo_import Data transfer and large objects
Stores the file into a PostgreSQL large object. Optionally, it associates the given comment with the object. Example:
-
\lo_list Data transfer and large objects
Shows a list of all PostgreSQL large objects currently stored in the database, along with any comments provided for them. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, each la…
-
\lo_unlink Data transfer and large objects
Deletes the large object with OID loid from the database.
-
\d Describe database objects
For each relation (table, view, materialized view, index, sequence, or foreign table) or composite type matching the pattern , show all columns, their types, the tablespace (if not the default) and any special attributes such as NOT NULL or defaults…
-
\dA Describe database objects
Lists access methods. If pattern is specified, only access methods whose names match the pattern are shown. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, each access method is…
-
\dAc Describe database objects
Lists operator classes (see Section 36.16.1 ). If access-method-pattern is specified, only operator classes associated with access methods whose names match that pattern are listed. If input-type-pattern is specified, only operator classes associate…
-
\dAf Describe database objects
Lists operator families (see Section 36.16.5 ). If access-method-pattern is specified, only operator families associated with access methods whose names match that pattern are listed. If input-type-pattern is specified, only operator families associ…
-
\dAo Describe database objects
Lists operators associated with operator families (see Section 36.16.2 ). If access-method-pattern is specified, only members of operator families associated with access methods whose names match that pattern are listed. If operator-family-pattern i…
-
\dAp Describe database objects
Lists support functions associated with operator families (see Section 36.16.3 ). If access-method-pattern is specified, only functions of operator families associated with access methods whose names match that pattern are listed. If operator-family…
-
\dC Describe database objects
Lists type casts. If pattern is specified, only casts whose source or target types match the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, additional infor…
-
\dD Describe database objects
Lists domains. If pattern is specified, only domains whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If x is appended to the command name, the re…
-
\dE Describe database objects
In this group of commands, the letters E , i , m , s , t , and v stand for foreign table, index, materialized view, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of…
-
\dF Describe database objects
Lists text search configurations. If pattern is specified, only configurations whose names match the pattern are shown. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, a full de…
-
\dFd Describe database objects
Lists text search dictionaries. If pattern is specified, only dictionaries whose names match the pattern are shown. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, additional in…
-
\dFp Describe database objects
Lists text search parsers. If pattern is specified, only parsers whose names match the pattern are shown. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, a full description of e…
-
\dFt Describe database objects
Lists text search templates. If pattern is specified, only templates whose names match the pattern are shown. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, additional informat…
-
\dL Describe database objects
Lists procedural languages. If pattern is specified, only languages whose names match the pattern are listed. By default, only user-created languages are shown; supply the S modifier to include system objects. If x is appended to the command name, t…
-
\dO Describe database objects
Lists collations. If pattern is specified, only collations whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If x is appended to the command name,…
-
\dP Describe database objects
Lists partitioned relations. If pattern is specified, only entries whose name matches the pattern are listed. The modifiers t (tables) and i (indexes) can be appended to the command, filtering the kind of relations to list. By default, partitioned t…
-
\dRp Describe database objects
Lists replication publications. If pattern is specified, only those publications whose names match the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, the ta…
-
\dRs Describe database objects
Lists replication subscriptions. If pattern is specified, only those subscriptions whose names match the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, addi…
-
\dT Describe database objects
Lists data types. If pattern is specified, only types whose names match the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, each type is listed with its inte…
-
\dX Describe database objects
Lists extended statistics. If pattern is specified, only those extended statistics whose names match the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode.
-
\da Describe database objects
Lists aggregate functions, together with their return type and the data types they operate on. If pattern is specified, only aggregates whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the…
-
\db Describe database objects
Lists tablespaces. If pattern is specified, only tablespaces whose names match the pattern are shown. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, each tablespace is listed w…
-
\dc Describe database objects
Lists conversions between character-set encodings. If pattern is specified, only conversions whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If …
-
\dconfig Describe database objects
Lists server configuration parameters and their values. If pattern is specified, only parameters whose names match the pattern are listed. Without a pattern , only parameters that are set to non-default values are listed. (Use \dconfig * to see all …
-
\dd Describe database objects
Shows the descriptions of objects of type constraint , operator class , operator family , rule , and trigger . All other comments may be viewed by the respective backslash commands for those object types.
-
\ddp Describe database objects
Lists default access privilege settings. An entry is shown for each role (and schema, if applicable) for which the default privilege settings have been changed from the built-in defaults. If pattern is specified, only entries whose role name or sche…
-
\des Describe database objects
Lists foreign servers (mnemonic: “ external servers ” ). If pattern is specified, only those servers whose name matches the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the…
-
\det Describe database objects
Lists foreign tables (mnemonic: “ external tables ” ). If pattern is specified, only entries whose table name or schema name matches the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is ap…
-
\deu Describe database objects
Lists user mappings (mnemonic: “ external users ” ). If pattern is specified, only those mappings whose user names match the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to th…
-
\dew Describe database objects
Lists foreign-data wrappers (mnemonic: “ external wrappers ” ). If pattern is specified, only those foreign-data wrappers whose name matches the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If…
-
\df Describe database objects
Lists functions, together with their result data types, argument data types, and function types, which are classified as “ agg ” (aggregate), “ normal ” , “ procedure ” , “ trigger ” , or “ window ” . To display only functions of specific type(s), a…
-
\dg Describe database objects
Lists database roles. (Since the concepts of “ users ” and “ groups ” have been unified into “ roles ” , this command is now equivalent to \du .) By default, only user-created roles are shown; supply the S modifier to include system roles. If patter…
-
\di Describe database objects
In this group of commands, the letters E , i , m , s , t , and v stand for foreign table, index, materialized view, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of…
-
\dl Describe database objects
This is an alias for \lo_list , which shows a list of large objects. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, each large object is listed with its associated permissions,…
-
\dm Describe database objects
In this group of commands, the letters E , i , m , s , t , and v stand for foreign table, index, materialized view, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of…
-
\dn Describe database objects
Lists schemas (namespaces). If pattern is specified, only schemas whose names match the pattern are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If x is appended to the comman…
-
\do Describe database objects
Lists operators with their operand and result types. If pattern is specified, only operators whose names match the pattern are listed. If one arg_pattern is specified, only prefix operators whose right argument's type name matches that pattern are l…
-
\dp Describe database objects
Lists tables, views and sequences with their associated access privileges. If pattern is specified, only tables, views and sequences whose names match the pattern are listed. By default only user-created objects are shown; supply a pattern or the S …
-
\drds Describe database objects
Lists defined configuration settings. These settings can be role-specific, database-specific, or both. role-pattern and database-pattern are used to select specific roles and databases to list, respectively. If omitted, or if * is specified, all set…
-
\drg Describe database objects
Lists information about each granted role membership, including assigned options ( ADMIN , INHERIT and/or SET ) and grantor. See the GRANT command for information about role memberships.
-
\ds Describe database objects
In this group of commands, the letters E , i , m , s , t , and v stand for foreign table, index, materialized view, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of…
-
\dt Describe database objects
In this group of commands, the letters E , i , m , s , t , and v stand for foreign table, index, materialized view, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of…
-
\du Describe database objects
Lists database roles. (Since the concepts of “ users ” and “ groups ” have been unified into “ roles ” , this command is now equivalent to \dg .) By default, only user-created roles are shown; supply the S modifier to include system roles. If patter…
-
\dv Describe database objects
In this group of commands, the letters E , i , m , s , t , and v stand for foreign table, index, materialized view, sequence, table, and view, respectively. You can specify any or all of these letters, in any order, to obtain a listing of objects of…
-
\dx Describe database objects
Lists installed extensions. If pattern is specified, only those extensions whose names match the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, all the obje…
-
\dy Describe database objects
Lists event triggers. If pattern is specified, only those event triggers whose names match the pattern are listed. If x is appended to the command name, the results are displayed in expanded mode. If + is appended to the command name, each object is…
-
\l Describe database objects
List the databases in the server and show their names, owners, character set encodings, and access privileges. If pattern is specified, only databases whose names match the pattern are listed. If x is appended to the command name, the results are di…
-
\list Describe database objects
List the databases in the server and show their names, owners, character set encodings, and access privileges. If pattern is specified, only databases whose names match the pattern are listed. If x is appended to the command name, the results are di…
-
\z Describe database objects
Lists tables, views and sequences with their associated access privileges. If a pattern is specified, only tables, views and sequences whose names match the pattern are listed. By default only user-created objects are shown; supply a pattern or the …
-
\C Output and formatting
Sets the title of any tables being printed as the result of a query or unset any such title. This command is equivalent to \pset title title . (The name of this command derives from “ caption ” , as it was previously only used to set the caption in …
-
\H Output and formatting
Turns on HTML query output format. If the HTML format is already on, it is switched back to the default aligned text format. This command is for compatibility and convenience, but see \pset about setting other output options.
-
\T Output and formatting
Specifies attributes to be placed within the table tag in HTML output format. This command is equivalent to \pset tableattr table_options .
-
\a Output and formatting
If the current table output format is unaligned, it is switched to aligned. If it is not unaligned, it is set to unaligned. This command is kept for backwards compatibility. See \pset for a more general solution.
-
\crosstabview Output and formatting
Executes the current query buffer (like \g ) and shows the results in a crosstab grid. The query must return at least three columns. The output column identified by colV becomes a vertical header and the output column identified by colH becomes a ho…
-
\f Output and formatting
Sets the field separator for unaligned query output. The default is the vertical bar ( | ). It is equivalent to \pset fieldsep .
-
\html Output and formatting
Turns on HTML query output format. If the HTML format is already on, it is switched back to the default aligned text format. This command is for compatibility and convenience, but see \pset about setting other output options.
-
\o Output and formatting
Arranges to save future query results to the file filename or pipe future results to the shell command command . If no argument is specified, the query output is reset to the standard output.
-
\out Output and formatting
Arranges to save future query results to the file filename or pipe future results to the shell command command . If no argument is specified, the query output is reset to the standard output.
-
\pset Output and formatting
This command sets options affecting the output of query result tables. option indicates which option is to be set. The semantics of value vary depending on the selected option. For some options, omitting value causes the option to be toggled or unse…
-
\t Output and formatting
Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.
-
\x Output and formatting
Sets or toggles expanded table formatting mode. As such it is equivalent to \pset expanded .
-
\e Query buffer and editing
If filename is specified, the file is edited; after the editor exits, the file's content is copied into the current query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashio…
-
\edit Query buffer and editing
If filename is specified, the file is edited; after the editor exits, the file's content is copied into the current query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashio…
-
\ef Query buffer and editing
This command fetches and edits the definition of the named function or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE command. Editing is done in the same way as for \edit . If you quit the editor without savin…
-
\ev Query buffer and editing
This command fetches and edits the definition of the named view, in the form of a CREATE OR REPLACE VIEW command. Editing is done in the same way as for \edit . If you quit the editor without saving, the statement is discarded. If you save and exit …
-
\p Query buffer and editing
Print the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.
-
\print Query buffer and editing
Print the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.
-
\r Query buffer and editing
Resets (clears) the query buffer.
-
\reset Query buffer and editing
Resets (clears) the query buffer.
-
\s Query buffer and editing
Print psql 's command line history to filename . If filename is omitted, the history is written to the standard output (using the pager if appropriate). This command is not available if psql was built without Readline support.
-
\sf Query buffer and editing
This command fetches and shows the definition of the named function or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE OR REPLACE PROCEDURE command. The definition is printed to the current query output channel, as set by \o .
-
\sv Query buffer and editing
This command fetches and shows the definition of the named view, in the form of a CREATE OR REPLACE VIEW command. The definition is printed to the current query output channel, as set by \o .
-
\w Query buffer and editing
Writes the current query buffer to the file filename or pipes it to the shell command command . If the current query buffer is empty, the most recently executed query is written instead.
-
\write Query buffer and editing
Writes the current query buffer to the file filename or pipes it to the shell command command . If the current query buffer is empty, the most recently executed query is written instead.
-
\; Query execution
Backslash-semicolon is not a meta-command in the same way as the preceding commands; rather, it simply causes a semicolon to be added to the query buffer without any further processing.
-
\bind Query execution
Sets query parameters for the next query execution, with the specified parameters passed for any parameter placeholders ( $1 etc.).
-
\bind_named Query execution
\bind_named is equivalent to \bind , except that it takes the name of an existing prepared statement as first parameter. An empty string denotes the unnamed prepared statement.
-
\close_prepared Query execution
Closes the specified prepared statement. An empty string denotes the unnamed prepared statement. If no prepared statement exists with this name, the operation is a no-op.
-
\endpipeline Query execution
This group of commands implements pipelining of SQL statements. A pipeline must begin with a \startpipeline and end with an \endpipeline . In between there may be any number of \syncpipeline commands, which sends a sync message without ending the on…
-
\flush Query execution
This group of commands implements pipelining of SQL statements. A pipeline must begin with a \startpipeline and end with an \endpipeline . In between there may be any number of \syncpipeline commands, which sends a sync message without ending the on…
-
\flushrequest Query execution
This group of commands implements pipelining of SQL statements. A pipeline must begin with a \startpipeline and end with an \endpipeline . In between there may be any number of \syncpipeline commands, which sends a sync message without ending the on…
-
\g Query execution
Sends the current query buffer to the server for execution.
-
\gdesc Query execution
Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the normal way.
-
\getresults Query execution
This group of commands implements pipelining of SQL statements. A pipeline must begin with a \startpipeline and end with an \endpipeline . In between there may be any number of \syncpipeline commands, which sends a sync message without ending the on…
-
\gexec Query execution
Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as an SQL statement to be executed. For example, to create an index on each column of my_table :
-
\gset Query execution
Sends the current query buffer to the server and stores the query's output into psql variables (see Variables below). The query to be executed must return exactly one row. Each column of the row is stored into a separate variable, named the same as …
-
\gx Query execution
\gx is equivalent to \g , except that it forces expanded output mode for this query, as if expanded=on were included in the list of \pset options. See also \x .
-
\parse Query execution
Creates a prepared statement from the current query buffer, based on the name of a destination prepared-statement object. An empty string denotes the unnamed prepared statement.
-
\sendpipeline Query execution
This group of commands implements pipelining of SQL statements. A pipeline must begin with a \startpipeline and end with an \endpipeline . In between there may be any number of \syncpipeline commands, which sends a sync message without ending the on…
-
\startpipeline Query execution
This group of commands implements pipelining of SQL statements. A pipeline must begin with a \startpipeline and end with an \endpipeline . In between there may be any number of \syncpipeline commands, which sends a sync message without ending the on…
RecordedFirst recordedInterface or attribute changeNo longer recorded
Squares indicate presence in sampled builds, not first introduction. Select a square for the same-version definition and sources.
Changes in PostgreSQL 18.6 · Export JSON
Reading this collection
The selected version is the psql client build. Backslash commands are case-sensitive: for example, \dD and \dd are different commands. Related command spellings can share a documented syntax block.