PostgreSQL

+
If the first argument is an unquoted `+-n+` the trailing newline is not written.
+
[TIP]
====
==== Tip

  If you use the `+\o+` command to redirect your query output you might wish to use `+\qecho+` instead of this command.
  ====
[.term]#`+\ef [ +`[.optional]#_`+function_description+`_`+ [ +`[.optional]#_`+line_number+`_#`+ ]+`#`+ ]+`#::
  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+`. After the editor exits, the updated command waits in the query buffer; type semicolon or `+\g+` to send it, or `+\r+` to cancel.
  +
  The target function can be specified by name alone, or by name and arguments, for example `+foo(integer, text)+`. The argument types must be given if there is more than one function of the same name.
  +
  If no function is specified, a blank `+CREATE FUNCTION+` template is presented for editing.
  +
  If a line number is specified, psql will position the cursor on the specified line of the function body. (Note that the function body typically does not begin on the first line of the file.)
  +
  Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of `+\ef+`, and neither variable interpolation nor backquote expansion are performed in the arguments.
  +
  [TIP]
  ====
  ==== Tip

  See under link:app-psql.html#APP-PSQL-ENVIRONMENT[Environment] for how to configure and customize your editor.
  ====
[.term]#`+\encoding [ +`_`+encoding+`_`+ ]+`#::
  Sets the client character set encoding. Without an argument, this command shows the current encoding.
[.term]#`+\errverbose+`#::
  Repeats the most recent server error message at maximum verbosity, as though `+VERBOSITY+` were set to `+verbose+` and `+SHOW_CONTEXT+` were set to `+always+`.
[.term]#`+\ev [ +`[.optional]#_`+view_name+`_`+ [ +`[.optional]#_`+line_number+`_#`+ ]+`#`+ ]+`#::
  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+`. After the editor exits, the updated command waits in the query buffer; type semicolon or `+\g+` to send it, or `+\r+` to cancel.
  +
  If no view is specified, a blank `+CREATE VIEW+` template is presented for editing.
  +
  If a line number is specified, psql will position the cursor on the specified line of the view definition.
  +
  Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of `+\ev+`, and neither variable interpolation nor backquote expansion are performed in the arguments.
[.term]#`+\f [ +`_`+string+`_`+ ]+`#::
  Sets the field separator for unaligned query output. The default is the vertical bar (`+|+`). It is equivalent to `+\pset fieldsep+`.
[.term]#`+\g [ +`_`+filename+`_`+ ]+` +
#[.term]#`+\g [ |+`_`+command+`_`+ ]+`#::
  Sends the current query buffer to the server for execution. If an argument is given, the query's output is written to the named file or piped to the given shell command, instead of displaying it as usual. The file or command is written to only if the query successfully returns zero or more tuples, not if the query fails or is a non-data-returning SQL command.
  +
  If the current query buffer is empty, the most recently sent query is re-executed instead. Except for that behavior, `+\g+` without an argument is essentially equivalent to a semicolon. A `+\g+` with argument is a [.quote]#“[.quote]#one-shot#”# alternative to the `+\o+` command.
  +
  If the argument begins with `+|+`, then the entire remainder of the line is taken to be the _`+command+`_ to execute, and neither variable interpolation nor backquote expansion are performed in it. The rest of the line is simply passed literally to the shell.
[.term]#`+\gdesc+`#::
  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.
  +
  If the current query buffer is empty, the most recently sent query is described instead.
[.term]#`+\gexec+`#::
  Sends the current query buffer to the server, then treats each column of each row of the query's output (if any) as a SQL statement to be executed. For example, to create an index on each column of `+my_table+`:
  +

  +
  The generated queries are executed in the order in which the rows are returned, and left-to-right within each row if there is more than one column. NULL fields are ignored. The generated queries are sent literally to the server for processing, so they cannot be psql meta-commands nor contain psql variable references. If any individual query fails, execution of the remaining queries continues unless `+ON_ERROR_STOP+` is set. Execution of each query is subject to `+ECHO+` processing. (Setting `+ECHO+` to `+all+` or `+queries+` is often advisable when using `+\gexec+`.) Query logging, single-step mode, timing, and other query execution features apply to each generated query as well.
  +
  If the current query buffer is empty, the most recently sent query is re-executed instead.
[.term]#`+\gset [ +`_`+prefix+`_`+ ]+`#::
  Sends the current query buffer to the server and stores the query's output into psql variables (see link:app-psql.html#APP-PSQL-VARIABLES[Variables]). 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 the column. For example:
  +

