1PSQL(1) PostgreSQL 9.2.24 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. In addition, it
15 provides a number of meta-commands and various shell-like features to
16 facilitate writing scripts and automating a wide variety of tasks.
17
19 -a, --echo-all
20 Print all nonempty input lines to standard output as they are read.
21 (This does not apply to lines read interactively.) This is
22 equivalent to setting the variable ECHO to all.
23 -A, --no-align
25 Switches to unaligned output mode. (The default output mode is
26 otherwise aligned.)
27 -c command, --command=command
29 Specifies that psql is to execute one command string, command, and
30 then exit. This is useful in shell scripts. Start-up files (psqlrc
31 and ~/.psqlrc) are ignored with this option.
32 command must be either a command string that is completely parsable
34 by the server (i.e., it contains no psql-specific features), or a
35 single backslash command. Thus you cannot mix SQL and psql
36 meta-commands with this option. To achieve that, you could pipe the
37 string into psql, for example: echo '\x \\ SELECT * FROM foo;' |
38 psql. (\\ is the separator meta-command.)
39 If the command string contains multiple SQL commands, they are
41 processed in a single transaction, unless there are explicit
42 BEGIN/COMMIT commands included in the string to divide it into
43 multiple transactions. This is different from the behavior when the
44 same string is fed to psql's standard input. Also, only the result
45 of the last SQL command is returned.
46 Because of these legacy behaviors, putting more than one command in
48 the -c string often has unexpected results. It's better to feed
49 multiple commands to psql's standard input, either using echo as
50 illustrated above, or via a shell here-document, for example:
51 psql <<EOF
53 \x
54 SELECT * FROM foo;
55 EOF
56 -d dbname, --dbname=dbname
59 Specifies the name of the database to connect to. This is
60 equivalent to specifying dbname as the first non-option argument on
61 the command line.
62 If this parameter contains an = sign or starts with a valid URI
64 prefix (postgresql:// or postgres://), it is treated as a conninfo
65 string. See Section 31.1, “Database Connection Control Functions”,
66 in the documentation for more information.
67 -e, --echo-queries
69 Copy all SQL commands sent to the server to standard output as
70 well. This is equivalent to setting the variable ECHO to queries.
71 -E, --echo-hidden
73 Echo the actual queries generated by \d and other backslash
74 commands. You can use this to study psql's internal operations.
75 This is equivalent to setting the variable ECHO_HIDDEN to on.
76 -f filename, --file=filename
78 Use the file filename as the source of commands instead of reading
79 commands interactively. After the file is processed, psql
80 terminates. This is in many ways equivalent to the meta-command \i.
81 If filename is - (hyphen), then standard input is read until an EOF
83 indication or \q meta-command. Note however that Readline is not
84 used in this case (much as if -n had been specified).
85 Using this option is subtly different from writing psql < filename.
87 In general, both will do what you expect, but using -f enables some
88 nice features such as error messages with line numbers. There is
89 also a slight chance that using this option will reduce the
90 start-up overhead. On the other hand, the variant using the shell's
91 input redirection is (in theory) guaranteed to yield exactly the
92 same output you would have received had you entered everything by
93 hand.
94 -F separator, --field-separator=separator
96 Use separator as the field separator for unaligned output. This is
97 equivalent to \pset fieldsep or \f.
98 -h hostname, --host=hostname
100 Specifies the host name of the machine on which the server is
101 running. If the value begins with a slash, it is used as the
102 directory for the Unix-domain socket.
103 -H, --html
105 Turn on HTML tabular output. This is equivalent to \pset format
106 html or the \H command.
107 -l, --list
109 List all available databases, then exit. Other non-connection
110 options are ignored. This is similar to the meta-command \list.
111 -L filename, --log-file=filename
113 Write all query output into file filename, in addition to the
114 normal output destination.
115 -n, --no-readline
117 Do not use Readline for line editing and do not use the command
118 history. This can be useful to turn off tab expansion when cutting
119 and pasting.
120 -o filename, --output=filename
122 Put all query output into file filename. This is equivalent to the
123 command \o.
124 -p port, --port=port
126 Specifies the TCP port or the local Unix-domain socket file
127 extension on which the server is listening for connections.
128 Defaults to the value of the PGPORT environment variable or, if not
129 set, to the port specified at compile time, usually 5432.
130 -P assignment, --pset=assignment
132 Specifies printing options, in the style of \pset. Note that here
133 you have to separate name and value with an equal sign instead of a
134 space. For example, to set the output format to LaTeX, you could
135 write -P format=latex.
136 -q, --quiet
138 Specifies that psql should do its work quietly. By default, it
139 prints welcome messages and various informational output. If this
140 option is used, none of this happens. This is useful with the -c
141 option. This is equivalent to setting the variable QUIET to on.
142 -R separator, --record-separator=separator
144 Use separator as the record separator for unaligned output. This is
145 equivalent to the \pset recordsep command.
146 -s, --single-step
148 Run in single-step mode. That means the user is prompted before
149 each command is sent to the server, with the option to cancel
150 execution as well. Use this to debug scripts.
151 -S, --single-line
153 Runs in single-line mode where a newline terminates an SQL command,
154 as a semicolon does.
155 Note
157 This mode is provided for those who insist on it, but you are
158 not necessarily encouraged to use it. In particular, if you mix
159 SQL and meta-commands on a line the order of execution might
160 not always be clear to the inexperienced user.
161 -t, --tuples-only
163 Turn off printing of column names and result row count footers,
164 etc. This is equivalent to the \t command.
165 -T table_options, --table-attr=table_options
167 Specifies options to be placed within the HTMLtable tag. See \pset
168 for details.
169 -U username, --username=username
171 Connect to the database as the user username instead of the
172 default. (You must have permission to do so, of course.)
173 -v assignment, --set=assignment, --variable=assignment
175 Perform a variable assignment, like the \set meta-command. Note
176 that you must separate name and value, if any, by an equal sign on
177 the command line. To unset a variable, leave off the equal sign. To
178 set a variable with an empty value, use the equal sign but leave
179 off the value. These assignments are done during a very early stage
180 of start-up, so variables reserved for internal purposes might get
181 overwritten later.
182 -V, --version
184 Print the psql version and exit.
185 -w, --no-password
187 Never issue a password prompt. If the server requires password
188 authentication and a password is not available by other means such
189 as a .pgpass file, the connection attempt will fail. This option
190 can be useful in batch jobs and scripts where no user is present to
191 enter a password.
192 Note that this option will remain set for the entire session, and
194 so it affects uses of the meta-command \connect as well as the
195 initial connection attempt.
196 -W, --password
198 Force psql to prompt for a password before connecting to a
199 database.
200 This option is never essential, since psql will automatically
202 prompt for a password if the server demands password
203 authentication. However, psql will waste a connection attempt
204 finding out that the server wants a password. In some cases it is
205 worth typing -W to avoid the extra connection attempt.
206 Note that this option will remain set for the entire session, and
208 so it affects uses of the meta-command \connect as well as the
209 initial connection attempt.
210 -x, --expanded
212 Turn on the expanded table formatting mode. This is equivalent to
213 the \x command.
214 -X,, --no-psqlrc
216 Do not read the start-up file (neither the system-wide psqlrc file
217 nor the user's ~/.psqlrc file).
218 -z, --field-separator-zero
220 Set the field separator for unaligned output to a zero byte.
221 -0, --record-separator-zero
223 Set the record separator for unaligned output to a zero byte. This
224 is useful for interfacing, for example, with xargs -0.
225 -1, --single-transaction
227 When psql executes a script with the -f option, adding this option
228 wraps BEGIN/COMMIT around the script to execute it as a single
229 transaction. This ensures that either all the commands complete
230 successfully, or no changes are applied.
231 If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option
233 will not have the desired effects. Also, if the script contains any
234 command that cannot be executed inside a transaction block,
235 specifying this option will cause that command (and hence the whole
236 transaction) to fail.
237 -?, --help
239 Show help about psql command line arguments, and exit.
240
242 psql returns 0 to the shell if it finished normally, 1 if a fatal error
243 of its own occurs (e.g. out of memory, file not found), 2 if the
244 connection to the server went bad and the session was not interactive,
245 and 3 if an error occurred in a script and the variable ON_ERROR_STOP
246 was set.
247
249 Connecting to a Database
250 psql is a regular PostgreSQL client application. In order to connect to
251 a database you need to know the name of your target database, the host
252 name and port number of the server, and what user name you want to
253 connect as. psql can be told about those parameters via command line
254 options, namely -d, -h, -p, and -U respectively. If an argument is
255 found that does not belong to any option it will be interpreted as the
256 database name (or the user name, if the database name is already
257 given). Not all of these options are required; there are useful
258 defaults. If you omit the host name, psql will connect via a
259 Unix-domain socket to a server on the local host, or via TCP/IP to
260 localhost on machines that don't have Unix-domain sockets. The default
261 port number is determined at compile time. Since the database server
262 uses the same default, you will not have to specify the port in most
263 cases. The default user name is your operating-system user name, as is
264 the default database name. Note that you cannot just connect to any
265 database under any user name. Your database administrator should have
266 informed you about your access rights.
267 When the defaults aren't quite right, you can save yourself some typing
269 by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or
270 PGUSER to appropriate values. (For additional environment variables,
271 see Section 31.14, “Environment Variables”, in the documentation.) It
272 is also convenient to have a ~/.pgpass file to avoid regularly having
273 to type in passwords. See Section 31.15, “The Password File”, in the
274 documentation for more information.
275 An alternative way to specify connection parameters is in a conninfo
277 string or a URI, which is used instead of a database name. This
278 mechanism give you very wide control over the connection. For example:
279 $ psql "service=myservice sslmode=require"
281 $ psql postgresql://dbmaster:5433/mydb?sslmode=require
282 This way you can also use LDAP for connection parameter lookup as
284 described in Section 31.17, “LDAP Lookup of Connection Parameters”, in
285 the documentation. See Section 31.1, “Database Connection Control
286 Functions”, in the documentation for more information on all the
287 available connection options.
288 If the connection could not be made for any reason (e.g., insufficient
290 privileges, server is not running on the targeted host, etc.), psql
291 will return an error and terminate.
292 If at least one of standard input or standard output are a terminal,
294 then psql sets the client encoding to “auto”, which will detect the
295 appropriate client encoding from the locale settings (LC_CTYPE
296 environment variable on Unix systems). If this doesn't work out as
297 expected, the client encoding can be overridden using the environment
298 variable PGCLIENTENCODING.
299 Entering SQL Commands
301 In normal operation, psql provides a prompt with the name of the
302 database to which psql is currently connected, followed by the string
303 =>. For example:
304 $ psql testdb
306 psql (9.2.24)
307 Type "help" for help.
308 testdb=>
310 At the prompt, the user can type in SQL commands. Ordinarily, input
312 lines are sent to the server when a command-terminating semicolon is
313 reached. An end of line does not terminate a command. Thus commands can
314 be spread over several lines for clarity. If the command was sent and
315 executed without error, the results of the command are displayed on the
316 screen.
317 Whenever a command is executed, psql also polls for asynchronous
319 notification events generated by LISTEN(7) and NOTIFY(7).
320 Meta-Commands
322 Anything you enter in psql that begins with an unquoted backslash is a
323 psql meta-command that is processed by psql itself. These commands make
324 psql more useful for administration or scripting. Meta-commands are
325 often called slash or backslash commands.
326 The format of a psql command is the backslash, followed immediately by
328 a command verb, then any arguments. The arguments are separated from
329 the command verb and each other by any number of whitespace characters.
330 To include whitespace in an argument you can quote it with single
332 quotes. To include a single quote in an argument, write two single
333 quotes within single-quoted text. Anything contained in single quotes
334 is furthermore subject to C-like substitutions for \n (new line), \t
335 (tab), \b (backspace), \r (carriage return), \f (form feed), \digits
336 (octal), and \xdigits (hexadecimal). A backslash preceding any other
337 character within single-quoted text quotes that single character,
338 whatever it is.
339 Within an argument, text that is enclosed in backquotes (`) is taken as
341 a command line that is passed to the shell. The output of the command
342 (with any trailing newline removed) replaces the backquoted text.
343 If an unquoted colon (:) followed by a psql variable name appears
345 within an argument, it is replaced by the variable's value, as
346 described in SQL Interpolation.
347 Some commands take an SQL identifier (such as a table name) as
349 argument. These arguments follow the syntax rules of SQL: Unquoted
350 letters are forced to lowercase, while double quotes (") protect
351 letters from case conversion and allow incorporation of whitespace into
352 the identifier. Within double quotes, paired double quotes reduce to a
353 single double quote in the resulting name. For example, FOO"BAR"BAZ is
354 interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.
355 Parsing for arguments stops at the end of the line, or when another
357 unquoted backslash is found. An unquoted backslash is taken as the
358 beginning of a new meta-command. The special sequence \\ (two
359 backslashes) marks the end of arguments and continues parsing SQL
360 commands, if any. That way SQL and psql commands can be freely mixed on
361 a line. But in any case, the arguments of a meta-command cannot
362 continue beyond the end of the line.
363 The following meta-commands are defined:
365 \a
367 If the current table output format is unaligned, it is switched to
368 aligned. If it is not unaligned, it is set to unaligned. This
369 command is kept for backwards compatibility. See \pset for a more
370 general solution.
371 \c or \connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host
373 ] [ port ] | conninfo ]
374 Establishes a new connection to a PostgreSQL server. The connection
375 parameters to use can be specified either using a positional
376 syntax, or using conninfo connection strings as detailed in Section
377 31.1.1, “Connection Strings”, in the documentation.
378 Where the command omits database name, user, host, or port, the new
380 connection can reuse values from the previous connection. By
381 default, values from the previous connection are reused except when
382 processing a conninfo string. Passing a first argument of
383 -reuse-previous=on or -reuse-previous=off overrides that default.
384 When the command neither specifies nor reuses a particular
385 parameter, the libpq default is used. Specifying any of dbname,
386 username, host or port as - is equivalent to omitting that
387 parameter.
388 If the new connection is successfully made, the previous connection
390 is closed. If the connection attempt failed (wrong user name,
391 access denied, etc.), the previous connection will only be kept if
392 psql is in interactive mode. When executing a non-interactive
393 script, processing will immediately stop with an error. This
394 distinction was chosen as a user convenience against typos on the
395 one hand, and a safety mechanism that scripts are not accidentally
396 acting on the wrong database on the other hand.
397 Examples:
399 => \c mydb myuser host.dom 6432
401 => \c service=foo
402 => \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
403 => \c postgresql://tom@localhost/mydb?application_name=myapp
404 \C [ title ]
406 Sets the title of any tables being printed as the result of a query
407 or unset any such title. This command is equivalent to \pset title
408 title. (The name of this command derives from “caption”, as it was
409 previously only used to set the caption in an HTML table.)
410 \cd [ directory ]
412 Changes the current working directory to directory. Without
413 argument, changes to the current user's home directory.
414 Tip
416 To print your current working directory, use \! pwd.
417 \conninfo
419 Outputs information about the current database connection.
420 \copy { table [ ( column_list ) ] | ( query ) } { from | to } {
422 filename | stdin | stdout | pstdin | pstdout } [ with ] [ binary ] [
423 oids ] [ delimiter [ as ] 'character' ] [ null [ as ] 'string' ] [ csv
424 [ header ] [ quote [ as ] 'character' ] [ escape [ as ] 'character' ] [
425 force quote column_list | * ] [ force not null column_list ] ]
426 Performs a frontend (client) copy. This is an operation that runs
427 an SQLCOPY(7) command, but instead of the server reading or writing
428 the specified file, psql reads or writes the file and routes the
429 data between the server and the local file system. This means that
430 file accessibility and privileges are those of the local user, not
431 the server, and no SQL superuser privileges are required.
432 The syntax of the command is similar to that of the SQLCOPY(7)
434 command. Note that, because of this, special parsing rules apply to
435 the \copy command. In particular, the variable substitution rules
436 and backslash escapes do not apply.
437 \copy ... from stdin | to stdout reads/writes based on the command
439 input and output respectively. All rows are read from the same
440 source that issued the command, continuing until \. is read or the
441 stream reaches EOF. Output is sent to the same place as command
442 output. To read/write from psql's standard input or output, use
443 pstdin or pstdout. This option is useful for populating tables
444 in-line within a SQL script file.
445 Tip
447 This operation is not as efficient as the SQLCOPY command
448 because all data must pass through the client/server
449 connection. For large amounts of data the SQL command might be
450 preferable.
451 \copyright
453 Shows the copyright and distribution terms of PostgreSQL.
454 \d[S+] [ pattern ]
456 For each relation (table, view, index, sequence, or foreign table)
457 or composite type matching the pattern, show all columns, their
458 types, the tablespace (if not the default) and any special
459 attributes such as NOT NULL or defaults. Associated indexes,
460 constraints, rules, and triggers are also shown. For foreign
461 tables, the associated foreign server is shown as well. (“Matching
462 the pattern” is defined in Patterns below.)
463 For some types of relation, \d shows additional information for
465 each column: column values for sequences, indexed expression for
466 indexes and foreign data wrapper options for foreign tables.
467 The command form \d+ is identical, except that more information is
469 displayed: any comments associated with the columns of the table
470 are shown, as is the presence of OIDs in the table, the view
471 definition if the relation is a view.
472 By default, only user-created objects are shown; supply a pattern
474 or the S modifier to include system objects.
475 Note
477 If \d is used without a pattern argument, it is equivalent to
478 \dtvsE which will show a list of all visible tables, views,
479 sequences and foreign tables. This is purely a convenience
480 measure.
481 \da[S] [ pattern ]
483 Lists aggregate functions, together with their return type and the
484 data types they operate on. If pattern is specified, only
485 aggregates whose names match the pattern are shown. By default,
486 only user-created objects are shown; supply a pattern or the S
487 modifier to include system objects.
488 \db[+] [ pattern ]
490 Lists tablespaces. If pattern is specified, only tablespaces whose
491 names match the pattern are shown. If + is appended to the command
492 name, each object is listed with its associated permissions.
493 \dc[S+] [ pattern ]
495 Lists conversions between character-set encodings. If pattern is
496 specified, only conversions whose names match the pattern are
497 listed. By default, only user-created objects are shown; supply a
498 pattern or the S modifier to include system objects. If + is
499 appended to the command name, each object is listed with its
500 associated description.
501 \dC[+] [ pattern ]
503 Lists type casts. If pattern is specified, only casts whose source
504 or target types match the pattern are listed. If + is appended to
505 the command name, each object is listed with its associated
506 description.
507 \dd[S] [ pattern ]
509 Shows the descriptions of objects of type constraint, operator
510 class, operator family, rule, and trigger. All other comments may
511 be viewed by the respective backslash commands for those object
512 types.
513 \dd displays descriptions for objects matching the pattern, or of
515 visible objects of the appropriate type if no argument is given.
516 But in either case, only objects that have a description are
517 listed. By default, only user-created objects are shown; supply a
518 pattern or the S modifier to include system objects.
519 Descriptions for objects can be created with the COMMENT(7)SQL
521 command.
522 \ddp [ pattern ]
524 Lists default access privilege settings. An entry is shown for each
525 role (and schema, if applicable) for which the default privilege
526 settings have been changed from the built-in defaults. If pattern
527 is specified, only entries whose role name or schema name matches
528 the pattern are listed.
529 The ALTER DEFAULT PRIVILEGES (ALTER_DEFAULT_PRIVILEGES(7)) command
531 is used to set default access privileges. The meaning of the
532 privilege display is explained under GRANT(7).
533 \dD[S+] [ pattern ]
535 Lists domains. If pattern is specified, only domains whose names
536 match the pattern are shown. By default, only user-created objects
537 are shown; supply a pattern or the S modifier to include system
538 objects. If + is appended to the command name, each object is
539 listed with its associated permissions and description.
540 \dE[S+] [ pattern ], \di[S+] [ pattern ], \ds[S+] [ pattern ], \dt[S+]
542 [ pattern ], \dv[S+] [ pattern ]
543 In this group of commands, the letters E, i, s, t, and v stand for
544 foreign table, index, sequence, table, and view, respectively. You
545 can specify any or all of these letters, in any order, to obtain a
546 listing of objects of these types. For example, \dit lists indexes
547 and tables. If + is appended to the command name, each object is
548 listed with its physical size on disk and its associated
549 description, if any. If pattern is specified, only objects whose
550 names match the pattern are listed. By default, only user-created
551 objects are shown; supply a pattern or the S modifier to include
552 system objects.
553 \des[+] [ pattern ]
555 Lists foreign servers (mnemonic: “external servers”). If pattern is
556 specified, only those servers whose name matches the pattern are
557 listed. If the form \des+ is used, a full description of each
558 server is shown, including the server's ACL, type, version,
559 options, and description.
560 \det[+] [ pattern ]
562 Lists foreign tables (mnemonic: “external tables”). If pattern is
563 specified, only entries whose table name or schema name matches the
564 pattern are listed. If the form \det+ is used, generic options and
565 the foreign table description are also displayed.
566 \deu[+] [ pattern ]
568 Lists user mappings (mnemonic: “external users”). If pattern is
569 specified, only those mappings whose user names match the pattern
570 are listed. If the form \deu+ is used, additional information about
571 each mapping is shown.
572 Caution
574 \deu+ might also display the user name and password of the
575 remote user, so care should be taken not to disclose them.
576 \dew[+] [ pattern ]
578 Lists foreign-data wrappers (mnemonic: “external wrappers”). If
579 pattern is specified, only those foreign-data wrappers whose name
580 matches the pattern are listed. If the form \dew+ is used, the ACL,
581 options, and description of the foreign-data wrapper are also
582 shown.
583 \df[antwS+] [ pattern ]
585 Lists functions, together with their arguments, return types, and
586 function types, which are classified as “agg” (aggregate),
587 “normal”, “trigger”, or “window”. To display only functions of
588 specific type(s), add the corresponding letters a, n, t, or w to
589 the command. If pattern is specified, only functions whose names
590 match the pattern are shown. If the form \df+ is used, additional
591 information about each function, including volatility, language,
592 source code and description, is shown. By default, only
593 user-created objects are shown; supply a pattern or the S modifier
594 to include system objects.
595 Tip
597 To look up functions taking arguments or returning values of a
598 specific type, use your pager's search capability to scroll
599 through the \df output.
600 \dF[+] [ pattern ]
602 Lists text search configurations. If pattern is specified, only
603 configurations whose names match the pattern are shown. If the form
604 \dF+ is used, a full description of each configuration is shown,
605 including the underlying text search parser and the dictionary list
606 for each parser token type.
607 \dFd[+] [ pattern ]
609 Lists text search dictionaries. If pattern is specified, only
610 dictionaries whose names match the pattern are shown. If the form
611 \dFd+ is used, additional information is shown about each selected
612 dictionary, including the underlying text search template and the
613 option values.
614 \dFp[+] [ pattern ]
616 Lists text search parsers. If pattern is specified, only parsers
617 whose names match the pattern are shown. If the form \dFp+ is used,
618 a full description of each parser is shown, including the
619 underlying functions and the list of recognized token types.
620 \dFt[+] [ pattern ]
622 Lists text search templates. If pattern is specified, only
623 templates whose names match the pattern are shown. If the form
624 \dFt+ is used, additional information is shown about each template,
625 including the underlying function names.
626 \dg[+] [ pattern ]
628 Lists database roles. (Since the concepts of “users” and “groups”
629 have been unified into “roles”, this command is now equivalent to
630 \du.) If pattern is specified, only those roles whose names match
631 the pattern are listed. If the form \dg+ is used, additional
632 information is shown about each role; currently this adds the
633 comment for each role.
634 \dl
636 This is an alias for \lo_list, which shows a list of large objects.
637 \dL[S+] [ pattern ]
639 Lists procedural languages. If pattern is specified, only languages
640 whose names match the pattern are listed. By default, only
641 user-created languages are shown; supply the S modifier to include
642 system objects. If + is appended to the command name, each language
643 is listed with its call handler, validator, access privileges, and
644 whether it is a system object.
645 \dn[S+] [ pattern ]
647 Lists schemas (namespaces). If pattern is specified, only schemas
648 whose names match the pattern are listed. By default, only
649 user-created objects are shown; supply a pattern or the S modifier
650 to include system objects. If + is appended to the command name,
651 each object is listed with its associated permissions and
652 description, if any.
653 \do[S] [ pattern ]
655 Lists operators with their operand and return types. If pattern is
656 specified, only operators whose names match the pattern are listed.
657 By default, only user-created objects are shown; supply a pattern
658 or the S modifier to include system objects.
659 \dO[S+] [ pattern ]
661 Lists collations. If pattern is specified, only collations whose
662 names match the pattern are listed. By default, only user-created
663 objects are shown; supply a pattern or the S modifier to include
664 system objects. If + is appended to the command name, each
665 collation is listed with its associated description, if any. Note
666 that only collations usable with the current database's encoding
667 are shown, so the results may vary in different databases of the
668 same installation.
669 \dp [ pattern ]
671 Lists tables, views and sequences with their associated access
672 privileges. If pattern is specified, only tables, views and
673 sequences whose names match the pattern are listed.
674 The GRANT(7) and REVOKE(7) commands are used to set access
676 privileges. The meaning of the privilege display is explained under
677 GRANT(7).
678 \drds [ role-pattern [ database-pattern ] ]
680 Lists defined configuration settings. These settings can be
681 role-specific, database-specific, or both. role-pattern and
682 database-pattern are used to select specific roles and databases to
683 list, respectively. If omitted, or if * is specified, all settings
684 are listed, including those not role-specific or database-specific,
685 respectively.
686 The ALTER ROLE (ALTER_ROLE(7)) and ALTER DATABASE
688 (ALTER_DATABASE(7)) commands are used to define per-role and
689 per-database configuration settings.
690 \dT[S+] [ pattern ]
692 Lists data types. If pattern is specified, only types whose names
693 match the pattern are listed. If + is appended to the command name,
694 each type is listed with its internal name and size, its allowed
695 values if it is an enum type, and its associated permissions. By
696 default, only user-created objects are shown; supply a pattern or
697 the S modifier to include system objects.
698 \du[+] [ pattern ]
700 Lists database roles. (Since the concepts of “users” and “groups”
701 have been unified into “roles”, this command is now equivalent to
702 \dg.) If pattern is specified, only those roles whose names match
703 the pattern are listed. If the form \du+ is used, additional
704 information is shown about each role; currently this adds the
705 comment for each role.
706 \dx[+] [ pattern ]
708 Lists installed extensions. If pattern is specified, only those
709 extensions whose names match the pattern are listed. If the form
710 \dx+ is used, all the objects belonging to each matching extension
711 are listed.
712 \e or \edit [ filename ] [ line_number ]
714 If filename is specified, the file is edited; after the editor
715 exits, its content is copied back to the query buffer. If no
716 filename is given, the current query buffer is copied to a
717 temporary file which is then edited in the same fashion.
718 The new query buffer is then re-parsed according to the normal
720 rules of psql, where the whole buffer is treated as a single line.
721 (Thus you cannot make scripts this way. Use \i for that.) This
722 means that if the query ends with (or contains) a semicolon, it is
723 immediately executed. Otherwise it will merely wait in the query
724 buffer; type semicolon or \g to send it, or \r to cancel.
725 If a line number is specified, psql will position the cursor on the
727 specified line of the file or query buffer. Note that if a single
728 all-digits argument is given, psql assumes it is a line number, not
729 a file name.
730 Tip
732 See under ENVIRONMENT for how to configure and customize your
733 editor.
734 \echo text [ ... ]
736 Prints the arguments to the standard output, separated by one space
737 and followed by a newline. This can be useful to intersperse
738 information in the output of scripts. For example:
739 => \echo `date`
741 Tue Oct 26 21:40:57 CEST 1999
742 If the first argument is an unquoted -n the trailing newline is not
744 written.
745 Tip
747 If you use the \o command to redirect your query output you
748 might wish to use \qecho instead of this command.
749 \ef [ function_description [ line_number ] ]
751 This command fetches and edits the definition of the named
752 function, in the form of a CREATE OR REPLACE FUNCTION command.
753 Editing is done in the same way as for \edit. After the editor
754 exits, the updated command waits in the query buffer; type
755 semicolon or \g to send it, or \r to cancel.
756 The target function can be specified by name alone, or by name and
758 arguments, for example foo(integer, text). The argument types must
759 be given if there is more than one function of the same name.
760 If no function is specified, a blank CREATE FUNCTION template is
762 presented for editing.
763 If a line number is specified, psql will position the cursor on the
765 specified line of the function body. (Note that the function body
766 typically does not begin on the first line of the file.)
767 Tip
769 See under ENVIRONMENT for how to configure and customize your
770 editor.
771 \encoding [ encoding ]
773 Sets the client character set encoding. Without an argument, this
774 command shows the current encoding.
775 \f [ string ]
777 Sets the field separator for unaligned query output. The default is
778 the vertical bar (|). See also \pset for a generic way of setting
779 output options.
780 \g [ filename ], \g [ |command ]
782 Sends the current query input buffer to the server, and optionally
783 stores the query's output in filename or pipes the output to the
784 shell command command. A bare \g is virtually equivalent to a
785 semicolon. A \g with argument is a “one-shot” alternative to the \o
786 command.
787 \h or \help [ command ]
789 Gives syntax help on the specified SQL command. If command is not
790 specified, then psql will list all the commands for which syntax
791 help is available. If command is an asterisk (*), then syntax help
792 on all SQL commands is shown.
793 Note
795 To simplify typing, commands that consists of several words do
796 not have to be quoted. Thus it is fine to type \help alter
797 table.
798 \H or \html
800 Turns on HTML query output format. If the HTML format is already
801 on, it is switched back to the default aligned text format. This
802 command is for compatibility and convenience, but see \pset about
803 setting other output options.
804 \i or \include filename
806 Reads input from the file filename and executes it as though it had
807 been typed on the keyboard.
808 If filename is - (hyphen), then standard input is read until an EOF
810 indication or \q meta-command. This can be used to intersperse
811 interactive input with input from files. Note that Readline
812 behavior will be used only if it is active at the outermost level.
813 Note
815 If you want to see the lines on the screen as they are read you
816 must set the variable ECHO to all.
817 \ir or \include_relative filename
819 The \ir command is similar to \i, but resolves relative file names
820 differently. When executing in interactive mode, the two commands
821 behave identically. However, when invoked from a script, \ir
822 interprets file names relative to the directory in which the script
823 is located, rather than the current working directory.
824 \l (or \list), \l+ (or \list+)
826 List the names, owners, character set encodings, and access
827 privileges of all the databases in the server. If + is appended to
828 the command name, database sizes, default tablespaces, and
829 descriptions are also displayed. (Size information is only
830 available for databases that the current user can connect to.)
831 \lo_export loid filename
833 Reads the large object with OIDloid from the database and writes it
834 to filename. Note that this is subtly different from the server
835 function lo_export, which acts with the permissions of the user
836 that the database server runs as and on the server's file system.
837 Tip
839 Use \lo_list to find out the large object's OID.
840 \lo_import filename [ comment ]
842 Stores the file into a PostgreSQL large object. Optionally, it
843 associates the given comment with the object. Example:
844 foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
846 lo_import 152801
847 The response indicates that the large object received object ID
849 152801, which can be used to access the newly-created large object
850 in the future. For the sake of readability, it is recommended to
851 always associate a human-readable comment with every object. Both
852 OIDs and comments can be viewed with the \lo_list command.
853 Note that this command is subtly different from the server-side
855 lo_import because it acts as the local user on the local file
856 system, rather than the server's user and file system.
857 \lo_list
859 Shows a list of all PostgreSQL large objects currently stored in
860 the database, along with any comments provided for them.
861 \lo_unlink loid
863 Deletes the large object with OIDloid from the database.
864 Tip
866 Use \lo_list to find out the large object's OID.
867 \o or \out [ filename ], \o or \out [ |command ]
869 Arranges to save future query results to the file filename or pipe
870 future results to the shell command command. If no argument is
871 specified, the query output is reset to the standard output.
872 “Query results” includes all tables, command responses, and notices
874 obtained from the database server, as well as output of various
875 backslash commands that query the database (such as \d), but not
876 error messages.
877 Tip
879 To intersperse text output in between query results, use
880 \qecho.
881 \p or \print
883 Print the current query buffer to the standard output.
884 \password [ username ]
886 Changes the password of the specified user (by default, the current
887 user). This command prompts for the new password, encrypts it, and
888 sends it to the server as an ALTER ROLE command. This makes sure
889 that the new password does not appear in cleartext in the command
890 history, the server log, or elsewhere.
891 \prompt [ text ] name
893 Prompts the user to supply text, which is assigned to the variable
894 name. An optional prompt string, text, can be specified. (For
895 multiword prompts, surround the text with single quotes.)
896 By default, \prompt uses the terminal for input and output.
898 However, if the -f command line switch was used, \prompt uses
899 standard input and standard output.
900 \pset option [ value ]
902 This command sets options affecting the output of query result
903 tables. option indicates which option is to be set. The semantics
904 of value vary depending on the selected option. For some options,
905 omitting value causes the option to be toggled or unset, as
906 described under the particular option. If no such behavior is
907 mentioned, then omitting value just results in the current setting
908 being displayed.
909 Adjustable printing options are:
911 border
913 The value must be a number. In general, the higher the number
914 the more borders and lines the tables will have, but this
915 depends on the particular format. In HTML format, this will
916 translate directly into the border=... attribute; in the other
917 formats only values 0 (no border), 1 (internal dividing lines),
918 and 2 (table frame) make sense.
919 columns
921 Sets the target width for the wrapped format, and also the
922 width limit for determining whether output is wide enough to
923 require the pager or switch to the vertical display in expanded
924 auto mode. Zero (the default) causes the target width to be
925 controlled by the environment variable COLUMNS, or the detected
926 screen width if COLUMNS is not set. In addition, if columns is
927 zero then the wrapped format only affects screen output. If
928 columns is nonzero then file and pipe output is wrapped to that
929 width as well.
930 expanded (or x)
932 If value is specified it must be either on or off, which will
933 enable or disable expanded mode, or auto. If value is omitted
934 the command toggles between the on and off settings. When
935 expanded mode is enabled, query results are displayed in two
936 columns, with the column name on the left and the data on the
937 right. This mode is useful if the data wouldn't fit on the
938 screen in the normal “horizontal” mode. In the auto setting,
939 the expanded mode is used whenever the query output is wider
940 than the screen, otherwise the regular mode is used. The auto
941 setting is only effective in the aligned and wrapped formats.
942 In other formats, it always behaves as if the expanded mode is
943 off.
944 fieldsep
946 Specifies the field separator to be used in unaligned output
947 format. That way one can create, for example, tab- or
948 comma-separated output, which other programs might prefer. To
949 set a tab as field separator, type \pset fieldsep '\t'. The
950 default field separator is '|' (a vertical bar).
951 fieldsep_zero
953 Sets the field separator to use in unaligned output format to a
954 zero byte.
955 footer
957 If value is specified it must be either on or off which will
958 enable or disable display of the table footer (the (n rows)
959 count). If value is omitted the command toggles footer display
960 on or off.
961 format
963 Sets the output format to one of unaligned, aligned, wrapped,
964 html, latex, or troff-ms. Unique abbreviations are allowed.
965 (That would mean one letter is enough.)
966 unaligned format writes all columns of a row on one line,
968 separated by the currently active field separator. This is
969 useful for creating output that might be intended to be read in
970 by other programs (for example, tab-separated or
971 comma-separated format).
972 aligned format is the standard, human-readable, nicely
974 formatted text output; this is the default.
975 wrapped format is like aligned but wraps wide data values
977 across lines to make the output fit in the target column width.
978 The target width is determined as described under the columns
979 option. Note that psql will not attempt to wrap column header
980 titles; therefore, wrapped format behaves the same as aligned
981 if the total width needed for column headers exceeds the
982 target.
983 The html, latex, and troff-ms formats put out tables that are
985 intended to be included in documents using the respective
986 mark-up language. They are not complete documents! (This might
987 not be so dramatic in HTML, but in LaTeX you must have a
988 complete document wrapper.)
989 linestyle
991 Sets the border line drawing style to one of ascii, old-ascii
992 or unicode. Unique abbreviations are allowed. (That would mean
993 one letter is enough.) The default setting is ascii. This
994 option only affects the aligned and wrapped output formats.
995 ascii style uses plain ASCII characters. Newlines in data are
997 shown using a + symbol in the right-hand margin. When the
998 wrapped format wraps data from one line to the next without a
999 newline character, a dot (.) is shown in the right-hand margin
1000 of the first line, and again in the left-hand margin of the
1001 following line.
1002 old-ascii style uses plain ASCII characters, using the
1004 formatting style used in PostgreSQL 8.4 and earlier. Newlines
1005 in data are shown using a : symbol in place of the left-hand
1006 column separator. When the data is wrapped from one line to the
1007 next without a newline character, a ; symbol is used in place
1008 of the left-hand column separator.
1009 unicode style uses Unicode box-drawing characters. Newlines in
1011 data are shown using a carriage return symbol in the right-hand
1012 margin. When the data is wrapped from one line to the next
1013 without a newline character, an ellipsis symbol is shown in the
1014 right-hand margin of the first line, and again in the left-hand
1015 margin of the following line.
1016 When the border setting is greater than zero, this option also
1018 determines the characters with which the border lines are
1019 drawn. Plain ASCII characters work everywhere, but Unicode
1020 characters look nicer on displays that recognize them.
1021 null
1023 Sets the string to be printed in place of a null value. The
1024 default is to print nothing, which can easily be mistaken for
1025 an empty string. For example, one might prefer \pset null
1026 '(null)'.
1027 numericlocale
1029 If value is specified it must be either on or off which will
1030 enable or disable display of a locale-specific character to
1031 separate groups of digits to the left of the decimal marker. If
1032 value is omitted the command toggles between regular and
1033 locale-specific numeric output.
1034 pager
1036 Controls use of a pager program for query and psql help output.
1037 If the environment variable PAGER is set, the output is piped
1038 to the specified program. Otherwise a platform-dependent
1039 default (such as more) is used.
1040 When the pager option is off, the pager program is not used.
1042 When the pager option is on, the pager is used when
1043 appropriate, i.e., when the output is to a terminal and will
1044 not fit on the screen. The pager option can also be set to
1045 always, which causes the pager to be used for all terminal
1046 output regardless of whether it fits on the screen. \pset
1047 pager without a value toggles pager use on and off.
1048 recordsep
1050 Specifies the record (line) separator to use in unaligned
1051 output format. The default is a newline character.
1052 recordsep_zero
1054 Sets the record separator to use in unaligned output format to
1055 a zero byte.
1056 tableattr (or T)
1058 Specifies attributes to be placed inside the HTMLtable tag in
1059 html output format. This could for example be cellpadding or
1060 bgcolor. Note that you probably don't want to specify border
1061 here, as that is already taken care of by \pset border. If no
1062 value is given, the table attributes are unset.
1063 title
1065 Sets the table title for any subsequently printed tables. This
1066 can be used to give your output descriptive tags. If no value
1067 is given, the title is unset.
1068 tuples_only (or t)
1070 If value is specified it must be either on or off which will
1071 enable or disable tuples-only mode. If value is omitted the
1072 command toggles between regular and tuples-only output. Regular
1073 output includes extra information such as column headers,
1074 titles, and various footers. In tuples-only mode, only actual
1075 table data is shown.
1076 Illustrations of how these different formats look can be seen in
1078 the EXAMPLES section.
1079 Tip
1081 There are various shortcut commands for \pset. See \a, \C, \H,
1082 \t, \T, and \x.
1083 Note
1085 It is an error to call \pset without any arguments. In the
1086 future this case might show the current status of all printing
1087 options.
1088 \q or \quit
1090 Quits the psql program. In a script file, only execution of that
1091 script is terminated.
1092 \qecho text [ ... ]
1094 This command is identical to \echo except that the output will be
1095 written to the query output channel, as set by \o.
1096 \r or \reset
1098 Resets (clears) the query buffer.
1099 \s [ filename ]
1101 Print psql's command line history to filename. If filename is
1102 omitted, the history is written to the standard output (using the
1103 pager if appropriate). This command is not available if psql was
1104 built without Readline support.
1105 \set [ name [ value [ ... ] ] ]
1107 Sets the psql variable name to value, or if more than one value is
1108 given, to the concatenation of all of them. If only one argument is
1109 given, the variable is set with an empty value. To unset a
1110 variable, use the \unset command.
1111 \set without any arguments displays the names and values of all
1113 currently-set psql variables.
1114 Valid variable names can contain letters, digits, and underscores.
1116 See the section Variables below for details. Variable names are
1117 case-sensitive.
1118 Although you are welcome to set any variable to anything you want,
1120 psql treats several variables as special. They are documented in
1121 the section about variables.
1122 Note
1124 This command is unrelated to the SQL command SET(7).
1125 \setenv name [ value ]
1127 Sets the environment variable name to value, or if the value is not
1128 supplied, unsets the environment variable. Example:
1129 testdb=> \setenv PAGER less
1131 testdb=> \setenv LESS -imx4F
1132 \sf[+] function_description
1134 This command fetches and shows the definition of the named
1135 function, in the form of a CREATE OR REPLACE FUNCTION command. The
1136 definition is printed to the current query output channel, as set
1137 by \o.
1138 The target function can be specified by name alone, or by name and
1140 arguments, for example foo(integer, text). The argument types must
1141 be given if there is more than one function of the same name.
1142 If + is appended to the command name, then the output lines are
1144 numbered, with the first line of the function body being line 1.
1145 \t
1147 Toggles the display of output column name headings and row count
1148 footer. This command is equivalent to \pset tuples_only and is
1149 provided for convenience.
1150 \T table_options
1152 Specifies attributes to be placed within the table tag in HTML
1153 output format. This command is equivalent to \pset tableattr
1154 table_options.
1155 \timing [ on | off ]
1157 Without parameter, toggles a display of how long each SQL statement
1158 takes, in milliseconds. With parameter, sets same.
1159 \unset name
1161 Unsets (deletes) the psql variable name.
1162 \w or \write filename, \w or \write |command
1164 Outputs the current query buffer to the file filename or pipes it
1165 to the shell command command.
1166 \x [ on | off | auto ]
1168 Sets or toggles expanded table formatting mode. As such it is
1169 equivalent to \pset expanded.
1170 \z [ pattern ]
1172 Lists tables, views and sequences with their associated access
1173 privileges. If a pattern is specified, only tables, views and
1174 sequences whose names match the pattern are listed.
1175 This is an alias for \dp (“display privileges”).
1177 \! [ command ]
1179 Escapes to a separate shell or executes the shell command command.
1180 The arguments are not further interpreted; the shell will see them
1181 as-is.
1182 \?
1184 Shows help information about the backslash commands.
1185 Patterns
1187 The various \d commands accept a pattern parameter to specify the
1188 object name(s) to be displayed. In the simplest case, a pattern is
1189 just the exact name of the object. The characters within a pattern
1190 are normally folded to lower case, just as in SQL names; for
1191 example, \dt FOO will display the table named foo. As in SQL names,
1192 placing double quotes around a pattern stops folding to lower case.
1193 Should you need to include an actual double quote character in a
1194 pattern, write it as a pair of double quotes within a double-quote
1195 sequence; again this is in accord with the rules for SQL quoted
1196 identifiers. For example, \dt "FOO""BAR" will display the table
1197 named FOO"BAR (not foo"bar). Unlike the normal rules for SQL names,
1198 you can put double quotes around just part of a pattern, for
1199 instance \dt FOO"FOO"BAR will display the table named fooFOObar.
1200 Whenever the pattern parameter is omitted completely, the \d
1202 commands display all objects that are visible in the current schema
1203 search path — this is equivalent to using * as the pattern. (An
1204 object is said to be visible if its containing schema is in the
1205 search path and no object of the same kind and name appears earlier
1206 in the search path. This is equivalent to the statement that the
1207 object can be referenced by name without explicit schema
1208 qualification.) To see all objects in the database regardless of
1209 visibility, use *.* as the pattern.
1210 Within a pattern, * matches any sequence of characters (including
1212 no characters) and ? matches any single character. (This notation
1213 is comparable to Unix shell file name patterns.) For example, \dt
1214 int* displays tables whose names begin with int. But within double
1215 quotes, * and ? lose these special meanings and are just matched
1216 literally.
1217 A pattern that contains a dot (.) is interpreted as a schema name
1219 pattern followed by an object name pattern. For example, \dt
1220 foo*.*bar* displays all tables whose table name includes bar that
1221 are in schemas whose schema name starts with foo. When no dot
1222 appears, then the pattern matches only objects that are visible in
1223 the current schema search path. Again, a dot within double quotes
1224 loses its special meaning and is matched literally.
1225 Advanced users can use regular-expression notations such as
1227 character classes, for example [0-9] to match any digit. All
1228 regular expression special characters work as specified in Section
1229 9.7.3, “POSIX Regular Expressions”, in the documentation, except
1230 for . which is taken as a separator as mentioned above, * which is
1231 translated to the regular-expression notation .*, ? which is
1232 translated to ., and $ which is matched literally. You can emulate
1233 these pattern characters at need by writing ? for ., (R+|) for R*,
1234 or (R|) for R?. $ is not needed as a regular-expression character
1235 since the pattern must match the whole name, unlike the usual
1236 interpretation of regular expressions (in other words, $ is
1237 automatically appended to your pattern). Write * at the beginning
1238 and/or end if you don't wish the pattern to be anchored. Note that
1239 within double quotes, all regular expression special characters
1240 lose their special meanings and are matched literally. Also, the
1241 regular expression special characters are matched literally in
1242 operator name patterns (i.e., the argument of \do).
1243 Advanced Features
1245 Variables
1246 psql provides variable substitution features similar to common Unix
1247 command shells. Variables are simply name/value pairs, where the
1248 value can be any string of any length. The name must consist of
1249 letters (including non-Latin letters), digits, and underscores.
1250 To set a variable, use the psql meta-command \set. For example,
1252 testdb=> \set foo bar
1254 sets the variable foo to the value bar. To retrieve the content of
1256 the variable, precede the name with a colon, for example:
1257 testdb=> \echo :foo
1259 bar
1260 This works in both regular SQL commands and meta-commands; there is
1262 more detail in SQL Interpolation, below.
1263 If you call \set without a second argument, the variable is set,
1265 with an empty string as value. To unset (i.e., delete) a variable,
1266 use the command \unset. To show the values of all variables, call
1267 \set without any argument.
1268 Note
1270 The arguments of \set are subject to the same substitution
1271 rules as with other commands. Thus you can construct
1272 interesting references such as \set :foo 'something' and get
1273 “soft links” or “variable variables” of Perl or PHP fame,
1274 respectively. Unfortunately (or fortunately?), there is no way
1275 to do anything useful with these constructs. On the other hand,
1276 \set bar :foo is a perfectly valid way to copy a variable.
1277 A number of these variables are treated specially by psql. They
1279 represent certain option settings that can be changed at run time
1280 by altering the value of the variable, or in some cases represent
1281 changeable state of psql. Although you can use these variables for
1282 other purposes, this is not recommended, as the program behavior
1283 might grow really strange really quickly. By convention, all
1284 specially treated variables' names consist of all upper-case ASCII
1285 letters (and possibly digits and underscores). To ensure maximum
1286 compatibility in the future, avoid using such variable names for
1287 your own purposes. A list of all specially treated variables
1288 follows.
1289 AUTOCOMMIT
1291 When on (the default), each SQL command is automatically
1292 committed upon successful completion. To postpone commit in
1293 this mode, you must enter a BEGIN or START TRANSACTION SQL
1294 command. When off or unset, SQL commands are not committed
1295 until you explicitly issue COMMIT or END. The autocommit-off
1296 mode works by issuing an implicit BEGIN for you, just before
1297 any command that is not already in a transaction block and is
1298 not itself a BEGIN or other transaction-control command, nor a
1299 command that cannot be executed inside a transaction block
1300 (such as VACUUM).
1301 Note
1303 In autocommit-off mode, you must explicitly abandon any
1304 failed transaction by entering ABORT or ROLLBACK. Also keep
1305 in mind that if you exit the session without committing,
1306 your work will be lost.
1307 Note
1309 The autocommit-on mode is PostgreSQL's traditional
1310 behavior, but autocommit-off is closer to the SQL spec. If
1311 you prefer autocommit-off, you might wish to set it in the
1312 system-wide psqlrc file or your ~/.psqlrc file.
1313 COMP_KEYWORD_CASE
1315 Determines which letter case to use when completing an SQL key
1316 word. If set to lower or upper, the completed word will be in
1317 lower or upper case, respectively. If set to preserve-lower or
1318 preserve-upper (the default), the completed word will be in the
1319 case of the word already entered, but words being completed
1320 without anything entered will be in lower or upper case,
1321 respectively.
1322 DBNAME
1324 The name of the database you are currently connected to. This
1325 is set every time you connect to a database (including program
1326 start-up), but can be unset.
1327 ECHO
1329 If set to all, all nonempty input lines are printed to standard
1330 output as they are read. (This does not apply to lines read
1331 interactively.) To select this behavior on program start-up,
1332 use the switch -a. If set to queries, psql prints each query to
1333 standard output as it is sent to the server. The switch for
1334 this is -e.
1335 ECHO_HIDDEN
1337 When this variable is set to on and a backslash command queries
1338 the database, the query is first shown. This feature helps you
1339 to study PostgreSQL internals and provide similar functionality
1340 in your own programs. (To select this behavior on program
1341 start-up, use the switch -E.) If you set the variable to the
1342 value noexec, the queries are just shown but are not actually
1343 sent to the server and executed.
1344 ENCODING
1346 The current client character set encoding.
1347 FETCH_COUNT
1349 If this variable is set to an integer value > 0, the results of
1350 SELECT queries are fetched and displayed in groups of that many
1351 rows, rather than the default behavior of collecting the entire
1352 result set before display. Therefore only a limited amount of
1353 memory is used, regardless of the size of the result set.
1354 Settings of 100 to 1000 are commonly used when enabling this
1355 feature. Keep in mind that when using this feature, a query
1356 might fail after having already displayed some rows.
1357 Tip
1359 Although you can use any output format with this feature,
1360 the default aligned format tends to look bad because each
1361 group of FETCH_COUNT rows will be formatted separately,
1362 leading to varying column widths across the row groups. The
1363 other output formats work better.
1364 HISTCONTROL
1366 If this variable is set to ignorespace, lines which begin with
1367 a space are not entered into the history list. If set to a
1368 value of ignoredups, lines matching the previous history line
1369 are not entered. A value of ignoreboth combines the two
1370 options. If unset, or if set to any other value than those
1371 above, all lines read in interactive mode are saved on the
1372 history list.
1373 Note
1375 This feature was shamelessly plagiarized from Bash.
1376 HISTFILE
1378 The file name that will be used to store the history list. The
1379 default value is ~/.psql_history. For example, putting:
1380 \set HISTFILE ~/.psql_history- :DBNAME
1382 in ~/.psqlrc will cause psql to maintain a separate history for
1384 each database.
1385 Note
1387 This feature was shamelessly plagiarized from Bash.
1388 HISTSIZE
1390 The number of commands to store in the command history. The
1391 default value is 500.
1392 Note
1394 This feature was shamelessly plagiarized from Bash.
1395 HOST
1397 The database server host you are currently connected to. This
1398 is set every time you connect to a database (including program
1399 start-up), but can be unset.
1400 IGNOREEOF
1402 If unset, sending an EOF character (usually Control+D) to an
1403 interactive session of psql will terminate the application. If
1404 set to a numeric value, that many EOF characters are ignored
1405 before the application terminates. If the variable is set but
1406 has no numeric value, the default is 10.
1407 Note
1409 This feature was shamelessly plagiarized from Bash.
1410 LASTOID
1412 The value of the last affected OID, as returned from an INSERT
1413 or \lo_import command. This variable is only guaranteed to be
1414 valid until after the result of the next SQL command has been
1415 displayed.
1416 ON_ERROR_ROLLBACK
1418 When set to on, if a statement in a transaction block generates
1419 an error, the error is ignored and the transaction continues.
1420 When set to interactive, such errors are only ignored in
1421 interactive sessions, and not when reading script files. When
1422 unset or set to off, a statement in a transaction block that
1423 generates an error aborts the entire transaction. The error
1424 rollback mode works by issuing an implicit SAVEPOINT for you,
1425 just before each command that is in a transaction block, and
1426 then rolling back to the savepoint if the command fails.
1427 ON_ERROR_STOP
1429 By default, command processing continues after an error. When
1430 this variable is set to on, processing will instead stop
1431 immediately. In interactive mode, psql will return to the
1432 command prompt; otherwise, psql will exit, returning error code
1433 3 to distinguish this case from fatal error conditions, which
1434 are reported using error code 1. In either case, any currently
1435 running scripts (the top-level script, if any, and any other
1436 scripts which it may have in invoked) will be terminated
1437 immediately. If the top-level command string contained multiple
1438 SQL commands, processing will stop with the current command.
1439 PORT
1441 The database server port to which you are currently connected.
1442 This is set every time you connect to a database (including
1443 program start-up), but can be unset.
1444 PROMPT1, PROMPT2, PROMPT3
1446 These specify what the prompts psql issues should look like.
1447 See Prompting below.
1448 QUIET
1450 Setting this variable to on is equivalent to the command line
1451 option -q. It is probably not too useful in interactive mode.
1452 SINGLELINE
1454 Setting this variable to on is equivalent to the command line
1455 option -S.
1456 SINGLESTEP
1458 Setting this variable to on is equivalent to the command line
1459 option -s.
1460 USER
1462 The database user you are currently connected as. This is set
1463 every time you connect to a database (including program
1464 start-up), but can be unset.
1465 VERBOSITY
1467 This variable can be set to the values default, verbose, or
1468 terse to control the verbosity of error reports.
1469 SQL Interpolation
1471 A key feature of psql variables is that you can substitute
1472 (“interpolate”) them into regular SQL statements, as well as the
1473 arguments of meta-commands. Furthermore, psql provides facilities
1474 for ensuring that variable values used as SQL literals and
1475 identifiers are properly quoted. The syntax for interpolating a
1476 value without any quoting is to prepend the variable name with a
1477 colon (:). For example,
1478 testdb=> \set foo 'my_table'
1480 testdb=> SELECT * FROM :foo;
1481 would query the table my_table. Note that this may be unsafe: the
1483 value of the variable is copied literally, so it can contain
1484 unbalanced quotes, or even backslash commands. You must make sure
1485 that it makes sense where you put it.
1486 When a value is to be used as an SQL literal or identifier, it is
1488 safest to arrange for it to be quoted. To quote the value of a
1489 variable as an SQL literal, write a colon followed by the variable
1490 name in single quotes. To quote the value as an SQL identifier,
1491 write a colon followed by the variable name in double quotes. These
1492 constructs deal correctly with quotes and other special characters
1493 embedded within the variable value. The previous example would be
1494 more safely written this way:
1495 testdb=> \set foo 'my_table'
1497 testdb=> SELECT * FROM :"foo";
1498 Variable interpolation will not be performed within quoted SQL
1500 literals and identifiers. Therefore, a construction such as ':foo'
1501 doesn't work to produce a quoted literal from a variable's value
1502 (and it would be unsafe if it did work, since it wouldn't correctly
1503 handle quotes embedded in the value).
1504 One example use of this mechanism is to copy the contents of a file
1506 into a table column. First load the file into a variable and then
1507 interpolate the variable's value as a quoted string:
1508 testdb=> \set content `cat my_file.txt`
1510 testdb=> INSERT INTO my_table VALUES (:'content');
1511 (Note that this still won't work if my_file.txt contains NUL bytes.
1513 psql does not support embedded NUL bytes in variable values.)
1514 Since colons can legally appear in SQL commands, an apparent
1516 attempt at interpolation (that is, :name, :'name', or :"name") is
1517 not replaced unless the named variable is currently set. In any
1518 case, you can escape a colon with a backslash to protect it from
1519 substitution.
1520 The colon syntax for variables is standard SQL for embedded query
1522 languages, such as ECPG. The colon syntaxes for array slices and
1523 type casts are PostgreSQL extensions, which can sometimes conflict
1524 with the standard usage. The colon-quote syntax for escaping a
1525 variable's value as an SQL literal or identifier is a psql
1526 extension.
1527 Prompting
1529 The prompts psql issues can be customized to your preference. The
1530 three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and
1531 special escape sequences that describe the appearance of the
1532 prompt. Prompt 1 is the normal prompt that is issued when psql
1533 requests a new command. Prompt 2 is issued when more input is
1534 expected during command entry, for example because the command was
1535 not terminated with a semicolon or a quote was not closed. Prompt 3
1536 is issued when you are running an SQLCOPY FROM STDIN command and
1537 you need to type in a row value on the terminal.
1538 The value of the selected prompt variable is printed literally,
1540 except where a percent sign (%) is encountered. Depending on the
1541 next character, certain other text is substituted instead. Defined
1542 substitutions are:
1543 %M
1545 The full host name (with domain name) of the database server,
1546 or [local] if the connection is over a Unix domain socket, or
1547 [local:/dir/name], if the Unix domain socket is not at the
1548 compiled in default location.
1549 %m
1551 The host name of the database server, truncated at the first
1552 dot, or [local] if the connection is over a Unix domain socket.
1553 %>
1555 The port number at which the database server is listening.
1556 %n
1558 The database session user name. (The expansion of this value
1559 might change during a database session as the result of the
1560 command SET SESSION AUTHORIZATION.)
1561 %/
1563 The name of the current database.
1564 %~
1566 Like %/, but the output is ~ (tilde) if the database is your
1567 default database.
1568 %#
1570 If the session user is a database superuser, then a #,
1571 otherwise a >. (The expansion of this value might change during
1572 a database session as the result of the command SET SESSION
1573 AUTHORIZATION.)
1574 %R
1576 In prompt 1 normally =, but ^ if in single-line mode, or ! if
1577 the session is disconnected from the database (which can happen
1578 if \connect fails). In prompt 2 %R is replaced by a character
1579 that depends on why psql expects more input: - if the command
1580 simply wasn't terminated yet, but * if there is an unfinished
1581 /* ... */ comment, a single quote if there is an unfinished
1582 quoted string, a double quote if there is an unfinished quoted
1583 identifier, a dollar sign if there is an unfinished
1584 dollar-quoted string, or ( if there is an unmatched left
1585 parenthesis. In prompt 3 %R doesn't produce anything.
1586 %x
1588 Transaction status: an empty string when not in a transaction
1589 block, or * when in a transaction block, or ! when in a failed
1590 transaction block, or ? when the transaction state is
1591 indeterminate (for example, because there is no connection).
1592 %digits
1594 The character with the indicated octal code is substituted.
1595 %:name:
1597 The value of the psql variable name. See the section Variables
1598 for details.
1599 %`command`
1601 The output of command, similar to ordinary “back-tick”
1602 substitution.
1603 %[ ... %]
1605 Prompts can contain terminal control characters which, for
1606 example, change the color, background, or style of the prompt
1607 text, or change the title of the terminal window. In order for
1608 the line editing features of Readline to work properly, these
1609 non-printing control characters must be designated as invisible
1610 by surrounding them with %[ and %]. Multiple pairs of these can
1611 occur within the prompt. For example:
1612 testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
1614 results in a boldfaced (1;) yellow-on-black (33;40) prompt on
1616 VT100-compatible, color-capable terminals.
1617 To insert a percent sign into your prompt, write %%. The default
1618 prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.
1619 Note
1621 This feature was shamelessly plagiarized from tcsh.
1622 Command-Line Editing
1624 psql supports the Readline library for convenient line editing and
1625 retrieval. The command history is automatically saved when psql
1626 exits and is reloaded when psql starts up. Tab-completion is also
1627 supported, although the completion logic makes no claim to be an
1628 SQL parser. If for some reason you do not like the tab completion,
1629 you can turn it off by putting this in a file named .inputrc in
1630 your home directory:
1631 $if psql
1633 set disable-completion on
1634 $endif
1635 (This is not a psql but a Readline feature. Read its documentation
1637 for further details.)
1638
1640 COLUMNS
1641 If \pset columns is zero, controls the width for the wrapped format
1642 and width for determining if wide output requires the pager or
1643 should be switched to the vertical format in expanded auto mode.
1644 PAGER
1646 If the query results do not fit on the screen, they are piped
1647 through this command. Typical values are more or less. The default
1648 is platform-dependent. Use of the pager can be disabled by setting
1649 PAGER to empty, or by using pager-related options of the \pset
1650 command.
1651 PGDATABASE, PGHOST, PGPORT, PGUSER
1653 Default connection parameters (see Section 31.14, “Environment
1654 Variables”, in the documentation).
1655 PSQL_EDITOR, EDITOR, VISUAL
1657 Editor used by the \e and \ef commands. The variables are examined
1658 in the order listed; the first that is set is used.
1659 The built-in default editors are vi on Unix systems and notepad.exe
1661 on Windows systems.
1662 PSQL_EDITOR_LINENUMBER_ARG
1664 When \e or \ef is used with a line number argument, this variable
1665 specifies the command-line argument used to pass the starting line
1666 number to the user's editor. For editors such as Emacs or vi, this
1667 is a plus sign. Include a trailing space in the value of the
1668 variable if there needs to be space between the option name and the
1669 line number. Examples:
1670 PSQL_EDITOR_LINENUMBER_ARG='+'
1672 PSQL_EDITOR_LINENUMBER_ARG='--line '
1673 The default is + on Unix systems (corresponding to the default
1675 editor vi, and useful for many other common editors); but there is
1676 no default on Windows systems.
1677 PSQL_HISTORY
1679 Alternative location for the command history file. Tilde (~)
1680 expansion is performed.
1681 PSQLRC
1683 Alternative location of the user's .psqlrc file. Tilde (~)
1684 expansion is performed.
1685 SHELL
1687 Command executed by the \! command.
1688 TMPDIR
1690 Directory for storing temporary files. The default is /tmp.
1691 This utility, like most other PostgreSQL utilities, also uses the
1693 environment variables supported by libpq (see Section 31.14,
1694 “Environment Variables”, in the documentation).
1695
1697 psqlrc and ~/.psqlrc
1698 Unless it is passed an -X or -c option, psql attempts to read and
1699 execute commands from the system-wide startup file (psqlrc) and
1700 then the user's personal startup file (~/.psqlrc), after connecting
1701 to the database but before accepting normal commands. These files
1702 can be used to set up the client and/or the server to taste,
1703 typically with \set and SET commands.
1704 The system-wide startup file is named psqlrc and is sought in the
1706 installation's “system configuration” directory, which is most
1707 reliably identified by running pg_config --sysconfdir. By default
1708 this directory will be ../etc/ relative to the directory containing
1709 the PostgreSQL executables. The name of this directory can be set
1710 explicitly via the PGSYSCONFDIR environment variable.
1711 The user's personal startup file is named .psqlrc and is sought in
1713 the invoking user's home directory. On Windows, which lacks such a
1714 concept, the personal startup file is named
1715 %APPDATA%\postgresql\psqlrc.conf. The location of the user's
1716 startup file can be set explicitly via the PSQLRC environment
1717 variable.
1718 Both the system-wide startup file and the user's personal startup
1720 file can be made psql-version-specific by appending a dash and the
1721 PostgreSQL major or minor release number to the file name, for
1722 example ~/.psqlrc-9.2 or ~/.psqlrc-9.2.5. The most specific
1723 version-matching file will be read in preference to a
1724 non-version-specific file.
1725 .psql_history
1727 The command-line history is stored in the file ~/.psql_history, or
1728 %APPDATA%\postgresql\psql_history on Windows.
1729 The location of the history file can be set explicitly via the
1731 PSQL_HISTORY environment variable.
1732
1734 · In an earlier life psql allowed the first argument of a
1735 single-letter backslash command to start directly after the
1736 command, without intervening whitespace. As of PostgreSQL 8.4 this
1737 is no longer allowed.
1738 · psql is only guaranteed to work smoothly with servers of the same
1740 version. That does not mean other combinations will fail outright,
1741 but subtle and not-so-subtle problems might come up. Backslash
1742 commands are particularly likely to fail if the server is of a
1743 newer version than psql itself. However, backslash commands of the
1744 \d family should work with servers of versions back to 7.4, though
1745 not necessarily with servers newer than psql itself.
1746
1748 psql is built as a “console application”. Since the Windows console
1749 windows use a different encoding than the rest of the system, you must
1750 take special care when using 8-bit characters within psql. If psql
1751 detects a problematic console code page, it will warn you at startup.
1752 To change the console code page, two things are necessary:
1753 · Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
1755 page that is appropriate for German; replace it with your value.)
1756 If you are using Cygwin, you can put this command in /etc/profile.
1757 · Set the console font to Lucida Console, because the raster font
1759 does not work with the ANSI code page.
1760
1762 The first example shows how to spread a command over several lines of
1763 input. Notice the changing prompt:
1764 testdb=> CREATE TABLE my_table (
1766 testdb(> first integer not null default 0,
1767 testdb(> second text)
1768 testdb-> ;
1769 CREATE TABLE
1770 Now look at the table definition again:
1772 testdb=> \d my_table
1774 Table "my_table"
1775 Attribute | Type | Modifier
1776 -----------+---------+--------------------
1777 first | integer | not null default 0
1778 second | text |
1779 Now we change the prompt to something more interesting:
1781 testdb=> \set PROMPT1 '%n@%m %~%R%# '
1783 peter@localhost testdb=>
1784 Let's assume you have filled the table with data and want to take a
1786 look at it:
1787 peter@localhost testdb=> SELECT * FROM my_table;
1789 first | second
1790 -------+--------
1791 1 | one
1792 2 | two
1793 3 | three
1794 4 | four
1795 (4 rows)
1796 You can display tables in different ways by using the \pset command:
1798 peter@localhost testdb=> \pset border 2
1800 Border style is 2.
1801 peter@localhost testdb=> SELECT * FROM my_table;
1802 +-------+--------+
1803 | first | second |
1804 +-------+--------+
1805 | 1 | one |
1806 | 2 | two |
1807 | 3 | three |
1808 | 4 | four |
1809 +-------+--------+
1810 (4 rows)
1811 peter@localhost testdb=> \pset border 0
1813 Border style is 0.
1814 peter@localhost testdb=> SELECT * FROM my_table;
1815 first second
1816 ----- ------
1817 1 one
1818 2 two
1819 3 three
1820 4 four
1821 (4 rows)
1822 peter@localhost testdb=> \pset border 1
1824 Border style is 1.
1825 peter@localhost testdb=> \pset format unaligned
1826 Output format is unaligned.
1827 peter@localhost testdb=> \pset fieldsep ","
1828 Field separator is ",".
1829 peter@localhost testdb=> \pset tuples_only
1830 Showing only tuples.
1831 peter@localhost testdb=> SELECT second, first FROM my_table;
1832 one,1
1833 two,2
1834 three,3
1835 four,4
1836 Alternatively, use the short commands:
1838 peter@localhost testdb=> \a \t \x
1840 Output format is aligned.
1841 Tuples only is off.
1842 Expanded display is on.
1843 peter@localhost testdb=> SELECT * FROM my_table;
1844 -[ RECORD 1 ]-
1845 first | 1
1846 second | one
1847 -[ RECORD 2 ]-
1848 first | 2
1849 second | two
1850 -[ RECORD 3 ]-
1851 first | 3
1852 second | three
1853 -[ RECORD 4 ]-
1854 first | 4
1855 second | four
1856PostgreSQL 9.2.24 2017-11-06 PSQL(1)