1PSQL(1)                  PostgreSQL 11.6 Documentation                 PSQL(1)
2
3
4

NAME

6       psql - PostgreSQL interactive terminal
7

SYNOPSIS

9       psql [option...] [dbname [username]]
10

DESCRIPTION

12       psql is a terminal-based front-end to PostgreSQL. It enables you to
13       type in queries interactively, issue them to PostgreSQL, and see the
14       query results. Alternatively, input can be from a file or from command
15       line arguments. In addition, psql provides a number of meta-commands
16       and various shell-like features to facilitate writing scripts and
17       automating a wide variety of tasks.
18

OPTIONS

20       -a
21       --echo-all
22           Print all nonempty input lines to standard output as they are read.
23           (This does not apply to lines read interactively.) This is
24           equivalent to setting the variable ECHO to all.
25
26       -A
27       --no-align
28           Switches to unaligned output mode. (The default output mode is
29           otherwise aligned.) This is equivalent to \pset format unaligned.
30
31       -b
32       --echo-errors
33           Print failed SQL commands to standard error output. This is
34           equivalent to setting the variable ECHO to errors.
35
36       -c command
37       --command=command
38           Specifies that psql is to execute the given command string,
39           command. This option can be repeated and combined in any order with
40           the -f option. When either -c or -f is specified, psql does not
41           read commands from standard input; instead it terminates after
42           processing all the -c and -f options in sequence.
43
44           command must be either a command string that is completely parsable
45           by the server (i.e., it contains no psql-specific features), or a
46           single backslash command. Thus you cannot mix SQL and psql
47           meta-commands within a -c option. To achieve that, you could use
48           repeated -c options or pipe the string into psql, for example:
49
50               psql -c '\x' -c 'SELECT * FROM foo;'
51
52           or
53
54               echo '\x \\ SELECT * FROM foo;' | psql
55
56           (\\ is the separator meta-command.)
57
58           Each SQL command string passed to -c is sent to the server as a
59           single request. Because of this, the server executes it as a single
60           transaction even if the string contains multiple SQL commands,
61           unless there are explicit BEGIN/COMMIT commands included in the
62           string to divide it into multiple transactions. (See
63           Section 53.2.2.1 for more details about how the server handles
64           multi-query strings.) Also, psql only prints the result of the last
65           SQL command in the string. This is different from the behavior when
66           the same string is read from a file or fed to psql's standard
67           input, because then psql sends each SQL command separately.
68
69           Because of this behavior, putting more than one SQL command in a
70           single -c string often has unexpected results. It's better to use
71           repeated -c commands or feed multiple commands to psql's standard
72           input, either using echo as illustrated above, or via a shell
73           here-document, for example:
74
75               psql <<EOF
76               \x
77               SELECT * FROM foo;
78               EOF
79
80
81       -d dbname
82       --dbname=dbname
83           Specifies the name of the database to connect to. This is
84           equivalent to specifying dbname as the first non-option argument on
85           the command line.
86
87           If this parameter contains an = sign or starts with a valid URI
88           prefix (postgresql:// or postgres://), it is treated as a conninfo
89           string. See Section 34.1.1 for more information.
90
91       -e
92       --echo-queries
93           Copy all SQL commands sent to the server to standard output as
94           well. This is equivalent to setting the variable ECHO to queries.
95
96       -E
97       --echo-hidden
98           Echo the actual queries generated by \d and other backslash
99           commands. You can use this to study psql's internal operations.
100           This is equivalent to setting the variable ECHO_HIDDEN to on.
101
102       -f filename
103       --file=filename
104           Read commands from the file filename, rather than standard input.
105           This option can be repeated and combined in any order with the -c
106           option. When either -c or -f is specified, psql does not read
107           commands from standard input; instead it terminates after
108           processing all the -c and -f options in sequence. Except for that,
109           this option is largely equivalent to the meta-command \i.
110
111           If filename is - (hyphen), then standard input is read until an EOF
112           indication or \q meta-command. This can be used to intersperse
113           interactive input with input from files. Note however that Readline
114           is not used in this case (much as if -n had been specified).
115
116           Using this option is subtly different from writing psql < filename.
117           In general, both will do what you expect, but using -f enables some
118           nice features such as error messages with line numbers. There is
119           also a slight chance that using this option will reduce the
120           start-up overhead. On the other hand, the variant using the shell's
121           input redirection is (in theory) guaranteed to yield exactly the
122           same output you would have received had you entered everything by
123           hand.
124
125       -F separator
126       --field-separator=separator
127           Use separator as the field separator for unaligned output. This is
128           equivalent to \pset fieldsep or \f.
129
130       -h hostname
131       --host=hostname
132           Specifies the host name of the machine on which the server is
133           running. If the value begins with a slash, it is used as the
134           directory for the Unix-domain socket.
135
136       -H
137       --html
138           Turn on HTML tabular output. This is equivalent to \pset format
139           html or the \H command.
140
141       -l
142       --list
143           List all available databases, then exit. Other non-connection
144           options are ignored. This is similar to the meta-command \list.
145
146           When this option is used, psql will connect to the database
147           postgres, unless a different database is named on the command line
148           (option -d or non-option argument, possibly via a service entry,
149           but not via an environment variable).
150
151       -L filename
152       --log-file=filename
153           Write all query output into file filename, in addition to the
154           normal output destination.
155
156       -n
157       --no-readline
158           Do not use Readline for line editing and do not use the command
159           history. This can be useful to turn off tab expansion when cutting
160           and pasting.
161
162       -o filename
163       --output=filename
164           Put all query output into file filename. This is equivalent to the
165           command \o.
166
167       -p port
168       --port=port
169           Specifies the TCP port or the local Unix-domain socket file
170           extension on which the server is listening for connections.
171           Defaults to the value of the PGPORT environment variable or, if not
172           set, to the port specified at compile time, usually 5432.
173
174       -P assignment
175       --pset=assignment
176           Specifies printing options, in the style of \pset. Note that here
177           you have to separate name and value with an equal sign instead of a
178           space. For example, to set the output format to LaTeX, you could
179           write -P format=latex.
180
181       -q
182       --quiet
183           Specifies that psql should do its work quietly. By default, it
184           prints welcome messages and various informational output. If this
185           option is used, none of this happens. This is useful with the -c
186           option. This is equivalent to setting the variable QUIET to on.
187
188       -R separator
189       --record-separator=separator
190           Use separator as the record separator for unaligned output. This is
191           equivalent to \pset recordsep.
192
193       -s
194       --single-step
195           Run in single-step mode. That means the user is prompted before
196           each command is sent to the server, with the option to cancel
197           execution as well. Use this to debug scripts.
198
199       -S
200       --single-line
201           Runs in single-line mode where a newline terminates an SQL command,
202           as a semicolon does.
203
204               Note
205               This mode is provided for those who insist on it, but you are
206               not necessarily encouraged to use it. In particular, if you mix
207               SQL and meta-commands on a line the order of execution might
208               not always be clear to the inexperienced user.
209
210       -t
211       --tuples-only
212           Turn off printing of column names and result row count footers,
213           etc. This is equivalent to \t or \pset tuples_only.
214
215       -T table_options
216       --table-attr=table_options
217           Specifies options to be placed within the HTML table tag. See \pset
218           tableattr for details.
219
220       -U username
221       --username=username
222           Connect to the database as the user username instead of the
223           default. (You must have permission to do so, of course.)
224
225       -v assignment
226       --set=assignment
227       --variable=assignment
228           Perform a variable assignment, like the \set meta-command. Note
229           that you must separate name and value, if any, by an equal sign on
230           the command line. To unset a variable, leave off the equal sign. To
231           set a variable with an empty value, use the equal sign but leave
232           off the value. These assignments are done during command line
233           processing, so variables that reflect connection state will get
234           overwritten later.
235
236       -V
237       --version
238           Print the psql version and exit.
239
240       -w
241       --no-password
242           Never issue a password prompt. If the server requires password
243           authentication and a password is not available by other means such
244           as a .pgpass file, the connection attempt will fail. This option
245           can be useful in batch jobs and scripts where no user is present to
246           enter a password.
247
248           Note that this option will remain set for the entire session, and
249           so it affects uses of the meta-command \connect as well as the
250           initial connection attempt.
251
252       -W
253       --password
254           Force psql to prompt for a password before connecting to a
255           database.
256
257           This option is never essential, since psql will automatically
258           prompt for a password if the server demands password
259           authentication. However, psql will waste a connection attempt
260           finding out that the server wants a password. In some cases it is
261           worth typing -W to avoid the extra connection attempt.
262
263           Note that this option will remain set for the entire session, and
264           so it affects uses of the meta-command \connect as well as the
265           initial connection attempt.
266
267       -x
268       --expanded
269           Turn on the expanded table formatting mode. This is equivalent to
270           \x or \pset expanded.
271
272       -X,
273       --no-psqlrc
274           Do not read the start-up file (neither the system-wide psqlrc file
275           nor the user's ~/.psqlrc file).
276
277       -z
278       --field-separator-zero
279           Set the field separator for unaligned output to a zero byte. This
280           is equivalent to \pset fieldsep_zero.
281
282       -0
283       --record-separator-zero
284           Set the record separator for unaligned output to a zero byte. This
285           is useful for interfacing, for example, with xargs -0. This is
286           equivalent to \pset recordsep_zero.
287
288       -1
289       --single-transaction
290           This option can only be used in combination with one or more -c
291           and/or -f options. It causes psql to issue a BEGIN command before
292           the first such option and a COMMIT command after the last one,
293           thereby wrapping all the commands into a single transaction. This
294           ensures that either all the commands complete successfully, or no
295           changes are applied.
296
297           If the commands themselves contain BEGIN, COMMIT, or ROLLBACK, this
298           option will not have the desired effects. Also, if an individual
299           command cannot be executed inside a transaction block, specifying
300           this option will cause the whole transaction to fail.
301
302       -?
303       --help[=topic]
304           Show help about psql and exit. The optional topic parameter
305           (defaulting to options) selects which part of psql is explained:
306           commands describes psql's backslash commands; options describes the
307           command-line options that can be passed to psql; and variables
308           shows help about psql configuration variables.
309

EXIT STATUS

311       psql returns 0 to the shell if it finished normally, 1 if a fatal error
312       of its own occurs (e.g. out of memory, file not found), 2 if the
313       connection to the server went bad and the session was not interactive,
314       and 3 if an error occurred in a script and the variable ON_ERROR_STOP
315       was set.
316

USAGE

318   Connecting to a Database
319       psql is a regular PostgreSQL client application. In order to connect to
320       a database you need to know the name of your target database, the host
321       name and port number of the server, and what user name you want to
322       connect as.  psql can be told about those parameters via command line
323       options, namely -d, -h, -p, and -U respectively. If an argument is
324       found that does not belong to any option it will be interpreted as the
325       database name (or the user name, if the database name is already
326       given). Not all of these options are required; there are useful
327       defaults. If you omit the host name, psql will connect via a
328       Unix-domain socket to a server on the local host, or via TCP/IP to
329       localhost on machines that don't have Unix-domain sockets. The default
330       port number is determined at compile time. Since the database server
331       uses the same default, you will not have to specify the port in most
332       cases. The default user name is your operating-system user name, as is
333       the default database name. Note that you cannot just connect to any
334       database under any user name. Your database administrator should have
335       informed you about your access rights.
336
337       When the defaults aren't quite right, you can save yourself some typing
338       by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or
339       PGUSER to appropriate values. (For additional environment variables,
340       see Section 34.14.) It is also convenient to have a ~/.pgpass file to
341       avoid regularly having to type in passwords. See Section 34.15 for more
342       information.
343
344       An alternative way to specify connection parameters is in a conninfo
345       string or a URI, which is used instead of a database name. This
346       mechanism give you very wide control over the connection. For example:
347
348           $ psql "service=myservice sslmode=require"
349           $ psql postgresql://dbmaster:5433/mydb?sslmode=require
350
351       This way you can also use LDAP for connection parameter lookup as
352       described in Section 34.17. See Section 34.1.2 for more information on
353       all the available connection options.
354
355       If the connection could not be made for any reason (e.g., insufficient
356       privileges, server is not running on the targeted host, etc.), psql
357       will return an error and terminate.
358
359       If both standard input and standard output are a terminal, then psql
360       sets the client encoding to “auto”, which will detect the appropriate
361       client encoding from the locale settings (LC_CTYPE environment variable
362       on Unix systems). If this doesn't work out as expected, the client
363       encoding can be overridden using the environment variable
364       PGCLIENTENCODING.
365
366   Entering SQL Commands
367       In normal operation, psql provides a prompt with the name of the
368       database to which psql is currently connected, followed by the string
369       =>. For example:
370
371           $ psql testdb
372           psql (11.6)
373           Type "help" for help.
374
375           testdb=>
376
377       At the prompt, the user can type in SQL commands. Ordinarily, input
378       lines are sent to the server when a command-terminating semicolon is
379       reached. An end of line does not terminate a command. Thus commands can
380       be spread over several lines for clarity. If the command was sent and
381       executed without error, the results of the command are displayed on the
382       screen.
383
384       If untrusted users have access to a database that has not adopted a
385       secure schema usage pattern, begin your session by removing
386       publicly-writable schemas from search_path. One can add
387       options=-csearch_path= to the connection string or issue SELECT
388       pg_catalog.set_config('search_path', '', false) before other SQL
389       commands. This consideration is not specific to psql; it applies to
390       every interface for executing arbitrary SQL commands.
391
392       Whenever a command is executed, psql also polls for asynchronous
393       notification events generated by LISTEN(7) and NOTIFY(7).
394
395       While C-style block comments are passed to the server for processing
396       and removal, SQL-standard comments are removed by psql.
397
398   Meta-Commands
399       Anything you enter in psql that begins with an unquoted backslash is a
400       psql meta-command that is processed by psql itself. These commands make
401       psql more useful for administration or scripting. Meta-commands are
402       often called slash or backslash commands.
403
404       The format of a psql command is the backslash, followed immediately by
405       a command verb, then any arguments. The arguments are separated from
406       the command verb and each other by any number of whitespace characters.
407
408       To include whitespace in an argument you can quote it with single
409       quotes. To include a single quote in an argument, write two single
410       quotes within single-quoted text. Anything contained in single quotes
411       is furthermore subject to C-like substitutions for \n (new line), \t
412       (tab), \b (backspace), \r (carriage return), \f (form feed), \digits
413       (octal), and \xdigits (hexadecimal). A backslash preceding any other
414       character within single-quoted text quotes that single character,
415       whatever it is.
416
417       If an unquoted colon (:) followed by a psql variable name appears
418       within an argument, it is replaced by the variable's value, as
419       described in SQL Interpolation. The forms :'variable_name' and
420       :"variable_name" described there work as well. The :{?variable_name}
421       syntax allows testing whether a variable is defined. It is substituted
422       by TRUE or FALSE. Escaping the colon with a backslash protects it from
423       substitution.
424
425       Within an argument, text that is enclosed in backquotes (`) is taken as
426       a command line that is passed to the shell. The output of the command
427       (with any trailing newline removed) replaces the backquoted text.
428       Within the text enclosed in backquotes, no special quoting or other
429       processing occurs, except that appearances of :variable_name where
430       variable_name is a psql variable name are replaced by the variable's
431       value. Also, appearances of :'variable_name' are replaced by the
432       variable's value suitably quoted to become a single shell command
433       argument. (The latter form is almost always preferable, unless you are
434       very sure of what is in the variable.) Because carriage return and line
435       feed characters cannot be safely quoted on all platforms, the
436       :'variable_name' form prints an error message and does not substitute
437       the variable value when such characters appear in the value.
438
439       Some commands take an SQL identifier (such as a table name) as
440       argument. These arguments follow the syntax rules of SQL: Unquoted
441       letters are forced to lowercase, while double quotes (") protect
442       letters from case conversion and allow incorporation of whitespace into
443       the identifier. Within double quotes, paired double quotes reduce to a
444       single double quote in the resulting name. For example, FOO"BAR"BAZ is
445       interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.
446
447       Parsing for arguments stops at the end of the line, or when another
448       unquoted backslash is found. An unquoted backslash is taken as the
449       beginning of a new meta-command. The special sequence \\ (two
450       backslashes) marks the end of arguments and continues parsing SQL
451       commands, if any. That way SQL and psql commands can be freely mixed on
452       a line. But in any case, the arguments of a meta-command cannot
453       continue beyond the end of the line.
454
455       Many of the meta-commands act on the current query buffer. This is
456       simply a buffer holding whatever SQL command text has been typed but
457       not yet sent to the server for execution. This will include previous
458       input lines as well as any text appearing before the meta-command on
459       the same line.
460
461       The following meta-commands are defined:
462
463       \a
464           If the current table output format is unaligned, it is switched to
465           aligned. If it is not unaligned, it is set to unaligned. This
466           command is kept for backwards compatibility. See \pset for a more
467           general solution.
468
469       \c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host
470       ] [ port ] | conninfo ]
471           Establishes a new connection to a PostgreSQL server. The connection
472           parameters to use can be specified either using a positional
473           syntax, or using conninfo connection strings as detailed in
474           Section 34.1.1.
475
476           Where the command omits database name, user, host, or port, the new
477           connection can reuse values from the previous connection. By
478           default, values from the previous connection are reused except when
479           processing a conninfo string. Passing a first argument of
480           -reuse-previous=on or -reuse-previous=off overrides that default.
481           When the command neither specifies nor reuses a particular
482           parameter, the libpq default is used. Specifying any of dbname,
483           username, host or port as - is equivalent to omitting that
484           parameter.
485
486           If the new connection is successfully made, the previous connection
487           is closed. If the connection attempt failed (wrong user name,
488           access denied, etc.), the previous connection will only be kept if
489           psql is in interactive mode. When executing a non-interactive
490           script, processing will immediately stop with an error. This
491           distinction was chosen as a user convenience against typos on the
492           one hand, and a safety mechanism that scripts are not accidentally
493           acting on the wrong database on the other hand.
494
495           Examples:
496
497               => \c mydb myuser host.dom 6432
498               => \c service=foo
499               => \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
500               => \c postgresql://tom@localhost/mydb?application_name=myapp
501
502       \C [ title ]
503           Sets the title of any tables being printed as the result of a query
504           or unset any such title. This command is equivalent to \pset title
505           title. (The name of this command derives from “caption”, as it was
506           previously only used to set the caption in an HTML table.)
507
508       \cd [ directory ]
509           Changes the current working directory to directory. Without
510           argument, changes to the current user's home directory.
511
512               Tip
513               To print your current working directory, use \! pwd.
514
515       \conninfo
516           Outputs information about the current database connection.
517
518       \copy { table [ ( column_list ) ] | ( query ) } { from | to } {
519       'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [
520       [ with ] ( option [, ...] ) ]
521           Performs a frontend (client) copy. This is an operation that runs
522           an SQL COPY(7) command, but instead of the server reading or
523           writing the specified file, psql reads or writes the file and
524           routes the data between the server and the local file system. This
525           means that file accessibility and privileges are those of the local
526           user, not the server, and no SQL superuser privileges are required.
527
528           When program is specified, command is executed by psql and the data
529           passed from or to command is routed between the server and the
530           client. Again, the execution privileges are those of the local
531           user, not the server, and no SQL superuser privileges are required.
532
533           For \copy ... from stdin, data rows are read from the same source
534           that issued the command, continuing until \.  is read or the stream
535           reaches EOF. This option is useful for populating tables in-line
536           within a SQL script file. For \copy ... to stdout, output is sent
537           to the same place as psql command output, and the COPY count
538           command status is not printed (since it might be confused with a
539           data row). To read/write psql's standard input or output regardless
540           of the current command source or \o option, write from pstdin or to
541           pstdout.
542
543           The syntax of this command is similar to that of the SQL COPY(7)
544           command. All options other than the data source/destination are as
545           specified for COPY(7). Because of this, special parsing rules apply
546           to the \copy meta-command. Unlike most other meta-commands, the
547           entire remainder of the line is always taken to be the arguments of
548           \copy, and neither variable interpolation nor backquote expansion
549           are performed in the arguments.
550
551               Tip
552               Another way to obtain the same result as \copy ... to is to use
553               the SQL COPY ... TO STDOUT command and terminate it with \g
554               filename or \g |program. Unlike \copy, this method allows the
555               command to span multiple lines; also, variable interpolation
556               and backquote expansion can be used.
557
558               Tip
559               These operations are not as efficient as the SQL COPY command
560               with a file or program data source or destination, because all
561               data must pass through the client/server connection. For large
562               amounts of data the SQL command might be preferable.
563
564       \copyright
565           Shows the copyright and distribution terms of PostgreSQL.
566
567       \crosstabview [ colV [ colH [ colD [ sortcolH ] ] ] ]
568           Executes the current query buffer (like \g) and shows the results
569           in a crosstab grid. The query must return at least three columns.
570           The output column identified by colV becomes a vertical header and
571           the output column identified by colH becomes a horizontal header.
572           colD identifies the output column to display within the grid.
573           sortcolH identifies an optional sort column for the horizontal
574           header.
575
576           Each column specification can be a column number (starting at 1) or
577           a column name. The usual SQL case folding and quoting rules apply
578           to column names. If omitted, colV is taken as column 1 and colH as
579           column 2.  colH must differ from colV. If colD is not specified,
580           then there must be exactly three columns in the query result, and
581           the column that is neither colV nor colH is taken to be colD.
582
583           The vertical header, displayed as the leftmost column, contains the
584           values found in column colV, in the same order as in the query
585           results, but with duplicates removed.
586
587           The horizontal header, displayed as the first row, contains the
588           values found in column colH, with duplicates removed. By default,
589           these appear in the same order as in the query results. But if the
590           optional sortcolH argument is given, it identifies a column whose
591           values must be integer numbers, and the values from colH will
592           appear in the horizontal header sorted according to the
593           corresponding sortcolH values.
594
595           Inside the crosstab grid, for each distinct value x of colH and
596           each distinct value y of colV, the cell located at the intersection
597           (x,y) contains the value of the colD column in the query result row
598           for which the value of colH is x and the value of colV is y. If
599           there is no such row, the cell is empty. If there are multiple such
600           rows, an error is reported.
601
602       \d[S+] [ pattern ]
603           For each relation (table, view, materialized view, index, sequence,
604           or foreign table) or composite type matching the pattern, show all
605           columns, their types, the tablespace (if not the default) and any
606           special attributes such as NOT NULL or defaults. Associated
607           indexes, constraints, rules, and triggers are also shown. For
608           foreign tables, the associated foreign server is shown as well.
609           (“Matching the pattern” is defined in Patterns below.)
610
611           For some types of relation, \d shows additional information for
612           each column: column values for sequences, indexed expressions for
613           indexes, and foreign data wrapper options for foreign tables.
614
615           The command form \d+ is identical, except that more information is
616           displayed: any comments associated with the columns of the table
617           are shown, as is the presence of OIDs in the table, the view
618           definition if the relation is a view, a non-default replica
619           identity setting.
620
621           By default, only user-created objects are shown; supply a pattern
622           or the S modifier to include system objects.
623
624               Note
625               If \d is used without a pattern argument, it is equivalent to
626               \dtvmsE which will show a list of all visible tables, views,
627               materialized views, sequences and foreign tables. This is
628               purely a convenience measure.
629
630       \da[S] [ pattern ]
631           Lists aggregate functions, together with their return type and the
632           data types they operate on. If pattern is specified, only
633           aggregates whose names match the pattern are shown. By default,
634           only user-created objects are shown; supply a pattern or the S
635           modifier to include system objects.
636
637       \dA[+] [ pattern ]
638           Lists access methods. If pattern is specified, only access methods
639           whose names match the pattern are shown. If + is appended to the
640           command name, each access method is listed with its associated
641           handler function and description.
642
643       \db[+] [ pattern ]
644           Lists tablespaces. If pattern is specified, only tablespaces whose
645           names match the pattern are shown. If + is appended to the command
646           name, each tablespace is listed with its associated options,
647           on-disk size, permissions and description.
648
649       \dc[S+] [ pattern ]
650           Lists conversions between character-set encodings. If pattern is
651           specified, only conversions whose names match the pattern are
652           listed. By default, only user-created objects are shown; supply a
653           pattern or the S modifier to include system objects. If + is
654           appended to the command name, each object is listed with its
655           associated description.
656
657       \dC[+] [ pattern ]
658           Lists type casts. If pattern is specified, only casts whose source
659           or target types match the pattern are listed. If + is appended to
660           the command name, each object is listed with its associated
661           description.
662
663       \dd[S] [ pattern ]
664           Shows the descriptions of objects of type constraint, operator
665           class, operator family, rule, and trigger. All other comments may
666           be viewed by the respective backslash commands for those object
667           types.
668
669           \dd displays descriptions for objects matching the pattern, or of
670           visible objects of the appropriate type if no argument is given.
671           But in either case, only objects that have a description are
672           listed. By default, only user-created objects are shown; supply a
673           pattern or the S modifier to include system objects.
674
675           Descriptions for objects can be created with the COMMENT(7) SQL
676           command.
677
678       \dD[S+] [ pattern ]
679           Lists domains. If pattern is specified, only domains whose names
680           match the pattern are shown. By default, only user-created objects
681           are shown; supply a pattern or the S modifier to include system
682           objects. If + is appended to the command name, each object is
683           listed with its associated permissions and description.
684
685       \ddp [ pattern ]
686           Lists default access privilege settings. An entry is shown for each
687           role (and schema, if applicable) for which the default privilege
688           settings have been changed from the built-in defaults. If pattern
689           is specified, only entries whose role name or schema name matches
690           the pattern are listed.
691
692           The ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7)) command
693           is used to set default access privileges. The meaning of the
694           privilege display is explained under GRANT(7).
695
696       \dE[S+] [ pattern ]
697       \di[S+] [ pattern ]
698       \dm[S+] [ pattern ]
699       \ds[S+] [ pattern ]
700       \dt[S+] [ pattern ]
701       \dv[S+] [ pattern ]
702           In this group of commands, the letters E, i, m, s, t, and v stand
703           for foreign table, index, materialized view, sequence, table, and
704           view, respectively. You can specify any or all of these letters, in
705           any order, to obtain a listing of objects of these types. For
706           example, \dit lists indexes and tables. If + is appended to the
707           command name, each object is listed with its physical size on disk
708           and its associated description, if any. If pattern is specified,
709           only objects whose names match the pattern are listed. By default,
710           only user-created objects are shown; supply a pattern or the S
711           modifier to include system objects.
712
713       \des[+] [ pattern ]
714           Lists foreign servers (mnemonic: “external servers”). If pattern is
715           specified, only those servers whose name matches the pattern are
716           listed. If the form \des+ is used, a full description of each
717           server is shown, including the server's ACL, type, version,
718           options, and description.
719
720       \det[+] [ pattern ]
721           Lists foreign tables (mnemonic: “external tables”). If pattern is
722           specified, only entries whose table name or schema name matches the
723           pattern are listed. If the form \det+ is used, generic options and
724           the foreign table description are also displayed.
725
726       \deu[+] [ pattern ]
727           Lists user mappings (mnemonic: “external users”). If pattern is
728           specified, only those mappings whose user names match the pattern
729           are listed. If the form \deu+ is used, additional information about
730           each mapping is shown.
731
732               Caution
733               \deu+ might also display the user name and password of the
734               remote user, so care should be taken not to disclose them.
735
736       \dew[+] [ pattern ]
737           Lists foreign-data wrappers (mnemonic: “external wrappers”). If
738           pattern is specified, only those foreign-data wrappers whose name
739           matches the pattern are listed. If the form \dew+ is used, the ACL,
740           options, and description of the foreign-data wrapper are also
741           shown.
742
743       \df[anptwS+] [ pattern ]
744           Lists functions, together with their result data types, argument
745           data types, and function types, which are classified as “agg”
746           (aggregate), “normal”, “procedure”, “trigger”, or “window”. To
747           display only functions of specific type(s), add the corresponding
748           letters a, n, p, t, or w to the command. If pattern is specified,
749           only functions whose names match the pattern are shown. By default,
750           only user-created objects are shown; supply a pattern or the S
751           modifier to include system objects. If the form \df+ is used,
752           additional information about each function is shown, including
753           volatility, parallel safety, owner, security classification, access
754           privileges, language, source code and description.
755
756               Tip
757               To look up functions taking arguments or returning values of a
758               specific data type, use your pager's search capability to
759               scroll through the \df output.
760
761       \dF[+] [ pattern ]
762           Lists text search configurations. If pattern is specified, only
763           configurations whose names match the pattern are shown. If the form
764           \dF+ is used, a full description of each configuration is shown,
765           including the underlying text search parser and the dictionary list
766           for each parser token type.
767
768       \dFd[+] [ pattern ]
769           Lists text search dictionaries. If pattern is specified, only
770           dictionaries whose names match the pattern are shown. If the form
771           \dFd+ is used, additional information is shown about each selected
772           dictionary, including the underlying text search template and the
773           option values.
774
775       \dFp[+] [ pattern ]
776           Lists text search parsers. If pattern is specified, only parsers
777           whose names match the pattern are shown. If the form \dFp+ is used,
778           a full description of each parser is shown, including the
779           underlying functions and the list of recognized token types.
780
781       \dFt[+] [ pattern ]
782           Lists text search templates. If pattern is specified, only
783           templates whose names match the pattern are shown. If the form
784           \dFt+ is used, additional information is shown about each template,
785           including the underlying function names.
786
787       \dg[S+] [ pattern ]
788           Lists database roles. (Since the concepts of “users” and “groups”
789           have been unified into “roles”, this command is now equivalent to
790           \du.) By default, only user-created roles are shown; supply the S
791           modifier to include system roles. If pattern is specified, only
792           those roles whose names match the pattern are listed. If the form
793           \dg+ is used, additional information is shown about each role;
794           currently this adds the comment for each role.
795
796       \dl
797           This is an alias for \lo_list, which shows a list of large objects.
798
799       \dL[S+] [ pattern ]
800           Lists procedural languages. If pattern is specified, only languages
801           whose names match the pattern are listed. By default, only
802           user-created languages are shown; supply the S modifier to include
803           system objects. If + is appended to the command name, each language
804           is listed with its call handler, validator, access privileges, and
805           whether it is a system object.
806
807       \dn[S+] [ pattern ]
808           Lists schemas (namespaces). If pattern is specified, only schemas
809           whose names match the pattern are listed. By default, only
810           user-created objects are shown; supply a pattern or the S modifier
811           to include system objects. If + is appended to the command name,
812           each object is listed with its associated permissions and
813           description, if any.
814
815       \do[S+] [ pattern ]
816           Lists operators with their operand and result types. If pattern is
817           specified, only operators whose names match the pattern are listed.
818           By default, only user-created objects are shown; supply a pattern
819           or the S modifier to include system objects. If + is appended to
820           the command name, additional information about each operator is
821           shown, currently just the name of the underlying function.
822
823       \dO[S+] [ pattern ]
824           Lists collations. If pattern is specified, only collations whose
825           names match the pattern are listed. By default, only user-created
826           objects are shown; supply a pattern or the S modifier to include
827           system objects. If + is appended to the command name, each
828           collation is listed with its associated description, if any. Note
829           that only collations usable with the current database's encoding
830           are shown, so the results may vary in different databases of the
831           same installation.
832
833       \dp [ pattern ]
834           Lists tables, views and sequences with their associated access
835           privileges. If pattern is specified, only tables, views and
836           sequences whose names match the pattern are listed.
837
838           The GRANT(7) and REVOKE(7) commands are used to set access
839           privileges. The meaning of the privilege display is explained under
840           GRANT(7).
841
842       \drds [ role-pattern [ database-pattern ] ]
843           Lists defined configuration settings. These settings can be
844           role-specific, database-specific, or both.  role-pattern and
845           database-pattern are used to select specific roles and databases to
846           list, respectively. If omitted, or if * is specified, all settings
847           are listed, including those not role-specific or database-specific,
848           respectively.
849
850           The ALTER ROLE (ALTER_ROLE(7)) and ALTER DATABASE
851           (ALTER_DATABASE(7)) commands are used to define per-role and
852           per-database configuration settings.
853
854       \dRp[+] [ pattern ]
855           Lists replication publications. If pattern is specified, only those
856           publications whose names match the pattern are listed. If + is
857           appended to the command name, the tables associated with each
858           publication are shown as well.
859
860       \dRs[+] [ pattern ]
861           Lists replication subscriptions. If pattern is specified, only
862           those subscriptions whose names match the pattern are listed. If +
863           is appended to the command name, additional properties of the
864           subscriptions are shown.
865
866       \dT[S+] [ pattern ]
867           Lists data types. If pattern is specified, only types whose names
868           match the pattern are listed. If + is appended to the command name,
869           each type is listed with its internal name and size, its allowed
870           values if it is an enum type, and its associated permissions. By
871           default, only user-created objects are shown; supply a pattern or
872           the S modifier to include system objects.
873
874       \du[S+] [ pattern ]
875           Lists database roles. (Since the concepts of “users” and “groups”
876           have been unified into “roles”, this command is now equivalent to
877           \dg.) By default, only user-created roles are shown; supply the S
878           modifier to include system roles. If pattern is specified, only
879           those roles whose names match the pattern are listed. If the form
880           \du+ is used, additional information is shown about each role;
881           currently this adds the comment for each role.
882
883       \dx[+] [ pattern ]
884           Lists installed extensions. If pattern is specified, only those
885           extensions whose names match the pattern are listed. If the form
886           \dx+ is used, all the objects belonging to each matching extension
887           are listed.
888
889       \dy[+] [ pattern ]
890           Lists event triggers. If pattern is specified, only those event
891           triggers whose names match the pattern are listed. If + is appended
892           to the command name, each object is listed with its associated
893           description.
894
895       \e or \edit [ filename ] [ line_number ]
896           If filename is specified, the file is edited; after the editor
897           exits, the file's content is copied into the current query buffer.
898           If no filename is given, the current query buffer is copied to a
899           temporary file which is then edited in the same fashion. Or, if the
900           current query buffer is empty, the most recently executed query is
901           copied to a temporary file and edited in the same fashion.
902
903           The new contents of the query buffer are then re-parsed according
904           to the normal rules of psql, treating the whole buffer as a single
905           line. Any complete queries are immediately executed; that is, if
906           the query buffer contains or ends with a semicolon, everything up
907           to that point is executed. Whatever remains will wait in the query
908           buffer; type semicolon or \g to send it, or \r to cancel it by
909           clearing the query buffer. Treating the buffer as a single line
910           primarily affects meta-commands: whatever is in the buffer after a
911           meta-command will be taken as argument(s) to the meta-command, even
912           if it spans multiple lines. (Thus you cannot make
913           meta-command-using scripts this way. Use \i for that.)
914
915           If a line number is specified, psql will position the cursor on the
916           specified line of the file or query buffer. Note that if a single
917           all-digits argument is given, psql assumes it is a line number, not
918           a file name.
919
920               Tip
921               See under ENVIRONMENT for how to configure and customize your
922               editor.
923
924       \echo text [ ... ]
925           Prints the arguments to the standard output, separated by one space
926           and followed by a newline. This can be useful to intersperse
927           information in the output of scripts. For example:
928
929               => \echo `date`
930               Tue Oct 26 21:40:57 CEST 1999
931
932           If the first argument is an unquoted -n the trailing newline is not
933           written.
934
935               Tip
936               If you use the \o command to redirect your query output you
937               might wish to use \qecho instead of this command.
938
939       \ef [ function_description [ line_number ] ]
940           This command fetches and edits the definition of the named function
941           or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE
942           OR REPLACE PROCEDURE command. Editing is done in the same way as
943           for \edit. After the editor exits, the updated command waits in the
944           query buffer; type semicolon or \g to send it, or \r to cancel.
945
946           The target function can be specified by name alone, or by name and
947           arguments, for example foo(integer, text). The argument types must
948           be given if there is more than one function of the same name.
949
950           If no function is specified, a blank CREATE FUNCTION template is
951           presented for editing.
952
953           If a line number is specified, psql will position the cursor on the
954           specified line of the function body. (Note that the function body
955           typically does not begin on the first line of the file.)
956
957           Unlike most other meta-commands, the entire remainder of the line
958           is always taken to be the argument(s) of \ef, and neither variable
959           interpolation nor backquote expansion are performed in the
960           arguments.
961
962               Tip
963               See under ENVIRONMENT for how to configure and customize your
964               editor.
965
966       \encoding [ encoding ]
967           Sets the client character set encoding. Without an argument, this
968           command shows the current encoding.
969
970       \errverbose
971           Repeats the most recent server error message at maximum verbosity,
972           as though VERBOSITY were set to verbose and SHOW_CONTEXT were set
973           to always.
974
975       \ev [ view_name [ line_number ] ]
976           This command fetches and edits the definition of the named view, in
977           the form of a CREATE OR REPLACE VIEW command. Editing is done in
978           the same way as for \edit. After the editor exits, the updated
979           command waits in the query buffer; type semicolon or \g to send it,
980           or \r to cancel.
981
982           If no view is specified, a blank CREATE VIEW template is presented
983           for editing.
984
985           If a line number is specified, psql will position the cursor on the
986           specified line of the view definition.
987
988           Unlike most other meta-commands, the entire remainder of the line
989           is always taken to be the argument(s) of \ev, and neither variable
990           interpolation nor backquote expansion are performed in the
991           arguments.
992
993       \f [ string ]
994           Sets the field separator for unaligned query output. The default is
995           the vertical bar (|). It is equivalent to \pset fieldsep.
996
997       \g [ filename ]
998       \g [ |command ]
999           Sends the current query buffer to the server for execution. If an
1000           argument is given, the query's output is written to the named file
1001           or piped to the given shell command, instead of displaying it as
1002           usual. The file or command is written to only if the query
1003           successfully returns zero or more tuples, not if the query fails or
1004           is a non-data-returning SQL command.
1005
1006           If the current query buffer is empty, the most recently sent query
1007           is re-executed instead. Except for that behavior, \g without an
1008           argument is essentially equivalent to a semicolon. A \g with
1009           argument is a “one-shot” alternative to the \o command.
1010
1011           If the argument begins with |, then the entire remainder of the
1012           line is taken to be the command to execute, and neither variable
1013           interpolation nor backquote expansion are performed in it. The rest
1014           of the line is simply passed literally to the shell.
1015
1016       \gdesc
1017           Shows the description (that is, the column names and data types) of
1018           the result of the current query buffer. The query is not actually
1019           executed; however, if it contains some type of syntax error, that
1020           error will be reported in the normal way.
1021
1022           If the current query buffer is empty, the most recently sent query
1023           is described instead.
1024
1025       \gexec
1026           Sends the current query buffer to the server, then treats each
1027           column of each row of the query's output (if any) as a SQL
1028           statement to be executed. For example, to create an index on each
1029           column of my_table:
1030
1031               => SELECT format('create index on my_table(%I)', attname)
1032               -> FROM pg_attribute
1033               -> WHERE attrelid = 'my_table'::regclass AND attnum > 0
1034               -> ORDER BY attnum
1035               -> \gexec
1036               CREATE INDEX
1037               CREATE INDEX
1038               CREATE INDEX
1039               CREATE INDEX
1040
1041           The generated queries are executed in the order in which the rows
1042           are returned, and left-to-right within each row if there is more
1043           than one column. NULL fields are ignored. The generated queries are
1044           sent literally to the server for processing, so they cannot be psql
1045           meta-commands nor contain psql variable references. If any
1046           individual query fails, execution of the remaining queries
1047           continues unless ON_ERROR_STOP is set. Execution of each query is
1048           subject to ECHO processing. (Setting ECHO to all or queries is
1049           often advisable when using \gexec.) Query logging, single-step
1050           mode, timing, and other query execution features apply to each
1051           generated query as well.
1052
1053           If the current query buffer is empty, the most recently sent query
1054           is re-executed instead.
1055
1056       \gset [ prefix ]
1057           Sends the current query buffer to the server and stores the query's
1058           output into psql variables (see Variables). The query to be
1059           executed must return exactly one row. Each column of the row is
1060           stored into a separate variable, named the same as the column. For
1061           example:
1062
1063               => SELECT 'hello' AS var1, 10 AS var2
1064               -> \gset
1065               => \echo :var1 :var2
1066               hello 10
1067
1068           If you specify a prefix, that string is prepended to the query's
1069           column names to create the variable names to use:
1070
1071               => SELECT 'hello' AS var1, 10 AS var2
1072               -> \gset result_
1073               => \echo :result_var1 :result_var2
1074               hello 10
1075
1076           If a column result is NULL, the corresponding variable is unset
1077           rather than being set.
1078
1079           If the query fails or does not return one row, no variables are
1080           changed.
1081
1082           If the current query buffer is empty, the most recently sent query
1083           is re-executed instead.
1084
1085       \gx [ filename ]
1086       \gx [ |command ]
1087           \gx is equivalent to \g, but forces expanded output mode for this
1088           query. See \x.
1089
1090       \h or \help [ command ]
1091           Gives syntax help on the specified SQL command. If command is not
1092           specified, then psql will list all the commands for which syntax
1093           help is available. If command is an asterisk (*), then syntax help
1094           on all SQL commands is shown.
1095
1096           Unlike most other meta-commands, the entire remainder of the line
1097           is always taken to be the argument(s) of \help, and neither
1098           variable interpolation nor backquote expansion are performed in the
1099           arguments.
1100
1101               Note
1102               To simplify typing, commands that consists of several words do
1103               not have to be quoted. Thus it is fine to type \help alter
1104               table.
1105
1106       \H or \html
1107           Turns on HTML query output format. If the HTML format is already
1108           on, it is switched back to the default aligned text format. This
1109           command is for compatibility and convenience, but see \pset about
1110           setting other output options.
1111
1112       \i or \include filename
1113           Reads input from the file filename and executes it as though it had
1114           been typed on the keyboard.
1115
1116           If filename is - (hyphen), then standard input is read until an EOF
1117           indication or \q meta-command. This can be used to intersperse
1118           interactive input with input from files. Note that Readline
1119           behavior will be used only if it is active at the outermost level.
1120
1121               Note
1122               If you want to see the lines on the screen as they are read you
1123               must set the variable ECHO to all.
1124
1125       \if expression
1126       \elif expression
1127       \else
1128       \endif
1129           This group of commands implements nestable conditional blocks. A
1130           conditional block must begin with an \if and end with an \endif. In
1131           between there may be any number of \elif clauses, which may
1132           optionally be followed by a single \else clause. Ordinary queries
1133           and other types of backslash commands may (and usually do) appear
1134           between the commands forming a conditional block.
1135
1136           The \if and \elif commands read their argument(s) and evaluate them
1137           as a boolean expression. If the expression yields true then
1138           processing continues normally; otherwise, lines are skipped until a
1139           matching \elif, \else, or \endif is reached. Once an \if or \elif
1140           test has succeeded, the arguments of later \elif commands in the
1141           same block are not evaluated but are treated as false. Lines
1142           following an \else are processed only if no earlier matching \if or
1143           \elif succeeded.
1144
1145           The expression argument of an \if or \elif command is subject to
1146           variable interpolation and backquote expansion, just like any other
1147           backslash command argument. After that it is evaluated like the
1148           value of an on/off option variable. So a valid value is any
1149           unambiguous case-insensitive match for one of: true, false, 1, 0,
1150           on, off, yes, no. For example, t, T, and tR will all be considered
1151           to be true.
1152
1153           Expressions that do not properly evaluate to true or false will
1154           generate a warning and be treated as false.
1155
1156           Lines being skipped are parsed normally to identify queries and
1157           backslash commands, but queries are not sent to the server, and
1158           backslash commands other than conditionals (\if, \elif, \else,
1159           \endif) are ignored. Conditional commands are checked only for
1160           valid nesting. Variable references in skipped lines are not
1161           expanded, and backquote expansion is not performed either.
1162
1163           All the backslash commands of a given conditional block must appear
1164           in the same source file. If EOF is reached on the main input file
1165           or an \include-ed file before all local \if-blocks have been
1166           closed, then psql will raise an error.
1167
1168           Here is an example:
1169
1170               -- check for the existence of two separate records in the database and store
1171               -- the results in separate psql variables
1172               SELECT
1173                   EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,
1174                   EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
1175               \gset
1176               \if :is_customer
1177                   SELECT * FROM customer WHERE customer_id = 123;
1178               \elif :is_employee
1179                   \echo 'is not a customer but is an employee'
1180                   SELECT * FROM employee WHERE employee_id = 456;
1181               \else
1182                   \if yes
1183                       \echo 'not a customer or employee'
1184                   \else
1185                       \echo 'this will never print'
1186                   \endif
1187               \endif
1188
1189       \ir or \include_relative filename
1190           The \ir command is similar to \i, but resolves relative file names
1191           differently. When executing in interactive mode, the two commands
1192           behave identically. However, when invoked from a script, \ir
1193           interprets file names relative to the directory in which the script
1194           is located, rather than the current working directory.
1195
1196       \l[+] or \list[+] [ pattern ]
1197           List the databases in the server and show their names, owners,
1198           character set encodings, and access privileges. If pattern is
1199           specified, only databases whose names match the pattern are listed.
1200           If + is appended to the command name, database sizes, default
1201           tablespaces, and descriptions are also displayed. (Size information
1202           is only available for databases that the current user can connect
1203           to.)
1204
1205       \lo_export loid filename
1206           Reads the large object with OID loid from the database and writes
1207           it to filename. Note that this is subtly different from the server
1208           function lo_export, which acts with the permissions of the user
1209           that the database server runs as and on the server's file system.
1210
1211               Tip
1212               Use \lo_list to find out the large object's OID.
1213
1214       \lo_import filename [ comment ]
1215           Stores the file into a PostgreSQL large object. Optionally, it
1216           associates the given comment with the object. Example:
1217
1218               foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
1219               lo_import 152801
1220
1221           The response indicates that the large object received object ID
1222           152801, which can be used to access the newly-created large object
1223           in the future. For the sake of readability, it is recommended to
1224           always associate a human-readable comment with every object. Both
1225           OIDs and comments can be viewed with the \lo_list command.
1226
1227           Note that this command is subtly different from the server-side
1228           lo_import because it acts as the local user on the local file
1229           system, rather than the server's user and file system.
1230
1231       \lo_list
1232           Shows a list of all PostgreSQL large objects currently stored in
1233           the database, along with any comments provided for them.
1234
1235       \lo_unlink loid
1236           Deletes the large object with OID loid from the database.
1237
1238               Tip
1239               Use \lo_list to find out the large object's OID.
1240
1241       \o or \out [ filename ]
1242       \o or \out [ |command ]
1243           Arranges to save future query results to the file filename or pipe
1244           future results to the shell command command. If no argument is
1245           specified, the query output is reset to the standard output.
1246
1247           If the argument begins with |, then the entire remainder of the
1248           line is taken to be the command to execute, and neither variable
1249           interpolation nor backquote expansion are performed in it. The rest
1250           of the line is simply passed literally to the shell.
1251
1252           “Query results” includes all tables, command responses, and notices
1253           obtained from the database server, as well as output of various
1254           backslash commands that query the database (such as \d); but not
1255           error messages.
1256
1257               Tip
1258               To intersperse text output in between query results, use
1259               \qecho.
1260
1261       \p or \print
1262           Print the current query buffer to the standard output. If the
1263           current query buffer is empty, the most recently executed query is
1264           printed instead.
1265
1266       \password [ username ]
1267           Changes the password of the specified user (by default, the current
1268           user). This command prompts for the new password, encrypts it, and
1269           sends it to the server as an ALTER ROLE command. This makes sure
1270           that the new password does not appear in cleartext in the command
1271           history, the server log, or elsewhere.
1272
1273       \prompt [ text ] name
1274           Prompts the user to supply text, which is assigned to the variable
1275           name. An optional prompt string, text, can be specified. (For
1276           multiword prompts, surround the text with single quotes.)
1277
1278           By default, \prompt uses the terminal for input and output.
1279           However, if the -f command line switch was used, \prompt uses
1280           standard input and standard output.
1281
1282       \pset [ option [ value ] ]
1283           This command sets options affecting the output of query result
1284           tables.  option indicates which option is to be set. The semantics
1285           of value vary depending on the selected option. For some options,
1286           omitting value causes the option to be toggled or unset, as
1287           described under the particular option. If no such behavior is
1288           mentioned, then omitting value just results in the current setting
1289           being displayed.
1290
1291           \pset without any arguments displays the current status of all
1292           printing options.
1293
1294           Adjustable printing options are:
1295
1296           border
1297               The value must be a number. In general, the higher the number
1298               the more borders and lines the tables will have, but details
1299               depend on the particular format. In HTML format, this will
1300               translate directly into the border=...  attribute. In most
1301               other formats only values 0 (no border), 1 (internal dividing
1302               lines), and 2 (table frame) make sense, and values above 2 will
1303               be treated the same as border = 2. The latex and
1304               latex-longtable formats additionally allow a value of 3 to add
1305               dividing lines between data rows.
1306
1307           columns
1308               Sets the target width for the wrapped format, and also the
1309               width limit for determining whether output is wide enough to
1310               require the pager or switch to the vertical display in expanded
1311               auto mode. Zero (the default) causes the target width to be
1312               controlled by the environment variable COLUMNS, or the detected
1313               screen width if COLUMNS is not set. In addition, if columns is
1314               zero then the wrapped format only affects screen output. If
1315               columns is nonzero then file and pipe output is wrapped to that
1316               width as well.
1317
1318           expanded (or x)
1319               If value is specified it must be either on or off, which will
1320               enable or disable expanded mode, or auto. If value is omitted
1321               the command toggles between the on and off settings. When
1322               expanded mode is enabled, query results are displayed in two
1323               columns, with the column name on the left and the data on the
1324               right. This mode is useful if the data wouldn't fit on the
1325               screen in the normal “horizontal” mode. In the auto setting,
1326               the expanded mode is used whenever the query output has more
1327               than one column and is wider than the screen; otherwise, the
1328               regular mode is used. The auto setting is only effective in the
1329               aligned and wrapped formats. In other formats, it always
1330               behaves as if the expanded mode is off.
1331
1332           fieldsep
1333               Specifies the field separator to be used in unaligned output
1334               format. That way one can create, for example, tab- or
1335               comma-separated output, which other programs might prefer. To
1336               set a tab as field separator, type \pset fieldsep '\t'. The
1337               default field separator is '|' (a vertical bar).
1338
1339           fieldsep_zero
1340               Sets the field separator to use in unaligned output format to a
1341               zero byte.
1342
1343           footer
1344               If value is specified it must be either on or off which will
1345               enable or disable display of the table footer (the (n rows)
1346               count). If value is omitted the command toggles footer display
1347               on or off.
1348
1349           format
1350               Sets the output format to one of unaligned, aligned, wrapped,
1351               html, asciidoc, latex (uses tabular), latex-longtable, or
1352               troff-ms. Unique abbreviations are allowed.
1353
1354               unaligned format writes all columns of a row on one line,
1355               separated by the currently active field separator. This is
1356               useful for creating output that might be intended to be read in
1357               by other programs (for example, tab-separated or
1358               comma-separated format).
1359
1360               aligned format is the standard, human-readable, nicely
1361               formatted text output; this is the default.
1362
1363               wrapped format is like aligned but wraps wide data values
1364               across lines to make the output fit in the target column width.
1365               The target width is determined as described under the columns
1366               option. Note that psql will not attempt to wrap column header
1367               titles; therefore, wrapped format behaves the same as aligned
1368               if the total width needed for column headers exceeds the
1369               target.
1370
1371               The html, asciidoc, latex, latex-longtable, and troff-ms
1372               formats put out tables that are intended to be included in
1373               documents using the respective mark-up language. They are not
1374               complete documents! This might not be necessary in HTML, but in
1375               LaTeX you must have a complete document wrapper.
1376               latex-longtable also requires the LaTeX longtable and booktabs
1377               packages.
1378
1379           linestyle
1380               Sets the border line drawing style to one of ascii, old-ascii,
1381               or unicode. Unique abbreviations are allowed. (That would mean
1382               one letter is enough.) The default setting is ascii. This
1383               option only affects the aligned and wrapped output formats.
1384
1385               ascii style uses plain ASCII characters. Newlines in data are
1386               shown using a + symbol in the right-hand margin. When the
1387               wrapped format wraps data from one line to the next without a
1388               newline character, a dot (.) is shown in the right-hand margin
1389               of the first line, and again in the left-hand margin of the
1390               following line.
1391
1392               old-ascii style uses plain ASCII characters, using the
1393               formatting style used in PostgreSQL 8.4 and earlier. Newlines
1394               in data are shown using a : symbol in place of the left-hand
1395               column separator. When the data is wrapped from one line to the
1396               next without a newline character, a ; symbol is used in place
1397               of the left-hand column separator.
1398
1399               unicode style uses Unicode box-drawing characters. Newlines in
1400               data are shown using a carriage return symbol in the right-hand
1401               margin. When the data is wrapped from one line to the next
1402               without a newline character, an ellipsis symbol is shown in the
1403               right-hand margin of the first line, and again in the left-hand
1404               margin of the following line.
1405
1406               When the border setting is greater than zero, the linestyle
1407               option also determines the characters with which the border
1408               lines are drawn. Plain ASCII characters work everywhere, but
1409               Unicode characters look nicer on displays that recognize them.
1410
1411           null
1412               Sets the string to be printed in place of a null value. The
1413               default is to print nothing, which can easily be mistaken for
1414               an empty string. For example, one might prefer \pset null
1415               '(null)'.
1416
1417           numericlocale
1418               If value is specified it must be either on or off which will
1419               enable or disable display of a locale-specific character to
1420               separate groups of digits to the left of the decimal marker. If
1421               value is omitted the command toggles between regular and
1422               locale-specific numeric output.
1423
1424           pager
1425               Controls use of a pager program for query and psql help output.
1426               If the environment variable PSQL_PAGER or PAGER is set, the
1427               output is piped to the specified program. Otherwise a
1428               platform-dependent default program (such as more) is used.
1429
1430               When the pager option is off, the pager program is not used.
1431               When the pager option is on, the pager is used when
1432               appropriate, i.e., when the output is to a terminal and will
1433               not fit on the screen. The pager option can also be set to
1434               always, which causes the pager to be used for all terminal
1435               output regardless of whether it fits on the screen.  \pset
1436               pager without a value toggles pager use on and off.
1437
1438           pager_min_lines
1439               If pager_min_lines is set to a number greater than the page
1440               height, the pager program will not be called unless there are
1441               at least this many lines of output to show. The default setting
1442               is 0.
1443
1444           recordsep
1445               Specifies the record (line) separator to use in unaligned
1446               output format. The default is a newline character.
1447
1448           recordsep_zero
1449               Sets the record separator to use in unaligned output format to
1450               a zero byte.
1451
1452           tableattr (or T)
1453               In HTML format, this specifies attributes to be placed inside
1454               the table tag. This could for example be cellpadding or
1455               bgcolor. Note that you probably don't want to specify border
1456               here, as that is already taken care of by \pset border. If no
1457               value is given, the table attributes are unset.
1458
1459               In latex-longtable format, this controls the proportional width
1460               of each column containing a left-aligned data type. It is
1461               specified as a whitespace-separated list of values, e.g.  '0.2
1462               0.2 0.6'. Unspecified output columns use the last specified
1463               value.
1464
1465           title (or C)
1466               Sets the table title for any subsequently printed tables. This
1467               can be used to give your output descriptive tags. If no value
1468               is given, the title is unset.
1469
1470           tuples_only (or t)
1471               If value is specified it must be either on or off which will
1472               enable or disable tuples-only mode. If value is omitted the
1473               command toggles between regular and tuples-only output. Regular
1474               output includes extra information such as column headers,
1475               titles, and various footers. In tuples-only mode, only actual
1476               table data is shown.
1477
1478           unicode_border_linestyle
1479               Sets the border drawing style for the unicode line style to one
1480               of single or double.
1481
1482           unicode_column_linestyle
1483               Sets the column drawing style for the unicode line style to one
1484               of single or double.
1485
1486           unicode_header_linestyle
1487               Sets the header drawing style for the unicode line style to one
1488               of single or double.
1489
1490           Illustrations of how these different formats look can be seen in
1491           the EXAMPLES section.
1492
1493               Tip
1494               There are various shortcut commands for \pset. See \a, \C, \f,
1495               \H, \t, \T, and \x.
1496
1497       \q or \quit
1498           Quits the psql program. In a script file, only execution of that
1499           script is terminated.
1500
1501       \qecho text [ ... ]
1502           This command is identical to \echo except that the output will be
1503           written to the query output channel, as set by \o.
1504
1505       \r or \reset
1506           Resets (clears) the query buffer.
1507
1508       \s [ filename ]
1509           Print psql's command line history to filename. If filename is
1510           omitted, the history is written to the standard output (using the
1511           pager if appropriate). This command is not available if psql was
1512           built without Readline support.
1513
1514       \set [ name [ value [ ... ] ] ]
1515           Sets the psql variable name to value, or if more than one value is
1516           given, to the concatenation of all of them. If only one argument is
1517           given, the variable is set to an empty-string value. To unset a
1518           variable, use the \unset command.
1519
1520           \set without any arguments displays the names and values of all
1521           currently-set psql variables.
1522
1523           Valid variable names can contain letters, digits, and underscores.
1524           See the section Variables below for details. Variable names are
1525           case-sensitive.
1526
1527           Certain variables are special, in that they control psql's behavior
1528           or are automatically set to reflect connection state. These
1529           variables are documented in Variables, below.
1530
1531               Note
1532               This command is unrelated to the SQL command SET(7).
1533
1534       \setenv name [ value ]
1535           Sets the environment variable name to value, or if the value is not
1536           supplied, unsets the environment variable. Example:
1537
1538               testdb=> \setenv PAGER less
1539               testdb=> \setenv LESS -imx4F
1540
1541       \sf[+] function_description
1542           This command fetches and shows the definition of the named function
1543           or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE
1544           OR REPLACE PROCEDURE command. The definition is printed to the
1545           current query output channel, as set by \o.
1546
1547           The target function can be specified by name alone, or by name and
1548           arguments, for example foo(integer, text). The argument types must
1549           be given if there is more than one function of the same name.
1550
1551           If + is appended to the command name, then the output lines are
1552           numbered, with the first line of the function body being line 1.
1553
1554           Unlike most other meta-commands, the entire remainder of the line
1555           is always taken to be the argument(s) of \sf, and neither variable
1556           interpolation nor backquote expansion are performed in the
1557           arguments.
1558
1559       \sv[+] view_name
1560           This command fetches and shows the definition of the named view, in
1561           the form of a CREATE OR REPLACE VIEW command. The definition is
1562           printed to the current query output channel, as set by \o.
1563
1564           If + is appended to the command name, then the output lines are
1565           numbered from 1.
1566
1567           Unlike most other meta-commands, the entire remainder of the line
1568           is always taken to be the argument(s) of \sv, and neither variable
1569           interpolation nor backquote expansion are performed in the
1570           arguments.
1571
1572       \t
1573           Toggles the display of output column name headings and row count
1574           footer. This command is equivalent to \pset tuples_only and is
1575           provided for convenience.
1576
1577       \T table_options
1578           Specifies attributes to be placed within the table tag in HTML
1579           output format. This command is equivalent to \pset tableattr
1580           table_options.
1581
1582       \timing [ on | off ]
1583           With a parameter, turns displaying of how long each SQL statement
1584           takes on or off. Without a parameter, toggles the display between
1585           on and off. The display is in milliseconds; intervals longer than 1
1586           second are also shown in minutes:seconds format, with hours and
1587           days fields added if needed.
1588
1589       \unset name
1590           Unsets (deletes) the psql variable name.
1591
1592           Most variables that control psql's behavior cannot be unset;
1593           instead, an \unset command is interpreted as setting them to their
1594           default values. See Variables, below.
1595
1596       \w or \write filename
1597       \w or \write |command
1598           Writes the current query buffer to the file filename or pipes it to
1599           the shell command command. If the current query buffer is empty,
1600           the most recently executed query is written instead.
1601
1602           If the argument begins with |, then the entire remainder of the
1603           line is taken to be the command to execute, and neither variable
1604           interpolation nor backquote expansion are performed in it. The rest
1605           of the line is simply passed literally to the shell.
1606
1607       \watch [ seconds ]
1608           Repeatedly execute the current query buffer (as \g does) until
1609           interrupted or the query fails. Wait the specified number of
1610           seconds (default 2) between executions. Each query result is
1611           displayed with a header that includes the \pset title string (if
1612           any), the time as of query start, and the delay interval.
1613
1614           If the current query buffer is empty, the most recently sent query
1615           is re-executed instead.
1616
1617       \x [ on | off | auto ]
1618           Sets or toggles expanded table formatting mode. As such it is
1619           equivalent to \pset expanded.
1620
1621       \z [ pattern ]
1622           Lists tables, views and sequences with their associated access
1623           privileges. If a pattern is specified, only tables, views and
1624           sequences whose names match the pattern are listed.
1625
1626           This is an alias for \dp (“display privileges”).
1627
1628       \! [ command ]
1629           With no argument, escapes to a sub-shell; psql resumes when the
1630           sub-shell exits. With an argument, executes the shell command
1631           command.
1632
1633           Unlike most other meta-commands, the entire remainder of the line
1634           is always taken to be the argument(s) of \!, and neither variable
1635           interpolation nor backquote expansion are performed in the
1636           arguments. The rest of the line is simply passed literally to the
1637           shell.
1638
1639       \? [ topic ]
1640           Shows help information. The optional topic parameter (defaulting to
1641           commands) selects which part of psql is explained: commands
1642           describes psql's backslash commands; options describes the
1643           command-line options that can be passed to psql; and variables
1644           shows help about psql configuration variables.
1645
1646       \;
1647           Backslash-semicolon is not a meta-command in the same way as the
1648           preceding commands; rather, it simply causes a semicolon to be
1649           added to the query buffer without any further processing.
1650
1651           Normally, psql will dispatch a SQL command to the server as soon as
1652           it reaches the command-ending semicolon, even if more input remains
1653           on the current line. Thus for example entering
1654
1655               select 1; select 2; select 3;
1656
1657           will result in the three SQL commands being individually sent to
1658           the server, with each one's results being displayed before
1659           continuing to the next command. However, a semicolon entered as \;
1660           will not trigger command processing, so that the command before it
1661           and the one after are effectively combined and sent to the server
1662           in one request. So for example
1663
1664               select 1\; select 2\; select 3;
1665
1666           results in sending the three SQL commands to the server in a single
1667           request, when the non-backslashed semicolon is reached. The server
1668           executes such a request as a single transaction, unless there are
1669           explicit BEGIN/COMMIT commands included in the string to divide it
1670           into multiple transactions. (See Section 53.2.2.1 for more details
1671           about how the server handles multi-query strings.)  psql prints
1672           only the last query result it receives for each request; in this
1673           example, although all three SELECTs are indeed executed, psql only
1674           prints the 3.
1675
1676       Patterns
1677           The various \d commands accept a pattern parameter to specify the
1678           object name(s) to be displayed. In the simplest case, a pattern is
1679           just the exact name of the object. The characters within a pattern
1680           are normally folded to lower case, just as in SQL names; for
1681           example, \dt FOO will display the table named foo. As in SQL names,
1682           placing double quotes around a pattern stops folding to lower case.
1683           Should you need to include an actual double quote character in a
1684           pattern, write it as a pair of double quotes within a double-quote
1685           sequence; again this is in accord with the rules for SQL quoted
1686           identifiers. For example, \dt "FOO""BAR" will display the table
1687           named FOO"BAR (not foo"bar). Unlike the normal rules for SQL names,
1688           you can put double quotes around just part of a pattern, for
1689           instance \dt FOO"FOO"BAR will display the table named fooFOObar.
1690
1691           Whenever the pattern parameter is omitted completely, the \d
1692           commands display all objects that are visible in the current schema
1693           search path — this is equivalent to using * as the pattern. (An
1694           object is said to be visible if its containing schema is in the
1695           search path and no object of the same kind and name appears earlier
1696           in the search path. This is equivalent to the statement that the
1697           object can be referenced by name without explicit schema
1698           qualification.) To see all objects in the database regardless of
1699           visibility, use *.*  as the pattern.
1700
1701           Within a pattern, * matches any sequence of characters (including
1702           no characters) and ?  matches any single character. (This notation
1703           is comparable to Unix shell file name patterns.) For example, \dt
1704           int* displays tables whose names begin with int. But within double
1705           quotes, * and ?  lose these special meanings and are just matched
1706           literally.
1707
1708           A pattern that contains a dot (.) is interpreted as a schema name
1709           pattern followed by an object name pattern. For example, \dt
1710           foo*.*bar* displays all tables whose table name includes bar that
1711           are in schemas whose schema name starts with foo. When no dot
1712           appears, then the pattern matches only objects that are visible in
1713           the current schema search path. Again, a dot within double quotes
1714           loses its special meaning and is matched literally.
1715
1716           Advanced users can use regular-expression notations such as
1717           character classes, for example [0-9] to match any digit. All
1718           regular expression special characters work as specified in
1719           Section 9.7.3, except for .  which is taken as a separator as
1720           mentioned above, * which is translated to the regular-expression
1721           notation .*, ?  which is translated to ., and $ which is matched
1722           literally. You can emulate these pattern characters at need by
1723           writing ?  for ., (R+|) for R*, or (R|) for R?.  $ is not needed as
1724           a regular-expression character since the pattern must match the
1725           whole name, unlike the usual interpretation of regular expressions
1726           (in other words, $ is automatically appended to your pattern).
1727           Write * at the beginning and/or end if you don't wish the pattern
1728           to be anchored. Note that within double quotes, all regular
1729           expression special characters lose their special meanings and are
1730           matched literally. Also, the regular expression special characters
1731           are matched literally in operator name patterns (i.e., the argument
1732           of \do).
1733
1734   Advanced Features
1735       Variables
1736           psql provides variable substitution features similar to common Unix
1737           command shells. Variables are simply name/value pairs, where the
1738           value can be any string of any length. The name must consist of
1739           letters (including non-Latin letters), digits, and underscores.
1740
1741           To set a variable, use the psql meta-command \set. For example,
1742
1743               testdb=> \set foo bar
1744
1745           sets the variable foo to the value bar. To retrieve the content of
1746           the variable, precede the name with a colon, for example:
1747
1748               testdb=> \echo :foo
1749               bar
1750
1751           This works in both regular SQL commands and meta-commands; there is
1752           more detail in SQL Interpolation, below.
1753
1754           If you call \set without a second argument, the variable is set to
1755           an empty-string value. To unset (i.e., delete) a variable, use the
1756           command \unset. To show the values of all variables, call \set
1757           without any argument.
1758
1759               Note
1760               The arguments of \set are subject to the same substitution
1761               rules as with other commands. Thus you can construct
1762               interesting references such as \set :foo 'something' and get
1763               “soft links” or “variable variables” of Perl or PHP fame,
1764               respectively. Unfortunately (or fortunately?), there is no way
1765               to do anything useful with these constructs. On the other hand,
1766               \set bar :foo is a perfectly valid way to copy a variable.
1767
1768           A number of these variables are treated specially by psql. They
1769           represent certain option settings that can be changed at run time
1770           by altering the value of the variable, or in some cases represent
1771           changeable state of psql. By convention, all specially treated
1772           variables' names consist of all upper-case ASCII letters (and
1773           possibly digits and underscores). To ensure maximum compatibility
1774           in the future, avoid using such variable names for your own
1775           purposes.
1776
1777           Variables that control psql's behavior generally cannot be unset or
1778           set to invalid values. An \unset command is allowed but is
1779           interpreted as setting the variable to its default value. A \set
1780           command without a second argument is interpreted as setting the
1781           variable to on, for control variables that accept that value, and
1782           is rejected for others. Also, control variables that accept the
1783           values on and off will also accept other common spellings of
1784           Boolean values, such as true and false.
1785
1786           The specially treated variables are:
1787
1788           AUTOCOMMIT
1789               When on (the default), each SQL command is automatically
1790               committed upon successful completion. To postpone commit in
1791               this mode, you must enter a BEGIN or START TRANSACTION SQL
1792               command. When off or unset, SQL commands are not committed
1793               until you explicitly issue COMMIT or END. The autocommit-off
1794               mode works by issuing an implicit BEGIN for you, just before
1795               any command that is not already in a transaction block and is
1796               not itself a BEGIN or other transaction-control command, nor a
1797               command that cannot be executed inside a transaction block
1798               (such as VACUUM).
1799
1800                   Note
1801                   In autocommit-off mode, you must explicitly abandon any
1802                   failed transaction by entering ABORT or ROLLBACK. Also keep
1803                   in mind that if you exit the session without committing,
1804                   your work will be lost.
1805
1806                   Note
1807                   The autocommit-on mode is PostgreSQL's traditional
1808                   behavior, but autocommit-off is closer to the SQL spec. If
1809                   you prefer autocommit-off, you might wish to set it in the
1810                   system-wide psqlrc file or your ~/.psqlrc file.
1811
1812           COMP_KEYWORD_CASE
1813               Determines which letter case to use when completing an SQL key
1814               word. If set to lower or upper, the completed word will be in
1815               lower or upper case, respectively. If set to preserve-lower or
1816               preserve-upper (the default), the completed word will be in the
1817               case of the word already entered, but words being completed
1818               without anything entered will be in lower or upper case,
1819               respectively.
1820
1821           DBNAME
1822               The name of the database you are currently connected to. This
1823               is set every time you connect to a database (including program
1824               start-up), but can be changed or unset.
1825
1826           ECHO
1827               If set to all, all nonempty input lines are printed to standard
1828               output as they are read. (This does not apply to lines read
1829               interactively.) To select this behavior on program start-up,
1830               use the switch -a. If set to queries, psql prints each query to
1831               standard output as it is sent to the server. The switch to
1832               select this behavior is -e. If set to errors, then only failed
1833               queries are displayed on standard error output. The switch for
1834               this behavior is -b. If set to none (the default), then no
1835               queries are displayed.
1836
1837           ECHO_HIDDEN
1838               When this variable is set to on and a backslash command queries
1839               the database, the query is first shown. This feature helps you
1840               to study PostgreSQL internals and provide similar functionality
1841               in your own programs. (To select this behavior on program
1842               start-up, use the switch -E.) If you set this variable to the
1843               value noexec, the queries are just shown but are not actually
1844               sent to the server and executed. The default value is off.
1845
1846           ENCODING
1847               The current client character set encoding. This is set every
1848               time you connect to a database (including program start-up),
1849               and when you change the encoding with \encoding, but it can be
1850               changed or unset.
1851
1852           ERROR
1853               true if the last SQL query failed, false if it succeeded. See
1854               also SQLSTATE.
1855
1856           FETCH_COUNT
1857               If this variable is set to an integer value greater than zero,
1858               the results of SELECT queries are fetched and displayed in
1859               groups of that many rows, rather than the default behavior of
1860               collecting the entire result set before display. Therefore only
1861               a limited amount of memory is used, regardless of the size of
1862               the result set. Settings of 100 to 1000 are commonly used when
1863               enabling this feature. Keep in mind that when using this
1864               feature, a query might fail after having already displayed some
1865               rows.
1866
1867                   Tip
1868                   Although you can use any output format with this feature,
1869                   the default aligned format tends to look bad because each
1870                   group of FETCH_COUNT rows will be formatted separately,
1871                   leading to varying column widths across the row groups. The
1872                   other output formats work better.
1873
1874           HISTCONTROL
1875               If this variable is set to ignorespace, lines which begin with
1876               a space are not entered into the history list. If set to a
1877               value of ignoredups, lines matching the previous history line
1878               are not entered. A value of ignoreboth combines the two
1879               options. If set to none (the default), all lines read in
1880               interactive mode are saved on the history list.
1881
1882                   Note
1883                   This feature was shamelessly plagiarized from Bash.
1884
1885           HISTFILE
1886               The file name that will be used to store the history list. If
1887               unset, the file name is taken from the PSQL_HISTORY environment
1888               variable. If that is not set either, the default is
1889               ~/.psql_history, or %APPDATA%\postgresql\psql_history on
1890               Windows. For example, putting:
1891
1892                   \set HISTFILE ~/.psql_history- :DBNAME
1893
1894               in ~/.psqlrc will cause psql to maintain a separate history for
1895               each database.
1896
1897                   Note
1898                   This feature was shamelessly plagiarized from Bash.
1899
1900           HISTSIZE
1901               The maximum number of commands to store in the command history
1902               (default 500). If set to a negative value, no limit is applied.
1903
1904                   Note
1905                   This feature was shamelessly plagiarized from Bash.
1906
1907           HOST
1908               The database server host you are currently connected to. This
1909               is set every time you connect to a database (including program
1910               start-up), but can be changed or unset.
1911
1912           IGNOREEOF
1913               If set to 1 or less, sending an EOF character (usually
1914               Control+D) to an interactive session of psql will terminate the
1915               application. If set to a larger numeric value, that many
1916               consecutive EOF characters must be typed to make an interactive
1917               session terminate. If the variable is set to a non-numeric
1918               value, it is interpreted as 10. The default is 0.
1919
1920                   Note
1921                   This feature was shamelessly plagiarized from Bash.
1922
1923           LASTOID
1924               The value of the last affected OID, as returned from an INSERT
1925               or \lo_import command. This variable is only guaranteed to be
1926               valid until after the result of the next SQL command has been
1927               displayed.
1928
1929           LAST_ERROR_MESSAGE
1930           LAST_ERROR_SQLSTATE
1931               The primary error message and associated SQLSTATE code for the
1932               most recent failed query in the current psql session, or an
1933               empty string and 00000 if no error has occurred in the current
1934               session.
1935
1936           ON_ERROR_ROLLBACK
1937               When set to on, if a statement in a transaction block generates
1938               an error, the error is ignored and the transaction continues.
1939               When set to interactive, such errors are only ignored in
1940               interactive sessions, and not when reading script files. When
1941               set to off (the default), a statement in a transaction block
1942               that generates an error aborts the entire transaction. The
1943               error rollback mode works by issuing an implicit SAVEPOINT for
1944               you, just before each command that is in a transaction block,
1945               and then rolling back to the savepoint if the command fails.
1946
1947           ON_ERROR_STOP
1948               By default, command processing continues after an error. When
1949               this variable is set to on, processing will instead stop
1950               immediately. In interactive mode, psql will return to the
1951               command prompt; otherwise, psql will exit, returning error code
1952               3 to distinguish this case from fatal error conditions, which
1953               are reported using error code 1. In either case, any currently
1954               running scripts (the top-level script, if any, and any other
1955               scripts which it may have in invoked) will be terminated
1956               immediately. If the top-level command string contained multiple
1957               SQL commands, processing will stop with the current command.
1958
1959           PORT
1960               The database server port to which you are currently connected.
1961               This is set every time you connect to a database (including
1962               program start-up), but can be changed or unset.
1963
1964           PROMPT1
1965           PROMPT2
1966           PROMPT3
1967               These specify what the prompts psql issues should look like.
1968               See Prompting below.
1969
1970           QUIET
1971               Setting this variable to on is equivalent to the command line
1972               option -q. It is probably not too useful in interactive mode.
1973
1974           ROW_COUNT
1975               The number of rows returned or affected by the last SQL query,
1976               or 0 if the query failed or did not report a row count.
1977
1978           SERVER_VERSION_NAME
1979           SERVER_VERSION_NUM
1980               The server's version number as a string, for example 9.6.2,
1981               10.1 or 11beta1, and in numeric form, for example 90602 or
1982               100001. These are set every time you connect to a database
1983               (including program start-up), but can be changed or unset.
1984
1985           SHOW_CONTEXT
1986               This variable can be set to the values never, errors, or always
1987               to control whether CONTEXT fields are displayed in messages
1988               from the server. The default is errors (meaning that context
1989               will be shown in error messages, but not in notice or warning
1990               messages). This setting has no effect when VERBOSITY is set to
1991               terse. (See also \errverbose, for use when you want a verbose
1992               version of the error you just got.)
1993
1994           SINGLELINE
1995               Setting this variable to on is equivalent to the command line
1996               option -S.
1997
1998           SINGLESTEP
1999               Setting this variable to on is equivalent to the command line
2000               option -s.
2001
2002           SQLSTATE
2003               The error code (see Appendix A) associated with the last SQL
2004               query's failure, or 00000 if it succeeded.
2005
2006           USER
2007               The database user you are currently connected as. This is set
2008               every time you connect to a database (including program
2009               start-up), but can be changed or unset.
2010
2011           VERBOSITY
2012               This variable can be set to the values default, verbose, or
2013               terse to control the verbosity of error reports. (See also
2014               \errverbose, for use when you want a verbose version of the
2015               error you just got.)
2016
2017           VERSION
2018           VERSION_NAME
2019           VERSION_NUM
2020               These variables are set at program start-up to reflect psql's
2021               version, respectively as a verbose string, a short string
2022               (e.g., 9.6.2, 10.1, or 11beta1), and a number (e.g., 90602 or
2023               100001). They can be changed or unset.
2024
2025       SQL Interpolation
2026           A key feature of psql variables is that you can substitute
2027           (“interpolate”) them into regular SQL statements, as well as the
2028           arguments of meta-commands. Furthermore, psql provides facilities
2029           for ensuring that variable values used as SQL literals and
2030           identifiers are properly quoted. The syntax for interpolating a
2031           value without any quoting is to prepend the variable name with a
2032           colon (:). For example,
2033
2034               testdb=> \set foo 'my_table'
2035               testdb=> SELECT * FROM :foo;
2036
2037           would query the table my_table. Note that this may be unsafe: the
2038           value of the variable is copied literally, so it can contain
2039           unbalanced quotes, or even backslash commands. You must make sure
2040           that it makes sense where you put it.
2041
2042           When a value is to be used as an SQL literal or identifier, it is
2043           safest to arrange for it to be quoted. To quote the value of a
2044           variable as an SQL literal, write a colon followed by the variable
2045           name in single quotes. To quote the value as an SQL identifier,
2046           write a colon followed by the variable name in double quotes. These
2047           constructs deal correctly with quotes and other special characters
2048           embedded within the variable value. The previous example would be
2049           more safely written this way:
2050
2051               testdb=> \set foo 'my_table'
2052               testdb=> SELECT * FROM :"foo";
2053
2054           Variable interpolation will not be performed within quoted SQL
2055           literals and identifiers. Therefore, a construction such as ':foo'
2056           doesn't work to produce a quoted literal from a variable's value
2057           (and it would be unsafe if it did work, since it wouldn't correctly
2058           handle quotes embedded in the value).
2059
2060           One example use of this mechanism is to copy the contents of a file
2061           into a table column. First load the file into a variable and then
2062           interpolate the variable's value as a quoted string:
2063
2064               testdb=> \set content `cat my_file.txt`
2065               testdb=> INSERT INTO my_table VALUES (:'content');
2066
2067           (Note that this still won't work if my_file.txt contains NUL bytes.
2068           psql does not support embedded NUL bytes in variable values.)
2069
2070           Since colons can legally appear in SQL commands, an apparent
2071           attempt at interpolation (that is, :name, :'name', or :"name") is
2072           not replaced unless the named variable is currently set. In any
2073           case, you can escape a colon with a backslash to protect it from
2074           substitution.
2075
2076           The :{?name} special syntax returns TRUE or FALSE depending on
2077           whether the variable exists or not, and is thus always substituted,
2078           unless the colon is backslash-escaped.
2079
2080           The colon syntax for variables is standard SQL for embedded query
2081           languages, such as ECPG. The colon syntaxes for array slices and
2082           type casts are PostgreSQL extensions, which can sometimes conflict
2083           with the standard usage. The colon-quote syntax for escaping a
2084           variable's value as an SQL literal or identifier is a psql
2085           extension.
2086
2087       Prompting
2088           The prompts psql issues can be customized to your preference. The
2089           three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and
2090           special escape sequences that describe the appearance of the
2091           prompt. Prompt 1 is the normal prompt that is issued when psql
2092           requests a new command. Prompt 2 is issued when more input is
2093           expected during command entry, for example because the command was
2094           not terminated with a semicolon or a quote was not closed. Prompt 3
2095           is issued when you are running an SQL COPY FROM STDIN command and
2096           you need to type in a row value on the terminal.
2097
2098           The value of the selected prompt variable is printed literally,
2099           except where a percent sign (%) is encountered. Depending on the
2100           next character, certain other text is substituted instead. Defined
2101           substitutions are:
2102
2103           %M
2104               The full host name (with domain name) of the database server,
2105               or [local] if the connection is over a Unix domain socket, or
2106               [local:/dir/name], if the Unix domain socket is not at the
2107               compiled in default location.
2108
2109           %m
2110               The host name of the database server, truncated at the first
2111               dot, or [local] if the connection is over a Unix domain socket.
2112
2113           %>
2114               The port number at which the database server is listening.
2115
2116           %n
2117               The database session user name. (The expansion of this value
2118               might change during a database session as the result of the
2119               command SET SESSION AUTHORIZATION.)
2120
2121           %/
2122               The name of the current database.
2123
2124           %~
2125               Like %/, but the output is ~ (tilde) if the database is your
2126               default database.
2127
2128           %#
2129               If the session user is a database superuser, then a #,
2130               otherwise a >. (The expansion of this value might change during
2131               a database session as the result of the command SET SESSION
2132               AUTHORIZATION.)
2133
2134           %p
2135               The process ID of the backend currently connected to.
2136
2137           %R
2138               In prompt 1 normally =, but @ if the session is in an inactive
2139               branch of a conditional block, or ^ if in single-line mode, or
2140               !  if the session is disconnected from the database (which can
2141               happen if \connect fails). In prompt 2 %R is replaced by a
2142               character that depends on why psql expects more input: - if the
2143               command simply wasn't terminated yet, but * if there is an
2144               unfinished /* ... */ comment, a single quote if there is an
2145               unfinished quoted string, a double quote if there is an
2146               unfinished quoted identifier, a dollar sign if there is an
2147               unfinished dollar-quoted string, or ( if there is an unmatched
2148               left parenthesis. In prompt 3 %R doesn't produce anything.
2149
2150           %x
2151               Transaction status: an empty string when not in a transaction
2152               block, or * when in a transaction block, or !  when in a failed
2153               transaction block, or ?  when the transaction state is
2154               indeterminate (for example, because there is no connection).
2155
2156           %l
2157               The line number inside the current statement, starting from 1.
2158
2159           %digits
2160               The character with the indicated octal code is substituted.
2161
2162           %:name:
2163               The value of the psql variable name. See the section Variables
2164               for details.
2165
2166           %`command`
2167               The output of command, similar to ordinary “back-tick”
2168               substitution.
2169
2170           %[ ... %]
2171               Prompts can contain terminal control characters which, for
2172               example, change the color, background, or style of the prompt
2173               text, or change the title of the terminal window. In order for
2174               the line editing features of Readline to work properly, these
2175               non-printing control characters must be designated as invisible
2176               by surrounding them with %[ and %]. Multiple pairs of these can
2177               occur within the prompt. For example:
2178
2179                   testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
2180
2181               results in a boldfaced (1;) yellow-on-black (33;40) prompt on
2182               VT100-compatible, color-capable terminals.
2183           To insert a percent sign into your prompt, write %%. The default
2184           prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.
2185
2186               Note
2187               This feature was shamelessly plagiarized from tcsh.
2188
2189       Command-Line Editing
2190           psql supports the Readline library for convenient line editing and
2191           retrieval. The command history is automatically saved when psql
2192           exits and is reloaded when psql starts up. Tab-completion is also
2193           supported, although the completion logic makes no claim to be an
2194           SQL parser. The queries generated by tab-completion can also
2195           interfere with other SQL commands, e.g.  SET TRANSACTION ISOLATION
2196           LEVEL. If for some reason you do not like the tab completion, you
2197           can turn it off by putting this in a file named .inputrc in your
2198           home directory:
2199
2200               $if psql
2201               set disable-completion on
2202               $endif
2203
2204           (This is not a psql but a Readline feature. Read its documentation
2205           for further details.)
2206

ENVIRONMENT

2208       COLUMNS
2209           If \pset columns is zero, controls the width for the wrapped format
2210           and width for determining if wide output requires the pager or
2211           should be switched to the vertical format in expanded auto mode.
2212
2213       PGDATABASE
2214       PGHOST
2215       PGPORT
2216       PGUSER
2217           Default connection parameters (see Section 34.14).
2218
2219       PSQL_EDITOR
2220       EDITOR
2221       VISUAL
2222           Editor used by the \e, \ef, and \ev commands. These variables are
2223           examined in the order listed; the first that is set is used. If
2224           none of them is set, the default is to use vi on Unix systems or
2225           notepad.exe on Windows systems.
2226
2227       PSQL_EDITOR_LINENUMBER_ARG
2228           When \e, \ef, or \ev is used with a line number argument, this
2229           variable specifies the command-line argument used to pass the
2230           starting line number to the user's editor. For editors such as
2231           Emacs or vi, this is a plus sign. Include a trailing space in the
2232           value of the variable if there needs to be space between the option
2233           name and the line number. Examples:
2234
2235               PSQL_EDITOR_LINENUMBER_ARG='+'
2236               PSQL_EDITOR_LINENUMBER_ARG='--line '
2237
2238           The default is + on Unix systems (corresponding to the default
2239           editor vi, and useful for many other common editors); but there is
2240           no default on Windows systems.
2241
2242       PSQL_HISTORY
2243           Alternative location for the command history file. Tilde (~)
2244           expansion is performed.
2245
2246       PSQL_PAGER
2247       PAGER
2248           If a query's results do not fit on the screen, they are piped
2249           through this command. Typical values are more or less. Use of the
2250           pager can be disabled by setting PSQL_PAGER or PAGER to an empty
2251           string, or by adjusting the pager-related options of the \pset
2252           command. These variables are examined in the order listed; the
2253           first that is set is used. If none of them is set, the default is
2254           to use more on most platforms, but less on Cygwin.
2255
2256       PSQLRC
2257           Alternative location of the user's .psqlrc file. Tilde (~)
2258           expansion is performed.
2259
2260       SHELL
2261           Command executed by the \!  command.
2262
2263       TMPDIR
2264           Directory for storing temporary files. The default is /tmp.
2265
2266       This utility, like most other PostgreSQL utilities, also uses the
2267       environment variables supported by libpq (see Section 34.14).
2268

FILES

2270       psqlrc and ~/.psqlrc
2271           Unless it is passed an -X option, psql attempts to read and execute
2272           commands from the system-wide startup file (psqlrc) and then the
2273           user's personal startup file (~/.psqlrc), after connecting to the
2274           database but before accepting normal commands. These files can be
2275           used to set up the client and/or the server to taste, typically
2276           with \set and SET commands.
2277
2278           The system-wide startup file is named psqlrc and is sought in the
2279           installation's “system configuration” directory, which is most
2280           reliably identified by running pg_config --sysconfdir. By default
2281           this directory will be ../etc/ relative to the directory containing
2282           the PostgreSQL executables. The name of this directory can be set
2283           explicitly via the PGSYSCONFDIR environment variable.
2284
2285           The user's personal startup file is named .psqlrc and is sought in
2286           the invoking user's home directory. On Windows, which lacks such a
2287           concept, the personal startup file is named
2288           %APPDATA%\postgresql\psqlrc.conf. The location of the user's
2289           startup file can be set explicitly via the PSQLRC environment
2290           variable.
2291
2292           Both the system-wide startup file and the user's personal startup
2293           file can be made psql-version-specific by appending a dash and the
2294           PostgreSQL major or minor release number to the file name, for
2295           example ~/.psqlrc-9.2 or ~/.psqlrc-9.2.5. The most specific
2296           version-matching file will be read in preference to a
2297           non-version-specific file.
2298
2299       .psql_history
2300           The command-line history is stored in the file ~/.psql_history, or
2301           %APPDATA%\postgresql\psql_history on Windows.
2302
2303           The location of the history file can be set explicitly via the
2304           HISTFILE psql variable or the PSQL_HISTORY environment variable.
2305

NOTES

2307       ·   psql works best with servers of the same or an older major version.
2308           Backslash commands are particularly likely to fail if the server is
2309           of a newer version than psql itself. However, backslash commands of
2310           the \d family should work with servers of versions back to 7.4,
2311           though not necessarily with servers newer than psql itself. The
2312           general functionality of running SQL commands and displaying query
2313           results should also work with servers of a newer major version, but
2314           this cannot be guaranteed in all cases.
2315
2316           If you want to use psql to connect to several servers of different
2317           major versions, it is recommended that you use the newest version
2318           of psql. Alternatively, you can keep around a copy of psql from
2319           each major version and be sure to use the version that matches the
2320           respective server. But in practice, this additional complication
2321           should not be necessary.
2322
2323       ·   Before PostgreSQL 9.6, the -c option implied -X (--no-psqlrc); this
2324           is no longer the case.
2325
2326       ·   Before PostgreSQL 8.4, psql allowed the first argument of a
2327           single-letter backslash command to start directly after the
2328           command, without intervening whitespace. Now, some whitespace is
2329           required.
2330

NOTES FOR WINDOWS USERS

2332       psql is built as a “console application”. Since the Windows console
2333       windows use a different encoding than the rest of the system, you must
2334       take special care when using 8-bit characters within psql. If psql
2335       detects a problematic console code page, it will warn you at startup.
2336       To change the console code page, two things are necessary:
2337
2338       ·   Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
2339           page that is appropriate for German; replace it with your value.)
2340           If you are using Cygwin, you can put this command in /etc/profile.
2341
2342       ·   Set the console font to Lucida Console, because the raster font
2343           does not work with the ANSI code page.
2344

EXAMPLES

2346       The first example shows how to spread a command over several lines of
2347       input. Notice the changing prompt:
2348
2349           testdb=> CREATE TABLE my_table (
2350           testdb(>  first integer not null default 0,
2351           testdb(>  second text)
2352           testdb-> ;
2353           CREATE TABLE
2354
2355       Now look at the table definition again:
2356
2357           testdb=> \d my_table
2358                         Table "public.my_table"
2359            Column |  Type   | Collation | Nullable | Default
2360           --------+---------+-----------+----------+---------
2361            first  | integer |           | not null | 0
2362            second | text    |           |          |
2363
2364       Now we change the prompt to something more interesting:
2365
2366           testdb=> \set PROMPT1 '%n@%m %~%R%# '
2367           peter@localhost testdb=>
2368
2369       Let's assume you have filled the table with data and want to take a
2370       look at it:
2371
2372           peter@localhost testdb=> SELECT * FROM my_table;
2373            first | second
2374           -------+--------
2375                1 | one
2376                2 | two
2377                3 | three
2378                4 | four
2379           (4 rows)
2380
2381
2382       You can display tables in different ways by using the \pset command:
2383
2384           peter@localhost testdb=> \pset border 2
2385           Border style is 2.
2386           peter@localhost testdb=> SELECT * FROM my_table;
2387           +-------+--------+
2388           | first | second |
2389           +-------+--------+
2390           |     1 | one    |
2391           |     2 | two    |
2392           |     3 | three  |
2393           |     4 | four   |
2394           +-------+--------+
2395           (4 rows)
2396
2397           peter@localhost testdb=> \pset border 0
2398           Border style is 0.
2399           peter@localhost testdb=> SELECT * FROM my_table;
2400           first second
2401           ----- ------
2402               1 one
2403               2 two
2404               3 three
2405               4 four
2406           (4 rows)
2407
2408           peter@localhost testdb=> \pset border 1
2409           Border style is 1.
2410           peter@localhost testdb=> \pset format unaligned
2411           Output format is unaligned.
2412           peter@localhost testdb=> \pset fieldsep ","
2413           Field separator is ",".
2414           peter@localhost testdb=> \pset tuples_only
2415           Showing only tuples.
2416           peter@localhost testdb=> SELECT second, first FROM my_table;
2417           one,1
2418           two,2
2419           three,3
2420           four,4
2421
2422       Alternatively, use the short commands:
2423
2424           peter@localhost testdb=> \a \t \x
2425           Output format is aligned.
2426           Tuples only is off.
2427           Expanded display is on.
2428           peter@localhost testdb=> SELECT * FROM my_table;
2429           -[ RECORD 1 ]-
2430           first  | 1
2431           second | one
2432           -[ RECORD 2 ]-
2433           first  | 2
2434           second | two
2435           -[ RECORD 3 ]-
2436           first  | 3
2437           second | three
2438           -[ RECORD 4 ]-
2439           first  | 4
2440           second | four
2441
2442       When suitable, query results can be shown in a crosstab representation
2443       with the \crosstabview command:
2444
2445           testdb=> SELECT first, second, first > 2 AS gt2 FROM my_table;
2446            first | second | gt2
2447           -------+--------+-----
2448                1 | one    | f
2449                2 | two    | f
2450                3 | three  | t
2451                4 | four   | t
2452           (4 rows)
2453
2454           testdb=> \crosstabview first second
2455            first | one | two | three | four
2456           -------+-----+-----+-------+------
2457                1 | f   |     |       |
2458                2 |     | f   |       |
2459                3 |     |     | t     |
2460                4 |     |     |       | t
2461           (4 rows)
2462
2463       This second example shows a multiplication table with rows sorted in
2464       reverse numerical order and columns with an independent, ascending
2465       numerical order.
2466
2467           testdb=> SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB",
2468           testdb(> row_number() over(order by t2.first) AS ord
2469           testdb(> FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC
2470           testdb(> \crosstabview "A" "B" "AxB" ord
2471            A | 101 | 102 | 103 | 104
2472           ---+-----+-----+-----+-----
2473            4 | 404 | 408 | 412 | 416
2474            3 | 303 | 306 | 309 | 312
2475            2 | 202 | 204 | 206 | 208
2476            1 | 101 | 102 | 103 | 104
2477           (4 rows)
2478
2479
2480
2481
2482
2483PostgreSQL 11.6                      2019                              PSQL(1)
Impressum