+
If you specify a _`+prefix+`_, that string is prepended to the query's column names to create the variable names to use:
+

  +
  If a column result is NULL, the corresponding variable is unset rather than being set.
  +
  If the query fails or does not return one row, no variables are changed.
  +
  If the current query buffer is empty, the most recently sent query is re-executed instead.
[.term]#`+\gx [ +`_`+filename+`_`+ ]+` +
#[.term]#`+\gx [ |+`_`+command+`_`+ ]+`#::
  `+\gx+` is equivalent to `+\g+`, but forces expanded output mode for this query. See `+\x+`.
[.term]#`+\h+` or `+\help+` `+[ +`_`+command+`_`+ ]+`#::
  Gives syntax help on the specified SQL command. If _`+command+`_ is not specified, then psql will list all the commands for which syntax help is available. If _`+command+`_ is an asterisk (`+*+`), then syntax help on all SQL commands is shown.
  +
  Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of `+\help+`, and neither variable interpolation nor backquote expansion are performed in the arguments.
  +
  [NOTE]
  ====
  ==== Note

  To simplify typing, commands that consists of several words do not have to be quoted. Thus it is fine to type *`+\help alter table+`*.
  ====
[.term]#`+\H+` or `+\html+`#::
  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.
[.term]#`+\i+` or `+\include+` _`+filename+`_#::
  Reads input from the file _`+filename+`_ and executes it as though it had been typed on the keyboard.
  +
  If _`+filename+`_ is `+-+` (hyphen), then standard input is read until an EOF indication or `+\q+` meta-command. This can be used to intersperse interactive input with input from files. Note that Readline behavior will be used only if it is active at the outermost level.
  +
  [NOTE]
  ====
  ==== Note

  If you want to see the lines on the screen as they are read you must set the variable `+ECHO+` to `+all+`.
  ====
[.term]#`+\if+` _`+expression+`_ +
#[.term]#`+\elif+` _`+expression+`_ +
#[.term]#`+\else+` +
#[.term]#`+\endif+`#::
  This group of commands implements nestable conditional blocks. A conditional block must begin with an `+\if+` and end with an `+\endif+`. In between there may be any number of `+\elif+` clauses, which may optionally be followed by a single `+\else+` clause. Ordinary queries and other types of backslash commands may (and usually do) appear between the commands forming a conditional block.
  +
  The `+\if+` and `+\elif+` commands read their argument(s) and evaluate them as a boolean expression. If the expression yields `+true+` then processing continues normally; otherwise, lines are skipped until a matching `+\elif+`, `+\else+`, or `+\endif+` is reached. Once an `+\if+` or `+\elif+` test has succeeded, the arguments of later `+\elif+` commands in the same block are not evaluated but are treated as false. Lines following an `+\else+` are processed only if no earlier matching `+\if+` or `+\elif+` succeeded.
  +
  The _`+expression+`_ argument of an `+\if+` or `+\elif+` command is subject to variable interpolation and backquote expansion, just like any other backslash command argument. After that it is evaluated like the value of an on/off option variable. So a valid value is any unambiguous case-insensitive match for one of: `+true+`, `+false+`, `+1+`, `+0+`, `+on+`, `+off+`, `+yes+`, `+no+`. For example, `+t+`, `+T+`, and `+tR+` will all be considered to be `+true+`.
  +
  Expressions that do not properly evaluate to true or false will generate a warning and be treated as false.
  +
  Lines being skipped are parsed normally to identify queries and backslash commands, but queries are not sent to the server, and backslash commands other than conditionals (`+\if+`, `+\elif+`, `+\else+`, `+\endif+`) are ignored. Conditional commands are checked only for valid nesting. Variable references in skipped lines are not expanded, and backquote expansion is not performed either.
  +
  All the backslash commands of a given conditional block must appear in the same source file. If EOF is reached on the main input file or an `+\include+`-ed file before all local `+\if+`-blocks have been closed, then psql will raise an error.
  +
  Here is an example:
  +

  +
  The response indicates that the large object received object ID 152801, which can be used to access the newly-created large object in the future. For the sake of readability, it is recommended to always associate a human-readable comment with every object. Both OIDs and comments can be viewed with the `+\lo_list+` command.
  +
  Note that this command is subtly different from the server-side `+lo_import+` because it acts as the local user on the local file system, rather than the server's user and file system.
[.term]#`+\lo_list+`#::
  Shows a list of all PostgreSQL large objects currently stored in the database, along with any comments provided for them.
[.term]#`+\lo_unlink +`_`+loid+`_#::
  Deletes the large object with OID _`+loid+`_ from the database.
  +
  [TIP]
  ====
  ==== Tip

  Use `+\lo_list+` to find out the large object's OID.
  ====
