1PSQL(1) PostgreSQL 12.2 Documentation PSQL(1)
2
6 psql - PostgreSQL interactive terminal
7
9 psql [option...] [dbname [username]]
10
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
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 -A
27 --no-align
28 Switches to unaligned output mode. (The default output mode is
29 aligned.) This is equivalent to \pset format unaligned.
30 -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 -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 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 psql -c '\x' -c 'SELECT * FROM foo;'
51 or
53 echo '\x \\ SELECT * FROM foo;' | psql
55 (\\ is the separator meta-command.)
57 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 52.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 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 psql <<EOF
76 \x
77 SELECT * FROM foo;
78 EOF
79 --csv
82 Switches to CSV (Comma-Separated Values) output mode. This is
83 equivalent to \pset format csv.
84 -d dbname
86 --dbname=dbname
87 Specifies the name of the database to connect to. This is
88 equivalent to specifying dbname as the first non-option argument on
89 the command line.
90 If this parameter contains an = sign or starts with a valid URI
92 prefix (postgresql:// or postgres://), it is treated as a conninfo
93 string. See Section 33.1.1 for more information.
94 -e
96 --echo-queries
97 Copy all SQL commands sent to the server to standard output as
98 well. This is equivalent to setting the variable ECHO to queries.
99 -E
101 --echo-hidden
102 Echo the actual queries generated by \d and other backslash
103 commands. You can use this to study psql's internal operations.
104 This is equivalent to setting the variable ECHO_HIDDEN to on.
105 -f filename
107 --file=filename
108 Read commands from the file filename, rather than standard input.
109 This option can be repeated and combined in any order with the -c
110 option. When either -c or -f is specified, psql does not read
111 commands from standard input; instead it terminates after
112 processing all the -c and -f options in sequence. Except for that,
113 this option is largely equivalent to the meta-command \i.
114 If filename is - (hyphen), then standard input is read until an EOF
116 indication or \q meta-command. This can be used to intersperse
117 interactive input with input from files. Note however that Readline
118 is not used in this case (much as if -n had been specified).
119 Using this option is subtly different from writing psql < filename.
121 In general, both will do what you expect, but using -f enables some
122 nice features such as error messages with line numbers. There is
123 also a slight chance that using this option will reduce the
124 start-up overhead. On the other hand, the variant using the shell's
125 input redirection is (in theory) guaranteed to yield exactly the
126 same output you would have received had you entered everything by
127 hand.
128 -F separator
130 --field-separator=separator
131 Use separator as the field separator for unaligned output. This is
132 equivalent to \pset fieldsep or \f.
133 -h hostname
135 --host=hostname
136 Specifies the host name of the machine on which the server is
137 running. If the value begins with a slash, it is used as the
138 directory for the Unix-domain socket.
139 -H
141 --html
142 Switches to HTML output mode. This is equivalent to \pset format
143 html or the \H command.
144 -l
146 --list
147 List all available databases, then exit. Other non-connection
148 options are ignored. This is similar to the meta-command \list.
149 When this option is used, psql will connect to the database
151 postgres, unless a different database is named on the command line
152 (option -d or non-option argument, possibly via a service entry,
153 but not via an environment variable).
154 -L filename
156 --log-file=filename
157 Write all query output into file filename, in addition to the
158 normal output destination.
159 -n
161 --no-readline
162 Do not use Readline for line editing and do not use the command
163 history. This can be useful to turn off tab expansion when cutting
164 and pasting.
165 -o filename
167 --output=filename
168 Put all query output into file filename. This is equivalent to the
169 command \o.
170 -p port
172 --port=port
173 Specifies the TCP port or the local Unix-domain socket file
174 extension on which the server is listening for connections.
175 Defaults to the value of the PGPORT environment variable or, if not
176 set, to the port specified at compile time, usually 5432.
177 -P assignment
179 --pset=assignment
180 Specifies printing options, in the style of \pset. Note that here
181 you have to separate name and value with an equal sign instead of a
182 space. For example, to set the output format to LaTeX, you could
183 write -P format=latex.
184 -q
186 --quiet
187 Specifies that psql should do its work quietly. By default, it
188 prints welcome messages and various informational output. If this
189 option is used, none of this happens. This is useful with the -c
190 option. This is equivalent to setting the variable QUIET to on.
191 -R separator
193 --record-separator=separator
194 Use separator as the record separator for unaligned output. This is
195 equivalent to \pset recordsep.
196 -s
198 --single-step
199 Run in single-step mode. That means the user is prompted before
200 each command is sent to the server, with the option to cancel
201 execution as well. Use this to debug scripts.
202 -S
204 --single-line
205 Runs in single-line mode where a newline terminates an SQL command,
206 as a semicolon does.
207 Note
209 This mode is provided for those who insist on it, but you are
210 not necessarily encouraged to use it. In particular, if you mix
211 SQL and meta-commands on a line the order of execution might
212 not always be clear to the inexperienced user.
213 -t
215 --tuples-only
216 Turn off printing of column names and result row count footers,
217 etc. This is equivalent to \t or \pset tuples_only.
218 -T table_options
220 --table-attr=table_options
221 Specifies options to be placed within the HTML table tag. See \pset
222 tableattr for details.
223 -U username
225 --username=username
226 Connect to the database as the user username instead of the
227 default. (You must have permission to do so, of course.)
228 -v assignment
230 --set=assignment
231 --variable=assignment
232 Perform a variable assignment, like the \set meta-command. Note
233 that you must separate name and value, if any, by an equal sign on
234 the command line. To unset a variable, leave off the equal sign. To
235 set a variable with an empty value, use the equal sign but leave
236 off the value. These assignments are done during command line
237 processing, so variables that reflect connection state will get
238 overwritten later.
239 -V
241 --version
242 Print the psql version and exit.
243 -w
245 --no-password
246 Never issue a password prompt. If the server requires password
247 authentication and a password is not available by other means such
248 as a .pgpass file, the connection attempt will fail. This option
249 can be useful in batch jobs and scripts where no user is present to
250 enter a password.
251 Note that this option will remain set for the entire session, and
253 so it affects uses of the meta-command \connect as well as the
254 initial connection attempt.
255 -W
257 --password
258 Force psql to prompt for a password before connecting to a
259 database.
260 This option is never essential, since psql will automatically
262 prompt for a password if the server demands password
263 authentication. However, psql will waste a connection attempt
264 finding out that the server wants a password. In some cases it is
265 worth typing -W to avoid the extra connection attempt.
266 Note that this option will remain set for the entire session, and
268 so it affects uses of the meta-command \connect as well as the
269 initial connection attempt.
270 -x
272 --expanded
273 Turn on the expanded table formatting mode. This is equivalent to
274 \x or \pset expanded.
275 -X,
277 --no-psqlrc
278 Do not read the start-up file (neither the system-wide psqlrc file
279 nor the user's ~/.psqlrc file).
280 -z
282 --field-separator-zero
283 Set the field separator for unaligned output to a zero byte. This
284 is equivalent to \pset fieldsep_zero.
285 -0
287 --record-separator-zero
288 Set the record separator for unaligned output to a zero byte. This
289 is useful for interfacing, for example, with xargs -0. This is
290 equivalent to \pset recordsep_zero.
291 -1
293 --single-transaction
294 This option can only be used in combination with one or more -c
295 and/or -f options. It causes psql to issue a BEGIN command before
296 the first such option and a COMMIT command after the last one,
297 thereby wrapping all the commands into a single transaction. This
298 ensures that either all the commands complete successfully, or no
299 changes are applied.
300 If the commands themselves contain BEGIN, COMMIT, or ROLLBACK, this
302 option will not have the desired effects. Also, if an individual
303 command cannot be executed inside a transaction block, specifying
304 this option will cause the whole transaction to fail.
305 -?
307 --help[=topic]
308 Show help about psql and exit. The optional topic parameter
309 (defaulting to options) selects which part of psql is explained:
310 commands describes psql's backslash commands; options describes the
311 command-line options that can be passed to psql; and variables
312 shows help about psql configuration variables.
313
315 psql returns 0 to the shell if it finished normally, 1 if a fatal error
316 of its own occurs (e.g. out of memory, file not found), 2 if the
317 connection to the server went bad and the session was not interactive,
318 and 3 if an error occurred in a script and the variable ON_ERROR_STOP
319 was set.
320
322 Connecting to a Database
323 psql is a regular PostgreSQL client application. In order to connect to
324 a database you need to know the name of your target database, the host
325 name and port number of the server, and what user name you want to
326 connect as. psql can be told about those parameters via command line
327 options, namely -d, -h, -p, and -U respectively. If an argument is
328 found that does not belong to any option it will be interpreted as the
329 database name (or the user name, if the database name is already
330 given). Not all of these options are required; there are useful
331 defaults. If you omit the host name, psql will connect via a
332 Unix-domain socket to a server on the local host, or via TCP/IP to
333 localhost on machines that don't have Unix-domain sockets. The default
334 port number is determined at compile time. Since the database server
335 uses the same default, you will not have to specify the port in most
336 cases. The default user name is your operating-system user name, as is
337 the default database name. Note that you cannot just connect to any
338 database under any user name. Your database administrator should have
339 informed you about your access rights.
340 When the defaults aren't quite right, you can save yourself some typing
342 by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or
343 PGUSER to appropriate values. (For additional environment variables,
344 see Section 33.14.) It is also convenient to have a ~/.pgpass file to
345 avoid regularly having to type in passwords. See Section 33.15 for more
346 information.
347 An alternative way to specify connection parameters is in a conninfo
349 string or a URI, which is used instead of a database name. This
350 mechanism give you very wide control over the connection. For example:
351 $ psql "service=myservice sslmode=require"
353 $ psql postgresql://dbmaster:5433/mydb?sslmode=require
354 This way you can also use LDAP for connection parameter lookup as
356 described in Section 33.17. See Section 33.1.2 for more information on
357 all the available connection options.
358 If the connection could not be made for any reason (e.g., insufficient
360 privileges, server is not running on the targeted host, etc.), psql
361 will return an error and terminate.
362 If both standard input and standard output are a terminal, then psql
364 sets the client encoding to “auto”, which will detect the appropriate
365 client encoding from the locale settings (LC_CTYPE environment variable
366 on Unix systems). If this doesn't work out as expected, the client
367 encoding can be overridden using the environment variable
368 PGCLIENTENCODING.
369 Entering SQL Commands
371 In normal operation, psql provides a prompt with the name of the
372 database to which psql is currently connected, followed by the string
373 =>. For example:
374 $ psql testdb
376 psql (12.2)
377 Type "help" for help.
378 testdb=>
380 At the prompt, the user can type in SQL commands. Ordinarily, input
382 lines are sent to the server when a command-terminating semicolon is
383 reached. An end of line does not terminate a command. Thus commands can
384 be spread over several lines for clarity. If the command was sent and
385 executed without error, the results of the command are displayed on the
386 screen.
387 If untrusted users have access to a database that has not adopted a
389 secure schema usage pattern, begin your session by removing
390 publicly-writable schemas from search_path. One can add
391 options=-csearch_path= to the connection string or issue SELECT
392 pg_catalog.set_config('search_path', '', false) before other SQL
393 commands. This consideration is not specific to psql; it applies to
394 every interface for executing arbitrary SQL commands.
395 Whenever a command is executed, psql also polls for asynchronous
397 notification events generated by LISTEN(7) and NOTIFY(7).
398 While C-style block comments are passed to the server for processing
400 and removal, SQL-standard comments are removed by psql.
401 Meta-Commands
403 Anything you enter in psql that begins with an unquoted backslash is a
404 psql meta-command that is processed by psql itself. These commands make
405 psql more useful for administration or scripting. Meta-commands are
406 often called slash or backslash commands.
407 The format of a psql command is the backslash, followed immediately by
409 a command verb, then any arguments. The arguments are separated from
410 the command verb and each other by any number of whitespace characters.
411 To include whitespace in an argument you can quote it with single
413 quotes. To include a single quote in an argument, write two single
414 quotes within single-quoted text. Anything contained in single quotes
415 is furthermore subject to C-like substitutions for \n (new line), \t
416 (tab), \b (backspace), \r (carriage return), \f (form feed), \digits
417 (octal), and \xdigits (hexadecimal). A backslash preceding any other
418 character within single-quoted text quotes that single character,
419 whatever it is.
420 If an unquoted colon (:) followed by a psql variable name appears
422 within an argument, it is replaced by the variable's value, as
423 described in SQL Interpolation. The forms :'variable_name' and
424 :"variable_name" described there work as well. The :{?variable_name}
425 syntax allows testing whether a variable is defined. It is substituted
426 by TRUE or FALSE. Escaping the colon with a backslash protects it from
427 substitution.
428 Within an argument, text that is enclosed in backquotes (`) is taken as
430 a command line that is passed to the shell. The output of the command
431 (with any trailing newline removed) replaces the backquoted text.
432 Within the text enclosed in backquotes, no special quoting or other
433 processing occurs, except that appearances of :variable_name where
434 variable_name is a psql variable name are replaced by the variable's
435 value. Also, appearances of :'variable_name' are replaced by the
436 variable's value suitably quoted to become a single shell command
437 argument. (The latter form is almost always preferable, unless you are
438 very sure of what is in the variable.) Because carriage return and line
439 feed characters cannot be safely quoted on all platforms, the
440 :'variable_name' form prints an error message and does not substitute
441 the variable value when such characters appear in the value.
442 Some commands take an SQL identifier (such as a table name) as
444 argument. These arguments follow the syntax rules of SQL: Unquoted
445 letters are forced to lowercase, while double quotes (") protect
446 letters from case conversion and allow incorporation of whitespace into
447 the identifier. Within double quotes, paired double quotes reduce to a
448 single double quote in the resulting name. For example, FOO"BAR"BAZ is
449 interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.
450 Parsing for arguments stops at the end of the line, or when another
452 unquoted backslash is found. An unquoted backslash is taken as the
453 beginning of a new meta-command. The special sequence \\ (two
454 backslashes) marks the end of arguments and continues parsing SQL
455 commands, if any. That way SQL and psql commands can be freely mixed on
456 a line. But in any case, the arguments of a meta-command cannot
457 continue beyond the end of the line.
458 Many of the meta-commands act on the current query buffer. This is
460 simply a buffer holding whatever SQL command text has been typed but
461 not yet sent to the server for execution. This will include previous
462 input lines as well as any text appearing before the meta-command on
463 the same line.
464 The following meta-commands are defined:
466 \a
468 If the current table output format is unaligned, it is switched to
469 aligned. If it is not unaligned, it is set to unaligned. This
470 command is kept for backwards compatibility. See \pset for a more
471 general solution.
472 \c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host
474 ] [ port ] | conninfo ]
475 Establishes a new connection to a PostgreSQL server. The connection
476 parameters to use can be specified either using a positional
477 syntax, or using conninfo connection strings as detailed in
478 Section 33.1.1.
479 Where the command omits database name, user, host, or port, the new
481 connection can reuse values from the previous connection. By
482 default, values from the previous connection are reused except when
483 processing a conninfo string. Passing a first argument of
484 -reuse-previous=on or -reuse-previous=off overrides that default.
485 When the command neither specifies nor reuses a particular
486 parameter, the libpq default is used. Specifying any of dbname,
487 username, host or port as - is equivalent to omitting that
488 parameter. If hostaddr was specified in the original connection's
489 conninfo, that address is reused for the new connection
490 (disregarding any other host specification).
491 If the new connection is successfully made, the previous connection
493 is closed. If the connection attempt failed (wrong user name,
494 access denied, etc.), the previous connection will only be kept if
495 psql is in interactive mode. When executing a non-interactive
496 script, processing will immediately stop with an error. This
497 distinction was chosen as a user convenience against typos on the
498 one hand, and a safety mechanism that scripts are not accidentally
499 acting on the wrong database on the other hand.
500 Examples:
502 => \c mydb myuser host.dom 6432
504 => \c service=foo
505 => \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
506 => \c postgresql://tom@localhost/mydb?application_name=myapp
507 \C [ title ]
509 Sets the title of any tables being printed as the result of a query
510 or unset any such title. This command is equivalent to \pset title
511 title. (The name of this command derives from “caption”, as it was
512 previously only used to set the caption in an HTML table.)
513 \cd [ directory ]
515 Changes the current working directory to directory. Without
516 argument, changes to the current user's home directory.
517 Tip
519 To print your current working directory, use \! pwd.
520 \conninfo
522 Outputs information about the current database connection.
523 \copy { table [ ( column_list ) ] | ( query ) } { from | to } {
525 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [
526 [ with ] ( option [, ...] ) ]
527 Performs a frontend (client) copy. This is an operation that runs
528 an SQL COPY(7) command, but instead of the server reading or
529 writing the specified file, psql reads or writes the file and
530 routes the data between the server and the local file system. This
531 means that file accessibility and privileges are those of the local
532 user, not the server, and no SQL superuser privileges are required.
533 When program is specified, command is executed by psql and the data
535 passed from or to command is routed between the server and the
536 client. Again, the execution privileges are those of the local
537 user, not the server, and no SQL superuser privileges are required.
538 For \copy ... from stdin, data rows are read from the same source
540 that issued the command, continuing until \. is read or the stream
541 reaches EOF. This option is useful for populating tables in-line
542 within a SQL script file. For \copy ... to stdout, output is sent
543 to the same place as psql command output, and the COPY count
544 command status is not printed (since it might be confused with a
545 data row). To read/write psql's standard input or output regardless
546 of the current command source or \o option, write from pstdin or to
547 pstdout.
548 The syntax of this command is similar to that of the SQL COPY(7)
550 command. All options other than the data source/destination are as
551 specified for COPY(7). Because of this, special parsing rules apply
552 to the \copy meta-command. Unlike most other meta-commands, the
553 entire remainder of the line is always taken to be the arguments of
554 \copy, and neither variable interpolation nor backquote expansion
555 are performed in the arguments.
556 Tip
558 Another way to obtain the same result as \copy ... to is to use
559 the SQL COPY ... TO STDOUT command and terminate it with \g
560 filename or \g |program. Unlike \copy, this method allows the
561 command to span multiple lines; also, variable interpolation
562 and backquote expansion can be used.
563 Tip
565 These operations are not as efficient as the SQL COPY command
566 with a file or program data source or destination, because all
567 data must pass through the client/server connection. For large
568 amounts of data the SQL command might be preferable.
569 \copyright
571 Shows the copyright and distribution terms of PostgreSQL.
572 \crosstabview [ colV [ colH [ colD [ sortcolH ] ] ] ]
574 Executes the current query buffer (like \g) and shows the results
575 in a crosstab grid. The query must return at least three columns.
576 The output column identified by colV becomes a vertical header and
577 the output column identified by colH becomes a horizontal header.
578 colD identifies the output column to display within the grid.
579 sortcolH identifies an optional sort column for the horizontal
580 header.
581 Each column specification can be a column number (starting at 1) or
583 a column name. The usual SQL case folding and quoting rules apply
584 to column names. If omitted, colV is taken as column 1 and colH as
585 column 2. colH must differ from colV. If colD is not specified,
586 then there must be exactly three columns in the query result, and
587 the column that is neither colV nor colH is taken to be colD.
588 The vertical header, displayed as the leftmost column, contains the
590 values found in column colV, in the same order as in the query
591 results, but with duplicates removed.
592 The horizontal header, displayed as the first row, contains the
594 values found in column colH, with duplicates removed. By default,
595 these appear in the same order as in the query results. But if the
596 optional sortcolH argument is given, it identifies a column whose
597 values must be integer numbers, and the values from colH will
598 appear in the horizontal header sorted according to the
599 corresponding sortcolH values.
600 Inside the crosstab grid, for each distinct value x of colH and
602 each distinct value y of colV, the cell located at the intersection
603 (x,y) contains the value of the colD column in the query result row
604 for which the value of colH is x and the value of colV is y. If
605 there is no such row, the cell is empty. If there are multiple such
606 rows, an error is reported.
607 \d[S+] [ pattern ]
609 For each relation (table, view, materialized view, index, sequence,
610 or foreign table) or composite type matching the pattern, show all
611 columns, their types, the tablespace (if not the default) and any
612 special attributes such as NOT NULL or defaults. Associated
613 indexes, constraints, rules, and triggers are also shown. For
614 foreign tables, the associated foreign server is shown as well.
615 (“Matching the pattern” is defined in Patterns below.)
616 For some types of relation, \d shows additional information for
618 each column: column values for sequences, indexed expressions for
619 indexes, and foreign data wrapper options for foreign tables.
620 The command form \d+ is identical, except that more information is
622 displayed: any comments associated with the columns of the table
623 are shown, as is the presence of OIDs in the table, the view
624 definition if the relation is a view, a non-default replica
625 identity setting.
626 By default, only user-created objects are shown; supply a pattern
628 or the S modifier to include system objects.
629 Note
631 If \d is used without a pattern argument, it is equivalent to
632 \dtvmsE which will show a list of all visible tables, views,
633 materialized views, sequences and foreign tables. This is
634 purely a convenience measure.
635 \da[S] [ pattern ]
637 Lists aggregate functions, together with their return type and the
638 data types they operate on. If pattern is specified, only
639 aggregates whose names match the pattern are shown. By default,
640 only user-created objects are shown; supply a pattern or the S
641 modifier to include system objects.
642 \dA[+] [ pattern ]
644 Lists access methods. If pattern is specified, only access methods
645 whose names match the pattern are shown. If + is appended to the
646 command name, each access method is listed with its associated
647 handler function and description.
648 \db[+] [ pattern ]
650 Lists tablespaces. If pattern is specified, only tablespaces whose
651 names match the pattern are shown. If + is appended to the command
652 name, each tablespace is listed with its associated options,
653 on-disk size, permissions and description.
654 \dc[S+] [ pattern ]
656 Lists conversions between character-set encodings. If pattern is
657 specified, only conversions whose names match the pattern are
658 listed. By default, only user-created objects are shown; supply a
659 pattern or the S modifier to include system objects. If + is
660 appended to the command name, each object is listed with its
661 associated description.
662 \dC[+] [ pattern ]
664 Lists type casts. If pattern is specified, only casts whose source
665 or target types match the pattern are listed. If + is appended to
666 the command name, each object is listed with its associated
667 description.
668 \dd[S] [ pattern ]
670 Shows the descriptions of objects of type constraint, operator
671 class, operator family, rule, and trigger. All other comments may
672 be viewed by the respective backslash commands for those object
673 types.
674 \dd displays descriptions for objects matching the pattern, or of
676 visible objects of the appropriate type if no argument is given.
677 But in either case, only objects that have a description are
678 listed. By default, only user-created objects are shown; supply a
679 pattern or the S modifier to include system objects.
680 Descriptions for objects can be created with the COMMENT(7) SQL
682 command.
683 \dD[S+] [ pattern ]
685 Lists domains. If pattern is specified, only domains whose names
686 match the pattern are shown. By default, only user-created objects
687 are shown; supply a pattern or the S modifier to include system
688 objects. If + is appended to the command name, each object is
689 listed with its associated permissions and description.
690 \ddp [ pattern ]
692 Lists default access privilege settings. An entry is shown for each
693 role (and schema, if applicable) for which the default privilege
694 settings have been changed from the built-in defaults. If pattern
695 is specified, only entries whose role name or schema name matches
696 the pattern are listed.
697 The ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7)) command
699 is used to set default access privileges. The meaning of the
700 privilege display is explained in Section 5.7.
701 \dE[S+] [ pattern ]
703 \di[S+] [ pattern ]
704 \dm[S+] [ pattern ]
705 \ds[S+] [ pattern ]
706 \dt[S+] [ pattern ]
707 \dv[S+] [ pattern ]
708 In this group of commands, the letters E, i, m, s, t, and v stand
709 for foreign table, index, materialized view, sequence, table, and
710 view, respectively. You can specify any or all of these letters, in
711 any order, to obtain a listing of objects of these types. For
712 example, \dit lists indexes and tables. If + is appended to the
713 command name, each object is listed with its physical size on disk
714 and its associated description, if any. If pattern is specified,
715 only objects whose names match the pattern are listed. By default,
716 only user-created objects are shown; supply a pattern or the S
717 modifier to include system objects.
718 \des[+] [ pattern ]
720 Lists foreign servers (mnemonic: “external servers”). If pattern is
721 specified, only those servers whose name matches the pattern are
722 listed. If the form \des+ is used, a full description of each
723 server is shown, including the server's access privileges, type,
724 version, options, and description.
725 \det[+] [ pattern ]
727 Lists foreign tables (mnemonic: “external tables”). If pattern is
728 specified, only entries whose table name or schema name matches the
729 pattern are listed. If the form \det+ is used, generic options and
730 the foreign table description are also displayed.
731 \deu[+] [ pattern ]
733 Lists user mappings (mnemonic: “external users”). If pattern is
734 specified, only those mappings whose user names match the pattern
735 are listed. If the form \deu+ is used, additional information about
736 each mapping is shown.
737 Caution
739 \deu+ might also display the user name and password of the
740 remote user, so care should be taken not to disclose them.
741 \dew[+] [ pattern ]
743 Lists foreign-data wrappers (mnemonic: “external wrappers”). If
744 pattern is specified, only those foreign-data wrappers whose name
745 matches the pattern are listed. If the form \dew+ is used, the
746 access privileges, options, and description of the foreign-data
747 wrapper are also shown.
748 \df[anptwS+] [ pattern ]
750 Lists functions, together with their result data types, argument
751 data types, and function types, which are classified as “agg”
752 (aggregate), “normal”, “procedure”, “trigger”, or “window”. To
753 display only functions of specific type(s), add the corresponding
754 letters a, n, p, t, or w to the command. If pattern is specified,
755 only functions whose names match the pattern are shown. By default,
756 only user-created objects are shown; supply a pattern or the S
757 modifier to include system objects. If the form \df+ is used,
758 additional information about each function is shown, including
759 volatility, parallel safety, owner, security classification, access
760 privileges, language, source code and description.
761 Tip
763 To look up functions taking arguments or returning values of a
764 specific data type, use your pager's search capability to
765 scroll through the \df output.
766 \dF[+] [ pattern ]
768 Lists text search configurations. If pattern is specified, only
769 configurations whose names match the pattern are shown. If the form
770 \dF+ is used, a full description of each configuration is shown,
771 including the underlying text search parser and the dictionary list
772 for each parser token type.
773 \dFd[+] [ pattern ]
775 Lists text search dictionaries. If pattern is specified, only
776 dictionaries whose names match the pattern are shown. If the form
777 \dFd+ is used, additional information is shown about each selected
778 dictionary, including the underlying text search template and the
779 option values.
780 \dFp[+] [ pattern ]
782 Lists text search parsers. If pattern is specified, only parsers
783 whose names match the pattern are shown. If the form \dFp+ is used,
784 a full description of each parser is shown, including the
785 underlying functions and the list of recognized token types.
786 \dFt[+] [ pattern ]
788 Lists text search templates. If pattern is specified, only
789 templates whose names match the pattern are shown. If the form
790 \dFt+ is used, additional information is shown about each template,
791 including the underlying function names.
792 \dg[S+] [ pattern ]
794 Lists database roles. (Since the concepts of “users” and “groups”
795 have been unified into “roles”, this command is now equivalent to
796 \du.) By default, only user-created roles are shown; supply the S
797 modifier to include system roles. If pattern is specified, only
798 those roles whose names match the pattern are listed. If the form
799 \dg+ is used, additional information is shown about each role;
800 currently this adds the comment for each role.
801 \dl
803 This is an alias for \lo_list, which shows a list of large objects.
804 \dL[S+] [ pattern ]
806 Lists procedural languages. If pattern is specified, only languages
807 whose names match the pattern are listed. By default, only
808 user-created languages are shown; supply the S modifier to include
809 system objects. If + is appended to the command name, each language
810 is listed with its call handler, validator, access privileges, and
811 whether it is a system object.
812 \dn[S+] [ pattern ]
814 Lists schemas (namespaces). If pattern is specified, only schemas
815 whose names match the pattern are listed. By default, only
816 user-created objects are shown; supply a pattern or the S modifier
817 to include system objects. If + is appended to the command name,
818 each object is listed with its associated permissions and
819 description, if any.
820 \do[S+] [ pattern ]
822 Lists operators with their operand and result types. If pattern is
823 specified, only operators whose names match the pattern are listed.
824 By default, only user-created objects are shown; supply a pattern
825 or the S modifier to include system objects. If + is appended to
826 the command name, additional information about each operator is
827 shown, currently just the name of the underlying function.
828 \dO[S+] [ pattern ]
830 Lists collations. If pattern is specified, only collations whose
831 names match the pattern are listed. By default, only user-created
832 objects are shown; supply a pattern or the S modifier to include
833 system objects. If + is appended to the command name, each
834 collation is listed with its associated description, if any. Note
835 that only collations usable with the current database's encoding
836 are shown, so the results may vary in different databases of the
837 same installation.
838 \dp [ pattern ]
840 Lists tables, views and sequences with their associated access
841 privileges. If pattern is specified, only tables, views and
842 sequences whose names match the pattern are listed.
843 The GRANT(7) and REVOKE(7) commands are used to set access
845 privileges. The meaning of the privilege display is explained in
846 Section 5.7.
847 \dP[itn+] [ pattern ]
849 Lists partitioned relations. If pattern is specified, only entries
850 whose name matches the pattern are listed. The modifiers t (tables)
851 and i (indexes) can be appended to the command, filtering the kind
852 of relations to list. By default, partitioned tables and indexes
853 are listed.
854 If the modifier n (“nested”) is used, or a pattern is specified,
856 then non-root partitioned relations are included, and a column is
857 shown displaying the parent of each partitioned relation.
858 If + is appended to the command name, the sum of the sizes of each
860 relation's partitions is also displayed, along with the relation's
861 description. If n is combined with +, two sizes are shown: one
862 including the total size of directly-attached leaf partitions, and
863 another showing the total size of all partitions, including
864 indirectly attached sub-partitions.
865 \drds [ role-pattern [ database-pattern ] ]
867 Lists defined configuration settings. These settings can be
868 role-specific, database-specific, or both. role-pattern and
869 database-pattern are used to select specific roles and databases to
870 list, respectively. If omitted, or if * is specified, all settings
871 are listed, including those not role-specific or database-specific,
872 respectively.
873 The ALTER ROLE (ALTER_ROLE(7)) and ALTER DATABASE
875 (ALTER_DATABASE(7)) commands are used to define per-role and
876 per-database configuration settings.
877 \dRp[+] [ pattern ]
879 Lists replication publications. If pattern is specified, only those
880 publications whose names match the pattern are listed. If + is
881 appended to the command name, the tables associated with each
882 publication are shown as well.
883 \dRs[+] [ pattern ]
885 Lists replication subscriptions. If pattern is specified, only
886 those subscriptions whose names match the pattern are listed. If +
887 is appended to the command name, additional properties of the
888 subscriptions are shown.
889 \dT[S+] [ pattern ]
891 Lists data types. If pattern is specified, only types whose names
892 match the pattern are listed. If + is appended to the command name,
893 each type is listed with its internal name and size, its allowed
894 values if it is an enum type, and its associated permissions. By
895 default, only user-created objects are shown; supply a pattern or
896 the S modifier to include system objects.
897 \du[S+] [ pattern ]
899 Lists database roles. (Since the concepts of “users” and “groups”
900 have been unified into “roles”, this command is now equivalent to
901 \dg.) By default, only user-created roles are shown; supply the S
902 modifier to include system roles. If pattern is specified, only
903 those roles whose names match the pattern are listed. If the form
904 \du+ is used, additional information is shown about each role;
905 currently this adds the comment for each role.
906 \dx[+] [ pattern ]
908 Lists installed extensions. If pattern is specified, only those
909 extensions whose names match the pattern are listed. If the form
910 \dx+ is used, all the objects belonging to each matching extension
911 are listed.
912 \dy[+] [ pattern ]
914 Lists event triggers. If pattern is specified, only those event
915 triggers whose names match the pattern are listed. If + is appended
916 to the command name, each object is listed with its associated
917 description.
918 \e or \edit [ filename ] [ line_number ]
920 If filename is specified, the file is edited; after the editor
921 exits, the file's content is copied into the current query buffer.
922 If no filename is given, the current query buffer is copied to a
923 temporary file which is then edited in the same fashion. Or, if the
924 current query buffer is empty, the most recently executed query is
925 copied to a temporary file and edited in the same fashion.
926 The new contents of the query buffer are then re-parsed according
928 to the normal rules of psql, treating the whole buffer as a single
929 line. Any complete queries are immediately executed; that is, if
930 the query buffer contains or ends with a semicolon, everything up
931 to that point is executed. Whatever remains will wait in the query
932 buffer; type semicolon or \g to send it, or \r to cancel it by
933 clearing the query buffer. Treating the buffer as a single line
934 primarily affects meta-commands: whatever is in the buffer after a
935 meta-command will be taken as argument(s) to the meta-command, even
936 if it spans multiple lines. (Thus you cannot make
937 meta-command-using scripts this way. Use \i for that.)
938 If a line number is specified, psql will position the cursor on the
940 specified line of the file or query buffer. Note that if a single
941 all-digits argument is given, psql assumes it is a line number, not
942 a file name.
943 Tip
945 See under ENVIRONMENT for how to configure and customize your
946 editor.
947 \echo text [ ... ]
949 Prints the arguments to the standard output, separated by one space
950 and followed by a newline. This can be useful to intersperse
951 information in the output of scripts. For example:
952 => \echo `date`
954 Tue Oct 26 21:40:57 CEST 1999
955 If the first argument is an unquoted -n the trailing newline is not
957 written.
958 Tip
960 If you use the \o command to redirect your query output you
961 might wish to use \qecho instead of this command.
962 \ef [ function_description [ line_number ] ]
964 This command fetches and edits the definition of the named function
965 or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE
966 OR REPLACE PROCEDURE command. Editing is done in the same way as
967 for \edit. After the editor exits, the updated command waits in the
968 query buffer; type semicolon or \g to send it, or \r to cancel.
969 The target function can be specified by name alone, or by name and
971 arguments, for example foo(integer, text). The argument types must
972 be given if there is more than one function of the same name.
973 If no function is specified, a blank CREATE FUNCTION template is
975 presented for editing.
976 If a line number is specified, psql will position the cursor on the
978 specified line of the function body. (Note that the function body
979 typically does not begin on the first line of the file.)
980 Unlike most other meta-commands, the entire remainder of the line
982 is always taken to be the argument(s) of \ef, and neither variable
983 interpolation nor backquote expansion are performed in the
984 arguments.
985 Tip
987 See under ENVIRONMENT for how to configure and customize your
988 editor.
989 \encoding [ encoding ]
991 Sets the client character set encoding. Without an argument, this
992 command shows the current encoding.
993 \errverbose
995 Repeats the most recent server error message at maximum verbosity,
996 as though VERBOSITY were set to verbose and SHOW_CONTEXT were set
997 to always.
998 \ev [ view_name [ line_number ] ]
1000 This command fetches and edits the definition of the named view, in
1001 the form of a CREATE OR REPLACE VIEW command. Editing is done in
1002 the same way as for \edit. After the editor exits, the updated
1003 command waits in the query buffer; type semicolon or \g to send it,
1004 or \r to cancel.
1005 If no view is specified, a blank CREATE VIEW template is presented
1007 for editing.
1008 If a line number is specified, psql will position the cursor on the
1010 specified line of the view definition.
1011 Unlike most other meta-commands, the entire remainder of the line
1013 is always taken to be the argument(s) of \ev, and neither variable
1014 interpolation nor backquote expansion are performed in the
1015 arguments.
1016 \f [ string ]
1018 Sets the field separator for unaligned query output. The default is
1019 the vertical bar (|). It is equivalent to \pset fieldsep.
1020 \g [ filename ]
1022 \g [ |command ]
1023 Sends the current query buffer to the server for execution. If an
1024 argument is given, the query's output is written to the named file
1025 or piped to the given shell command, instead of displaying it as
1026 usual. The file or command is written to only if the query
1027 successfully returns zero or more tuples, not if the query fails or
1028 is a non-data-returning SQL command.
1029 If the current query buffer is empty, the most recently sent query
1031 is re-executed instead. Except for that behavior, \g without an
1032 argument is essentially equivalent to a semicolon. A \g with
1033 argument is a “one-shot” alternative to the \o command.
1034 If the argument begins with |, then the entire remainder of the
1036 line is taken to be the command to execute, and neither variable
1037 interpolation nor backquote expansion are performed in it. The rest
1038 of the line is simply passed literally to the shell.
1039 \gdesc
1041 Shows the description (that is, the column names and data types) of
1042 the result of the current query buffer. The query is not actually
1043 executed; however, if it contains some type of syntax error, that
1044 error will be reported in the normal way.
1045 If the current query buffer is empty, the most recently sent query
1047 is described instead.
1048 \gexec
1050 Sends the current query buffer to the server, then treats each
1051 column of each row of the query's output (if any) as a SQL
1052 statement to be executed. For example, to create an index on each
1053 column of my_table:
1054 => SELECT format('create index on my_table(%I)', attname)
1056 -> FROM pg_attribute
1057 -> WHERE attrelid = 'my_table'::regclass AND attnum > 0
1058 -> ORDER BY attnum
1059 -> \gexec
1060 CREATE INDEX
1061 CREATE INDEX
1062 CREATE INDEX
1063 CREATE INDEX
1064 The generated queries are executed in the order in which the rows
1066 are returned, and left-to-right within each row if there is more
1067 than one column. NULL fields are ignored. The generated queries are
1068 sent literally to the server for processing, so they cannot be psql
1069 meta-commands nor contain psql variable references. If any
1070 individual query fails, execution of the remaining queries
1071 continues unless ON_ERROR_STOP is set. Execution of each query is
1072 subject to ECHO processing. (Setting ECHO to all or queries is
1073 often advisable when using \gexec.) Query logging, single-step
1074 mode, timing, and other query execution features apply to each
1075 generated query as well.
1076 If the current query buffer is empty, the most recently sent query
1078 is re-executed instead.
1079 \gset [ prefix ]
1081 Sends the current query buffer to the server and stores the query's
1082 output into psql variables (see Variables). The query to be
1083 executed must return exactly one row. Each column of the row is
1084 stored into a separate variable, named the same as the column. For
1085 example:
1086 => SELECT 'hello' AS var1, 10 AS var2
1088 -> \gset
1089 => \echo :var1 :var2
1090 hello 10
1091 If you specify a prefix, that string is prepended to the query's
1093 column names to create the variable names to use:
1094 => SELECT 'hello' AS var1, 10 AS var2
1096 -> \gset result_
1097 => \echo :result_var1 :result_var2
1098 hello 10
1099 If a column result is NULL, the corresponding variable is unset
1101 rather than being set.
1102 If the query fails or does not return one row, no variables are
1104 changed.
1105 If the current query buffer is empty, the most recently sent query
1107 is re-executed instead.
1108 \gx [ filename ]
1110 \gx [ |command ]
1111 \gx is equivalent to \g, but forces expanded output mode for this
1112 query. See \x.
1113 \h or \help [ command ]
1115 Gives syntax help on the specified SQL command. If command is not
1116 specified, then psql will list all the commands for which syntax
1117 help is available. If command is an asterisk (*), then syntax help
1118 on all SQL commands is shown.
1119 Unlike most other meta-commands, the entire remainder of the line
1121 is always taken to be the argument(s) of \help, and neither
1122 variable interpolation nor backquote expansion are performed in the
1123 arguments.
1124 Note
1126 To simplify typing, commands that consists of several words do
1127 not have to be quoted. Thus it is fine to type \help alter
1128 table.
1129 \H or \html
1131 Turns on HTML query output format. If the HTML format is already
1132 on, it is switched back to the default aligned text format. This
1133 command is for compatibility and convenience, but see \pset about
1134 setting other output options.
1135 \i or \include filename
1137 Reads input from the file filename and executes it as though it had
1138 been typed on the keyboard.
1139 If filename is - (hyphen), then standard input is read until an EOF
1141 indication or \q meta-command. This can be used to intersperse
1142 interactive input with input from files. Note that Readline
1143 behavior will be used only if it is active at the outermost level.
1144 Note
1146 If you want to see the lines on the screen as they are read you
1147 must set the variable ECHO to all.
1148 \if expression
1150 \elif expression
1151 \else
1152 \endif
1153 This group of commands implements nestable conditional blocks. A
1154 conditional block must begin with an \if and end with an \endif. In
1155 between there may be any number of \elif clauses, which may
1156 optionally be followed by a single \else clause. Ordinary queries
1157 and other types of backslash commands may (and usually do) appear
1158 between the commands forming a conditional block.
1159 The \if and \elif commands read their argument(s) and evaluate them
1161 as a boolean expression. If the expression yields true then
1162 processing continues normally; otherwise, lines are skipped until a
1163 matching \elif, \else, or \endif is reached. Once an \if or \elif
1164 test has succeeded, the arguments of later \elif commands in the
1165 same block are not evaluated but are treated as false. Lines
1166 following an \else are processed only if no earlier matching \if or
1167 \elif succeeded.
1168 The expression argument of an \if or \elif command is subject to
1170 variable interpolation and backquote expansion, just like any other
1171 backslash command argument. After that it is evaluated like the
1172 value of an on/off option variable. So a valid value is any
1173 unambiguous case-insensitive match for one of: true, false, 1, 0,
1174 on, off, yes, no. For example, t, T, and tR will all be considered
1175 to be true.
1176 Expressions that do not properly evaluate to true or false will
1178 generate a warning and be treated as false.
1179 Lines being skipped are parsed normally to identify queries and
1181 backslash commands, but queries are not sent to the server, and
1182 backslash commands other than conditionals (\if, \elif, \else,
1183 \endif) are ignored. Conditional commands are checked only for
1184 valid nesting. Variable references in skipped lines are not
1185 expanded, and backquote expansion is not performed either.
1186 All the backslash commands of a given conditional block must appear
1188 in the same source file. If EOF is reached on the main input file
1189 or an \include-ed file before all local \if-blocks have been
1190 closed, then psql will raise an error.
1191 Here is an example:
1193 -- check for the existence of two separate records in the database and store
1195 -- the results in separate psql variables
1196 SELECT
1197 EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,
1198 EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
1199 \gset
1200 \if :is_customer
1201 SELECT * FROM customer WHERE customer_id = 123;
1202 \elif :is_employee
1203 \echo 'is not a customer but is an employee'
1204 SELECT * FROM employee WHERE employee_id = 456;
1205 \else
1206 \if yes
1207 \echo 'not a customer or employee'
1208 \else
1209 \echo 'this will never print'
1210 \endif
1211 \endif
1212 \ir or \include_relative filename
1214 The \ir command is similar to \i, but resolves relative file names
1215 differently. When executing in interactive mode, the two commands
1216 behave identically. However, when invoked from a script, \ir
1217 interprets file names relative to the directory in which the script
1218 is located, rather than the current working directory.
1219 \l[+] or \list[+] [ pattern ]
1221 List the databases in the server and show their names, owners,
1222 character set encodings, and access privileges. If pattern is
1223 specified, only databases whose names match the pattern are listed.
1224 If + is appended to the command name, database sizes, default
1225 tablespaces, and descriptions are also displayed. (Size information
1226 is only available for databases that the current user can connect
1227 to.)
1228 \lo_export loid filename
1230 Reads the large object with OID loid from the database and writes
1231 it to filename. Note that this is subtly different from the server
1232 function lo_export, which acts with the permissions of the user
1233 that the database server runs as and on the server's file system.
1234 Tip
1236 Use \lo_list to find out the large object's OID.
1237 \lo_import filename [ comment ]
1239 Stores the file into a PostgreSQL large object. Optionally, it
1240 associates the given comment with the object. Example:
1241 foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
1243 lo_import 152801
1244 The response indicates that the large object received object ID
1246 152801, which can be used to access the newly-created large object
1247 in the future. For the sake of readability, it is recommended to
1248 always associate a human-readable comment with every object. Both
1249 OIDs and comments can be viewed with the \lo_list command.
1250 Note that this command is subtly different from the server-side
1252 lo_import because it acts as the local user on the local file
1253 system, rather than the server's user and file system.
1254 \lo_list
1256 Shows a list of all PostgreSQL large objects currently stored in
1257 the database, along with any comments provided for them.
1258 \lo_unlink loid
1260 Deletes the large object with OID loid from the database.
1261 Tip
1263 Use \lo_list to find out the large object's OID.
1264 \o or \out [ filename ]
1266 \o or \out [ |command ]
1267 Arranges to save future query results to the file filename or pipe
1268 future results to the shell command command. If no argument is
1269 specified, the query output is reset to the standard output.
1270 If the argument begins with |, then the entire remainder of the
1272 line is taken to be the command to execute, and neither variable
1273 interpolation nor backquote expansion are performed in it. The rest
1274 of the line is simply passed literally to the shell.
1275 “Query results” includes all tables, command responses, and notices
1277 obtained from the database server, as well as output of various
1278 backslash commands that query the database (such as \d); but not
1279 error messages.
1280 Tip
1282 To intersperse text output in between query results, use
1283 \qecho.
1284 \p or \print
1286 Print the current query buffer to the standard output. If the
1287 current query buffer is empty, the most recently executed query is
1288 printed instead.
1289 \password [ username ]
1291 Changes the password of the specified user (by default, the current
1292 user). This command prompts for the new password, encrypts it, and
1293 sends it to the server as an ALTER ROLE command. This makes sure
1294 that the new password does not appear in cleartext in the command
1295 history, the server log, or elsewhere.
1296 \prompt [ text ] name
1298 Prompts the user to supply text, which is assigned to the variable
1299 name. An optional prompt string, text, can be specified. (For
1300 multiword prompts, surround the text with single quotes.)
1301 By default, \prompt uses the terminal for input and output.
1303 However, if the -f command line switch was used, \prompt uses
1304 standard input and standard output.
1305 \pset [ option [ value ] ]
1307 This command sets options affecting the output of query result
1308 tables. option indicates which option is to be set. The semantics
1309 of value vary depending on the selected option. For some options,
1310 omitting value causes the option to be toggled or unset, as
1311 described under the particular option. If no such behavior is
1312 mentioned, then omitting value just results in the current setting
1313 being displayed.
1314 \pset without any arguments displays the current status of all
1316 printing options.
1317 Adjustable printing options are:
1319 border
1321 The value must be a number. In general, the higher the number
1322 the more borders and lines the tables will have, but details
1323 depend on the particular format. In HTML format, this will
1324 translate directly into the border=... attribute. In most
1325 other formats only values 0 (no border), 1 (internal dividing
1326 lines), and 2 (table frame) make sense, and values above 2 will
1327 be treated the same as border = 2. The latex and
1328 latex-longtable formats additionally allow a value of 3 to add
1329 dividing lines between data rows.
1330 columns
1332 Sets the target width for the wrapped format, and also the
1333 width limit for determining whether output is wide enough to
1334 require the pager or switch to the vertical display in expanded
1335 auto mode. Zero (the default) causes the target width to be
1336 controlled by the environment variable COLUMNS, or the detected
1337 screen width if COLUMNS is not set. In addition, if columns is
1338 zero then the wrapped format only affects screen output. If
1339 columns is nonzero then file and pipe output is wrapped to that
1340 width as well.
1341 csv_fieldsep
1343 Specifies the field separator to be used in CSV output format.
1344 If the separator character appears in a field's value, that
1345 field is output within double quotes, following standard CSV
1346 rules. The default is a comma.
1347 expanded (or x)
1349 If value is specified it must be either on or off, which will
1350 enable or disable expanded mode, or auto. If value is omitted
1351 the command toggles between the on and off settings. When
1352 expanded mode is enabled, query results are displayed in two
1353 columns, with the column name on the left and the data on the
1354 right. This mode is useful if the data wouldn't fit on the
1355 screen in the normal “horizontal” mode. In the auto setting,
1356 the expanded mode is used whenever the query output has more
1357 than one column and is wider than the screen; otherwise, the
1358 regular mode is used. The auto setting is only effective in the
1359 aligned and wrapped formats. In other formats, it always
1360 behaves as if the expanded mode is off.
1361 fieldsep
1363 Specifies the field separator to be used in unaligned output
1364 format. That way one can create, for example, tab-separated
1365 output, which other programs might prefer. To set a tab as
1366 field separator, type \pset fieldsep '\t'. The default field
1367 separator is '|' (a vertical bar).
1368 fieldsep_zero
1370 Sets the field separator to use in unaligned output format to a
1371 zero byte.
1372 footer
1374 If value is specified it must be either on or off which will
1375 enable or disable display of the table footer (the (n rows)
1376 count). If value is omitted the command toggles footer display
1377 on or off.
1378 format
1380 Sets the output format to one of aligned, asciidoc, csv, html,
1381 latex, latex-longtable, troff-ms, unaligned, or wrapped. Unique
1382 abbreviations are allowed.
1383 aligned format is the standard, human-readable, nicely
1385 formatted text output; this is the default.
1386 unaligned format writes all columns of a row on one line,
1388 separated by the currently active field separator. This is
1389 useful for creating output that might be intended to be read in
1390 by other programs, for example, tab-separated or
1391 comma-separated format. However, the field separator character
1392 is not treated specially if it appears in a column's value; so
1393 CSV format may be better suited for such purposes.
1394 csv format
1396 writes column values separated by commas, applying the quoting
1398 rules described in RFC 4180. This output is compatible with the
1399 CSV format of the server's COPY command. A header line with
1400 column names is generated unless the tuples_only parameter is
1401 on. Titles and footers are not printed. Each row is terminated
1402 by the system-dependent end-of-line character, which is
1403 typically a single newline (\n) for Unix-like systems or a
1404 carriage return and newline sequence (\r\n) for Microsoft
1405 Windows. Field separator characters other than comma can be
1406 selected with \pset csv_fieldsep.
1407 wrapped format is like aligned but wraps wide data values
1409 across lines to make the output fit in the target column width.
1410 The target width is determined as described under the columns
1411 option. Note that psql will not attempt to wrap column header
1412 titles; therefore, wrapped format behaves the same as aligned
1413 if the total width needed for column headers exceeds the
1414 target.
1415 The asciidoc, html, latex, latex-longtable, and troff-ms
1417 formats put out tables that are intended to be included in
1418 documents using the respective mark-up language. They are not
1419 complete documents! This might not be necessary in HTML, but in
1420 LaTeX you must have a complete document wrapper. The latex
1421 format uses LaTeX's tabular environment. The latex-longtable
1422 format requires the LaTeX longtable and booktabs packages.
1423 linestyle
1425 Sets the border line drawing style to one of ascii, old-ascii,
1426 or unicode. Unique abbreviations are allowed. (That would mean
1427 one letter is enough.) The default setting is ascii. This
1428 option only affects the aligned and wrapped output formats.
1429 ascii style uses plain ASCII characters. Newlines in data are
1431 shown using a + symbol in the right-hand margin. When the
1432 wrapped format wraps data from one line to the next without a
1433 newline character, a dot (.) is shown in the right-hand margin
1434 of the first line, and again in the left-hand margin of the
1435 following line.
1436 old-ascii style uses plain ASCII characters, using the
1438 formatting style used in PostgreSQL 8.4 and earlier. Newlines
1439 in data are shown using a : symbol in place of the left-hand
1440 column separator. When the data is wrapped from one line to the
1441 next without a newline character, a ; symbol is used in place
1442 of the left-hand column separator.
1443 unicode style uses Unicode box-drawing characters. Newlines in
1445 data are shown using a carriage return symbol in the right-hand
1446 margin. When the data is wrapped from one line to the next
1447 without a newline character, an ellipsis symbol is shown in the
1448 right-hand margin of the first line, and again in the left-hand
1449 margin of the following line.
1450 When the border setting is greater than zero, the linestyle
1452 option also determines the characters with which the border
1453 lines are drawn. Plain ASCII characters work everywhere, but
1454 Unicode characters look nicer on displays that recognize them.
1455 null
1457 Sets the string to be printed in place of a null value. The
1458 default is to print nothing, which can easily be mistaken for
1459 an empty string. For example, one might prefer \pset null
1460 '(null)'.
1461 numericlocale
1463 If value is specified it must be either on or off which will
1464 enable or disable display of a locale-specific character to
1465 separate groups of digits to the left of the decimal marker. If
1466 value is omitted the command toggles between regular and
1467 locale-specific numeric output.
1468 pager
1470 Controls use of a pager program for query and psql help output.
1471 If the environment variable PSQL_PAGER or PAGER is set, the
1472 output is piped to the specified program. Otherwise a
1473 platform-dependent default program (such as more) is used.
1474 When the pager option is off, the pager program is not used.
1476 When the pager option is on, the pager is used when
1477 appropriate, i.e., when the output is to a terminal and will
1478 not fit on the screen. The pager option can also be set to
1479 always, which causes the pager to be used for all terminal
1480 output regardless of whether it fits on the screen. \pset
1481 pager without a value toggles pager use on and off.
1482 pager_min_lines
1484 If pager_min_lines is set to a number greater than the page
1485 height, the pager program will not be called unless there are
1486 at least this many lines of output to show. The default setting
1487 is 0.
1488 recordsep
1490 Specifies the record (line) separator to use in unaligned
1491 output format. The default is a newline character.
1492 recordsep_zero
1494 Sets the record separator to use in unaligned output format to
1495 a zero byte.
1496 tableattr (or T)
1498 In HTML format, this specifies attributes to be placed inside
1499 the table tag. This could for example be cellpadding or
1500 bgcolor. Note that you probably don't want to specify border
1501 here, as that is already taken care of by \pset border. If no
1502 value is given, the table attributes are unset.
1503 In latex-longtable format, this controls the proportional width
1505 of each column containing a left-aligned data type. It is
1506 specified as a whitespace-separated list of values, e.g. '0.2
1507 0.2 0.6'. Unspecified output columns use the last specified
1508 value.
1509 title (or C)
1511 Sets the table title for any subsequently printed tables. This
1512 can be used to give your output descriptive tags. If no value
1513 is given, the title is unset.
1514 tuples_only (or t)
1516 If value is specified it must be either on or off which will
1517 enable or disable tuples-only mode. If value is omitted the
1518 command toggles between regular and tuples-only output. Regular
1519 output includes extra information such as column headers,
1520 titles, and various footers. In tuples-only mode, only actual
1521 table data is shown.
1522 unicode_border_linestyle
1524 Sets the border drawing style for the unicode line style to one
1525 of single or double.
1526 unicode_column_linestyle
1528 Sets the column drawing style for the unicode line style to one
1529 of single or double.
1530 unicode_header_linestyle
1532 Sets the header drawing style for the unicode line style to one
1533 of single or double.
1534 Illustrations of how these different formats look can be seen in
1536 the EXAMPLES section.
1537 Tip
1539 There are various shortcut commands for \pset. See \a, \C, \f,
1540 \H, \t, \T, and \x.
1541 \q or \quit
1543 Quits the psql program. In a script file, only execution of that
1544 script is terminated.
1545 \qecho text [ ... ]
1547 This command is identical to \echo except that the output will be
1548 written to the query output channel, as set by \o.
1549 \r or \reset
1551 Resets (clears) the query buffer.
1552 \s [ filename ]
1554 Print psql's command line history to filename. If filename is
1555 omitted, the history is written to the standard output (using the
1556 pager if appropriate). This command is not available if psql was
1557 built without Readline support.
1558 \set [ name [ value [ ... ] ] ]
1560 Sets the psql variable name to value, or if more than one value is
1561 given, to the concatenation of all of them. If only one argument is
1562 given, the variable is set to an empty-string value. To unset a
1563 variable, use the \unset command.
1564 \set without any arguments displays the names and values of all
1566 currently-set psql variables.
1567 Valid variable names can contain letters, digits, and underscores.
1569 See the section Variables below for details. Variable names are
1570 case-sensitive.
1571 Certain variables are special, in that they control psql's behavior
1573 or are automatically set to reflect connection state. These
1574 variables are documented in Variables, below.
1575 Note
1577 This command is unrelated to the SQL command SET(7).
1578 \setenv name [ value ]
1580 Sets the environment variable name to value, or if the value is not
1581 supplied, unsets the environment variable. Example:
1582 testdb=> \setenv PAGER less
1584 testdb=> \setenv LESS -imx4F
1585 \sf[+] function_description
1587 This command fetches and shows the definition of the named function
1588 or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE
1589 OR REPLACE PROCEDURE command. The definition is printed to the
1590 current query output channel, as set by \o.
1591 The target function can be specified by name alone, or by name and
1593 arguments, for example foo(integer, text). The argument types must
1594 be given if there is more than one function of the same name.
1595 If + is appended to the command name, then the output lines are
1597 numbered, with the first line of the function body being line 1.
1598 Unlike most other meta-commands, the entire remainder of the line
1600 is always taken to be the argument(s) of \sf, and neither variable
1601 interpolation nor backquote expansion are performed in the
1602 arguments.
1603 \sv[+] view_name
1605 This command fetches and shows the definition of the named view, in
1606 the form of a CREATE OR REPLACE VIEW command. The definition is
1607 printed to the current query output channel, as set by \o.
1608 If + is appended to the command name, then the output lines are
1610 numbered from 1.
1611 Unlike most other meta-commands, the entire remainder of the line
1613 is always taken to be the argument(s) of \sv, and neither variable
1614 interpolation nor backquote expansion are performed in the
1615 arguments.
1616 \t
1618 Toggles the display of output column name headings and row count
1619 footer. This command is equivalent to \pset tuples_only and is
1620 provided for convenience.
1621 \T table_options
1623 Specifies attributes to be placed within the table tag in HTML
1624 output format. This command is equivalent to \pset tableattr
1625 table_options.
1626 \timing [ on | off ]
1628 With a parameter, turns displaying of how long each SQL statement
1629 takes on or off. Without a parameter, toggles the display between
1630 on and off. The display is in milliseconds; intervals longer than 1
1631 second are also shown in minutes:seconds format, with hours and
1632 days fields added if needed.
1633 \unset name
1635 Unsets (deletes) the psql variable name.
1636 Most variables that control psql's behavior cannot be unset;
1638 instead, an \unset command is interpreted as setting them to their
1639 default values. See Variables, below.
1640 \w or \write filename
1642 \w or \write |command
1643 Writes the current query buffer to the file filename or pipes it to
1644 the shell command command. If the current query buffer is empty,
1645 the most recently executed query is written instead.
1646 If the argument begins with |, then the entire remainder of the
1648 line is taken to be the command to execute, and neither variable
1649 interpolation nor backquote expansion are performed in it. The rest
1650 of the line is simply passed literally to the shell.
1651 \watch [ seconds ]
1653 Repeatedly execute the current query buffer (as \g does) until
1654 interrupted or the query fails. Wait the specified number of
1655 seconds (default 2) between executions. Each query result is
1656 displayed with a header that includes the \pset title string (if
1657 any), the time as of query start, and the delay interval.
1658 If the current query buffer is empty, the most recently sent query
1660 is re-executed instead.
1661 \x [ on | off | auto ]
1663 Sets or toggles expanded table formatting mode. As such it is
1664 equivalent to \pset expanded.
1665 \z [ pattern ]
1667 Lists tables, views and sequences with their associated access
1668 privileges. If a pattern is specified, only tables, views and
1669 sequences whose names match the pattern are listed.
1670 This is an alias for \dp (“display privileges”).
1672 \! [ command ]
1674 With no argument, escapes to a sub-shell; psql resumes when the
1675 sub-shell exits. With an argument, executes the shell command
1676 command.
1677 Unlike most other meta-commands, the entire remainder of the line
1679 is always taken to be the argument(s) of \!, and neither variable
1680 interpolation nor backquote expansion are performed in the
1681 arguments. The rest of the line is simply passed literally to the
1682 shell.
1683 \? [ topic ]
1685 Shows help information. The optional topic parameter (defaulting to
1686 commands) selects which part of psql is explained: commands
1687 describes psql's backslash commands; options describes the
1688 command-line options that can be passed to psql; and variables
1689 shows help about psql configuration variables.
1690 \;
1692 Backslash-semicolon is not a meta-command in the same way as the
1693 preceding commands; rather, it simply causes a semicolon to be
1694 added to the query buffer without any further processing.
1695 Normally, psql will dispatch a SQL command to the server as soon as
1697 it reaches the command-ending semicolon, even if more input remains
1698 on the current line. Thus for example entering
1699 select 1; select 2; select 3;
1701 will result in the three SQL commands being individually sent to
1703 the server, with each one's results being displayed before
1704 continuing to the next command. However, a semicolon entered as \;
1705 will not trigger command processing, so that the command before it
1706 and the one after are effectively combined and sent to the server
1707 in one request. So for example
1708 select 1\; select 2\; select 3;
1710 results in sending the three SQL commands to the server in a single
1712 request, when the non-backslashed semicolon is reached. The server
1713 executes such a request as a single transaction, unless there are
1714 explicit BEGIN/COMMIT commands included in the string to divide it
1715 into multiple transactions. (See Section 52.2.2.1 for more details
1716 about how the server handles multi-query strings.) psql prints
1717 only the last query result it receives for each request; in this
1718 example, although all three SELECTs are indeed executed, psql only
1719 prints the 3.
1720 Patterns
1722 The various \d commands accept a pattern parameter to specify the
1723 object name(s) to be displayed. In the simplest case, a pattern is
1724 just the exact name of the object. The characters within a pattern
1725 are normally folded to lower case, just as in SQL names; for
1726 example, \dt FOO will display the table named foo. As in SQL names,
1727 placing double quotes around a pattern stops folding to lower case.
1728 Should you need to include an actual double quote character in a
1729 pattern, write it as a pair of double quotes within a double-quote
1730 sequence; again this is in accord with the rules for SQL quoted
1731 identifiers. For example, \dt "FOO""BAR" will display the table
1732 named FOO"BAR (not foo"bar). Unlike the normal rules for SQL names,
1733 you can put double quotes around just part of a pattern, for
1734 instance \dt FOO"FOO"BAR will display the table named fooFOObar.
1735 Whenever the pattern parameter is omitted completely, the \d
1737 commands display all objects that are visible in the current schema
1738 search path — this is equivalent to using * as the pattern. (An
1739 object is said to be visible if its containing schema is in the
1740 search path and no object of the same kind and name appears earlier
1741 in the search path. This is equivalent to the statement that the
1742 object can be referenced by name without explicit schema
1743 qualification.) To see all objects in the database regardless of
1744 visibility, use *.* as the pattern.
1745 Within a pattern, * matches any sequence of characters (including
1747 no characters) and ? matches any single character. (This notation
1748 is comparable to Unix shell file name patterns.) For example, \dt
1749 int* displays tables whose names begin with int. But within double
1750 quotes, * and ? lose these special meanings and are just matched
1751 literally.
1752 A pattern that contains a dot (.) is interpreted as a schema name
1754 pattern followed by an object name pattern. For example, \dt
1755 foo*.*bar* displays all tables whose table name includes bar that
1756 are in schemas whose schema name starts with foo. When no dot
1757 appears, then the pattern matches only objects that are visible in
1758 the current schema search path. Again, a dot within double quotes
1759 loses its special meaning and is matched literally.
1760 Advanced users can use regular-expression notations such as
1762 character classes, for example [0-9] to match any digit. All
1763 regular expression special characters work as specified in
1764 Section 9.7.3, except for . which is taken as a separator as
1765 mentioned above, * which is translated to the regular-expression
1766 notation .*, ? which is translated to ., and $ which is matched
1767 literally. You can emulate these pattern characters at need by
1768 writing ? for ., (R+|) for R*, or (R|) for R?. $ is not needed as
1769 a regular-expression character since the pattern must match the
1770 whole name, unlike the usual interpretation of regular expressions
1771 (in other words, $ is automatically appended to your pattern).
1772 Write * at the beginning and/or end if you don't wish the pattern
1773 to be anchored. Note that within double quotes, all regular
1774 expression special characters lose their special meanings and are
1775 matched literally. Also, the regular expression special characters
1776 are matched literally in operator name patterns (i.e., the argument
1777 of \do).
1778 Advanced Features
1780 Variables
1781 psql provides variable substitution features similar to common Unix
1782 command shells. Variables are simply name/value pairs, where the
1783 value can be any string of any length. The name must consist of
1784 letters (including non-Latin letters), digits, and underscores.
1785 To set a variable, use the psql meta-command \set. For example,
1787 testdb=> \set foo bar
1789 sets the variable foo to the value bar. To retrieve the content of
1791 the variable, precede the name with a colon, for example:
1792 testdb=> \echo :foo
1794 bar
1795 This works in both regular SQL commands and meta-commands; there is
1797 more detail in SQL Interpolation, below.
1798 If you call \set without a second argument, the variable is set to
1800 an empty-string value. To unset (i.e., delete) a variable, use the
1801 command \unset. To show the values of all variables, call \set
1802 without any argument.
1803 Note
1805 The arguments of \set are subject to the same substitution
1806 rules as with other commands. Thus you can construct
1807 interesting references such as \set :foo 'something' and get
1808 “soft links” or “variable variables” of Perl or PHP fame,
1809 respectively. Unfortunately (or fortunately?), there is no way
1810 to do anything useful with these constructs. On the other hand,
1811 \set bar :foo is a perfectly valid way to copy a variable.
1812 A number of these variables are treated specially by psql. They
1814 represent certain option settings that can be changed at run time
1815 by altering the value of the variable, or in some cases represent
1816 changeable state of psql. By convention, all specially treated
1817 variables' names consist of all upper-case ASCII letters (and
1818 possibly digits and underscores). To ensure maximum compatibility
1819 in the future, avoid using such variable names for your own
1820 purposes.
1821 Variables that control psql's behavior generally cannot be unset or
1823 set to invalid values. An \unset command is allowed but is
1824 interpreted as setting the variable to its default value. A \set
1825 command without a second argument is interpreted as setting the
1826 variable to on, for control variables that accept that value, and
1827 is rejected for others. Also, control variables that accept the
1828 values on and off will also accept other common spellings of
1829 Boolean values, such as true and false.
1830 The specially treated variables are:
1832 AUTOCOMMIT
1834 When on (the default), each SQL command is automatically
1835 committed upon successful completion. To postpone commit in
1836 this mode, you must enter a BEGIN or START TRANSACTION SQL
1837 command. When off or unset, SQL commands are not committed
1838 until you explicitly issue COMMIT or END. The autocommit-off
1839 mode works by issuing an implicit BEGIN for you, just before
1840 any command that is not already in a transaction block and is
1841 not itself a BEGIN or other transaction-control command, nor a
1842 command that cannot be executed inside a transaction block
1843 (such as VACUUM).
1844 Note
1846 In autocommit-off mode, you must explicitly abandon any
1847 failed transaction by entering ABORT or ROLLBACK. Also keep
1848 in mind that if you exit the session without committing,
1849 your work will be lost.
1850 Note
1852 The autocommit-on mode is PostgreSQL's traditional
1853 behavior, but autocommit-off is closer to the SQL spec. If
1854 you prefer autocommit-off, you might wish to set it in the
1855 system-wide psqlrc file or your ~/.psqlrc file.
1856 COMP_KEYWORD_CASE
1858 Determines which letter case to use when completing an SQL key
1859 word. If set to lower or upper, the completed word will be in
1860 lower or upper case, respectively. If set to preserve-lower or
1861 preserve-upper (the default), the completed word will be in the
1862 case of the word already entered, but words being completed
1863 without anything entered will be in lower or upper case,
1864 respectively.
1865 DBNAME
1867 The name of the database you are currently connected to. This
1868 is set every time you connect to a database (including program
1869 start-up), but can be changed or unset.
1870 ECHO
1872 If set to all, all nonempty input lines are printed to standard
1873 output as they are read. (This does not apply to lines read
1874 interactively.) To select this behavior on program start-up,
1875 use the switch -a. If set to queries, psql prints each query to
1876 standard output as it is sent to the server. The switch to
1877 select this behavior is -e. If set to errors, then only failed
1878 queries are displayed on standard error output. The switch for
1879 this behavior is -b. If set to none (the default), then no
1880 queries are displayed.
1881 ECHO_HIDDEN
1883 When this variable is set to on and a backslash command queries
1884 the database, the query is first shown. This feature helps you
1885 to study PostgreSQL internals and provide similar functionality
1886 in your own programs. (To select this behavior on program
1887 start-up, use the switch -E.) If you set this variable to the
1888 value noexec, the queries are just shown but are not actually
1889 sent to the server and executed. The default value is off.
1890 ENCODING
1892 The current client character set encoding. This is set every
1893 time you connect to a database (including program start-up),
1894 and when you change the encoding with \encoding, but it can be
1895 changed or unset.
1896 ERROR
1898 true if the last SQL query failed, false if it succeeded. See
1899 also SQLSTATE.
1900 FETCH_COUNT
1902 If this variable is set to an integer value greater than zero,
1903 the results of SELECT queries are fetched and displayed in
1904 groups of that many rows, rather than the default behavior of
1905 collecting the entire result set before display. Therefore only
1906 a limited amount of memory is used, regardless of the size of
1907 the result set. Settings of 100 to 1000 are commonly used when
1908 enabling this feature. Keep in mind that when using this
1909 feature, a query might fail after having already displayed some
1910 rows.
1911 Tip
1913 Although you can use any output format with this feature,
1914 the default aligned format tends to look bad because each
1915 group of FETCH_COUNT rows will be formatted separately,
1916 leading to varying column widths across the row groups. The
1917 other output formats work better.
1918 HIDE_TABLEAM
1920 If this variable is set to true, a table's access method
1921 details are not displayed. This is mainly useful for regression
1922 tests.
1923 HISTCONTROL
1925 If this variable is set to ignorespace, lines which begin with
1926 a space are not entered into the history list. If set to a
1927 value of ignoredups, lines matching the previous history line
1928 are not entered. A value of ignoreboth combines the two
1929 options. If set to none (the default), all lines read in
1930 interactive mode are saved on the history list.
1931 Note
1933 This feature was shamelessly plagiarized from Bash.
1934 HISTFILE
1936 The file name that will be used to store the history list. If
1937 unset, the file name is taken from the PSQL_HISTORY environment
1938 variable. If that is not set either, the default is
1939 ~/.psql_history, or %APPDATA%\postgresql\psql_history on
1940 Windows. For example, putting:
1941 \set HISTFILE ~/.psql_history- :DBNAME
1943 in ~/.psqlrc will cause psql to maintain a separate history for
1945 each database.
1946 Note
1948 This feature was shamelessly plagiarized from Bash.
1949 HISTSIZE
1951 The maximum number of commands to store in the command history
1952 (default 500). If set to a negative value, no limit is applied.
1953 Note
1955 This feature was shamelessly plagiarized from Bash.
1956 HOST
1958 The database server host you are currently connected to. This
1959 is set every time you connect to a database (including program
1960 start-up), but can be changed or unset.
1961 IGNOREEOF
1963 If set to 1 or less, sending an EOF character (usually
1964 Control+D) to an interactive session of psql will terminate the
1965 application. If set to a larger numeric value, that many
1966 consecutive EOF characters must be typed to make an interactive
1967 session terminate. If the variable is set to a non-numeric
1968 value, it is interpreted as 10. The default is 0.
1969 Note
1971 This feature was shamelessly plagiarized from Bash.
1972 LASTOID
1974 The value of the last affected OID, as returned from an INSERT
1975 or \lo_import command. This variable is only guaranteed to be
1976 valid until after the result of the next SQL command has been
1977 displayed. PostgreSQL servers since version 12 do not support
1978 OID system columns anymore, thus LASTOID will always be 0
1979 following INSERT when targeting such servers.
1980 LAST_ERROR_MESSAGE
1982 LAST_ERROR_SQLSTATE
1983 The primary error message and associated SQLSTATE code for the
1984 most recent failed query in the current psql session, or an
1985 empty string and 00000 if no error has occurred in the current
1986 session.
1987 ON_ERROR_ROLLBACK
1989 When set to on, if a statement in a transaction block generates
1990 an error, the error is ignored and the transaction continues.
1991 When set to interactive, such errors are only ignored in
1992 interactive sessions, and not when reading script files. When
1993 set to off (the default), a statement in a transaction block
1994 that generates an error aborts the entire transaction. The
1995 error rollback mode works by issuing an implicit SAVEPOINT for
1996 you, just before each command that is in a transaction block,
1997 and then rolling back to the savepoint if the command fails.
1998 ON_ERROR_STOP
2000 By default, command processing continues after an error. When
2001 this variable is set to on, processing will instead stop
2002 immediately. In interactive mode, psql will return to the
2003 command prompt; otherwise, psql will exit, returning error code
2004 3 to distinguish this case from fatal error conditions, which
2005 are reported using error code 1. In either case, any currently
2006 running scripts (the top-level script, if any, and any other
2007 scripts which it may have in invoked) will be terminated
2008 immediately. If the top-level command string contained multiple
2009 SQL commands, processing will stop with the current command.
2010 PORT
2012 The database server port to which you are currently connected.
2013 This is set every time you connect to a database (including
2014 program start-up), but can be changed or unset.
2015 PROMPT1
2017 PROMPT2
2018 PROMPT3
2019 These specify what the prompts psql issues should look like.
2020 See Prompting below.
2021 QUIET
2023 Setting this variable to on is equivalent to the command line
2024 option -q. It is probably not too useful in interactive mode.
2025 ROW_COUNT
2027 The number of rows returned or affected by the last SQL query,
2028 or 0 if the query failed or did not report a row count.
2029 SERVER_VERSION_NAME
2031 SERVER_VERSION_NUM
2032 The server's version number as a string, for example 9.6.2,
2033 10.1 or 11beta1, and in numeric form, for example 90602 or
2034 100001. These are set every time you connect to a database
2035 (including program start-up), but can be changed or unset.
2036 SHOW_CONTEXT
2038 This variable can be set to the values never, errors, or always
2039 to control whether CONTEXT fields are displayed in messages
2040 from the server. The default is errors (meaning that context
2041 will be shown in error messages, but not in notice or warning
2042 messages). This setting has no effect when VERBOSITY is set to
2043 terse or sqlstate. (See also \errverbose, for use when you want
2044 a verbose version of the error you just got.)
2045 SINGLELINE
2047 Setting this variable to on is equivalent to the command line
2048 option -S.
2049 SINGLESTEP
2051 Setting this variable to on is equivalent to the command line
2052 option -s.
2053 SQLSTATE
2055 The error code (see Appendix A) associated with the last SQL
2056 query's failure, or 00000 if it succeeded.
2057 USER
2059 The database user you are currently connected as. This is set
2060 every time you connect to a database (including program
2061 start-up), but can be changed or unset.
2062 VERBOSITY
2064 This variable can be set to the values default, verbose, terse,
2065 or sqlstate to control the verbosity of error reports. (See
2066 also \errverbose, for use when you want a verbose version of
2067 the error you just got.)
2068 VERSION
2070 VERSION_NAME
2071 VERSION_NUM
2072 These variables are set at program start-up to reflect psql's
2073 version, respectively as a verbose string, a short string
2074 (e.g., 9.6.2, 10.1, or 11beta1), and a number (e.g., 90602 or
2075 100001). They can be changed or unset.
2076 SQL Interpolation
2078 A key feature of psql variables is that you can substitute
2079 (“interpolate”) them into regular SQL statements, as well as the
2080 arguments of meta-commands. Furthermore, psql provides facilities
2081 for ensuring that variable values used as SQL literals and
2082 identifiers are properly quoted. The syntax for interpolating a
2083 value without any quoting is to prepend the variable name with a
2084 colon (:). For example,
2085 testdb=> \set foo 'my_table'
2087 testdb=> SELECT * FROM :foo;
2088 would query the table my_table. Note that this may be unsafe: the
2090 value of the variable is copied literally, so it can contain
2091 unbalanced quotes, or even backslash commands. You must make sure
2092 that it makes sense where you put it.
2093 When a value is to be used as an SQL literal or identifier, it is
2095 safest to arrange for it to be quoted. To quote the value of a
2096 variable as an SQL literal, write a colon followed by the variable
2097 name in single quotes. To quote the value as an SQL identifier,
2098 write a colon followed by the variable name in double quotes. These
2099 constructs deal correctly with quotes and other special characters
2100 embedded within the variable value. The previous example would be
2101 more safely written this way:
2102 testdb=> \set foo 'my_table'
2104 testdb=> SELECT * FROM :"foo";
2105 Variable interpolation will not be performed within quoted SQL
2107 literals and identifiers. Therefore, a construction such as ':foo'
2108 doesn't work to produce a quoted literal from a variable's value
2109 (and it would be unsafe if it did work, since it wouldn't correctly
2110 handle quotes embedded in the value).
2111 One example use of this mechanism is to copy the contents of a file
2113 into a table column. First load the file into a variable and then
2114 interpolate the variable's value as a quoted string:
2115 testdb=> \set content `cat my_file.txt`
2117 testdb=> INSERT INTO my_table VALUES (:'content');
2118 (Note that this still won't work if my_file.txt contains NUL bytes.
2120 psql does not support embedded NUL bytes in variable values.)
2121 Since colons can legally appear in SQL commands, an apparent
2123 attempt at interpolation (that is, :name, :'name', or :"name") is
2124 not replaced unless the named variable is currently set. In any
2125 case, you can escape a colon with a backslash to protect it from
2126 substitution.
2127 The :{?name} special syntax returns TRUE or FALSE depending on
2129 whether the variable exists or not, and is thus always substituted,
2130 unless the colon is backslash-escaped.
2131 The colon syntax for variables is standard SQL for embedded query
2133 languages, such as ECPG. The colon syntaxes for array slices and
2134 type casts are PostgreSQL extensions, which can sometimes conflict
2135 with the standard usage. The colon-quote syntax for escaping a
2136 variable's value as an SQL literal or identifier is a psql
2137 extension.
2138 Prompting
2140 The prompts psql issues can be customized to your preference. The
2141 three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and
2142 special escape sequences that describe the appearance of the
2143 prompt. Prompt 1 is the normal prompt that is issued when psql
2144 requests a new command. Prompt 2 is issued when more input is
2145 expected during command entry, for example because the command was
2146 not terminated with a semicolon or a quote was not closed. Prompt 3
2147 is issued when you are running an SQL COPY FROM STDIN command and
2148 you need to type in a row value on the terminal.
2149 The value of the selected prompt variable is printed literally,
2151 except where a percent sign (%) is encountered. Depending on the
2152 next character, certain other text is substituted instead. Defined
2153 substitutions are:
2154 %M
2156 The full host name (with domain name) of the database server,
2157 or [local] if the connection is over a Unix domain socket, or
2158 [local:/dir/name], if the Unix domain socket is not at the
2159 compiled in default location.
2160 %m
2162 The host name of the database server, truncated at the first
2163 dot, or [local] if the connection is over a Unix domain socket.
2164 %>
2166 The port number at which the database server is listening.
2167 %n
2169 The database session user name. (The expansion of this value
2170 might change during a database session as the result of the
2171 command SET SESSION AUTHORIZATION.)
2172 %/
2174 The name of the current database.
2175 %~
2177 Like %/, but the output is ~ (tilde) if the database is your
2178 default database.
2179 %#
2181 If the session user is a database superuser, then a #,
2182 otherwise a >. (The expansion of this value might change during
2183 a database session as the result of the command SET SESSION
2184 AUTHORIZATION.)
2185 %p
2187 The process ID of the backend currently connected to.
2188 %R
2190 In prompt 1 normally =, but @ if the session is in an inactive
2191 branch of a conditional block, or ^ if in single-line mode, or
2192 ! if the session is disconnected from the database (which can
2193 happen if \connect fails). In prompt 2 %R is replaced by a
2194 character that depends on why psql expects more input: - if the
2195 command simply wasn't terminated yet, but * if there is an
2196 unfinished /* ... */ comment, a single quote if there is an
2197 unfinished quoted string, a double quote if there is an
2198 unfinished quoted identifier, a dollar sign if there is an
2199 unfinished dollar-quoted string, or ( if there is an unmatched
2200 left parenthesis. In prompt 3 %R doesn't produce anything.
2201 %x
2203 Transaction status: an empty string when not in a transaction
2204 block, or * when in a transaction block, or ! when in a failed
2205 transaction block, or ? when the transaction state is
2206 indeterminate (for example, because there is no connection).
2207 %l
2209 The line number inside the current statement, starting from 1.
2210 %digits
2212 The character with the indicated octal code is substituted.
2213 %:name:
2215 The value of the psql variable name. See the section Variables
2216 for details.
2217 %`command`
2219 The output of command, similar to ordinary “back-tick”
2220 substitution.
2221 %[ ... %]
2223 Prompts can contain terminal control characters which, for
2224 example, change the color, background, or style of the prompt
2225 text, or change the title of the terminal window. In order for
2226 the line editing features of Readline to work properly, these
2227 non-printing control characters must be designated as invisible
2228 by surrounding them with %[ and %]. Multiple pairs of these can
2229 occur within the prompt. For example:
2230 testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
2232 results in a boldfaced (1;) yellow-on-black (33;40) prompt on
2234 VT100-compatible, color-capable terminals.
2235 To insert a percent sign into your prompt, write %%. The default
2236 prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.
2237 Note
2239 This feature was shamelessly plagiarized from tcsh.
2240 Command-Line Editing
2242 psql supports the Readline library for convenient line editing and
2243 retrieval. The command history is automatically saved when psql
2244 exits and is reloaded when psql starts up. Tab-completion is also
2245 supported, although the completion logic makes no claim to be an
2246 SQL parser. The queries generated by tab-completion can also
2247 interfere with other SQL commands, e.g. SET TRANSACTION ISOLATION
2248 LEVEL. If for some reason you do not like the tab completion, you
2249 can turn it off by putting this in a file named .inputrc in your
2250 home directory:
2251 $if psql
2253 set disable-completion on
2254 $endif
2255 (This is not a psql but a Readline feature. Read its documentation
2257 for further details.)
2258
2260 COLUMNS
2261 If \pset columns is zero, controls the width for the wrapped format
2262 and width for determining if wide output requires the pager or
2263 should be switched to the vertical format in expanded auto mode.
2264 PGDATABASE
2266 PGHOST
2267 PGPORT
2268 PGUSER
2269 Default connection parameters (see Section 33.14).
2270 PG_COLOR
2272 Specifies whether to use color in diagnostics messages. Possible
2273 values are always, auto, never.
2274 PSQL_EDITOR
2276 EDITOR
2277 VISUAL
2278 Editor used by the \e, \ef, and \ev commands. These variables are
2279 examined in the order listed; the first that is set is used. If
2280 none of them is set, the default is to use vi on Unix systems or
2281 notepad.exe on Windows systems.
2282 PSQL_EDITOR_LINENUMBER_ARG
2284 When \e, \ef, or \ev is used with a line number argument, this
2285 variable specifies the command-line argument used to pass the
2286 starting line number to the user's editor. For editors such as
2287 Emacs or vi, this is a plus sign. Include a trailing space in the
2288 value of the variable if there needs to be space between the option
2289 name and the line number. Examples:
2290 PSQL_EDITOR_LINENUMBER_ARG='+'
2292 PSQL_EDITOR_LINENUMBER_ARG='--line '
2293 The default is + on Unix systems (corresponding to the default
2295 editor vi, and useful for many other common editors); but there is
2296 no default on Windows systems.
2297 PSQL_HISTORY
2299 Alternative location for the command history file. Tilde (~)
2300 expansion is performed.
2301 PSQL_PAGER
2303 PAGER
2304 If a query's results do not fit on the screen, they are piped
2305 through this command. Typical values are more or less. Use of the
2306 pager can be disabled by setting PSQL_PAGER or PAGER to an empty
2307 string, or by adjusting the pager-related options of the \pset
2308 command. These variables are examined in the order listed; the
2309 first that is set is used. If none of them is set, the default is
2310 to use more on most platforms, but less on Cygwin.
2311 PSQLRC
2313 Alternative location of the user's .psqlrc file. Tilde (~)
2314 expansion is performed.
2315 SHELL
2317 Command executed by the \! command.
2318 TMPDIR
2320 Directory for storing temporary files. The default is /tmp.
2321 This utility, like most other PostgreSQL utilities, also uses the
2323 environment variables supported by libpq (see Section 33.14).
2324
2326 psqlrc and ~/.psqlrc
2327 Unless it is passed an -X option, psql attempts to read and execute
2328 commands from the system-wide startup file (psqlrc) and then the
2329 user's personal startup file (~/.psqlrc), after connecting to the
2330 database but before accepting normal commands. These files can be
2331 used to set up the client and/or the server to taste, typically
2332 with \set and SET commands.
2333 The system-wide startup file is named psqlrc and is sought in the
2335 installation's “system configuration” directory, which is most
2336 reliably identified by running pg_config --sysconfdir. By default
2337 this directory will be ../etc/ relative to the directory containing
2338 the PostgreSQL executables. The name of this directory can be set
2339 explicitly via the PGSYSCONFDIR environment variable.
2340 The user's personal startup file is named .psqlrc and is sought in
2342 the invoking user's home directory. On Windows, which lacks such a
2343 concept, the personal startup file is named
2344 %APPDATA%\postgresql\psqlrc.conf. The location of the user's
2345 startup file can be set explicitly via the PSQLRC environment
2346 variable.
2347 Both the system-wide startup file and the user's personal startup
2349 file can be made psql-version-specific by appending a dash and the
2350 PostgreSQL major or minor release number to the file name, for
2351 example ~/.psqlrc-9.2 or ~/.psqlrc-9.2.5. The most specific
2352 version-matching file will be read in preference to a
2353 non-version-specific file.
2354 .psql_history
2356 The command-line history is stored in the file ~/.psql_history, or
2357 %APPDATA%\postgresql\psql_history on Windows.
2358 The location of the history file can be set explicitly via the
2360 HISTFILE psql variable or the PSQL_HISTORY environment variable.
2361
2363 · psql works best with servers of the same or an older major version.
2364 Backslash commands are particularly likely to fail if the server is
2365 of a newer version than psql itself. However, backslash commands of
2366 the \d family should work with servers of versions back to 7.4,
2367 though not necessarily with servers newer than psql itself. The
2368 general functionality of running SQL commands and displaying query
2369 results should also work with servers of a newer major version, but
2370 this cannot be guaranteed in all cases.
2371 If you want to use psql to connect to several servers of different
2373 major versions, it is recommended that you use the newest version
2374 of psql. Alternatively, you can keep around a copy of psql from
2375 each major version and be sure to use the version that matches the
2376 respective server. But in practice, this additional complication
2377 should not be necessary.
2378 · Before PostgreSQL 9.6, the -c option implied -X (--no-psqlrc); this
2380 is no longer the case.
2381 · Before PostgreSQL 8.4, psql allowed the first argument of a
2383 single-letter backslash command to start directly after the
2384 command, without intervening whitespace. Now, some whitespace is
2385 required.
2386
2388 psql is built as a “console application”. Since the Windows console
2389 windows use a different encoding than the rest of the system, you must
2390 take special care when using 8-bit characters within psql. If psql
2391 detects a problematic console code page, it will warn you at startup.
2392 To change the console code page, two things are necessary:
2393 · Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
2395 page that is appropriate for German; replace it with your value.)
2396 If you are using Cygwin, you can put this command in /etc/profile.
2397 · Set the console font to Lucida Console, because the raster font
2399 does not work with the ANSI code page.
2400
2402 The first example shows how to spread a command over several lines of
2403 input. Notice the changing prompt:
2404 testdb=> CREATE TABLE my_table (
2406 testdb(> first integer not null default 0,
2407 testdb(> second text)
2408 testdb-> ;
2409 CREATE TABLE
2410 Now look at the table definition again:
2412 testdb=> \d my_table
2414 Table "public.my_table"
2415 Column | Type | Collation | Nullable | Default
2416 --------+---------+-----------+----------+---------
2417 first | integer | | not null | 0
2418 second | text | | |
2419 Now we change the prompt to something more interesting:
2421 testdb=> \set PROMPT1 '%n@%m %~%R%# '
2423 peter@localhost testdb=>
2424 Let's assume you have filled the table with data and want to take a
2426 look at it:
2427 peter@localhost testdb=> SELECT * FROM my_table;
2429 first | second
2430 -------+--------
2431 1 | one
2432 2 | two
2433 3 | three
2434 4 | four
2435 (4 rows)
2436 You can display tables in different ways by using the \pset command:
2439 peter@localhost testdb=> \pset border 2
2441 Border style is 2.
2442 peter@localhost testdb=> SELECT * FROM my_table;
2443 +-------+--------+
2444 | first | second |
2445 +-------+--------+
2446 | 1 | one |
2447 | 2 | two |
2448 | 3 | three |
2449 | 4 | four |
2450 +-------+--------+
2451 (4 rows)
2452 peter@localhost testdb=> \pset border 0
2454 Border style is 0.
2455 peter@localhost testdb=> SELECT * FROM my_table;
2456 first second
2457 ----- ------
2458 1 one
2459 2 two
2460 3 three
2461 4 four
2462 (4 rows)
2463 peter@localhost testdb=> \pset border 1
2465 Border style is 1.
2466 peter@localhost testdb=> \pset format csv
2467 Output format is csv.
2468 peter@localhost testdb=> \pset tuples_only
2469 Tuples only is on.
2470 peter@localhost testdb=> SELECT second, first FROM my_table;
2471 one,1
2472 two,2
2473 three,3
2474 four,4
2475 peter@localhost testdb=> \pset format unaligned
2476 Output format is unaligned.
2477 peter@localhost testdb=> \pset fieldsep '\t'
2478 Field separator is " ".
2479 peter@localhost testdb=> SELECT second, first FROM my_table;
2480 one 1
2481 two 2
2482 three 3
2483 four 4
2484 Alternatively, use the short commands:
2486 peter@localhost testdb=> \a \t \x
2488 Output format is aligned.
2489 Tuples only is off.
2490 Expanded display is on.
2491 peter@localhost testdb=> SELECT * FROM my_table;
2492 -[ RECORD 1 ]-
2493 first | 1
2494 second | one
2495 -[ RECORD 2 ]-
2496 first | 2
2497 second | two
2498 -[ RECORD 3 ]-
2499 first | 3
2500 second | three
2501 -[ RECORD 4 ]-
2502 first | 4
2503 second | four
2504 When suitable, query results can be shown in a crosstab representation
2506 with the \crosstabview command:
2507 testdb=> SELECT first, second, first > 2 AS gt2 FROM my_table;
2509 first | second | gt2
2510 -------+--------+-----
2511 1 | one | f
2512 2 | two | f
2513 3 | three | t
2514 4 | four | t
2515 (4 rows)
2516 testdb=> \crosstabview first second
2518 first | one | two | three | four
2519 -------+-----+-----+-------+------
2520 1 | f | | |
2521 2 | | f | |
2522 3 | | | t |
2523 4 | | | | t
2524 (4 rows)
2525 This second example shows a multiplication table with rows sorted in
2527 reverse numerical order and columns with an independent, ascending
2528 numerical order.
2529 testdb=> SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB",
2531 testdb(> row_number() over(order by t2.first) AS ord
2532 testdb(> FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC
2533 testdb(> \crosstabview "A" "B" "AxB" ord
2534 A | 101 | 102 | 103 | 104
2535 ---+-----+-----+-----+-----
2536 4 | 404 | 408 | 412 | 416
2537 3 | 303 | 306 | 309 | 312
2538 2 | 202 | 204 | 206 | 208
2539 1 | 101 | 102 | 103 | 104
2540 (4 rows)
2541PostgreSQL 12.2 2020 PSQL(1)