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