1PSQL(1) PostgreSQL 16.1 Documentation PSQL(1)
2
6 psql - PostgreSQL interactive terminal
7
9 psql [option...] [dbname [username]]
10
12 psql is a terminal-based front-end to PostgreSQL. It enables you to
13 type in queries interactively, issue them to PostgreSQL, and see the
14 query results. Alternatively, input can be from a file or from command
15 line arguments. In addition, psql provides a number of meta-commands
16 and various shell-like features to facilitate writing scripts and
17 automating a wide variety of tasks.
18
20 -a
21 --echo-all
22 Print all nonempty input lines to standard output as they are read.
23 (This does not apply to lines read interactively.) This is
24 equivalent to setting the variable ECHO to all.
25 -A
27 --no-align
28 Switches to unaligned output mode. (The default output mode is
29 aligned.) This is equivalent to \pset format unaligned.
30 -b
32 --echo-errors
33 Print failed SQL commands to standard error output. This is
34 equivalent to setting the variable ECHO to errors.
35 -c command
37 --command=command
38 Specifies that psql is to execute the given command string,
39 command. This option can be repeated and combined in any order with
40 the -f option. When either -c or -f is specified, psql does not
41 read commands from standard input; instead it terminates after
42 processing all the -c and -f options in sequence.
43 command must be either a command string that is completely parsable
45 by the server (i.e., it contains no psql-specific features), or a
46 single backslash command. Thus you cannot mix SQL and psql
47 meta-commands within a -c option. To achieve that, you could use
48 repeated -c options or pipe the string into psql, for example:
49 psql -c '\x' -c 'SELECT * FROM foo;'
51 or
53 echo '\x \\ SELECT * FROM foo;' | psql
55 (\\ is the separator meta-command.)
57 Each SQL command string passed to -c is sent to the server as a
59 single request. Because of this, the server executes it as a single
60 transaction even if the string contains multiple SQL commands,
61 unless there are explicit BEGIN/COMMIT commands included in the
62 string to divide it into multiple transactions. (See
63 Section 55.2.2.1 for more details about how the server handles
64 multi-query strings.)
65 If having several commands executed in one transaction is not
67 desired, use repeated -c commands or feed multiple commands to
68 psql's standard input, either using echo as illustrated above, or
69 via a shell here-document, for example:
70 psql <<EOF
72 \x
73 SELECT * FROM foo;
74 EOF
75 --csv
77 Switches to CSV (Comma-Separated Values) output mode. This is
78 equivalent to \pset format csv.
79 -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. The dbname can be a connection string. If so,
85 connection string parameters will override any conflicting command
86 line options.
87 -e
89 --echo-queries
90 Copy all SQL commands sent to the server to standard output as
91 well. This is equivalent to setting the variable ECHO to queries.
92 -E
94 --echo-hidden
95 Echo the actual queries generated by \d and other backslash
96 commands. You can use this to study psql's internal operations.
97 This is equivalent to setting the variable ECHO_HIDDEN to on.
98 -f filename
100 --file=filename
101 Read commands from the file filename, rather than standard input.
102 This option can be repeated and combined in any order with the -c
103 option. When either -c or -f is specified, psql does not read
104 commands from standard input; instead it terminates after
105 processing all the -c and -f options in sequence. Except for that,
106 this option is largely equivalent to the meta-command \i.
107 If filename is - (hyphen), then standard input is read until an EOF
109 indication or \q meta-command. This can be used to intersperse
110 interactive input with input from files. Note however that Readline
111 is not used in this case (much as if -n had been specified).
112 Using this option is subtly different from writing psql < filename.
114 In general, both will do what you expect, but using -f enables some
115 nice features such as error messages with line numbers. There is
116 also a slight chance that using this option will reduce the
117 start-up overhead. On the other hand, the variant using the shell's
118 input redirection is (in theory) guaranteed to yield exactly the
119 same output you would have received had you entered everything by
120 hand.
121 -F separator
123 --field-separator=separator
124 Use separator as the field separator for unaligned output. This is
125 equivalent to \pset fieldsep or \f.
126 -h hostname
128 --host=hostname
129 Specifies the host name of the machine on which the server is
130 running. If the value begins with a slash, it is used as the
131 directory for the Unix-domain socket.
132 -H
134 --html
135 Switches to HTML output mode. This is equivalent to \pset format
136 html or the \H command.
137 -l
139 --list
140 List all available databases, then exit. Other non-connection
141 options are ignored. This is similar to the meta-command \list.
142 When this option is used, psql will connect to the database
144 postgres, unless a different database is named on the command line
145 (option -d or non-option argument, possibly via a service entry,
146 but not via an environment variable).
147 -L filename
149 --log-file=filename
150 Write all query output into file filename, in addition to the
151 normal output destination.
152 -n
154 --no-readline
155 Do not use Readline for line editing and do not use the command
156 history (see the section called “Command-Line Editing” below).
157 -o filename
159 --output=filename
160 Put all query output into file filename. This is equivalent to the
161 command \o.
162 -p port
164 --port=port
165 Specifies the TCP port or the local Unix-domain socket file
166 extension on which the server is listening for connections.
167 Defaults to the value of the PGPORT environment variable or, if not
168 set, to the port specified at compile time, usually 5432.
169 -P assignment
171 --pset=assignment
172 Specifies printing options, in the style of \pset. Note that here
173 you have to separate name and value with an equal sign instead of a
174 space. For example, to set the output format to LaTeX, you could
175 write -P format=latex.
176 -q
178 --quiet
179 Specifies that psql should do its work quietly. By default, it
180 prints welcome messages and various informational output. If this
181 option is used, none of this happens. This is useful with the -c
182 option. This is equivalent to setting the variable QUIET to on.
183 -R separator
185 --record-separator=separator
186 Use separator as the record separator for unaligned output. This is
187 equivalent to \pset recordsep.
188 -s
190 --single-step
191 Run in single-step mode. That means the user is prompted before
192 each command is sent to the server, with the option to cancel
193 execution as well. Use this to debug scripts.
194 -S
196 --single-line
197 Runs in single-line mode where a newline terminates an SQL command,
198 as a semicolon does.
199 Note
201 This mode is provided for those who insist on it, but you are
202 not necessarily encouraged to use it. In particular, if you mix
203 SQL and meta-commands on a line the order of execution might
204 not always be clear to the inexperienced user.
205 -t
207 --tuples-only
208 Turn off printing of column names and result row count footers,
209 etc. This is equivalent to \t or \pset tuples_only.
210 -T table_options
212 --table-attr=table_options
213 Specifies options to be placed within the HTML table tag. See \pset
214 tableattr for details.
215 -U username
217 --username=username
218 Connect to the database as the user username instead of the
219 default. (You must have permission to do so, of course.)
220 -v assignment
222 --set=assignment
223 --variable=assignment
224 Perform a variable assignment, like the \set meta-command. Note
225 that you must separate name and value, if any, by an equal sign on
226 the command line. To unset a variable, leave off the equal sign. To
227 set a variable with an empty value, use the equal sign but leave
228 off the value. These assignments are done during command line
229 processing, so variables that reflect connection state will get
230 overwritten later.
231 -V
233 --version
234 Print the psql version and exit.
235 -w
237 --no-password
238 Never issue a password prompt. If the server requires password
239 authentication and a password is not available from other sources
240 such as a .pgpass file, the connection attempt will fail. This
241 option can be useful in batch jobs and scripts where no user is
242 present to enter a password.
243 Note that this option will remain set for the entire session, and
245 so it affects uses of the meta-command \connect as well as the
246 initial connection attempt.
247 -W
249 --password
250 Force psql to prompt for a password before connecting to a
251 database, even if the password will not be used.
252 If the server requires password authentication and a password is
254 not available from other sources such as a .pgpass file, psql will
255 prompt for a password in any case. However, psql will waste a
256 connection attempt finding out that the server wants a password. In
257 some cases it is worth typing -W to avoid the extra connection
258 attempt.
259 Note that this option will remain set for the entire session, and
261 so it affects uses of the meta-command \connect as well as the
262 initial connection attempt.
263 -x
265 --expanded
266 Turn on the expanded table formatting mode. This is equivalent to
267 \x or \pset expanded.
268 -X,
270 --no-psqlrc
271 Do not read the start-up file (neither the system-wide psqlrc file
272 nor the user's ~/.psqlrc file).
273 -z
275 --field-separator-zero
276 Set the field separator for unaligned output to a zero byte. This
277 is equivalent to \pset fieldsep_zero.
278 -0
280 --record-separator-zero
281 Set the record separator for unaligned output to a zero byte. This
282 is useful for interfacing, for example, with xargs -0. This is
283 equivalent to \pset recordsep_zero.
284 -1
286 --single-transaction
287 This option can only be used in combination with one or more -c
288 and/or -f options. It causes psql to issue a BEGIN command before
289 the first such option and a COMMIT command after the last one,
290 thereby wrapping all the commands into a single transaction. If any
291 of the commands fails and the variable ON_ERROR_STOP was set, a
292 ROLLBACK command is sent instead. This ensures that either all the
293 commands complete successfully, or no changes are applied.
294 If the commands themselves contain BEGIN, COMMIT, or ROLLBACK, this
296 option will not have the desired effects. Also, if an individual
297 command cannot be executed inside a transaction block, specifying
298 this option will cause the whole transaction to fail.
299 -?
301 --help[=topic]
302 Show help about psql and exit. The optional topic parameter
303 (defaulting to options) selects which part of psql is explained:
304 commands describes psql's backslash commands; options describes the
305 command-line options that can be passed to psql; and variables
306 shows help about psql configuration variables.
307
309 psql returns 0 to the shell if it finished normally, 1 if a fatal error
310 of its own occurs (e.g., out of memory, file not found), 2 if the
311 connection to the server went bad and the session was not interactive,
312 and 3 if an error occurred in a script and the variable ON_ERROR_STOP
313 was set.
314
316 Connecting to a Database
317 psql is a regular PostgreSQL client application. In order to connect to
318 a database you need to know the name of your target database, the host
319 name and port number of the server, and what database user name you
320 want to connect as. psql can be told about those parameters via
321 command line options, namely -d, -h, -p, and -U respectively. If an
322 argument is found that does not belong to any option it will be
323 interpreted as the database name (or the database user name, if the
324 database name is already given). Not all of these options are required;
325 there are useful defaults. If you omit the host name, psql will connect
326 via a Unix-domain socket to a server on the local host, or via TCP/IP
327 to localhost on Windows. The default port number is determined at
328 compile time. Since the database server uses the same default, you will
329 not have to specify the port in most cases. The default database user
330 name is your operating-system user name. Once the database user name is
331 determined, it is used as the default database name. Note that you
332 cannot just connect to any database under any database user name. Your
333 database administrator should have informed you about your access
334 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 34.15.) It is also convenient to have a ~/.pgpass file to
340 avoid regularly having to type in passwords. See Section 34.16 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 34.18. See Section 34.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 (16.1)
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 and NOTIFY.
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 below. The forms :'variable_name' and
419 :"variable_name" described there work as well. The :{?variable_name}
420 syntax allows testing whether a variable is defined. It is substituted
421 by TRUE or FALSE. Escaping the colon with a backslash protects it from
422 substitution.
423 Within an argument, text that is enclosed in backquotes (`) is taken as
425 a command line that is passed to the shell. The output of the command
426 (with any trailing newline removed) replaces the backquoted text.
427 Within the text enclosed in backquotes, no special quoting or other
428 processing occurs, except that appearances of :variable_name where
429 variable_name is a psql variable name are replaced by the variable's
430 value. Also, appearances of :'variable_name' are replaced by the
431 variable's value suitably quoted to become a single shell command
432 argument. (The latter form is almost always preferable, unless you are
433 very sure of what is in the variable.) Because carriage return and line
434 feed characters cannot be safely quoted on all platforms, the
435 :'variable_name' form prints an error message and does not substitute
436 the variable value when such characters appear in the value.
437 Some commands take an SQL identifier (such as a table name) as
439 argument. These arguments follow the syntax rules of SQL: Unquoted
440 letters are forced to lowercase, while double quotes (") protect
441 letters from case conversion and allow incorporation of whitespace into
442 the identifier. Within double quotes, paired double quotes reduce to a
443 single double quote in the resulting name. For example, FOO"BAR"BAZ is
444 interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.
445 Parsing for arguments stops at the end of the line, or when another
447 unquoted backslash is found. An unquoted backslash is taken as the
448 beginning of a new meta-command. The special sequence \\ (two
449 backslashes) marks the end of arguments and continues parsing SQL
450 commands, if any. That way SQL and psql commands can be freely mixed on
451 a line. But in any case, the arguments of a meta-command cannot
452 continue beyond the end of the line.
453 Many of the meta-commands act on the current query buffer. This is
455 simply a buffer holding whatever SQL command text has been typed but
456 not yet sent to the server for execution. This will include previous
457 input lines as well as any text appearing before the meta-command on
458 the same line.
459 The following meta-commands are defined:
461 \a
463 If the current table output format is unaligned, it is switched to
464 aligned. If it is not unaligned, it is set to unaligned. This
465 command is kept for backwards compatibility. See \pset for a more
466 general solution.
467 \bind [ parameter ] ...
469 Sets query parameters for the next query execution, with the
470 specified parameters passed for any parameter placeholders ($1
471 etc.).
472 Example:
474 INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
476 This also works for query-execution commands besides \g, such as
478 \gx and \gset.
479 This command causes the extended query protocol (see
481 Section 55.1.2) to be used, unlike normal psql operation, which
482 uses the simple query protocol. So this command can be useful to
483 test the extended query protocol from psql. (The extended query
484 protocol is used even if the query has no parameters and this
485 command specifies zero parameters.) This command affects only the
486 next query executed; all subsequent queries will use the simple
487 query protocol by default.
488 \c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host
490 ] [ port ] | conninfo ]
491 Establishes a new connection to a PostgreSQL server. The connection
492 parameters to use can be specified either using a positional syntax
493 (one or more of database name, user, host, and port), or using a
494 conninfo connection string as detailed in Section 34.1.1. If no
495 arguments are given, a new connection is made using the same
496 parameters as before.
497 Specifying any of dbname, username, host or port as - is equivalent
499 to omitting that parameter.
500 The new connection can re-use connection parameters from the
502 previous connection; not only database name, user, host, and port,
503 but other settings such as sslmode. By default, parameters are
504 re-used in the positional syntax, but not when a conninfo string is
505 given. Passing a first argument of -reuse-previous=on or
506 -reuse-previous=off overrides that default. If parameters are
507 re-used, then any parameter not explicitly specified as a
508 positional parameter or in the conninfo string is taken from the
509 existing connection's parameters. An exception is that if the host
510 setting is changed from its previous value using the positional
511 syntax, any hostaddr setting present in the existing connection's
512 parameters is dropped. Also, any password used for the existing
513 connection will be re-used only if the user, host, and port
514 settings are not changed. When the command neither specifies nor
515 reuses a particular parameter, the libpq default is used.
516 If the new connection is successfully made, the previous connection
518 is closed. If the connection attempt fails (wrong user name, access
519 denied, etc.), the previous connection will be kept if psql is in
520 interactive mode. But when executing a non-interactive script, the
521 old connection is closed and an error is reported. That may or may
522 not terminate the script; if it does not, all database-accessing
523 commands will fail until another \connect command is successfully
524 executed. This distinction was chosen as a user convenience against
525 typos on the one hand, and a safety mechanism that scripts are not
526 accidentally acting on the wrong database on the other hand. Note
527 that whenever a \connect command attempts to re-use parameters, the
528 values re-used are those of the last successful connection, not of
529 any failed attempts made subsequently. However, in the case of a
530 non-interactive \connect failure, no parameters are allowed to be
531 re-used later, since the script would likely be expecting the
532 values from the failed \connect to be re-used.
533 Examples:
535 => \c mydb myuser host.dom 6432
537 => \c service=foo
538 => \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
539 => \c -reuse-previous=on sslmode=require -- changes only sslmode
540 => \c postgresql://tom@localhost/mydb?application_name=myapp
541 \C [ title ]
543 Sets the title of any tables being printed as the result of a query
544 or unset any such title. This command is equivalent to \pset title
545 title. (The name of this command derives from “caption”, as it was
546 previously only used to set the caption in an HTML table.)
547 \cd [ directory ]
549 Changes the current working directory to directory. Without
550 argument, changes to the current user's home directory.
551 Tip
553 To print your current working directory, use \! pwd.
554 \conninfo
556 Outputs information about the current database connection.
557 \copy { table [ ( column_list ) ] } from { 'filename' | program
559 'command' | stdin | pstdin } [ [ with ] ( option [, ...] ) ] [ where
560 condition ]
561 \copy { table [ ( column_list ) ] | ( query ) } to { 'filename' |
562 program 'command' | stdout | pstdout } [ [ with ] ( option [, ...] ) ]
563 Performs a frontend (client) copy. This is an operation that runs
564 an SQL COPY command, but instead of the server reading or writing
565 the specified file, psql reads or writes the file and routes the
566 data between the server and the local file system. This means that
567 file accessibility and privileges are those of the local user, not
568 the server, and no SQL superuser privileges are required.
569 When program is specified, command is executed by psql and the data
571 passed from or to command is routed between the server and the
572 client. Again, the execution privileges are those of the local
573 user, not the server, and no SQL superuser privileges are required.
574 For \copy ... from stdin, data rows are read from the same source
576 that issued the command, continuing until \. is read or the stream
577 reaches EOF. This option is useful for populating tables in-line
578 within an SQL script file. For \copy ... to stdout, output is sent
579 to the same place as psql command output, and the COPY count
580 command status is not printed (since it might be confused with a
581 data row). To read/write psql's standard input or output regardless
582 of the current command source or \o option, write from pstdin or to
583 pstdout.
584 The syntax of this command is similar to that of the SQL COPY
586 command. All options other than the data source/destination are as
587 specified for COPY. Because of this, special parsing rules apply to
588 the \copy meta-command. Unlike most other meta-commands, the entire
589 remainder of the line is always taken to be the arguments of \copy,
590 and neither variable interpolation nor backquote expansion are
591 performed in the arguments.
592 Tip
594 Another way to obtain the same result as \copy ... to is to use
595 the SQL COPY ... TO STDOUT command and terminate it with \g
596 filename or \g |program. Unlike \copy, this method allows the
597 command to span multiple lines; also, variable interpolation
598 and backquote expansion can be used.
599 Tip
601 These operations are not as efficient as the SQL COPY command
602 with a file or program data source or destination, because all
603 data must pass through the client/server connection. For large
604 amounts of data the SQL command might be preferable. Also,
605 because of this pass-through method, \copy ... from in CSV mode
606 will erroneously treat a \. data value alone on a line as an
607 end-of-input marker.
608 \copyright
610 Shows the copyright and distribution terms of PostgreSQL.
611 \crosstabview [ colV [ colH [ colD [ sortcolH ] ] ] ]
613 Executes the current query buffer (like \g) and shows the results
614 in a crosstab grid. The query must return at least three columns.
615 The output column identified by colV becomes a vertical header and
616 the output column identified by colH becomes a horizontal header.
617 colD identifies the output column to display within the grid.
618 sortcolH identifies an optional sort column for the horizontal
619 header.
620 Each column specification can be a column number (starting at 1) or
622 a column name. The usual SQL case folding and quoting rules apply
623 to column names. If omitted, colV is taken as column 1 and colH as
624 column 2. colH must differ from colV. If colD is not specified,
625 then there must be exactly three columns in the query result, and
626 the column that is neither colV nor colH is taken to be colD.
627 The vertical header, displayed as the leftmost column, contains the
629 values found in column colV, in the same order as in the query
630 results, but with duplicates removed.
631 The horizontal header, displayed as the first row, contains the
633 values found in column colH, with duplicates removed. By default,
634 these appear in the same order as in the query results. But if the
635 optional sortcolH argument is given, it identifies a column whose
636 values must be integer numbers, and the values from colH will
637 appear in the horizontal header sorted according to the
638 corresponding sortcolH values.
639 Inside the crosstab grid, for each distinct value x of colH and
641 each distinct value y of colV, the cell located at the intersection
642 (x,y) contains the value of the colD column in the query result row
643 for which the value of colH is x and the value of colV is y. If
644 there is no such row, the cell is empty. If there are multiple such
645 rows, an error is reported.
646 \d[S+] [ pattern ]
648 For each relation (table, view, materialized view, index, sequence,
649 or foreign table) or composite type matching the pattern, show all
650 columns, their types, the tablespace (if not the default) and any
651 special attributes such as NOT NULL or defaults. Associated
652 indexes, constraints, rules, and triggers are also shown. For
653 foreign tables, the associated foreign server is shown as well.
654 (“Matching the pattern” is defined in Patterns below.)
655 For some types of relation, \d shows additional information for
657 each column: column values for sequences, indexed expressions for
658 indexes, and foreign data wrapper options for foreign tables.
659 The command form \d+ is identical, except that more information is
661 displayed: any comments associated with the columns of the table
662 are shown, as is the presence of OIDs in the table, the view
663 definition if the relation is a view, a non-default replica
664 identity setting and the access method name if the relation has an
665 access method.
666 By default, only user-created objects are shown; supply a pattern
668 or the S modifier to include system objects.
669 Note
671 If \d is used without a pattern argument, it is equivalent to
672 \dtvmsE which will show a list of all visible tables, views,
673 materialized views, sequences and foreign tables. This is
674 purely a convenience measure.
675 \da[S] [ pattern ]
677 Lists aggregate functions, together with their return type and the
678 data types they operate on. If pattern is specified, only
679 aggregates whose names match the pattern are shown. By default,
680 only user-created objects are shown; supply a pattern or the S
681 modifier to include system objects.
682 \dA[+] [ pattern ]
684 Lists access methods. If pattern is specified, only access methods
685 whose names match the pattern are shown. If + is appended to the
686 command name, each access method is listed with its associated
687 handler function and description.
688 \dAc[+] [access-method-pattern [input-type-pattern]]
690 Lists operator classes (see Section 38.16.1). If
691 access-method-pattern is specified, only operator classes
692 associated with access methods whose names match that pattern are
693 listed. If input-type-pattern is specified, only operator classes
694 associated with input types whose names match that pattern are
695 listed. If + is appended to the command name, each operator class
696 is listed with its associated operator family and owner.
697 \dAf[+] [access-method-pattern [input-type-pattern]]
699 Lists operator families (see Section 38.16.5). If
700 access-method-pattern is specified, only operator families
701 associated with access methods whose names match that pattern are
702 listed. If input-type-pattern is specified, only operator families
703 associated with input types whose names match that pattern are
704 listed. If + is appended to the command name, each operator family
705 is listed with its owner.
706 \dAo[+] [access-method-pattern [operator-family-pattern]]
708 Lists operators associated with operator families (see
709 Section 38.16.2). If access-method-pattern is specified, only
710 members of operator families associated with access methods whose
711 names match that pattern are listed. If operator-family-pattern is
712 specified, only members of operator families whose names match that
713 pattern are listed. If + is appended to the command name, each
714 operator is listed with its sort operator family (if it is an
715 ordering operator).
716 \dAp[+] [access-method-pattern [operator-family-pattern]]
718 Lists support functions associated with operator families (see
719 Section 38.16.3). If access-method-pattern is specified, only
720 functions of operator families associated with access methods whose
721 names match that pattern are listed. If operator-family-pattern is
722 specified, only functions of operator families whose names match
723 that pattern are listed. If + is appended to the command name,
724 functions are displayed verbosely, with their actual parameter
725 lists.
726 \db[+] [ pattern ]
728 Lists tablespaces. If pattern is specified, only tablespaces whose
729 names match the pattern are shown. If + is appended to the command
730 name, each tablespace is listed with its associated options,
731 on-disk size, permissions and description.
732 \dc[S+] [ pattern ]
734 Lists conversions between character-set encodings. If pattern is
735 specified, only conversions whose names match the pattern are
736 listed. By default, only user-created objects are shown; supply a
737 pattern or the S modifier to include system objects. If + is
738 appended to the command name, each object is listed with its
739 associated description.
740 \dconfig[+] [ pattern ]
742 Lists server configuration parameters and their values. If pattern
743 is specified, only parameters whose names match the pattern are
744 listed. Without a pattern, only parameters that are set to
745 non-default values are listed. (Use \dconfig * to see all
746 parameters.) If + is appended to the command name, each parameter
747 is listed with its data type, context in which the parameter can be
748 set, and access privileges (if non-default access privileges have
749 been granted).
750 \dC[+] [ pattern ]
752 Lists type casts. If pattern is specified, only casts whose source
753 or target types match the pattern are listed. If + is appended to
754 the command name, each object is listed with its associated
755 description.
756 \dd[S] [ pattern ]
758 Shows the descriptions of objects of type constraint, operator
759 class, operator family, rule, and trigger. All other comments may
760 be viewed by the respective backslash commands for those object
761 types.
762 \dd displays descriptions for objects matching the pattern, or of
764 visible objects of the appropriate type if no argument is given.
765 But in either case, only objects that have a description are
766 listed. By default, only user-created objects are shown; supply a
767 pattern or the S modifier to include system objects.
768 Descriptions for objects can be created with the COMMENT SQL
770 command.
771 \dD[S+] [ pattern ]
773 Lists domains. If pattern is specified, only domains whose names
774 match the pattern are shown. By default, only user-created objects
775 are shown; supply a pattern or the S modifier to include system
776 objects. If + is appended to the command name, each object is
777 listed with its associated permissions and description.
778 \ddp [ pattern ]
780 Lists default access privilege settings. An entry is shown for each
781 role (and schema, if applicable) for which the default privilege
782 settings have been changed from the built-in defaults. If pattern
783 is specified, only entries whose role name or schema name matches
784 the pattern are listed.
785 The ALTER DEFAULT PRIVILEGES command is used to set default access
787 privileges. The meaning of the privilege display is explained in
788 Section 5.7.
789 \dE[S+] [ pattern ]
791 \di[S+] [ pattern ]
792 \dm[S+] [ pattern ]
793 \ds[S+] [ pattern ]
794 \dt[S+] [ pattern ]
795 \dv[S+] [ pattern ]
796 In this group of commands, the letters E, i, m, s, t, and v stand
797 for foreign table, index, materialized view, sequence, table, and
798 view, respectively. You can specify any or all of these letters, in
799 any order, to obtain a listing of objects of these types. For
800 example, \dti lists tables and indexes. If + is appended to the
801 command name, each object is listed with its persistence status
802 (permanent, temporary, or unlogged), physical size on disk, and
803 associated description if any. If pattern is specified, only
804 objects whose names match the pattern are listed. By default, only
805 user-created objects are shown; supply a pattern or the S modifier
806 to include system objects.
807 \des[+] [ pattern ]
809 Lists foreign servers (mnemonic: “external servers”). If pattern is
810 specified, only those servers whose name matches the pattern are
811 listed. If the form \des+ is used, a full description of each
812 server is shown, including the server's access privileges, type,
813 version, options, and description.
814 \det[+] [ pattern ]
816 Lists foreign tables (mnemonic: “external tables”). If pattern is
817 specified, only entries whose table name or schema name matches the
818 pattern are listed. If the form \det+ is used, generic options and
819 the foreign table description are also displayed.
820 \deu[+] [ pattern ]
822 Lists user mappings (mnemonic: “external users”). If pattern is
823 specified, only those mappings whose user names match the pattern
824 are listed. If the form \deu+ is used, additional information about
825 each mapping is shown.
826 Caution
828 \deu+ might also display the user name and password of the
829 remote user, so care should be taken not to disclose them.
830 \dew[+] [ pattern ]
832 Lists foreign-data wrappers (mnemonic: “external wrappers”). If
833 pattern is specified, only those foreign-data wrappers whose name
834 matches the pattern are listed. If the form \dew+ is used, the
835 access privileges, options, and description of the foreign-data
836 wrapper are also shown.
837 \df[anptwS+] [ pattern [ arg_pattern ... ] ]
839 Lists functions, together with their result data types, argument
840 data types, and function types, which are classified as “agg”
841 (aggregate), “normal”, “procedure”, “trigger”, or “window”. To
842 display only functions of specific type(s), add the corresponding
843 letters a, n, p, t, or w to the command. If pattern is specified,
844 only functions whose names match the pattern are shown. Any
845 additional arguments are type-name patterns, which are matched to
846 the type names of the first, second, and so on arguments of the
847 function. (Matching functions can have more arguments than what you
848 specify. To prevent that, write a dash - as the last arg_pattern.)
849 By default, only user-created objects are shown; supply a pattern
850 or the S modifier to include system objects. If the form \df+ is
851 used, additional information about each function is shown,
852 including volatility, parallel safety, owner, security
853 classification, access privileges, language, internal name (for C
854 and internal functions only), and description. Source code for a
855 specific function can be seen using \sf.
856 \dF[+] [ pattern ]
858 Lists text search configurations. If pattern is specified, only
859 configurations whose names match the pattern are shown. If the form
860 \dF+ is used, a full description of each configuration is shown,
861 including the underlying text search parser and the dictionary list
862 for each parser token type.
863 \dFd[+] [ pattern ]
865 Lists text search dictionaries. If pattern is specified, only
866 dictionaries whose names match the pattern are shown. If the form
867 \dFd+ is used, additional information is shown about each selected
868 dictionary, including the underlying text search template and the
869 option values.
870 \dFp[+] [ pattern ]
872 Lists text search parsers. If pattern is specified, only parsers
873 whose names match the pattern are shown. If the form \dFp+ is used,
874 a full description of each parser is shown, including the
875 underlying functions and the list of recognized token types.
876 \dFt[+] [ pattern ]
878 Lists text search templates. If pattern is specified, only
879 templates whose names match the pattern are shown. If the form
880 \dFt+ is used, additional information is shown about each template,
881 including the underlying function names.
882 \dg[S+] [ pattern ]
884 Lists database roles. (Since the concepts of “users” and “groups”
885 have been unified into “roles”, this command is now equivalent to
886 \du.) By default, only user-created roles are shown; supply the S
887 modifier to include system roles. If pattern is specified, only
888 those roles whose names match the pattern are listed. If the form
889 \dg+ is used, additional information is shown about each role;
890 currently this adds the comment for each role.
891 \dl[+]
893 This is an alias for \lo_list, which shows a list of large objects.
894 If + is appended to the command name, each large object is listed
895 with its associated permissions, if any.
896 \dL[S+] [ pattern ]
898 Lists procedural languages. If pattern is specified, only languages
899 whose names match the pattern are listed. By default, only
900 user-created languages are shown; supply the S modifier to include
901 system objects. If + is appended to the command name, each language
902 is listed with its call handler, validator, access privileges, and
903 whether it is a system object.
904 \dn[S+] [ pattern ]
906 Lists schemas (namespaces). If pattern is specified, only schemas
907 whose names match the pattern are listed. By default, only
908 user-created objects are shown; supply a pattern or the S modifier
909 to include system objects. If + is appended to the command name,
910 each object is listed with its associated permissions and
911 description, if any.
912 \do[S+] [ pattern [ arg_pattern [ arg_pattern ] ] ]
914 Lists operators with their operand and result types. If pattern is
915 specified, only operators whose names match the pattern are listed.
916 If one arg_pattern is specified, only prefix operators whose right
917 argument's type name matches that pattern are listed. If two
918 arg_patterns are specified, only binary operators whose argument
919 type names match those patterns are listed. (Alternatively, write -
920 for the unused argument of a unary operator.) By default, only
921 user-created objects are shown; supply a pattern or the S modifier
922 to include system objects. If + is appended to the command name,
923 additional information about each operator is shown, currently just
924 the name of the underlying function.
925 \dO[S+] [ pattern ]
927 Lists collations. If pattern is specified, only collations whose
928 names match the pattern are listed. By default, only user-created
929 objects are shown; supply a pattern or the S modifier to include
930 system objects. If + is appended to the command name, each
931 collation is listed with its associated description, if any. Note
932 that only collations usable with the current database's encoding
933 are shown, so the results may vary in different databases of the
934 same installation.
935 \dp[S] [ pattern ]
937 Lists tables, views and sequences with their associated access
938 privileges. If pattern is specified, only tables, views and
939 sequences whose names match the pattern are listed. By default only
940 user-created objects are shown; supply a pattern or the S modifier
941 to include system objects.
942 The GRANT and REVOKE commands are used to set access privileges.
944 The meaning of the privilege display is explained in Section 5.7.
945 \dP[itn+] [ pattern ]
947 Lists partitioned relations. If pattern is specified, only entries
948 whose name matches the pattern are listed. The modifiers t (tables)
949 and i (indexes) can be appended to the command, filtering the kind
950 of relations to list. By default, partitioned tables and indexes
951 are listed.
952 If the modifier n (“nested”) is used, or a pattern is specified,
954 then non-root partitioned relations are included, and a column is
955 shown displaying the parent of each partitioned relation.
956 If + is appended to the command name, the sum of the sizes of each
958 relation's partitions is also displayed, along with the relation's
959 description. If n is combined with +, two sizes are shown: one
960 including the total size of directly-attached leaf partitions, and
961 another showing the total size of all partitions, including
962 indirectly attached sub-partitions.
963 \drds [ role-pattern [ database-pattern ] ]
965 Lists defined configuration settings. These settings can be
966 role-specific, database-specific, or both. role-pattern and
967 database-pattern are used to select specific roles and databases to
968 list, respectively. If omitted, or if * is specified, all settings
969 are listed, including those not role-specific or database-specific,
970 respectively.
971 The ALTER ROLE and ALTER DATABASE commands are used to define
973 per-role and per-database configuration settings.
974 \drg[S] [ pattern ]
976 Lists information about each granted role membership, including
977 assigned options (ADMIN, INHERIT and/or SET) and grantor. See the
978 GRANT command for information about role memberships.
979 By default, only grants to user-created roles are shown; supply the
981 S modifier to include system roles. If pattern is specified, only
982 grants to those roles whose names match the pattern are listed.
983 \dRp[+] [ pattern ]
985 Lists replication publications. If pattern is specified, only those
986 publications whose names match the pattern are listed. If + is
987 appended to the command name, the tables and schemas associated
988 with each publication are shown as well.
989 \dRs[+] [ pattern ]
991 Lists replication subscriptions. If pattern is specified, only
992 those subscriptions whose names match the pattern are listed. If +
993 is appended to the command name, additional properties of the
994 subscriptions are shown.
995 \dT[S+] [ pattern ]
997 Lists data types. If pattern is specified, only types whose names
998 match the pattern are listed. If + is appended to the command name,
999 each type is listed with its internal name and size, its allowed
1000 values if it is an enum type, and its associated permissions. By
1001 default, only user-created objects are shown; supply a pattern or
1002 the S modifier to include system objects.
1003 \du[S+] [ pattern ]
1005 Lists database roles. (Since the concepts of “users” and “groups”
1006 have been unified into “roles”, this command is now equivalent to
1007 \dg.) By default, only user-created roles are shown; supply the S
1008 modifier to include system roles. If pattern is specified, only
1009 those roles whose names match the pattern are listed. If the form
1010 \du+ is used, additional information is shown about each role;
1011 currently this adds the comment for each role.
1012 \dx[+] [ pattern ]
1014 Lists installed extensions. If pattern is specified, only those
1015 extensions whose names match the pattern are listed. If the form
1016 \dx+ is used, all the objects belonging to each matching extension
1017 are listed.
1018 \dX [ pattern ]
1020 Lists extended statistics. If pattern is specified, only those
1021 extended statistics whose names match the pattern are listed.
1022 The status of each kind of extended statistics is shown in a column
1024 named after its statistic kind (e.g. Ndistinct). defined means
1025 that it was requested when creating the statistics, and NULL means
1026 it wasn't requested. You can use pg_stats_ext if you'd like to know
1027 whether ANALYZE was run and statistics are available to the
1028 planner.
1029 \dy[+] [ pattern ]
1031 Lists event triggers. If pattern is specified, only those event
1032 triggers whose names match the pattern are listed. If + is appended
1033 to the command name, each object is listed with its associated
1034 description.
1035 \e or \edit [ filename ] [ line_number ]
1037 If filename is specified, the file is edited; after the editor
1038 exits, the file's content is copied into the current query buffer.
1039 If no filename is given, the current query buffer is copied to a
1040 temporary file which is then edited in the same fashion. Or, if the
1041 current query buffer is empty, the most recently executed query is
1042 copied to a temporary file and edited in the same fashion.
1043 If you edit a file or the previous query, and you quit the editor
1045 without modifying the file, the query buffer is cleared. Otherwise,
1046 the new contents of the query buffer are re-parsed according to the
1047 normal rules of psql, treating the whole buffer as a single line.
1048 Any complete queries are immediately executed; that is, if the
1049 query buffer contains or ends with a semicolon, everything up to
1050 that point is executed and removed from the query buffer. Whatever
1051 remains in the query buffer is redisplayed. Type semicolon or \g to
1052 send it, or \r to cancel it by clearing the query buffer.
1053 Treating the buffer as a single line primarily affects
1055 meta-commands: whatever is in the buffer after a meta-command will
1056 be taken as argument(s) to the meta-command, even if it spans
1057 multiple lines. (Thus you cannot make meta-command-using scripts
1058 this way. Use \i for that.)
1059 If a line number is specified, psql will position the cursor on the
1061 specified line of the file or query buffer. Note that if a single
1062 all-digits argument is given, psql assumes it is a line number, not
1063 a file name.
1064 Tip
1066 See Environment, below, for how to configure and customize your
1067 editor.
1068 \echo text [ ... ]
1070 Prints the evaluated arguments to standard output, separated by
1071 spaces and followed by a newline. This can be useful to intersperse
1072 information in the output of scripts. For example:
1073 => \echo `date`
1075 Tue Oct 26 21:40:57 CEST 1999
1076 If the first argument is an unquoted -n the trailing newline is not
1078 written (nor is the first argument).
1079 Tip
1081 If you use the \o command to redirect your query output you
1082 might wish to use \qecho instead of this command. See also
1083 \warn.
1084 \ef [ function_description [ line_number ] ]
1086 This command fetches and edits the definition of the named function
1087 or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE
1088 OR REPLACE PROCEDURE command. Editing is done in the same way as
1089 for \edit. If you quit the editor without saving, the statement is
1090 discarded. If you save and exit the editor, the updated command is
1091 executed immediately if you added a semicolon to it. Otherwise it
1092 is redisplayed; type semicolon or \g to send it, or \r to cancel.
1093 The target function can be specified by name alone, or by name and
1095 arguments, for example foo(integer, text). The argument types must
1096 be given if there is more than one function of the same name.
1097 If no function is specified, a blank CREATE FUNCTION template is
1099 presented for editing.
1100 If a line number is specified, psql will position the cursor on the
1102 specified line of the function body. (Note that the function body
1103 typically does not begin on the first line of the file.)
1104 Unlike most other meta-commands, the entire remainder of the line
1106 is always taken to be the argument(s) of \ef, and neither variable
1107 interpolation nor backquote expansion are performed in the
1108 arguments.
1109 Tip
1111 See Environment, below, for how to configure and customize your
1112 editor.
1113 \encoding [ encoding ]
1115 Sets the client character set encoding. Without an argument, this
1116 command shows the current encoding.
1117 \errverbose
1119 Repeats the most recent server error message at maximum verbosity,
1120 as though VERBOSITY were set to verbose and SHOW_CONTEXT were set
1121 to always.
1122 \ev [ view_name [ line_number ] ]
1124 This command fetches and edits the definition of the named view, in
1125 the form of a CREATE OR REPLACE VIEW command. Editing is done in
1126 the same way as for \edit. If you quit the editor without saving,
1127 the statement is discarded. If you save and exit the editor, the
1128 updated command is executed immediately if you added a semicolon to
1129 it. Otherwise it is redisplayed; type semicolon or \g to send it,
1130 or \r to cancel.
1131 If no view is specified, a blank CREATE VIEW template is presented
1133 for editing.
1134 If a line number is specified, psql will position the cursor on the
1136 specified line of the view definition.
1137 Unlike most other meta-commands, the entire remainder of the line
1139 is always taken to be the argument(s) of \ev, and neither variable
1140 interpolation nor backquote expansion are performed in the
1141 arguments.
1142 \f [ string ]
1144 Sets the field separator for unaligned query output. The default is
1145 the vertical bar (|). It is equivalent to \pset fieldsep.
1146 \g [ (option=value [...]) ] [ filename ]
1148 \g [ (option=value [...]) ] [ |command ]
1149 Sends the current query buffer to the server for execution.
1150 If parentheses appear after \g, they surround a space-separated
1152 list of option=value formatting-option clauses, which are
1153 interpreted in the same way as \pset option value commands, but
1154 take effect only for the duration of this query. In this list,
1155 spaces are not allowed around = signs, but are required between
1156 option clauses. If =value is omitted, the named option is changed
1157 in the same way as for \pset option with no explicit value.
1158 If a filename or |command argument is given, the query's output is
1160 written to the named file or piped to the given shell command,
1161 instead of displaying it as usual. The file or command is written
1162 to only if the query successfully returns zero or more tuples, not
1163 if the query fails or is a non-data-returning SQL command.
1164 If the current query buffer is empty, the most recently sent query
1166 is re-executed instead. Except for that behavior, \g without any
1167 arguments is essentially equivalent to a semicolon. With arguments,
1168 \g provides a “one-shot” alternative to the \o command, and
1169 additionally allows one-shot adjustments of the output formatting
1170 options normally set by \pset.
1171 When the last argument begins with |, the entire remainder of the
1173 line is taken to be the command to execute, and neither variable
1174 interpolation nor backquote expansion are performed in it. The rest
1175 of the line is simply passed literally to the shell.
1176 \gdesc
1178 Shows the description (that is, the column names and data types) of
1179 the result of the current query buffer. The query is not actually
1180 executed; however, if it contains some type of syntax error, that
1181 error will be reported in the normal way.
1182 If the current query buffer is empty, the most recently sent query
1184 is described instead.
1185 \getenv psql_var env_var
1187 Gets the value of the environment variable env_var and assigns it
1188 to the psql variable psql_var. If env_var is not defined in the
1189 psql process's environment, psql_var is not changed. Example:
1190 => \getenv home HOME
1192 => \echo :home
1193 /home/postgres
1194 \gexec
1196 Sends the current query buffer to the server, then treats each
1197 column of each row of the query's output (if any) as an SQL
1198 statement to be executed. For example, to create an index on each
1199 column of my_table:
1200 => SELECT format('create index on my_table(%I)', attname)
1202 -> FROM pg_attribute
1203 -> WHERE attrelid = 'my_table'::regclass AND attnum > 0
1204 -> ORDER BY attnum
1205 -> \gexec
1206 CREATE INDEX
1207 CREATE INDEX
1208 CREATE INDEX
1209 CREATE INDEX
1210 The generated queries are executed in the order in which the rows
1212 are returned, and left-to-right within each row if there is more
1213 than one column. NULL fields are ignored. The generated queries are
1214 sent literally to the server for processing, so they cannot be psql
1215 meta-commands nor contain psql variable references. If any
1216 individual query fails, execution of the remaining queries
1217 continues unless ON_ERROR_STOP is set. Execution of each query is
1218 subject to ECHO processing. (Setting ECHO to all or queries is
1219 often advisable when using \gexec.) Query logging, single-step
1220 mode, timing, and other query execution features apply to each
1221 generated query as well.
1222 If the current query buffer is empty, the most recently sent query
1224 is re-executed instead.
1225 \gset [ prefix ]
1227 Sends the current query buffer to the server and stores the query's
1228 output into psql variables (see Variables below). The query to be
1229 executed must return exactly one row. Each column of the row is
1230 stored into a separate variable, named the same as the column. For
1231 example:
1232 => SELECT 'hello' AS var1, 10 AS var2
1234 -> \gset
1235 => \echo :var1 :var2
1236 hello 10
1237 If you specify a prefix, that string is prepended to the query's
1239 column names to create the variable names to use:
1240 => SELECT 'hello' AS var1, 10 AS var2
1242 -> \gset result_
1243 => \echo :result_var1 :result_var2
1244 hello 10
1245 If a column result is NULL, the corresponding variable is unset
1247 rather than being set.
1248 If the query fails or does not return one row, no variables are
1250 changed.
1251 If the current query buffer is empty, the most recently sent query
1253 is re-executed instead.
1254 \gx [ (option=value [...]) ] [ filename ]
1256 \gx [ (option=value [...]) ] [ |command ]
1257 \gx is equivalent to \g, except that it forces expanded output mode
1258 for this query, as if expanded=on were included in the list of
1259 \pset options. See also \x.
1260 \h or \help [ command ]
1262 Gives syntax help on the specified SQL command. If command is not
1263 specified, then psql will list all the commands for which syntax
1264 help is available. If command is an asterisk (*), then syntax help
1265 on all SQL commands is shown.
1266 Unlike most other meta-commands, the entire remainder of the line
1268 is always taken to be the argument(s) of \help, and neither
1269 variable interpolation nor backquote expansion are performed in the
1270 arguments.
1271 Note
1273 To simplify typing, commands that consists of several words do
1274 not have to be quoted. Thus it is fine to type \help alter
1275 table.
1276 \H or \html
1278 Turns on HTML query output format. If the HTML format is already
1279 on, it is switched back to the default aligned text format. This
1280 command is for compatibility and convenience, but see \pset about
1281 setting other output options.
1282 \i or \include filename
1284 Reads input from the file filename and executes it as though it had
1285 been typed on the keyboard.
1286 If filename is - (hyphen), then standard input is read until an EOF
1288 indication or \q meta-command. This can be used to intersperse
1289 interactive input with input from files. Note that Readline
1290 behavior will be used only if it is active at the outermost level.
1291 Note
1293 If you want to see the lines on the screen as they are read you
1294 must set the variable ECHO to all.
1295 \if expression
1297 \elif expression
1298 \else
1299 \endif
1300 This group of commands implements nestable conditional blocks. A
1301 conditional block must begin with an \if and end with an \endif. In
1302 between there may be any number of \elif clauses, which may
1303 optionally be followed by a single \else clause. Ordinary queries
1304 and other types of backslash commands may (and usually do) appear
1305 between the commands forming a conditional block.
1306 The \if and \elif commands read their argument(s) and evaluate them
1308 as a Boolean expression. If the expression yields true then
1309 processing continues normally; otherwise, lines are skipped until a
1310 matching \elif, \else, or \endif is reached. Once an \if or \elif
1311 test has succeeded, the arguments of later \elif commands in the
1312 same block are not evaluated but are treated as false. Lines
1313 following an \else are processed only if no earlier matching \if or
1314 \elif succeeded.
1315 The expression argument of an \if or \elif command is subject to
1317 variable interpolation and backquote expansion, just like any other
1318 backslash command argument. After that it is evaluated like the
1319 value of an on/off option variable. So a valid value is any
1320 unambiguous case-insensitive match for one of: true, false, 1, 0,
1321 on, off, yes, no. For example, t, T, and tR will all be considered
1322 to be true.
1323 Expressions that do not properly evaluate to true or false will
1325 generate a warning and be treated as false.
1326 Lines being skipped are parsed normally to identify queries and
1328 backslash commands, but queries are not sent to the server, and
1329 backslash commands other than conditionals (\if, \elif, \else,
1330 \endif) are ignored. Conditional commands are checked only for
1331 valid nesting. Variable references in skipped lines are not
1332 expanded, and backquote expansion is not performed either.
1333 All the backslash commands of a given conditional block must appear
1335 in the same source file. If EOF is reached on the main input file
1336 or an \include-ed file before all local \if-blocks have been
1337 closed, then psql will raise an error.
1338 Here is an example:
1340 -- check for the existence of two separate records in the database and store
1342 -- the results in separate psql variables
1343 SELECT
1344 EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,
1345 EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee
1346 \gset
1347 \if :is_customer
1348 SELECT * FROM customer WHERE customer_id = 123;
1349 \elif :is_employee
1350 \echo 'is not a customer but is an employee'
1351 SELECT * FROM employee WHERE employee_id = 456;
1352 \else
1353 \if yes
1354 \echo 'not a customer or employee'
1355 \else
1356 \echo 'this will never print'
1357 \endif
1358 \endif
1359 \ir or \include_relative filename
1361 The \ir command is similar to \i, but resolves relative file names
1362 differently. When executing in interactive mode, the two commands
1363 behave identically. However, when invoked from a script, \ir
1364 interprets file names relative to the directory in which the script
1365 is located, rather than the current working directory.
1366 \l[+] or \list[+] [ pattern ]
1368 List the databases in the server and show their names, owners,
1369 character set encodings, and access privileges. If pattern is
1370 specified, only databases whose names match the pattern are listed.
1371 If + is appended to the command name, database sizes, default
1372 tablespaces, and descriptions are also displayed. (Size information
1373 is only available for databases that the current user can connect
1374 to.)
1375 \lo_export loid filename
1377 Reads the large object with OID loid from the database and writes
1378 it to filename. Note that this is subtly different from the server
1379 function lo_export, which acts with the permissions of the user
1380 that the database server runs as and on the server's file system.
1381 Tip
1383 Use \lo_list to find out the large object's OID.
1384 \lo_import filename [ comment ]
1386 Stores the file into a PostgreSQL large object. Optionally, it
1387 associates the given comment with the object. Example:
1388 foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
1390 lo_import 152801
1391 The response indicates that the large object received object ID
1393 152801, which can be used to access the newly-created large object
1394 in the future. For the sake of readability, it is recommended to
1395 always associate a human-readable comment with every object. Both
1396 OIDs and comments can be viewed with the \lo_list command.
1397 Note that this command is subtly different from the server-side
1399 lo_import because it acts as the local user on the local file
1400 system, rather than the server's user and file system.
1401 \lo_list[+]
1403 Shows a list of all PostgreSQL large objects currently stored in
1404 the database, along with any comments provided for them. If + is
1405 appended to the command name, each large object is listed with its
1406 associated permissions, if any.
1407 \lo_unlink loid
1409 Deletes the large object with OID loid from the database.
1410 Tip
1412 Use \lo_list to find out the large object's OID.
1413 \o or \out [ filename ]
1415 \o or \out [ |command ]
1416 Arranges to save future query results to the file filename or pipe
1417 future results to the shell command command. If no argument is
1418 specified, the query output is reset to the standard output.
1419 If the argument begins with |, then the entire remainder of the
1421 line is taken to be the command to execute, and neither variable
1422 interpolation nor backquote expansion are performed in it. The rest
1423 of the line is simply passed literally to the shell.
1424 “Query results” includes all tables, command responses, and notices
1426 obtained from the database server, as well as output of various
1427 backslash commands that query the database (such as \d); but not
1428 error messages.
1429 Tip
1431 To intersperse text output in between query results, use
1432 \qecho.
1433 \p or \print
1435 Print the current query buffer to the standard output. If the
1436 current query buffer is empty, the most recently executed query is
1437 printed instead.
1438 \password [ username ]
1440 Changes the password of the specified user (by default, the current
1441 user). This command prompts for the new password, encrypts it, and
1442 sends it to the server as an ALTER ROLE command. This makes sure
1443 that the new password does not appear in cleartext in the command
1444 history, the server log, or elsewhere.
1445 \prompt [ text ] name
1447 Prompts the user to supply text, which is assigned to the variable
1448 name. An optional prompt string, text, can be specified. (For
1449 multiword prompts, surround the text with single quotes.)
1450 By default, \prompt uses the terminal for input and output.
1452 However, if the -f command line switch was used, \prompt uses
1453 standard input and standard output.
1454 \pset [ option [ value ] ]
1456 This command sets options affecting the output of query result
1457 tables. option indicates which option is to be set. The semantics
1458 of value vary depending on the selected option. For some options,
1459 omitting value causes the option to be toggled or unset, as
1460 described under the particular option. If no such behavior is
1461 mentioned, then omitting value just results in the current setting
1462 being displayed.
1463 \pset without any arguments displays the current status of all
1465 printing options.
1466 Adjustable printing options are:
1468 border
1470 The value must be a number. In general, the higher the number
1471 the more borders and lines the tables will have, but details
1472 depend on the particular format. In HTML format, this will
1473 translate directly into the border=... attribute. In most
1474 other formats only values 0 (no border), 1 (internal dividing
1475 lines), and 2 (table frame) make sense, and values above 2 will
1476 be treated the same as border = 2. The latex and
1477 latex-longtable formats additionally allow a value of 3 to add
1478 dividing lines between data rows.
1479 columns
1481 Sets the target width for the wrapped format, and also the
1482 width limit for determining whether output is wide enough to
1483 require the pager or switch to the vertical display in expanded
1484 auto mode. Zero (the default) causes the target width to be
1485 controlled by the environment variable COLUMNS, or the detected
1486 screen width if COLUMNS is not set. In addition, if columns is
1487 zero then the wrapped format only affects screen output. If
1488 columns is nonzero then file and pipe output is wrapped to that
1489 width as well.
1490 csv_fieldsep
1492 Specifies the field separator to be used in CSV output format.
1493 If the separator character appears in a field's value, that
1494 field is output within double quotes, following standard CSV
1495 rules. The default is a comma.
1496 expanded (or x)
1498 If value is specified it must be either on or off, which will
1499 enable or disable expanded mode, or auto. If value is omitted
1500 the command toggles between the on and off settings. When
1501 expanded mode is enabled, query results are displayed in two
1502 columns, with the column name on the left and the data on the
1503 right. This mode is useful if the data wouldn't fit on the
1504 screen in the normal “horizontal” mode. In the auto setting,
1505 the expanded mode is used whenever the query output has more
1506 than one column and is wider than the screen; otherwise, the
1507 regular mode is used. The auto setting is only effective in the
1508 aligned and wrapped formats. In other formats, it always
1509 behaves as if the expanded mode is off.
1510 fieldsep
1512 Specifies the field separator to be used in unaligned output
1513 format. That way one can create, for example, tab-separated
1514 output, which other programs might prefer. To set a tab as
1515 field separator, type \pset fieldsep '\t'. The default field
1516 separator is '|' (a vertical bar).
1517 fieldsep_zero
1519 Sets the field separator to use in unaligned output format to a
1520 zero byte.
1521 footer
1523 If value is specified it must be either on or off which will
1524 enable or disable display of the table footer (the (n rows)
1525 count). If value is omitted the command toggles footer display
1526 on or off.
1527 format
1529 Sets the output format to one of aligned, asciidoc, csv, html,
1530 latex, latex-longtable, troff-ms, unaligned, or wrapped. Unique
1531 abbreviations are allowed.
1532 aligned format is the standard, human-readable, nicely
1534 formatted text output; this is the default.
1535 unaligned format writes all columns of a row on one line,
1537 separated by the currently active field separator. This is
1538 useful for creating output that might be intended to be read in
1539 by other programs, for example, tab-separated or
1540 comma-separated format. However, the field separator character
1541 is not treated specially if it appears in a column's value; so
1542 CSV format may be better suited for such purposes.
1543 csv format
1545 writes column values separated by commas, applying the quoting
1547 rules described in RFC 4180. This output is compatible with the
1548 CSV format of the server's COPY command. A header line with
1549 column names is generated unless the tuples_only parameter is
1550 on. Titles and footers are not printed. Each row is terminated
1551 by the system-dependent end-of-line character, which is
1552 typically a single newline (\n) for Unix-like systems or a
1553 carriage return and newline sequence (\r\n) for Microsoft
1554 Windows. Field separator characters other than comma can be
1555 selected with \pset csv_fieldsep.
1556 wrapped format is like aligned but wraps wide data values
1558 across lines to make the output fit in the target column width.
1559 The target width is determined as described under the columns
1560 option. Note that psql will not attempt to wrap column header
1561 titles; therefore, wrapped format behaves the same as aligned
1562 if the total width needed for column headers exceeds the
1563 target.
1564 The asciidoc, html, latex, latex-longtable, and troff-ms
1566 formats put out tables that are intended to be included in
1567 documents using the respective mark-up language. They are not
1568 complete documents! This might not be necessary in HTML, but in
1569 LaTeX you must have a complete document wrapper. The latex
1570 format uses LaTeX's tabular environment. The latex-longtable
1571 format requires the LaTeX longtable and booktabs packages.
1572 linestyle
1574 Sets the border line drawing style to one of ascii, old-ascii,
1575 or unicode. Unique abbreviations are allowed. (That would mean
1576 one letter is enough.) The default setting is ascii. This
1577 option only affects the aligned and wrapped output formats.
1578 ascii style uses plain ASCII characters. Newlines in data are
1580 shown using a + symbol in the right-hand margin. When the
1581 wrapped format wraps data from one line to the next without a
1582 newline character, a dot (.) is shown in the right-hand margin
1583 of the first line, and again in the left-hand margin of the
1584 following line.
1585 old-ascii style uses plain ASCII characters, using the
1587 formatting style used in PostgreSQL 8.4 and earlier. Newlines
1588 in data are shown using a : symbol in place of the left-hand
1589 column separator. When the data is wrapped from one line to the
1590 next without a newline character, a ; symbol is used in place
1591 of the left-hand column separator.
1592 unicode style uses Unicode box-drawing characters. Newlines in
1594 data are shown using a carriage return symbol in the right-hand
1595 margin. When the data is wrapped from one line to the next
1596 without a newline character, an ellipsis symbol is shown in the
1597 right-hand margin of the first line, and again in the left-hand
1598 margin of the following line.
1599 When the border setting is greater than zero, the linestyle
1601 option also determines the characters with which the border
1602 lines are drawn. Plain ASCII characters work everywhere, but
1603 Unicode characters look nicer on displays that recognize them.
1604 null
1606 Sets the string to be printed in place of a null value. The
1607 default is to print nothing, which can easily be mistaken for
1608 an empty string. For example, one might prefer \pset null
1609 '(null)'.
1610 numericlocale
1612 If value is specified it must be either on or off which will
1613 enable or disable display of a locale-specific character to
1614 separate groups of digits to the left of the decimal marker. If
1615 value is omitted the command toggles between regular and
1616 locale-specific numeric output.
1617 pager
1619 Controls use of a pager program for query and psql help output.
1620 When the pager option is off, the pager program is not used.
1621 When the pager option is on, the pager is used when
1622 appropriate, i.e., when the output is to a terminal and will
1623 not fit on the screen. The pager option can also be set to
1624 always, which causes the pager to be used for all terminal
1625 output regardless of whether it fits on the screen. \pset
1626 pager without a value toggles pager use on and off.
1627 If the environment variable PSQL_PAGER or PAGER is set, output
1629 to be paged is piped to the specified program. Otherwise a
1630 platform-dependent default program (such as more) is used.
1631 When using the \watch command to execute a query repeatedly,
1633 the environment variable PSQL_WATCH_PAGER is used to find the
1634 pager program instead, on Unix systems. This is configured
1635 separately because it may confuse traditional pagers, but can
1636 be used to send output to tools that understand psql's output
1637 format (such as pspg --stream).
1638 pager_min_lines
1640 If pager_min_lines is set to a number greater than the page
1641 height, the pager program will not be called unless there are
1642 at least this many lines of output to show. The default setting
1643 is 0.
1644 recordsep
1646 Specifies the record (line) separator to use in unaligned
1647 output format. The default is a newline character.
1648 recordsep_zero
1650 Sets the record separator to use in unaligned output format to
1651 a zero byte.
1652 tableattr (or T)
1654 In HTML format, this specifies attributes to be placed inside
1655 the table tag. This could for example be cellpadding or
1656 bgcolor. Note that you probably don't want to specify border
1657 here, as that is already taken care of by \pset border. If no
1658 value is given, the table attributes are unset.
1659 In latex-longtable format, this controls the proportional width
1661 of each column containing a left-aligned data type. It is
1662 specified as a whitespace-separated list of values, e.g., '0.2
1663 0.2 0.6'. Unspecified output columns use the last specified
1664 value.
1665 title (or C)
1667 Sets the table title for any subsequently printed tables. This
1668 can be used to give your output descriptive tags. If no value
1669 is given, the title is unset.
1670 tuples_only (or t)
1672 If value is specified it must be either on or off which will
1673 enable or disable tuples-only mode. If value is omitted the
1674 command toggles between regular and tuples-only output. Regular
1675 output includes extra information such as column headers,
1676 titles, and various footers. In tuples-only mode, only actual
1677 table data is shown.
1678 unicode_border_linestyle
1680 Sets the border drawing style for the unicode line style to one
1681 of single or double.
1682 unicode_column_linestyle
1684 Sets the column drawing style for the unicode line style to one
1685 of single or double.
1686 unicode_header_linestyle
1688 Sets the header drawing style for the unicode line style to one
1689 of single or double.
1690 xheader_width
1692 Sets the maximum width of the header for expanded output to one
1693 of full (the default value), column, page, or an integer value.
1694 full: the expanded header is not truncated, and will be as wide
1696 as the widest output line.
1697 column: truncate the header line to the width of the first
1699 column.
1700 page: truncate the header line to the terminal width.
1702 integer value: specify the exact maximum width of the header
1704 line.
1705 Illustrations of how these different formats look can be seen in
1707 Examples, below.
1708 Tip
1710 There are various shortcut commands for \pset. See \a, \C, \f,
1711 \H, \t, \T, and \x.
1712 \q or \quit
1714 Quits the psql program. In a script file, only execution of that
1715 script is terminated.
1716 \qecho text [ ... ]
1718 This command is identical to \echo except that the output will be
1719 written to the query output channel, as set by \o.
1720 \r or \reset
1722 Resets (clears) the query buffer.
1723 \s [ filename ]
1725 Print psql's command line history to filename. If filename is
1726 omitted, the history is written to the standard output (using the
1727 pager if appropriate). This command is not available if psql was
1728 built without Readline support.
1729 \set [ name [ value [ ... ] ] ]
1731 Sets the psql variable name to value, or if more than one value is
1732 given, to the concatenation of all of them. If only one argument is
1733 given, the variable is set to an empty-string value. To unset a
1734 variable, use the \unset command.
1735 \set without any arguments displays the names and values of all
1737 currently-set psql variables.
1738 Valid variable names can contain letters, digits, and underscores.
1740 See Variables below for details. Variable names are case-sensitive.
1741 Certain variables are special, in that they control psql's behavior
1743 or are automatically set to reflect connection state. These
1744 variables are documented in Variables, below.
1745 Note
1747 This command is unrelated to the SQL command SET.
1748 \setenv name [ value ]
1750 Sets the environment variable name to value, or if the value is not
1751 supplied, unsets the environment variable. Example:
1752 testdb=> \setenv PAGER less
1754 testdb=> \setenv LESS -imx4F
1755 \sf[+] function_description
1757 This command fetches and shows the definition of the named function
1758 or procedure, in the form of a CREATE OR REPLACE FUNCTION or CREATE
1759 OR REPLACE PROCEDURE command. The definition is printed to the
1760 current query output channel, as set by \o.
1761 The target function can be specified by name alone, or by name and
1763 arguments, for example foo(integer, text). The argument types must
1764 be given if there is more than one function of the same name.
1765 If + is appended to the command name, then the output lines are
1767 numbered, with the first line of the function body being line 1.
1768 Unlike most other meta-commands, the entire remainder of the line
1770 is always taken to be the argument(s) of \sf, and neither variable
1771 interpolation nor backquote expansion are performed in the
1772 arguments.
1773 \sv[+] view_name
1775 This command fetches and shows the definition of the named view, in
1776 the form of a CREATE OR REPLACE VIEW command. The definition is
1777 printed to the current query output channel, as set by \o.
1778 If + is appended to the command name, then the output lines are
1780 numbered from 1.
1781 Unlike most other meta-commands, the entire remainder of the line
1783 is always taken to be the argument(s) of \sv, and neither variable
1784 interpolation nor backquote expansion are performed in the
1785 arguments.
1786 \t
1788 Toggles the display of output column name headings and row count
1789 footer. This command is equivalent to \pset tuples_only and is
1790 provided for convenience.
1791 \T table_options
1793 Specifies attributes to be placed within the table tag in HTML
1794 output format. This command is equivalent to \pset tableattr
1795 table_options.
1796 \timing [ on | off ]
1798 With a parameter, turns displaying of how long each SQL statement
1799 takes on or off. Without a parameter, toggles the display between
1800 on and off. The display is in milliseconds; intervals longer than 1
1801 second are also shown in minutes:seconds format, with hours and
1802 days fields added if needed.
1803 \unset name
1805 Unsets (deletes) the psql variable name.
1806 Most variables that control psql's behavior cannot be unset;
1808 instead, an \unset command is interpreted as setting them to their
1809 default values. See Variables below.
1810 \w or \write filename
1812 \w or \write |command
1813 Writes the current query buffer to the file filename or pipes it to
1814 the shell command command. If the current query buffer is empty,
1815 the most recently executed query is written instead.
1816 If the argument begins with |, then the entire remainder of the
1818 line is taken to be the command to execute, and neither variable
1819 interpolation nor backquote expansion are performed in it. The rest
1820 of the line is simply passed literally to the shell.
1821 \warn text [ ... ]
1823 This command is identical to \echo except that the output will be
1824 written to psql's standard error channel, rather than standard
1825 output.
1826 \watch [ i[nterval]=seconds ] [ c[ount]=times ] [ seconds ]
1828 Repeatedly execute the current query buffer (as \g does) until
1829 interrupted, or the query fails, or the execution count limit (if
1830 given) is reached. Wait the specified number of seconds (default 2)
1831 between executions. For backwards compatibility, seconds can be
1832 specified with or without an interval= prefix. Each query result is
1833 displayed with a header that includes the \pset title string (if
1834 any), the time as of query start, and the delay interval.
1835 If the current query buffer is empty, the most recently sent query
1837 is re-executed instead.
1838 \x [ on | off | auto ]
1840 Sets or toggles expanded table formatting mode. As such it is
1841 equivalent to \pset expanded.
1842 \z[S] [ pattern ]
1844 Lists tables, views and sequences with their associated access
1845 privileges. If a pattern is specified, only tables, views and
1846 sequences whose names match the pattern are listed. By default only
1847 user-created objects are shown; supply a pattern or the S modifier
1848 to include system objects.
1849 This is an alias for \dp (“display privileges”).
1851 \! [ command ]
1853 With no argument, escapes to a sub-shell; psql resumes when the
1854 sub-shell exits. With an argument, executes the shell command
1855 command.
1856 Unlike most other meta-commands, the entire remainder of the line
1858 is always taken to be the argument(s) of \!, and neither variable
1859 interpolation nor backquote expansion are performed in the
1860 arguments. The rest of the line is simply passed literally to the
1861 shell.
1862 \? [ topic ]
1864 Shows help information. The optional topic parameter (defaulting to
1865 commands) selects which part of psql is explained: commands
1866 describes psql's backslash commands; options describes the
1867 command-line options that can be passed to psql; and variables
1868 shows help about psql configuration variables.
1869 \;
1871 Backslash-semicolon is not a meta-command in the same way as the
1872 preceding commands; rather, it simply causes a semicolon to be
1873 added to the query buffer without any further processing.
1874 Normally, psql will dispatch an SQL command to the server as soon
1876 as it reaches the command-ending semicolon, even if more input
1877 remains on the current line. Thus for example entering
1878 select 1; select 2; select 3;
1880 will result in the three SQL commands being individually sent to
1882 the server, with each one's results being displayed before
1883 continuing to the next command. However, a semicolon entered as \;
1884 will not trigger command processing, so that the command before it
1885 and the one after are effectively combined and sent to the server
1886 in one request. So for example
1887 select 1\; select 2\; select 3;
1889 results in sending the three SQL commands to the server in a single
1891 request, when the non-backslashed semicolon is reached. The server
1892 executes such a request as a single transaction, unless there are
1893 explicit BEGIN/COMMIT commands included in the string to divide it
1894 into multiple transactions. (See Section 55.2.2.1 for more details
1895 about how the server handles multi-query strings.)
1896 Patterns
1898 The various \d commands accept a pattern parameter to specify the
1899 object name(s) to be displayed. In the simplest case, a pattern is
1900 just the exact name of the object. The characters within a pattern
1901 are normally folded to lower case, just as in SQL names; for
1902 example, \dt FOO will display the table named foo. As in SQL names,
1903 placing double quotes around a pattern stops folding to lower case.
1904 Should you need to include an actual double quote character in a
1905 pattern, write it as a pair of double quotes within a double-quote
1906 sequence; again this is in accord with the rules for SQL quoted
1907 identifiers. For example, \dt "FOO""BAR" will display the table
1908 named FOO"BAR (not foo"bar). Unlike the normal rules for SQL names,
1909 you can put double quotes around just part of a pattern, for
1910 instance \dt FOO"FOO"BAR will display the table named fooFOObar.
1911 Whenever the pattern parameter is omitted completely, the \d
1913 commands display all objects that are visible in the current schema
1914 search path — this is equivalent to using * as the pattern. (An
1915 object is said to be visible if its containing schema is in the
1916 search path and no object of the same kind and name appears earlier
1917 in the search path. This is equivalent to the statement that the
1918 object can be referenced by name without explicit schema
1919 qualification.) To see all objects in the database regardless of
1920 visibility, use *.* as the pattern.
1921 Within a pattern, * matches any sequence of characters (including
1923 no characters) and ? matches any single character. (This notation
1924 is comparable to Unix shell file name patterns.) For example, \dt
1925 int* displays tables whose names begin with int. But within double
1926 quotes, * and ? lose these special meanings and are just matched
1927 literally.
1928 A relation pattern that contains a dot (.) is interpreted as a
1930 schema name pattern followed by an object name pattern. For
1931 example, \dt foo*.*bar* displays all tables whose table name
1932 includes bar that are in schemas whose schema name starts with foo.
1933 When no dot appears, then the pattern matches only objects that are
1934 visible in the current schema search path. Again, a dot within
1935 double quotes loses its special meaning and is matched literally. A
1936 relation pattern that contains two dots (.) is interpreted as a
1937 database name followed by a schema name pattern followed by an
1938 object name pattern. The database name portion will not be treated
1939 as a pattern and must match the name of the currently connected
1940 database, else an error will be raised.
1941 A schema pattern that contains a dot (.) is interpreted as a
1943 database name followed by a schema name pattern. For example, \dn
1944 mydb.*foo* displays all schemas whose schema name includes foo. The
1945 database name portion will not be treated as a pattern and must
1946 match the name of the currently connected database, else an error
1947 will be raised.
1948 Advanced users can use regular-expression notations such as
1950 character classes, for example [0-9] to match any digit. All
1951 regular expression special characters work as specified in
1952 Section 9.7.3, except for . which is taken as a separator as
1953 mentioned above, * which is translated to the regular-expression
1954 notation .*, ? which is translated to ., and $ which is matched
1955 literally. You can emulate these pattern characters at need by
1956 writing ? for ., (R+|) for R*, or (R|) for R?. $ is not needed as
1957 a regular-expression character since the pattern must match the
1958 whole name, unlike the usual interpretation of regular expressions
1959 (in other words, $ is automatically appended to your pattern).
1960 Write * at the beginning and/or end if you don't wish the pattern
1961 to be anchored. Note that within double quotes, all regular
1962 expression special characters lose their special meanings and are
1963 matched literally. Also, the regular expression special characters
1964 are matched literally in operator name patterns (i.e., the argument
1965 of \do).
1966 Advanced Features
1968 Variables
1969 psql provides variable substitution features similar to common Unix
1970 command shells. Variables are simply name/value pairs, where the
1971 value can be any string of any length. The name must consist of
1972 letters (including non-Latin letters), digits, and underscores.
1973 To set a variable, use the psql meta-command \set. For example,
1975 testdb=> \set foo bar
1977 sets the variable foo to the value bar. To retrieve the content of
1979 the variable, precede the name with a colon, for example:
1980 testdb=> \echo :foo
1982 bar
1983 This works in both regular SQL commands and meta-commands; there is
1985 more detail in SQL Interpolation, below.
1986 If you call \set without a second argument, the variable is set to
1988 an empty-string value. To unset (i.e., delete) a variable, use the
1989 command \unset. To show the values of all variables, call \set
1990 without any argument.
1991 Note
1993 The arguments of \set are subject to the same substitution
1994 rules as with other commands. Thus you can construct
1995 interesting references such as \set :foo 'something' and get
1996 “soft links” or “variable variables” of Perl or PHP fame,
1997 respectively. Unfortunately (or fortunately?), there is no way
1998 to do anything useful with these constructs. On the other hand,
1999 \set bar :foo is a perfectly valid way to copy a variable.
2000 A number of these variables are treated specially by psql. They
2002 represent certain option settings that can be changed at run time
2003 by altering the value of the variable, or in some cases represent
2004 changeable state of psql. By convention, all specially treated
2005 variables' names consist of all upper-case ASCII letters (and
2006 possibly digits and underscores). To ensure maximum compatibility
2007 in the future, avoid using such variable names for your own
2008 purposes.
2009 Variables that control psql's behavior generally cannot be unset or
2011 set to invalid values. An \unset command is allowed but is
2012 interpreted as setting the variable to its default value. A \set
2013 command without a second argument is interpreted as setting the
2014 variable to on, for control variables that accept that value, and
2015 is rejected for others. Also, control variables that accept the
2016 values on and off will also accept other common spellings of
2017 Boolean values, such as true and false.
2018 The specially treated variables are:
2020 AUTOCOMMIT
2022 When on (the default), each SQL command is automatically
2023 committed upon successful completion. To postpone commit in
2024 this mode, you must enter a BEGIN or START TRANSACTION SQL
2025 command. When off or unset, SQL commands are not committed
2026 until you explicitly issue COMMIT or END. The autocommit-off
2027 mode works by issuing an implicit BEGIN for you, just before
2028 any command that is not already in a transaction block and is
2029 not itself a BEGIN or other transaction-control command, nor a
2030 command that cannot be executed inside a transaction block
2031 (such as VACUUM).
2032 Note
2034 In autocommit-off mode, you must explicitly abandon any
2035 failed transaction by entering ABORT or ROLLBACK. Also keep
2036 in mind that if you exit the session without committing,
2037 your work will be lost.
2038 Note
2040 The autocommit-on mode is PostgreSQL's traditional
2041 behavior, but autocommit-off is closer to the SQL spec. If
2042 you prefer autocommit-off, you might wish to set it in the
2043 system-wide psqlrc file or your ~/.psqlrc file.
2044 COMP_KEYWORD_CASE
2046 Determines which letter case to use when completing an SQL key
2047 word. If set to lower or upper, the completed word will be in
2048 lower or upper case, respectively. If set to preserve-lower or
2049 preserve-upper (the default), the completed word will be in the
2050 case of the word already entered, but words being completed
2051 without anything entered will be in lower or upper case,
2052 respectively.
2053 DBNAME
2055 The name of the database you are currently connected to. This
2056 is set every time you connect to a database (including program
2057 start-up), but can be changed or unset.
2058 ECHO
2060 If set to all, all nonempty input lines are printed to standard
2061 output as they are read. (This does not apply to lines read
2062 interactively.) To select this behavior on program start-up,
2063 use the switch -a. If set to queries, psql prints each query to
2064 standard output as it is sent to the server. The switch to
2065 select this behavior is -e. If set to errors, then only failed
2066 queries are displayed on standard error output. The switch for
2067 this behavior is -b. If set to none (the default), then no
2068 queries are displayed.
2069 ECHO_HIDDEN
2071 When this variable is set to on and a backslash command queries
2072 the database, the query is first shown. This feature helps you
2073 to study PostgreSQL internals and provide similar functionality
2074 in your own programs. (To select this behavior on program
2075 start-up, use the switch -E.) If you set this variable to the
2076 value noexec, the queries are just shown but are not actually
2077 sent to the server and executed. The default value is off.
2078 ENCODING
2080 The current client character set encoding. This is set every
2081 time you connect to a database (including program start-up),
2082 and when you change the encoding with \encoding, but it can be
2083 changed or unset.
2084 ERROR
2086 true if the last SQL query failed, false if it succeeded. See
2087 also SQLSTATE.
2088 FETCH_COUNT
2090 If this variable is set to an integer value greater than zero,
2091 the results of SELECT queries are fetched and displayed in
2092 groups of that many rows, rather than the default behavior of
2093 collecting the entire result set before display. Therefore only
2094 a limited amount of memory is used, regardless of the size of
2095 the result set. Settings of 100 to 1000 are commonly used when
2096 enabling this feature. Keep in mind that when using this
2097 feature, a query might fail after having already displayed some
2098 rows.
2099 Tip
2101 Although you can use any output format with this feature,
2102 the default aligned format tends to look bad because each
2103 group of FETCH_COUNT rows will be formatted separately,
2104 leading to varying column widths across the row groups. The
2105 other output formats work better.
2106 HIDE_TABLEAM
2108 If this variable is set to true, a table's access method
2109 details are not displayed. This is mainly useful for regression
2110 tests.
2111 HIDE_TOAST_COMPRESSION
2113 If this variable is set to true, column compression method
2114 details are not displayed. This is mainly useful for regression
2115 tests.
2116 HISTCONTROL
2118 If this variable is set to ignorespace, lines which begin with
2119 a space are not entered into the history list. If set to a
2120 value of ignoredups, lines matching the previous history line
2121 are not entered. A value of ignoreboth combines the two
2122 options. If set to none (the default), all lines read in
2123 interactive mode are saved on the history list.
2124 Note
2126 This feature was shamelessly plagiarized from Bash.
2127 HISTFILE
2129 The file name that will be used to store the history list. If
2130 unset, the file name is taken from the PSQL_HISTORY environment
2131 variable. If that is not set either, the default is
2132 ~/.psql_history, or %APPDATA%\postgresql\psql_history on
2133 Windows. For example, putting:
2134 \set HISTFILE ~/.psql_history-:DBNAME
2136 in ~/.psqlrc will cause psql to maintain a separate history for
2138 each database.
2139 Note
2141 This feature was shamelessly plagiarized from Bash.
2142 HISTSIZE
2144 The maximum number of commands to store in the command history
2145 (default 500). If set to a negative value, no limit is applied.
2146 Note
2148 This feature was shamelessly plagiarized from Bash.
2149 HOST
2151 The database server host you are currently connected to. This
2152 is set every time you connect to a database (including program
2153 start-up), but can be changed or unset.
2154 IGNOREEOF
2156 If set to 1 or less, sending an EOF character (usually
2157 Control+D) to an interactive session of psql will terminate the
2158 application. If set to a larger numeric value, that many
2159 consecutive EOF characters must be typed to make an interactive
2160 session terminate. If the variable is set to a non-numeric
2161 value, it is interpreted as 10. The default is 0.
2162 Note
2164 This feature was shamelessly plagiarized from Bash.
2165 LASTOID
2167 The value of the last affected OID, as returned from an INSERT
2168 or \lo_import command. This variable is only guaranteed to be
2169 valid until after the result of the next SQL command has been
2170 displayed. PostgreSQL servers since version 12 do not support
2171 OID system columns anymore, thus LASTOID will always be 0
2172 following INSERT when targeting such servers.
2173 LAST_ERROR_MESSAGE
2175 LAST_ERROR_SQLSTATE
2176 The primary error message and associated SQLSTATE code for the
2177 most recent failed query in the current psql session, or an
2178 empty string and 00000 if no error has occurred in the current
2179 session.
2180 ON_ERROR_ROLLBACK
2182 When set to on, if a statement in a transaction block generates
2183 an error, the error is ignored and the transaction continues.
2184 When set to interactive, such errors are only ignored in
2185 interactive sessions, and not when reading script files. When
2186 set to off (the default), a statement in a transaction block
2187 that generates an error aborts the entire transaction. The
2188 error rollback mode works by issuing an implicit SAVEPOINT for
2189 you, just before each command that is in a transaction block,
2190 and then rolling back to the savepoint if the command fails.
2191 ON_ERROR_STOP
2193 By default, command processing continues after an error. When
2194 this variable is set to on, processing will instead stop
2195 immediately. In interactive mode, psql will return to the
2196 command prompt; otherwise, psql will exit, returning error code
2197 3 to distinguish this case from fatal error conditions, which
2198 are reported using error code 1. In either case, any currently
2199 running scripts (the top-level script, if any, and any other
2200 scripts which it may have in invoked) will be terminated
2201 immediately. If the top-level command string contained multiple
2202 SQL commands, processing will stop with the current command.
2203 PORT
2205 The database server port to which you are currently connected.
2206 This is set every time you connect to a database (including
2207 program start-up), but can be changed or unset.
2208 PROMPT1
2210 PROMPT2
2211 PROMPT3
2212 These specify what the prompts psql issues should look like.
2213 See Prompting below.
2214 QUIET
2216 Setting this variable to on is equivalent to the command line
2217 option -q. It is probably not too useful in interactive mode.
2218 ROW_COUNT
2220 The number of rows returned or affected by the last SQL query,
2221 or 0 if the query failed or did not report a row count.
2222 SERVER_VERSION_NAME
2224 SERVER_VERSION_NUM
2225 The server's version number as a string, for example 9.6.2,
2226 10.1 or 11beta1, and in numeric form, for example 90602 or
2227 100001. These are set every time you connect to a database
2228 (including program start-up), but can be changed or unset.
2229 SHELL_ERROR
2231 true if the last shell command failed, false if it succeeded.
2232 This applies to shell commands invoked via the \!, \g, \o, \w,
2233 and \copy meta-commands, as well as backquote (`) expansion.
2234 Note that for \o, this variable is updated when the output pipe
2235 is closed by the next \o command. See also SHELL_EXIT_CODE.
2236 SHELL_EXIT_CODE
2238 The exit status returned by the last shell command. 0–127
2239 represent program exit codes, 128–255 indicate termination by a
2240 signal, and -1 indicates failure to launch a program or to
2241 collect its exit status. This applies to shell commands invoked
2242 via the \!, \g, \o, \w, and \copy meta-commands, as well as
2243 backquote (`) expansion. Note that for \o, this variable is
2244 updated when the output pipe is closed by the next \o command.
2245 See also SHELL_ERROR.
2246 SHOW_ALL_RESULTS
2248 When this variable is set to off, only the last result of a
2249 combined query (\;) is shown instead of all of them. The
2250 default is on. The off behavior is for compatibility with older
2251 versions of psql.
2252 SHOW_CONTEXT
2254 This variable can be set to the values never, errors, or always
2255 to control whether CONTEXT fields are displayed in messages
2256 from the server. The default is errors (meaning that context
2257 will be shown in error messages, but not in notice or warning
2258 messages). This setting has no effect when VERBOSITY is set to
2259 terse or sqlstate. (See also \errverbose, for use when you want
2260 a verbose version of the error you just got.)
2261 SINGLELINE
2263 Setting this variable to on is equivalent to the command line
2264 option -S.
2265 SINGLESTEP
2267 Setting this variable to on is equivalent to the command line
2268 option -s.
2269 SQLSTATE
2271 The error code (see Appendix A) associated with the last SQL
2272 query's failure, or 00000 if it succeeded.
2273 USER
2275 The database user you are currently connected as. This is set
2276 every time you connect to a database (including program
2277 start-up), but can be changed or unset.
2278 VERBOSITY
2280 This variable can be set to the values default, verbose, terse,
2281 or sqlstate to control the verbosity of error reports. (See
2282 also \errverbose, for use when you want a verbose version of
2283 the error you just got.)
2284 VERSION
2286 VERSION_NAME
2287 VERSION_NUM
2288 These variables are set at program start-up to reflect psql's
2289 version, respectively as a verbose string, a short string
2290 (e.g., 9.6.2, 10.1, or 11beta1), and a number (e.g., 90602 or
2291 100001). They can be changed or unset.
2292 SQL Interpolation
2294 A key feature of psql variables is that you can substitute
2295 (“interpolate”) them into regular SQL statements, as well as the
2296 arguments of meta-commands. Furthermore, psql provides facilities
2297 for ensuring that variable values used as SQL literals and
2298 identifiers are properly quoted. The syntax for interpolating a
2299 value without any quoting is to prepend the variable name with a
2300 colon (:). For example,
2301 testdb=> \set foo 'my_table'
2303 testdb=> SELECT * FROM :foo;
2304 would query the table my_table. Note that this may be unsafe: the
2306 value of the variable is copied literally, so it can contain
2307 unbalanced quotes, or even backslash commands. You must make sure
2308 that it makes sense where you put it.
2309 When a value is to be used as an SQL literal or identifier, it is
2311 safest to arrange for it to be quoted. To quote the value of a
2312 variable as an SQL literal, write a colon followed by the variable
2313 name in single quotes. To quote the value as an SQL identifier,
2314 write a colon followed by the variable name in double quotes. These
2315 constructs deal correctly with quotes and other special characters
2316 embedded within the variable value. The previous example would be
2317 more safely written this way:
2318 testdb=> \set foo 'my_table'
2320 testdb=> SELECT * FROM :"foo";
2321 Variable interpolation will not be performed within quoted SQL
2323 literals and identifiers. Therefore, a construction such as ':foo'
2324 doesn't work to produce a quoted literal from a variable's value
2325 (and it would be unsafe if it did work, since it wouldn't correctly
2326 handle quotes embedded in the value).
2327 One example use of this mechanism is to copy the contents of a file
2329 into a table column. First load the file into a variable and then
2330 interpolate the variable's value as a quoted string:
2331 testdb=> \set content `cat my_file.txt`
2333 testdb=> INSERT INTO my_table VALUES (:'content');
2334 (Note that this still won't work if my_file.txt contains NUL bytes.
2336 psql does not support embedded NUL bytes in variable values.)
2337 Since colons can legally appear in SQL commands, an apparent
2339 attempt at interpolation (that is, :name, :'name', or :"name") is
2340 not replaced unless the named variable is currently set. In any
2341 case, you can escape a colon with a backslash to protect it from
2342 substitution.
2343 The :{?name} special syntax returns TRUE or FALSE depending on
2345 whether the variable exists or not, and is thus always substituted,
2346 unless the colon is backslash-escaped.
2347 The colon syntax for variables is standard SQL for embedded query
2349 languages, such as ECPG. The colon syntaxes for array slices and
2350 type casts are PostgreSQL extensions, which can sometimes conflict
2351 with the standard usage. The colon-quote syntax for escaping a
2352 variable's value as an SQL literal or identifier is a psql
2353 extension.
2354 Prompting
2356 The prompts psql issues can be customized to your preference. The
2357 three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and
2358 special escape sequences that describe the appearance of the
2359 prompt. Prompt 1 is the normal prompt that is issued when psql
2360 requests a new command. Prompt 2 is issued when more input is
2361 expected during command entry, for example because the command was
2362 not terminated with a semicolon or a quote was not closed. Prompt 3
2363 is issued when you are running an SQL COPY FROM STDIN command and
2364 you need to type in a row value on the terminal.
2365 The value of the selected prompt variable is printed literally,
2367 except where a percent sign (%) is encountered. Depending on the
2368 next character, certain other text is substituted instead. Defined
2369 substitutions are:
2370 %M
2372 The full host name (with domain name) of the database server,
2373 or [local] if the connection is over a Unix domain socket, or
2374 [local:/dir/name], if the Unix domain socket is not at the
2375 compiled in default location.
2376 %m
2378 The host name of the database server, truncated at the first
2379 dot, or [local] if the connection is over a Unix domain socket.
2380 %>
2382 The port number at which the database server is listening.
2383 %n
2385 The database session user name. (The expansion of this value
2386 might change during a database session as the result of the
2387 command SET SESSION AUTHORIZATION.)
2388 %/
2390 The name of the current database.
2391 %~
2393 Like %/, but the output is ~ (tilde) if the database is your
2394 default database.
2395 %#
2397 If the session user is a database superuser, then a #,
2398 otherwise a >. (The expansion of this value might change during
2399 a database session as the result of the command SET SESSION
2400 AUTHORIZATION.)
2401 %p
2403 The process ID of the backend currently connected to.
2404 %R
2406 In prompt 1 normally =, but @ if the session is in an inactive
2407 branch of a conditional block, or ^ if in single-line mode, or
2408 ! if the session is disconnected from the database (which can
2409 happen if \connect fails). In prompt 2 %R is replaced by a
2410 character that depends on why psql expects more input: - if the
2411 command simply wasn't terminated yet, but * if there is an
2412 unfinished /* ... */ comment, a single quote if there is an
2413 unfinished quoted string, a double quote if there is an
2414 unfinished quoted identifier, a dollar sign if there is an
2415 unfinished dollar-quoted string, or ( if there is an unmatched
2416 left parenthesis. In prompt 3 %R doesn't produce anything.
2417 %x
2419 Transaction status: an empty string when not in a transaction
2420 block, or * when in a transaction block, or ! when in a failed
2421 transaction block, or ? when the transaction state is
2422 indeterminate (for example, because there is no connection).
2423 %l
2425 The line number inside the current statement, starting from 1.
2426 %digits
2428 The character with the indicated octal code is substituted.
2429 %:name:
2431 The value of the psql variable name. See Variables, above, for
2432 details.
2433 %`command`
2435 The output of command, similar to ordinary “back-tick”
2436 substitution.
2437 %[ ... %]
2439 Prompts can contain terminal control characters which, for
2440 example, change the color, background, or style of the prompt
2441 text, or change the title of the terminal window. In order for
2442 the line editing features of Readline to work properly, these
2443 non-printing control characters must be designated as invisible
2444 by surrounding them with %[ and %]. Multiple pairs of these can
2445 occur within the prompt. For example:
2446 testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
2448 results in a boldfaced (1;) yellow-on-black (33;40) prompt on
2450 VT100-compatible, color-capable terminals.
2451 %w
2453 Whitespace of the same width as the most recent output of
2454 PROMPT1. This can be used as a PROMPT2 setting, so that
2455 multi-line statements are aligned with the first line, but
2456 there is no visible secondary prompt.
2457 To insert a percent sign into your prompt, write %%. The default
2458 prompts are '%/%R%x%# ' for prompts 1 and 2, and '>> ' for prompt
2459 3.
2460 Note
2462 This feature was shamelessly plagiarized from tcsh.
2463 Command-Line Editing
2465 psql uses the Readline or libedit library, if available, for
2466 convenient line editing and retrieval. The command history is
2467 automatically saved when psql exits and is reloaded when psql
2468 starts up. Type up-arrow or control-P to retrieve previous lines.
2469 You can also use tab completion to fill in partially-typed keywords
2471 and SQL object names in many (by no means all) contexts. For
2472 example, at the start of a command, typing ins and pressing TAB
2473 will fill in insert into. Then, typing a few characters of a table
2474 or schema name and pressing TAB will fill in the unfinished name,
2475 or offer a menu of possible completions when there's more than one.
2476 (Depending on the library in use, you may need to press TAB more
2477 than once to get a menu.)
2478 Tab completion for SQL object names requires sending queries to the
2480 server to find possible matches. In some contexts this can
2481 interfere with other operations. For example, after BEGIN it will
2482 be too late to issue SET TRANSACTION ISOLATION LEVEL if a
2483 tab-completion query is issued in between. If you do not want tab
2484 completion at all, you can turn it off permanently by putting this
2485 in a file named .inputrc in your home directory:
2486 $if psql
2488 set disable-completion on
2489 $endif
2490 (This is not a psql but a Readline feature. Read its documentation
2492 for further details.)
2493 The -n (--no-readline) command line option can also be useful to
2495 disable use of Readline for a single run of psql. This prevents tab
2496 completion, use or recording of command line history, and editing
2497 of multi-line commands. It is particularly useful when you need to
2498 copy-and-paste text that contains TAB characters.
2499
2501 COLUMNS
2502 If \pset columns is zero, controls the width for the wrapped format
2503 and width for determining if wide output requires the pager or
2504 should be switched to the vertical format in expanded auto mode.
2505 PGDATABASE
2507 PGHOST
2508 PGPORT
2509 PGUSER
2510 Default connection parameters (see Section 34.15).
2511 PG_COLOR
2513 Specifies whether to use color in diagnostic messages. Possible
2514 values are always, auto and never.
2515 PSQL_EDITOR
2517 EDITOR
2518 VISUAL
2519 Editor used by the \e, \ef, and \ev commands. These variables are
2520 examined in the order listed; the first that is set is used. If
2521 none of them is set, the default is to use vi on Unix systems or
2522 notepad.exe on Windows systems.
2523 PSQL_EDITOR_LINENUMBER_ARG
2525 When \e, \ef, or \ev is used with a line number argument, this
2526 variable specifies the command-line argument used to pass the
2527 starting line number to the user's editor. For editors such as
2528 Emacs or vi, this is a plus sign. Include a trailing space in the
2529 value of the variable if there needs to be space between the option
2530 name and the line number. Examples:
2531 PSQL_EDITOR_LINENUMBER_ARG='+'
2533 PSQL_EDITOR_LINENUMBER_ARG='--line '
2534 The default is + on Unix systems (corresponding to the default
2536 editor vi, and useful for many other common editors); but there is
2537 no default on Windows systems.
2538 PSQL_HISTORY
2540 Alternative location for the command history file. Tilde (~)
2541 expansion is performed.
2542 PSQL_PAGER
2544 PAGER
2545 If a query's results do not fit on the screen, they are piped
2546 through this command. Typical values are more or less. Use of the
2547 pager can be disabled by setting PSQL_PAGER or PAGER to an empty
2548 string, or by adjusting the pager-related options of the \pset
2549 command. These variables are examined in the order listed; the
2550 first that is set is used. If neither of them is set, the default
2551 is to use more on most platforms, but less on Cygwin.
2552 PSQL_WATCH_PAGER
2554 When a query is executed repeatedly with the \watch command, a
2555 pager is not used by default. This behavior can be changed by
2556 setting PSQL_WATCH_PAGER to a pager command, on Unix systems. The
2557 pspg pager (not part of PostgreSQL but available in many open
2558 source software distributions) can display the output of \watch if
2559 started with the option --stream.
2560 PSQLRC
2562 Alternative location of the user's .psqlrc file. Tilde (~)
2563 expansion is performed.
2564 SHELL
2566 Command executed by the \! command.
2567 TMPDIR
2569 Directory for storing temporary files. The default is /tmp.
2570 This utility, like most other PostgreSQL utilities, also uses the
2572 environment variables supported by libpq (see Section 34.15).
2573
2575 psqlrc and ~/.psqlrc
2576 Unless it is passed an -X option, psql attempts to read and execute
2577 commands from the system-wide startup file (psqlrc) and then the
2578 user's personal startup file (~/.psqlrc), after connecting to the
2579 database but before accepting normal commands. These files can be
2580 used to set up the client and/or the server to taste, typically
2581 with \set and SET commands.
2582 The system-wide startup file is named psqlrc. By default it is
2584 sought in the installation's “system configuration” directory,
2585 which is most reliably identified by running pg_config
2586 --sysconfdir. Typically this directory will be ../etc/ relative to
2587 the directory containing the PostgreSQL executables. The directory
2588 to look in can be set explicitly via the PGSYSCONFDIR environment
2589 variable.
2590 The user's personal startup file is named .psqlrc and is sought in
2592 the invoking user's home directory. On Windows the personal startup
2593 file is instead named %APPDATA%\postgresql\psqlrc.conf. In either
2594 case, this default file path can be overridden by setting the
2595 PSQLRC environment variable.
2596 Both the system-wide startup file and the user's personal startup
2598 file can be made psql-version-specific by appending a dash and the
2599 PostgreSQL major or minor release identifier to the file name, for
2600 example ~/.psqlrc-16 or ~/.psqlrc-16.1. The most specific
2601 version-matching file will be read in preference to a
2602 non-version-specific file. These version suffixes are added after
2603 determining the file path as explained above.
2604 .psql_history
2606 The command-line history is stored in the file ~/.psql_history, or
2607 %APPDATA%\postgresql\psql_history on Windows.
2608 The location of the history file can be set explicitly via the
2610 HISTFILE psql variable or the PSQL_HISTORY environment variable.
2611
2613 • psql works best with servers of the same or an older major version.
2614 Backslash commands are particularly likely to fail if the server is
2615 of a newer version than psql itself. However, backslash commands of
2616 the \d family should work with servers of versions back to 9.2,
2617 though not necessarily with servers newer than psql itself. The
2618 general functionality of running SQL commands and displaying query
2619 results should also work with servers of a newer major version, but
2620 this cannot be guaranteed in all cases.
2621 If you want to use psql to connect to several servers of different
2623 major versions, it is recommended that you use the newest version
2624 of psql. Alternatively, you can keep around a copy of psql from
2625 each major version and be sure to use the version that matches the
2626 respective server. But in practice, this additional complication
2627 should not be necessary.
2628 • Before PostgreSQL 9.6, the -c option implied -X (--no-psqlrc); this
2630 is no longer the case.
2631 • Before PostgreSQL 8.4, psql allowed the first argument of a
2633 single-letter backslash command to start directly after the
2634 command, without intervening whitespace. Now, some whitespace is
2635 required.
2636
2638 psql is built as a “console application”. Since the Windows console
2639 windows use a different encoding than the rest of the system, you must
2640 take special care when using 8-bit characters within psql. If psql
2641 detects a problematic console code page, it will warn you at startup.
2642 To change the console code page, two things are necessary:
2643 • Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
2645 page that is appropriate for German; replace it with your value.)
2646 If you are using Cygwin, you can put this command in /etc/profile.
2647 • Set the console font to Lucida Console, because the raster font
2649 does not work with the ANSI code page.
2650
2652 The first example shows how to spread a command over several lines of
2653 input. Notice the changing prompt:
2654 testdb=> CREATE TABLE my_table (
2656 testdb(> first integer not null default 0,
2657 testdb(> second text)
2658 testdb-> ;
2659 CREATE TABLE
2660 Now look at the table definition again:
2662 testdb=> \d my_table
2664 Table "public.my_table"
2665 Column | Type | Collation | Nullable | Default
2666 --------+---------+-----------+----------+---------
2667 first | integer | | not null | 0
2668 second | text | | |
2669 Now we change the prompt to something more interesting:
2671 testdb=> \set PROMPT1 '%n@%m %~%R%# '
2673 peter@localhost testdb=>
2674 Let's assume you have filled the table with data and want to take a
2676 look at it:
2677 peter@localhost testdb=> SELECT * FROM my_table;
2679 first | second
2680 -------+--------
2681 1 | one
2682 2 | two
2683 3 | three
2684 4 | four
2685 (4 rows)
2686 You can display tables in different ways by using the \pset command:
2688 peter@localhost testdb=> \pset border 2
2690 Border style is 2.
2691 peter@localhost testdb=> SELECT * FROM my_table;
2692 +-------+--------+
2693 | first | second |
2694 +-------+--------+
2695 | 1 | one |
2696 | 2 | two |
2697 | 3 | three |
2698 | 4 | four |
2699 +-------+--------+
2700 (4 rows)
2701 peter@localhost testdb=> \pset border 0
2703 Border style is 0.
2704 peter@localhost testdb=> SELECT * FROM my_table;
2705 first second
2706 ----- ------
2707 1 one
2708 2 two
2709 3 three
2710 4 four
2711 (4 rows)
2712 peter@localhost testdb=> \pset border 1
2714 Border style is 1.
2715 peter@localhost testdb=> \pset format csv
2716 Output format is csv.
2717 peter@localhost testdb=> \pset tuples_only
2718 Tuples only is on.
2719 peter@localhost testdb=> SELECT second, first FROM my_table;
2720 one,1
2721 two,2
2722 three,3
2723 four,4
2724 peter@localhost testdb=> \pset format unaligned
2725 Output format is unaligned.
2726 peter@localhost testdb=> \pset fieldsep '\t'
2727 Field separator is " ".
2728 peter@localhost testdb=> SELECT second, first FROM my_table;
2729 one 1
2730 two 2
2731 three 3
2732 four 4
2733 Alternatively, use the short commands:
2735 peter@localhost testdb=> \a \t \x
2737 Output format is aligned.
2738 Tuples only is off.
2739 Expanded display is on.
2740 peter@localhost testdb=> SELECT * FROM my_table;
2741 -[ RECORD 1 ]-
2742 first | 1
2743 second | one
2744 -[ RECORD 2 ]-
2745 first | 2
2746 second | two
2747 -[ RECORD 3 ]-
2748 first | 3
2749 second | three
2750 -[ RECORD 4 ]-
2751 first | 4
2752 second | four
2753 Also, these output format options can be set for just one query by
2755 using \g:
2756 peter@localhost testdb=> SELECT * FROM my_table
2758 peter@localhost testdb-> \g (format=aligned tuples_only=off expanded=on)
2759 -[ RECORD 1 ]-
2760 first | 1
2761 second | one
2762 -[ RECORD 2 ]-
2763 first | 2
2764 second | two
2765 -[ RECORD 3 ]-
2766 first | 3
2767 second | three
2768 -[ RECORD 4 ]-
2769 first | 4
2770 second | four
2771 Here is an example of using the \df command to find only functions with
2773 names matching int*pl and whose second argument is of type bigint:
2774 testdb=> \df int*pl * bigint
2776 List of functions
2777 Schema | Name | Result data type | Argument data types | Type
2778 ------------+---------+------------------+---------------------+------
2779 pg_catalog | int28pl | bigint | smallint, bigint | func
2780 pg_catalog | int48pl | bigint | integer, bigint | func
2781 pg_catalog | int8pl | bigint | bigint, bigint | func
2782 (3 rows)
2783 When suitable, query results can be shown in a crosstab representation
2785 with the \crosstabview command:
2786 testdb=> SELECT first, second, first > 2 AS gt2 FROM my_table;
2788 first | second | gt2
2789 -------+--------+-----
2790 1 | one | f
2791 2 | two | f
2792 3 | three | t
2793 4 | four | t
2794 (4 rows)
2795 testdb=> \crosstabview first second
2797 first | one | two | three | four
2798 -------+-----+-----+-------+------
2799 1 | f | | |
2800 2 | | f | |
2801 3 | | | t |
2802 4 | | | | t
2803 (4 rows)
2804 This second example shows a multiplication table with rows sorted in
2806 reverse numerical order and columns with an independent, ascending
2807 numerical order.
2808 testdb=> SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB",
2810 testdb(> row_number() over(order by t2.first) AS ord
2811 testdb(> FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC
2812 testdb(> \crosstabview "A" "B" "AxB" ord
2813 A | 101 | 102 | 103 | 104
2814 ---+-----+-----+-----+-----
2815 4 | 404 | 408 | 412 | 416
2816 3 | 303 | 306 | 309 | 312
2817 2 | 202 | 204 | 206 | 208
2818 1 | 101 | 102 | 103 | 104
2819 (4 rows)
2820PostgreSQL 16.1 2023 PSQL(1)