Notes for windows users postgresql

$ psql testdb
psql (10.5)
Type "help" for help.
testdb=>

Establishes a new connection to a PostgreSQL server. The
connection parameters to use can be specified either using a positional
syntax, or using conninfo connection strings as detailed in
Section 33.1.1.

Where the command omits database name, user, host, or port, the
new connection can reuse values from the previous connection. By default,
values from the previous connection are reused except when processing a
conninfo string. Passing a first argument of -reuse-previous=on or
-reuse-previous=off overrides that default. When the command neither
specifies nor reuses a particular parameter, the libpq default is used.
Specifying any of dbname, username, host or port
as — is equivalent to omitting that parameter.

If the new connection is successfully made, the previous
connection is closed. If the connection attempt failed (wrong user name,
access denied, etc.), the previous connection will only be kept if psql is
in interactive mode. When executing a non-interactive script, processing
will immediately stop with an error. This distinction was chosen as a user
convenience against typos on the one hand, and a safety mechanism that
scripts are not accidentally acting on the wrong database on the other
hand.

Examples:

Changes the current working directory to
directory. Without argument, changes to the current user’s home
directory.

Performs a frontend (client) copy. This is an operation
that runs an SQL COPY(7) 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. This means that file
accessibility and privileges are those of the local user, not the server, and
no SQL superuser privileges are required.

When program is specified, command is executed by psql and
the data passed from or to command is routed between the server and
the client. Again, the execution privileges are those of the local user, not
the server, and no SQL superuser privileges are required.

For \copy … from stdin, data rows are read from the same source
that issued the command, continuing until \. is read or the stream reaches
EOF. This option is useful for populating tables in-line within a SQL script
file. For \copy … to stdout, output is sent to the same place as psql
command output, and the COPY count command status is not printed
(since it might be confused with a data row). To read/write psql’s standard
input or output regardless of the current command source or \o option, write
from pstdin or to pstdout.

The syntax of this command is similar to that of the SQL
COPY(7) command. All options other than the data source/destination
are as specified for COPY(7). Because of this, special parsing rules
apply to the \copy meta-command. Unlike most other meta-commands, the
entire remainder of the line is always taken to be the arguments of
\copy, and neither variable interpolation nor backquote expansion are
performed in the arguments.

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 horizontal header.
colD identifies the output column to display within the grid.
sortcolH identifies an optional sort column for the horizontal header.

Each column specification can be a column number (starting at 1)
or a column name. The usual SQL case folding and quoting rules apply to
column names. If omitted, colV is taken as column 1 and colH
as column 2. colH must differ from colV. If colD is not
specified, then there must be exactly three columns in the query result, and
the column that is neither colV nor colH is taken to be
colD.

The vertical header, displayed as the leftmost column, contains
the values found in column colV, in the same order as in the query
results, but with duplicates removed.

The horizontal header, displayed as the first row, contains the
values found in column colH, with duplicates removed. By default,
these appear in the same order as in the query results. But if the optional
sortcolH argument is given, it identifies a column whose values must
be integer numbers, and the values from colH will appear in the
horizontal header sorted according to the corresponding sortcolH
values.

Inside the crosstab grid, for each distinct value x of colH
and each distinct value y of colV, the cell located at the
intersection (x,y) contains the value of the colD column in the query result
row for which the value of colH is x and the value of colV is
y. If there is no such row, the cell is empty. If there are multiple such
rows, an error is reported.

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. Associated indexes,
constraints, rules, and triggers are also shown. For foreign tables, the
associated foreign server is shown as well. (“Matching the
pattern” is defined in Patterns below.)

For some types of relation, \d shows additional information for
each column: column values for sequences, indexed expressions for indexes,
and foreign data wrapper options for foreign tables.

The command form \d+ is identical, except that more information is
displayed: any comments associated with the columns of the table are shown,
as is the presence of OIDs in the table, the view definition if the relation
is a view, a non-default replica identity setting.

By default, only user-created objects are shown; supply a pattern
or the S modifier to include system 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.