[.term]#`+\o+` or `+\out [ +`_`+filename+`_`+ ]+` +
#[.term]#`+\o+` or `+\out [ |+`_`+command+`_`+ ]+`#::
  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.
  +
  If the argument begins with `+|+`, then the entire remainder of the line is taken to be the _`+command+`_ to execute, and neither variable interpolation nor backquote expansion are performed in it. The rest of the line is simply passed literally to the shell.
  +
  [.quote]#“[.quote]#Query results#”# includes all tables, command responses, and notices obtained from the database server, as well as output of various backslash commands that query the database (such as `+\d+`); but not error messages.
  +
  [TIP]
  ====
  ==== Tip

  To intersperse text output in between query results, use `+\qecho+`.
  ====
[.term]#`+\p+` or `+\print+`#::
  Print the current query buffer to the standard output. If the current query buffer is empty, the most recently executed query is printed instead.
[.term]#`+\password [ +`_`+username+`_`+ ]+`#::
  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 cleartext in the command history, the server log, or elsewhere.
[.term]#`+\prompt [ +`_`+text+`_`+ ] +`_`+name+`_#::
  Prompts the user to supply text, which is assigned to the variable _`+name+`_. An optional prompt string, _`+text+`_, can be specified. (For multiword prompts, surround the text with single quotes.)
  +
  By default, `+\prompt+` uses the terminal for input and output. However, if the `+-f+` command line switch was used, `+\prompt+` uses standard input and standard output.
