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