\dd displays descriptions for objects matching the pattern,
or of visible objects of the appropriate type if no argument is given. But
in either case, only objects that have a description are listed. By default,
only user-created objects are shown; supply a pattern or the S modifier to
include system objects.

Descriptions for objects can be created with the COMMENT(7)
SQL command.

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 schema name
matches the pattern are listed.

The ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7))
command is used to set default access privileges. The meaning of the
privilege display is explained under GRANT(7).

Lists user mappings (mnemonic: “external
users”). If pattern is specified, only those mappings whose user
names match the pattern are listed. If the form \deu+ is used, additional
information about each mapping is shown.

Lists functions, together with their result data types,
argument data types, and function types, which are classified as
“agg” (aggregate), “normal”,
“trigger”, or “window”. To display only functions
of specific type(s), add the corresponding letters a, n, t, or w to the
command. If pattern is specified, only functions 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 the form \df+ is used,
additional information about each function is shown, including volatility,
parallel safety, owner, security classification, access privileges, language,
source code and description.

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.

The GRANT(7) and REVOKE(7) commands are used to set
access privileges. The meaning of the privilege display is explained under
GRANT(7).

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 settings are listed,
including those not role-specific or database-specific, respectively.

The ALTER ROLE (ALTER_ROLE(7)) and ALTER DATABASE
(ALTER_DATABASE(7)) commands are used to define per-role and
per-database configuration settings.

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 fashion. Or, if the current
query buffer is empty, the most recently executed query is copied to a
temporary file and edited in the same fashion.

The new contents of the query buffer are then re-parsed according
to the normal rules of psql, treating the whole buffer as a single line. Any
complete queries are immediately executed; that is, if the query buffer
contains or ends with a semicolon, everything up to that point is executed.
Whatever remains will wait in the query buffer; type semicolon or \g to send
it, or \r to cancel it by clearing the query buffer. Treating the buffer as
a single line primarily affects meta-commands: whatever is in the buffer
after a meta-command will be taken as argument(s) to the meta-command, even
if it spans multiple lines. (Thus you cannot make meta-command-using scripts
this way. Use \i for that.)

If a line number is specified, psql will position the cursor on
the specified line of the file or query buffer. Note that if a single
all-digits argument is given, psql assumes it is a line number, not a file
name.

Prints the arguments to the standard output, separated by
one space and followed by a newline. This can be useful to intersperse
information in the output of scripts. For example:

=> \echo `date`
Tue Oct 26 21:40:57 CEST 1999

If the first argument is an unquoted -n the trailing newline is
not written.

This command fetches and edits the definition of the
named function, in the form of a CREATE OR REPLACE FUNCTION 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.

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.

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
“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.

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:

=> SELECT format('create index on my_table(%I)', attname)
-> FROM pg_attribute
-> WHERE attrelid = 'my_table'::regclass AND attnum > 0
-> ORDER BY attnum
-> \gexec
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

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.

Sends the current query buffer to the server and stores
the query’s output into psql variables (see 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:

=> SELECT 'hello' AS var1, 10 AS var2
-> \gset
=> \echo :var1 :var2
hello 10

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

=> SELECT 'hello' AS var1, 10 AS var2
-> \gset result_
=> \echo :result_var1 :result_var2
hello 10

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.

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.

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.

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:

-- check for the existence of two separate records in the database and store
-- the results in separate psql variables
SELECT


    EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,


    EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
\gset
\if :is_customer


    SELECT * FROM customer WHERE customer_id = 123;
\elif :is_employee


    \echo 'is not a customer but is an employee'


    SELECT * FROM employee WHERE employee_id = 456;
\else


    \if yes


        \echo 'not a customer or employee'


    \else


        \echo 'this will never print'


    \endif
\endif

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’s file system.

Stores the file into a PostgreSQL large object.
Optionally, it associates the given comment with the object. Example:

foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
lo_import 152801

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.

Deletes the large object with OID loid from the
database.

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.

“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.

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.

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:

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.

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.

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 “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.

fieldsep

Specifies the field separator to be used in unaligned
output format. That way one can create, for example, tab- or comma-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).