[.term]#`+\pset [ +`_`+option+`_`+ [ +`_`+value+`_`+ ] ]+`#::
  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 unset, as described under the particular option. If no such behavior is mentioned, then omitting _`+value+`_ just results in the current setting being displayed.
  +
  `+\pset+` without any arguments displays the current status of all printing options.
  +
  Adjustable printing options are:
  +
  [.term]#`+border+`#;;
    The _`+value+`_ must be a number. In general, the higher the number the more borders and lines the tables will have, but details depend on the particular format. In HTML format, this will translate directly into the `+border=...+` attribute. In most other formats only values 0 (no border), 1 (internal dividing lines), and 2 (table frame) make sense, and values above 2 will be treated the same as `+border = 2+`. The `+latex+` and `+latex-longtable+` formats additionally allow a value of 3 to add dividing lines between data rows.
  [.term]#`+columns+`#;;
    Sets the target width for the `+wrapped+` format, and also the width limit for determining whether output is wide enough to require the pager or switch to the vertical display in expanded auto mode. Zero (the default) causes the target width to be controlled by the environment variable `+COLUMNS+`, or the detected screen width if `+COLUMNS+` is not set. In addition, if `+columns+` is zero then the `+wrapped+` format only affects screen output. If `+columns+` is nonzero then file and pipe output is wrapped to that width as well.
  [.term]#`+csv_fieldsep+`#;;
    Specifies the field separator to be used in CSV output format. If the separator character appears in a field's value, that field is output within double quotes, following standard CSV rules. The default is a comma.
  [.term]#`+expanded+` (or `+x+`)#;;
    If _`+value+`_ is specified it must be either `+on+` or `+off+`, which will enable or disable expanded mode, or `+auto+`. If _`+value+`_ is omitted the command toggles between the on and off settings. When expanded mode is enabled, query results are displayed in two columns, with the column name on the left and the data on the right. This mode is useful if the data wouldn't fit on the screen in the normal [.quote]#“[.quote]#horizontal#”# mode. In the auto setting, the expanded mode is used whenever the query output has more than one column and is wider than the screen; otherwise, the regular mode is used. The auto setting is only effective in the aligned and wrapped formats. In other formats, it always behaves as if the expanded mode is off.
  [.term]#`+fieldsep+`#;;
    Specifies the field separator to be used in unaligned output format. That way one can create, for example, tab-separated output, which other programs might prefer. To set a tab as field separator, type `+\pset fieldsep '\t'+`. The default field separator is `+'|'+` (a vertical bar).
  [.term]#`+fieldsep_zero+`#;;
    Sets the field separator to use in unaligned output format to a zero byte.
  [.term]#`+footer+`#;;
    If _`+value+`_ is specified it must be either `+on+` or `+off+` which will enable or disable display of the table footer (the `+(+`_`+n+`_`+ rows)+` count). If _`+value+`_ is omitted the command toggles footer display on or off.
  [.term]#`+format+`#;;
    Sets the output format to one of `+aligned+`, `+asciidoc+`, `+csv+`, `+html+`, `+latex+`, `+latex-longtable+`, `+troff-ms+`, `+unaligned+`, or `+wrapped+`. Unique abbreviations are allowed.
    +
    `+aligned+` format is the standard, human-readable, nicely formatted text output; this is the default.
    +
    `+unaligned+` format writes all columns of a row on one line, separated by the currently active field separator. This is useful for creating output that might be intended to be read in by other programs, for example, tab-separated or comma-separated format. However, the field separator character is not treated specially if it appears in a column's value; so CSV format may be better suited for such purposes.
    +
    `+csv+` format  writes column values separated by commas, applying the quoting rules described in https://tools.ietf.org/html/rfc4180[RFC 4180]. This output is compatible with the CSV format of the server's `+COPY+` command. A header line with column names is generated unless the `+tuples_only+` parameter is `+on+`. Titles and footers are not printed. Each row is terminated by the system-dependent end-of-line character, which is typically a single newline (`+\n+`) for Unix-like systems or a carriage return and newline sequence (`+\r\n+`) for Microsoft Windows. Field separator characters other than comma can be selected with `+\pset csv_fieldsep+`.
    +
    `+wrapped+` format is like `+aligned+` but wraps wide data values across lines to make the output fit in the target column width. The target width is determined as described under the `+columns+` option. Note that psql will not attempt to wrap column header titles; therefore, `+wrapped+` format behaves the same as `+aligned+` if the total width needed for column headers exceeds the target.
    +
    The `+asciidoc+`, `+html+`, `+latex+`, `+latex-longtable+`, and `+troff-ms+` formats put out tables that are intended to be included in documents using the respective mark-up language. They are not complete documents! This might not be necessary in HTML, but in LaTeX you must have a complete document wrapper. The `+latex+` format uses LaTeX's `+tabular+` environment. The `+latex-longtable+` format requires the LaTeX `+longtable+` and `+booktabs+` packages.
  [.term]#`+linestyle+`#;;
    Sets the border line drawing style to one of `+ascii+`, `+old-ascii+`, or `+unicode+`. Unique abbreviations are allowed. (That would mean one letter is enough.) The default setting is `+ascii+`. This option only affects the `+aligned+` and `+wrapped+` output formats.
    +
    `+ascii+` style uses plain ASCII characters. Newlines in data are shown using a `+++` symbol in the right-hand margin. When the `+wrapped+` format wraps data from one line to the next without a newline character, a dot (`+.+`) is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.
    +
    `+old-ascii+` style uses plain ASCII characters, using the formatting style used in PostgreSQL 8.4 and earlier. Newlines in data are shown using a `+:+` symbol in place of the left-hand column separator. When the data is wrapped from one line to the next without a newline character, a `+;+` symbol is used in place of the left-hand column separator.
    +
    `+unicode+` style uses Unicode box-drawing characters. Newlines in data are shown using a carriage return symbol in the right-hand margin. When the data is wrapped from one line to the next without a newline character, an ellipsis symbol is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.
    +
    When the `+border+` setting is greater than zero, the `+linestyle+` option also determines the characters with which the border lines are drawn. Plain ASCII characters work everywhere, but Unicode characters look nicer on displays that recognize them.
  [.term]#`+null+`#;;
    Sets the string to be printed in place of a null value. The default is to print nothing, which can easily be mistaken for an empty string. For example, one might prefer `+\pset null '(null)'+`.
  [.term]#`+numericlocale+`#;;
    If _`+value+`_ is specified it must be either `+on+` or `+off+` which will enable or disable display of a locale-specific character to separate groups of digits to the left of the decimal marker. If _`+value+`_ is omitted the command toggles between regular and locale-specific numeric output.
  [.term]#`+pager+`#;;
    Controls use of a pager program for query and psql help output. If the environment variable `+PSQL_PAGER+` or `+PAGER+` is set, the output is piped to the specified program. Otherwise a platform-dependent default program (such as `+more+`) is used.
    +
    When the `+pager+` option is `+off+`, the pager program is not used. When the `+pager+` option is `+on+`, the pager is used when appropriate, i.e., when the output is to a terminal and will not fit on the screen. The `+pager+` option can also be set to `+always+`, which causes the pager to be used for all terminal output regardless of whether it fits on the screen. `+\pset pager+` without a _`+value+`_ toggles pager use on and off.
  [.term]#`+pager_min_lines+`#;;
    If `+pager_min_lines+` is set to a number greater than the page height, the pager program will not be called unless there are at least this many lines of output to show. The default setting is 0.
  [.term]#`+recordsep+`#;;
    Specifies the record (line) separator to use in unaligned output format. The default is a newline character.
  [.term]#`+recordsep_zero+`#;;
    Sets the record separator to use in unaligned output format to a zero byte.
  [.term]#`+tableattr+` (or `+T+`)#;;
    In HTML format, this specifies attributes to be placed inside the `+table+` tag. This could for example be `+cellpadding+` or `+bgcolor+`. Note that you probably don't want to specify `+border+` here, as that is already taken care of by `+\pset border+`. If no _`+value+`_ is given, the table attributes are unset.
    +
    In `+latex-longtable+` format, this controls the proportional width of each column containing a left-aligned data type. It is specified as a whitespace-separated list of values, e.g., `+'0.2 0.2 0.6'+`. Unspecified output columns use the last specified value.
  [.term]#`+title+` (or `+C+`)#;;
    Sets the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no _`+value+`_ is given, the title is unset.
  [.term]#`+tuples_only+` (or `+t+`)#;;
    If _`+value+`_ is specified it must be either `+on+` or `+off+` which will enable or disable tuples-only mode. If _`+value+`_ is omitted the command toggles between regular and tuples-only output. Regular output includes extra information such as column headers, titles, and various footers. In tuples-only mode, only actual table data is shown.
  [.term]#`+unicode_border_linestyle+`#;;
    Sets the border drawing style for the `+unicode+` line style to one of `+single+` or `+double+`.
  [.term]#`+unicode_column_linestyle+`#;;
    Sets the column drawing style for the `+unicode+` line style to one of `+single+` or `+double+`.
  [.term]#`+unicode_header_linestyle+`#;;
    Sets the header drawing style for the `+unicode+` line style to one of `+single+` or `+double+`.
  +
  Illustrations of how these different formats look can be seen in the link:app-psql.html#APP-PSQL-EXAMPLES[Examples] section.
  +
  [TIP]
  ====
  ==== Tip

  There are various shortcut commands for `+\pset+`. See `+\a+`, `+\C+`, `+\f+`, `+\H+`, `+\t+`, `+\T+`, and `+\x+`.
  ====