fieldsep_zero

Sets the field separator to use in unaligned output
format to a zero byte.

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.

format

linestyle

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)’.

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.

pager

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.

recordsep

Specifies the record (line) separator to use in unaligned
output format. The default is a newline character.

recordsep_zero

Sets the record separator to use in unaligned output
format to a zero byte.

tableattr (or T)

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.

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.

unicode_border_linestyle

Sets the border drawing style for the unicode line style
to one of single or double.

unicode_column_linestyle

Sets the column drawing style for the unicode line style
to one of single or double.

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 EXAMPLES section.

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 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 Variables, below.

Sets the environment variable name to
value, or if the value is not supplied, unsets the environment
variable. Example:

testdb=> \setenv PAGER less
testdb=> \setenv LESS -imx4F

This command fetches and shows the definition of the
named function, in the form of a CREATE OR REPLACE FUNCTION command.
The definition is printed to the current query output channel, as set by
\o.

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 + is appended to the command name, then the output lines are
numbered, with the first line of the function body being line 1.

Unlike most other meta-commands, the entire remainder of the line
is always taken to be the argument(s) of \sf, and neither variable
interpolation nor backquote expansion are performed in the arguments.

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.

If + is appended to the command name, then the output lines are
numbered from 1.

Unlike most other meta-commands, the entire remainder of the line
is always taken to be the argument(s) of \sv, and neither variable
interpolation nor backquote expansion are performed in the arguments.

Unsets (deletes) the psql variable name.

Most variables that control psql’s behavior cannot be unset;
instead, an \unset command is interpreted as setting them to their default
values. See Variables, below.

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.

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.

Repeatedly execute the current query buffer (as \g does)
until interrupted or the query fails. Wait the specified number of seconds
(default 2) between executions. Each query result is displayed with a header
that includes the \pset title string (if any), the time as of query start, and
the delay interval.

If the current query buffer is empty, the most recently sent query
is re-executed instead.

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.

This is an alias for \dp (“display
privileges”).

With no argument, escapes to a sub-shell; psql resumes
when the sub-shell exits. With an argument, executes the shell command
command.

Unlike most other meta-commands, the entire remainder of the line
is always taken to be the argument(s) of \!, and neither variable
interpolation nor backquote expansion are performed in the arguments. The
rest of the line is simply passed literally to the shell.

The various \d commands accept a pattern parameter to
specify the object name(s) to be displayed. In the simplest case, a pattern
is just the exact name of the object. The characters within a pattern are
normally folded to lower case, just as in SQL names; for example, \dt FOO
will display the table named foo. As in SQL names, placing double quotes
around a pattern stops folding to lower case. Should you need to include an
actual double quote character in a pattern, write it as a pair of double
quotes within a double-quote sequence; again this is in accord with the
rules for SQL quoted identifiers. For example, \dt
«FOO»»BAR» will display the table named FOO»BAR
(not foo»bar). Unlike the normal rules for SQL names, you can put
double quotes around just part of a pattern, for instance \dt
FOO»FOO»BAR will display the table named fooFOObar.

Whenever the pattern parameter is omitted completely, the
\d commands display all objects that are visible in the current schema
search path — this is equivalent to using * as the pattern. (An
object is said to be visible if its containing schema is in the search path
and no object of the same kind and name appears earlier in the search path.
This is equivalent to the statement that the object can be referenced by
name without explicit schema qualification.) To see all objects in the
database regardless of visibility, use *.* as the pattern.

Within a pattern, * matches any sequence of characters (including
no characters) and ? matches any single character. (This notation is
comparable to Unix shell file name patterns.) For example, \dt int* displays
tables whose names begin with int. But within double quotes, * and ? lose
these special meanings and are just matched literally.