[.term]#`+\q+` or `+\quit+`#::
  Quits the psql program. In a script file, only execution of that script is terminated.
[.term]#`+\qecho +`_`+text+`_`+ [ ... ]+`#::
  This command is identical to `+\echo+` except that the output will be written to the query output channel, as set by `+\o+`.
[.term]#`+\r+` or `+\reset+`#::
  Resets (clears) the query buffer.
[.term]#`+\s [ +`_`+filename+`_`+ ]+`#::
  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.
[.term]#`+\set [ +`_`+name+`_`+ [ +`_`+value+`_`+ [ ... ] ] ]+`#::
  Sets the psql variable _`+name+`_ to _`+value+`_, or if more than one value is given, to the concatenation of all of them. If only one argument is given, the variable is set to an empty-string value. To unset a variable, use the `+\unset+` command.
  +
  `+\set+` without any arguments displays the names and values of all currently-set psql variables.
  +
  Valid variable names can contain letters, digits, and underscores. See the section link:app-psql.html#APP-PSQL-VARIABLES[Variables] below for details. Variable names are case-sensitive.
  +
  Certain variables are special, in that they control psql's behavior or are automatically set to reflect connection state. These variables are documented in link:app-psql.html#APP-PSQL-VARIABLES[Variables], below.
  +
  [NOTE]
  ====
  ==== Note

  This command is unrelated to the SQL command link:sql-set.html[[.refentrytitle]#SET#].
  ====
[.term]#`+\setenv +`_`+name+`_`+ [ +`_`+value+`_`+ ]+`#::
  Sets the environment variable _`+name+`_ to _`+value+`_, or if the _`+value+`_ is not supplied, unsets the environment variable. Example:
  +

+
will result in the three SQL commands being individually sent to the server, with each one's results being displayed before continuing to the next command. However, a semicolon entered as `+\;+` will not trigger command processing, so that the command before it and the one after are effectively combined and sent to the server in one request. So for example
+

+
results in sending the three SQL commands to the server in a single request, when the non-backslashed semicolon is reached. The server executes such a request as a single transaction, unless there are explicit `+BEGIN+`/`+COMMIT+` commands included in the string to divide it into multiple transactions. (See link:protocol-flow.html#PROTOCOL-FLOW-MULTI-STATEMENT[Section 52.2.2.1] for more details about how the server handles multi-query strings.) psql prints only the last query result it receives for each request; in this example, although all three `+SELECT+`s are indeed executed, psql only prints the `+3+`.