A pattern that contains a dot (.) is interpreted as a schema name
pattern followed by an object name pattern. For example, \dt foo*.*bar*
displays all tables whose table name includes bar that are in schemas whose
schema name starts with foo. When no dot appears, then the pattern matches
only objects that are visible in the current schema search path. Again, a
dot within double quotes loses its special meaning and is matched
literally.

Advanced users can use regular-expression notations such as
character classes, for example [0-9] to match any digit. All regular
expression special characters work as specified in Section 9.7.3,
except for . which is taken as a separator as mentioned above, * which is
translated to the regular-expression notation .*, ? which is translated to
., and $ which is matched literally. You can emulate these pattern
characters at need by writing ? for ., (R+|) for R*, or
(R|) for R?. $ is not needed as a regular-expression character
since the pattern must match the whole name, unlike the usual interpretation
of regular expressions (in other words, $ is automatically appended to your
pattern). Write * at the beginning and/or end if you don’t wish the pattern
to be anchored. Note that within double quotes, all regular expression
special characters lose their special meanings and are matched literally.
Also, the regular expression special characters are matched literally in
operator name patterns (i.e., the argument of \do).

psql provides variable substitution features similar to common
Unix command shells. Variables are simply name/value pairs, where the value
can be any string of any length. The name must consist of letters (including
non-Latin letters), digits, and underscores.

To set a variable, use the psql meta-command \set. For
example,

sets the variable foo to the value bar. To retrieve the content of
the variable, precede the name with a colon, for example:

This works in both regular SQL commands and meta-commands; there
is more detail in SQL Interpolation, below.

If you call \set without a second argument, the variable is
set to an empty-string value. To unset (i.e., delete) a variable, use the
command \unset. To show the values of all variables, call \set
without any argument.

A number of these variables are treated specially by psql. They
represent certain option settings that can be changed at run time by
altering the value of the variable, or in some cases represent changeable
state of psql. By convention, all specially treated variables’ names consist
of all upper-case ASCII letters (and possibly digits and underscores). To
ensure maximum compatibility in the future, avoid using such variable names
for your own purposes.

Variables that control psql’s behavior generally cannot be unset
or set to invalid values. An \unset command is allowed but is interpreted as
setting the variable to its default value. A \set command without a second
argument is interpreted as setting the variable to on, for control variables
that accept that value, and is rejected for others. Also, control variables
that accept the values on and off will also accept other common spellings of
Boolean values, such as true and false.

The specially treated variables are:

AUTOCOMMIT

COMP_KEYWORD_CASE

Determines which letter case to use when completing an
SQL key word. If set to lower or upper, the completed word will be in lower or
upper case, respectively. If set to preserve-lower or preserve-upper (the
default), the completed word will be in the case of the word already entered,
but words being completed without anything entered will be in lower or upper
case, respectively.

DBNAME

The name of the database you are currently connected to.
This is set every time you connect to a database (including program start-up),
but can be changed or unset.

ECHO

If set to all, all nonempty input lines are printed to
standard output as they are read. (This does not apply to lines read
interactively.) To select this behavior on program start-up, use the switch
-a. If set to queries, psql prints each query to standard output as it
is sent to the server. The switch to select this behavior is -e. If set
to errors, then only failed queries are displayed on standard error output.
The switch for this behavior is -b. If set to none (the default), then
no queries are displayed.

ECHO_HIDDEN

When this variable is set to on and a backslash command
queries the database, the query is first shown. This feature helps you to
study PostgreSQL internals and provide similar functionality in your own
programs. (To select this behavior on program start-up, use the switch
-E.) If you set this variable to the value noexec, the queries are just
shown but are not actually sent to the server and executed. The default value
is off.

ENCODING

The current client character set encoding. This is set
every time you connect to a database (including program start-up), and when
you change the encoding with \encoding, but it can be changed or unset.

FETCH_COUNT

HISTCONTROL

HISTFILE

\set HISTFILE ~/.psql_history- :DBNAME

HISTSIZE

HOST

The database server host you are currently connected to.
This is set every time you connect to a database (including program start-up),
but can be changed or unset.

IGNOREEOF

LASTOID

The value of the last affected OID, as returned from an
INSERT or \lo_import command. This variable is only guaranteed
to be valid until after the result of the next SQL command has been
displayed.

ON_ERROR_ROLLBACK

When set to on, if a statement in a transaction block
generates an error, the error is ignored and the transaction continues. When
set to interactive, such errors are only ignored in interactive sessions, and
not when reading script files. When set to off (the default), a statement in a
transaction block that generates an error aborts the entire transaction. The
error rollback mode works by issuing an implicit SAVEPOINT for you,
just before each command that is in a transaction block, and then rolling back
to the savepoint if the command fails.

ON_ERROR_STOP

By default, command processing continues after an error.
When this variable is set to on, processing will instead stop immediately. In
interactive mode, psql will return to the command prompt; otherwise, psql will
exit, returning error code 3 to distinguish this case from fatal error
conditions, which are reported using error code 1. In either case, any
currently running scripts (the top-level script, if any, and any other scripts
which it may have in invoked) will be terminated immediately. If the top-level
command string contained multiple SQL commands, processing will stop with the
current command.

PORT

The database server port to which you are currently
connected. This is set every time you connect to a database (including program
start-up), but can be changed or unset.

PROMPT1

PROMPT2

PROMPT3

These specify what the prompts psql issues should look
like. See Prompting below.

QUIET

Setting this variable to on is equivalent to the command
line option -q. It is probably not too useful in interactive
mode.

SERVER_VERSION_NAME

SERVER_VERSION_NUM

The server’s version number as a string, for example
9.6.2, 10.1 or 11beta1, and in numeric form, for example 90602 or 100001.
These are set every time you connect to a database (including program
start-up), but can be changed or unset.

SHOW_CONTEXT

This variable can be set to the values never, errors, or
always to control whether CONTEXT fields are displayed in messages from the
server. The default is errors (meaning that context will be shown in error
messages, but not in notice or warning messages). This setting has no effect
when VERBOSITY is set to terse. (See also \errverbose, for use
when you want a verbose version of the error you just got.)

SINGLELINE

Setting this variable to on is equivalent to the command
line option -S.

SINGLESTEP

Setting this variable to on is equivalent to the command
line option -s.

USER

The database user you are currently connected as. This is
set every time you connect to a database (including program start-up), but can
be changed or unset.

VERBOSITY

This variable can be set to the values default, verbose,
or terse to control the verbosity of error reports. (See also
\errverbose, for use when you want a verbose version of the error you
just got.)

VERSION

VERSION_NAME

VERSION_NUM

These variables are set at program start-up to reflect
psql’s version, respectively as a verbose string, a short string (e.g., 9.6.2,
10.1, or 11beta1), and a number (e.g., 90602 or 100001). They can be changed
or unset.

A key feature of psql variables is that you can substitute
(“interpolate”) them into regular SQL statements, as well as
the arguments of meta-commands. Furthermore, psql provides facilities for
ensuring that variable values used as SQL literals and identifiers are
properly quoted. The syntax for interpolating a value without any quoting is
to prepend the variable name with a colon (:). For example,

testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;

would query the table my_table. Note that this may be unsafe: the
value of the variable is copied literally, so it can contain unbalanced
quotes, or even backslash commands. You must make sure that it makes sense
where you put it.

When a value is to be used as an SQL literal or identifier, it is
safest to arrange for it to be quoted. To quote the value of a variable as
an SQL literal, write a colon followed by the variable name in single
quotes. To quote the value as an SQL identifier, write a colon followed by
the variable name in double quotes. These constructs deal correctly with
quotes and other special characters embedded within the variable value. The
previous example would be more safely written this way:

testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";

Variable interpolation will not be performed within quoted SQL
literals and identifiers. Therefore, a construction such as ‘:foo’ doesn’t
work to produce a quoted literal from a variable’s value (and it would be
unsafe if it did work, since it wouldn’t correctly handle quotes embedded in
the value).

One example use of this mechanism is to copy the contents of a
file into a table column. First load the file into a variable and then
interpolate the variable’s value as a quoted string:

testdb=> \set content `cat my_file.txt`
testdb=> INSERT INTO my_table VALUES (:'content');

(Note that this still won’t work if my_file.txt contains NUL
bytes. psql does not support embedded NUL bytes in variable values.)

Since colons can legally appear in SQL commands, an apparent
attempt at interpolation (that is, :name, :’name’, or :»name») is
not replaced unless the named variable is currently set. In any case, you
can escape a colon with a backslash to protect it from substitution.

The colon syntax for variables is standard SQL for embedded query
languages, such as ECPG. The colon syntaxes for array slices and type casts
are PostgreSQL extensions, which can sometimes conflict with the standard
usage. The colon-quote syntax for escaping a variable’s value as an SQL
literal or identifier is a psql extension.

The prompts psql issues can be customized to your preference. The
three variables PROMPT1, PROMPT2, and PROMPT3 contain
strings and special escape sequences that describe the appearance of the
prompt. Prompt 1 is the normal prompt that is issued when psql requests a
new command. Prompt 2 is issued when more input is expected during command
entry, for example because the command was not terminated with a semicolon
or a quote was not closed. Prompt 3 is issued when you are running an SQL
COPY FROM STDIN command and you need to type in a row value on the
terminal.

The value of the selected prompt variable is printed literally,
except where a percent sign (%) is encountered. Depending on the next
character, certain other text is substituted instead. Defined substitutions
are:

%M

The full host name (with domain name) of the database
server, or [local] if the connection is over a Unix domain socket, or
[local:/dir/name], if the Unix domain socket is not at the compiled in
default location.

%m

The host name of the database server, truncated at the
first dot, or [local] if the connection is over a Unix domain socket.

%>

The port number at which the database server is
listening.

%n

The database session user name. (The expansion of this
value might change during a database session as the result of the command
SET SESSION AUTHORIZATION.)

%/

The name of the current database.

%~

Like %/, but the output is ~ (tilde) if the database is
your default database.

%#

If the session user is a database superuser, then a #,
otherwise a >. (The expansion of this value might change during a database
session as the result of the command SET SESSION AUTHORIZATION.)

%p

The process ID of the backend currently connected
to.

%R

In prompt 1 normally =, but @ if the session is in an
inactive branch of a conditional block, or ^ if in single-line mode, or ! if
the session is disconnected from the database (which can happen if
\connect fails). In prompt 2 %R is replaced by a character that depends
on why psql expects more input: — if the command simply wasn’t terminated yet,
but * if there is an unfinished /* … */ comment, a single quote if there is
an unfinished quoted string, a double quote if there is an unfinished quoted
identifier, a dollar sign if there is an unfinished dollar-quoted string, or (
if there is an unmatched left parenthesis. In prompt 3 %R doesn’t produce
anything.

%x

Transaction status: an empty string when not in a
transaction block, or * when in a transaction block, or ! when in a failed
transaction block, or ? when the transaction state is indeterminate (for
example, because there is no connection).

%l

The line number inside the current statement, starting
from 1.

%digits

The character with the indicated octal code is
substituted.

%:name:

The value of the psql variable name. See the
section Variables for details.

%`command`

The output of command, similar to ordinary
“back-tick” substitution.

%[ … %]

testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '

To insert a percent sign into your prompt, write %%. The default prompts are
‘%/%R%# ‘ for prompts 1 and 2, and ‘>> ‘ for prompt 3.

psql supports the Readline library for convenient line editing and
retrieval. The command history is automatically saved when psql exits and is
reloaded when psql starts up. Tab-completion is also supported, although the
completion logic makes no claim to be an SQL parser. The queries generated
by tab-completion can also interfere with other SQL commands, e.g. SET
TRANSACTION ISOLATION LEVEL. If for some reason you do not like the tab
completion, you can turn it off by putting this in a file named .inputrc in
your home directory:

$if psql
set disable-completion on
$endif

(This is not a psql but a Readline feature. Read its documentation
for further details.)