1PSQL(1) PostgreSQL Client Applications PSQL(1)
2
6 psql - PostgreSQL interactive terminal
7
10 psql [ option... ] [ dbname
11 [ username ] ]
12
14 psql is a terminal-based front-end to PostgreSQL. It enables you to
15 type in queries interactively, issue them to PostgreSQL, and see the
16 query results. Alternatively, input can be from a file. In addition,
17 it provides a number of meta-commands and various shell-like features
18 to facilitate writing scripts and automating a wide variety of tasks.
19
21 -a
22 --echo-all
24 Print all input lines to standard output as they are read. This
25 is more useful for script processing rather than interactive
26 mode. This is equivalent to setting the variable ECHO to all.
27 -A
29 --no-align
31 Switches to unaligned output mode. (The default output mode is
32 otherwise aligned.)
33 -c command
35 --command command
37 Specifies that psql is to execute one command string, command,
38 and then exit. This is useful in shell scripts.
39 command must be either a command string that is completely
41 parsable by the server (i.e., it contains no psql specific fea‐
42 tures), or a single backslash command. Thus you cannot mix SQL
43 and psql meta-commands with this option. To achieve that, you
44 could pipe the string into psql, for example: echo '\x \\ SELECT
45 * FROM foo;' | psql. (\\ is the separator meta-command.)
46 If the command string contains multiple SQL commands, they are
48 processed in a single transaction, unless there are explicit
49 BEGIN/COMMIT commands included in the string to divide it into
50 multiple transactions. This is different from the behavior when
51 the same string is fed to psql's standard input. Also, only the
52 result of the last SQL command is returned.
53 Because of these legacy behaviors, putting more than one command
55 in the -c string often has unexpected results. It's better to
56 feed multiple commands to psql's standard input, either using
57 echo as illustrated above, or via a shell here-document, for
58 example:
59 psql <<EOF
61 \x
62 SELECT * FROM foo;
63 EOF
64 -d dbname
67 --dbname dbname
69 Specifies the name of the database to connect to. This is equiv‐
70 alent to specifying dbname as the first non-option argument on
71 the command line.
72 If this parameter contains an = sign, it is treated as a con‐
74 ninfo string. See in the documentation for more information.
75 -e
77 --echo-queries
79 Copy all SQL commands sent to the server to standard output as
80 well. This is equivalent to setting the variable ECHO to
81 queries.
82 -E
84 --echo-hidden
86 Echo the actual queries generated by \d and other backslash com‐
87 mands. You can use this to study psql's internal operations.
88 This is equivalent to setting the variable ECHO_HIDDEN from
89 within psql.
90 -f filename
92 --file filename
94 Use the file filename as the source of commands instead of read‐
95 ing commands interactively. After the file is processed, psql
96 terminates. This is in many ways equivalent to the internal com‐
97 mand \i.
98 If filename is - (hyphen), then standard input is read.
100 Using this option is subtly different from writing psql < file‐
102 name. In general, both will do what you expect, but using -f
103 enables some nice features such as error messages with line num‐
104 bers. There is also a slight chance that using this option will
105 reduce the start-up overhead. On the other hand, the variant
106 using the shell's input redirection is (in theory) guaranteed to
107 yield exactly the same output that you would have gotten had you
108 entered everything by hand.
109 -F separator
111 --field-separator separator
113 Use separator as the field separator for unaligned output. This
114 is equivalent to \pset fieldsep or \f.
115 -h hostname
117 --host hostname
119 Specifies the host name of the machine on which the server is
120 running. If the value begins with a slash, it is used as the
121 directory for the Unix-domain socket.
122 -H
124 --html Turn on HTML tabular output. This is equivalent to \pset format
126 html or the \H command.
127 -l
129 --list List all available databases, then exit. Other non-connection
131 options are ignored. This is similar to the internal command
132 \list.
133 -L filename
135 --log-file filename
137 Write all query output into file filename, in addition to the
138 normal output destination.
139 -n
141 --no-readline
143 Do not use readline for line editing and do not use the history.
144 This can be useful to turn off tab expansion when cutting and
145 pasting.
146 -o filename
148 --output filename
150 Put all query output into file filename. This is equivalent to
151 the command \o.
152 -p port
154 --port port
156 Specifies the TCP port or the local Unix-domain socket file
157 extension on which the server is listening for connections.
158 Defaults to the value of the PGPORT environment variable or, if
159 not set, to the port specified at compile time, usually 5432.
160 -P assignment
162 --pset assignment
164 Allows you to specify printing options in the style of \pset on
165 the command line. Note that here you have to separate name and
166 value with an equal sign instead of a space. Thus to set the
167 output format to LaTeX, you could write -P format=latex.
168 -q
170 --quiet
172 Specifies that psql should do its work quietly. By default, it
173 prints welcome messages and various informational output. If
174 this option is used, none of this happens. This is useful with
175 the -c option. Within psql you can also set the QUIET variable
176 to achieve the same effect.
177 -R separator
179 --record-separator separator
181 Use separator as the record separator for unaligned output. This
182 is equivalent to the \pset recordsep command.
183 -s
185 --single-step
187 Run in single-step mode. That means the user is prompted before
188 each command is sent to the server, with the option to cancel
189 execution as well. Use this to debug scripts.
190 -S
192 --single-line
194 Runs in single-line mode where a newline terminates an SQL com‐
195 mand, as a semicolon does.
196 Note: This mode is provided for those who insist on it, but you
198 are not necessarily encouraged to use it. In particular, if you
199 mix SQL and meta-commands on a line the order of execution might
200 not always be clear to the inexperienced user.
201 -t
204 --tuples-only
206 Turn off printing of column names and result row count footers,
207 etc. This is equivalent to the \t command.
208 -T table_options
210 --table-attr table_options
212 Allows you to specify options to be placed within the HTML table
213 tag. See \pset for details.
214 -U username
216 --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
224 --variable assignment
226 Perform a variable assignment, like the \set internal command.
227 Note that you must separate name and value, if any, by an equal
228 sign on the command line. To unset a variable, leave off the
229 equal sign. To just set a variable without a value, use the
230 equal sign but leave off the value. These assignments are done
231 during a very early stage of start-up, so variables reserved for
232 internal purposes might get overwritten later.
233 -V
235 --version
237 Print the psql version and exit.
238 -w
240 --no-password
242 Never issue a password prompt. If the server requires password
243 authentication and a password is not available by other means
244 such as a .pgpass file, the connection attempt will fail. This
245 option can be useful in batch jobs and scripts where no user is
246 present to enter a password.
247 Note that this option will remain set for the entire session,
249 and so it affects uses of the meta-command \connect as well as
250 the initial connection attempt.
251 -W
253 --password
255 Force psql to prompt for a password before connecting to a data‐
256 base.
257 This option is never essential, since psql will automatically
259 prompt for a password if the server demands password authentica‐
260 tion. However, psql will waste a connection attempt finding out
261 that the server wants a password. In some cases it is worth typ‐
262 ing -W to avoid the extra connection attempt.
263 Note that this option will remain set for the entire session,
265 and so it affects uses of the meta-command \connect as well as
266 the initial connection attempt.
267 -x
269 --expanded
271 Turn on the expanded table formatting mode. This is equivalent
272 to the \x command.
273 -X,
275 --no-psqlrc
277 Do not read the start-up file (neither the system-wide psqlrc
278 file nor the user's ~/.psqlrc file).
279 -1
281 --single-transaction
283 When psql executes a script with the -f option, adding this
284 option wraps BEGIN/COMMIT around the script to execute it as a
285 single transaction. This ensures that either all the commands
286 complete successfully, or no changes are applied.
287 If the script itself uses BEGIN, COMMIT, or ROLLBACK, this
289 option will not have the desired effects. Also, if the script
290 contains any command that cannot be executed inside a transac‐
291 tion block, specifying this option will cause that command (and
292 hence the whole transaction) to fail.
293 -?
295 --help Show help about psql command line arguments, and exit.
297
299 psql returns 0 to the shell if it finished normally, 1 if a fatal error
300 of its own (out of memory, file not found) occurs, 2 if the connection
301 to the server went bad and the session was not interactive, and 3 if an
302 error occurred in a script and the variable ON_ERROR_STOP was set.
303
305 CONNECTING TO A DATABASE
306 psql is a regular PostgreSQL client application. In order to connect to
307 a database you need to know the name of your target database, the host
308 name and port number of the server and what user name you want to con‐
309 nect as. psql can be told about those parameters via command line
310 options, namely -d, -h, -p, and -U respectively. If an argument is
311 found that does not belong to any option it will be interpreted as the
312 database name (or the user name, if the database name is already
313 given). Not all these options are required; there are useful defaults.
314 If you omit the host name, psql will connect via a Unix-domain socket
315 to a server on the local host, or via TCP/IP to localhost on machines
316 that don't have Unix-domain sockets. The default port number is deter‐
317 mined at compile time. Since the database server uses the same
318 default, you will not have to specify the port in most cases. The
319 default user name is your Unix user name, as is the default database
320 name. Note that you cannot just connect to any database under any user
321 name. Your database administrator should have informed you about your
322 access rights.
323 When the defaults aren't quite right, you can save yourself some typing
325 by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or
326 PGUSER to appropriate values. (For additional environment variables,
327 see in the documentation.) It is also convenient to have a ~/.pgpass
328 file to avoid regularly having to type in passwords. See in the docu‐
329 mentation for more information.
330 An alternative way to specify connection parameters is in a conninfo
332 string, which is used instead of a database name. This mechanism give
333 you very wide control over the connection. For example:
334 $ psql "service=myservice sslmode=require"
336 This way you can also use LDAP for connection parameter lookup as
338 described in in the documentation. See in the documentation for more
339 information on all the available connection options.
340 If the connection could not be made for any reason (e.g., insufficient
342 privileges, server is not running on the targeted host, etc.), psql
343 will return an error and terminate.
344 ENTERING SQL COMMANDS
346 In normal operation, psql provides a prompt with the name of the data‐
347 base to which psql is currently connected, followed by the string =>.
348 For example:
349 $ psql testdb
351 psql (8.4.20)
352 Type "help" for help.
353 testdb=>
355 At the prompt, the user can type in SQL commands. Ordinarily, input
358 lines are sent to the server when a command-terminating semicolon is
359 reached. An end of line does not terminate a command. Thus commands can
360 be spread over several lines for clarity. If the command was sent and
361 executed without error, the results of the command are displayed on the
362 screen.
363 Whenever a command is executed, psql also polls for asynchronous noti‐
365 fication events generated by LISTEN [listen(7)] and NOTIFY [notify(7)].
366 META-COMMANDS
368 Anything you enter in psql that begins with an unquoted backslash is a
369 psql meta-command that is processed by psql itself. These commands help
370 make psql more useful for administration or scripting. Meta-commands
371 are more commonly called slash or backslash commands.
372 The format of a psql command is the backslash, followed immediately by
374 a command verb, then any arguments. The arguments are separated from
375 the command verb and each other by any number of whitespace characters.
376 To include whitespace into an argument you can quote it with a single
378 quote. To include a single quote into such an argument, use two single
379 quotes. Anything contained in single quotes is furthermore subject to
380 C-like substitutions for \n (new line), \t (tab), \digits (octal), and
381 \xdigits (hexadecimal).
382 If an unquoted argument begins with a colon (:), it is taken as a psql
384 variable and the value of the variable is used as the argument instead.
385 Arguments that are enclosed in backquotes (`) are taken as a command
387 line that is passed to the shell. The output of the command (with any
388 trailing newline removed) is taken as the argument value. The above
389 escape sequences also apply in backquotes.
390 Some commands take an SQL identifier (such as a table name) as argu‐
392 ment. These arguments follow the syntax rules of SQL: Unquoted letters
393 are forced to lowercase, while double quotes (") protect letters from
394 case conversion and allow incorporation of whitespace into the identi‐
395 fier. Within double quotes, paired double quotes reduce to a single
396 double quote in the resulting name. For example, FOO"BAR"BAZ is inter‐
397 preted as fooBARbaz, and "A weird"" name" becomes A weird" name.
398 Parsing for arguments stops when another unquoted backslash occurs.
400 This is taken as the beginning of a new meta-command. The special
401 sequence \\ (two backslashes) marks the end of arguments and continues
402 parsing SQL commands, if any. That way SQL and psql commands can be
403 freely mixed on a line. But in any case, the arguments of a meta-com‐
404 mand cannot continue beyond the end of the line.
405 The following meta-commands are defined:
407 \a If the current table output format is unaligned, it is switched
409 to aligned. If it is not unaligned, it is set to unaligned.
410 This command is kept for backwards compatibility. See \pset for
411 a more general solution.
412 \cd [ directory ]
414 Changes the current working directory to directory. Without
415 argument, changes to the current user's home directory.
416 Tip: To print your current working directory, use \! pwd.
418 \C [ title ]
421 Sets the title of any tables being printed as the result of a
422 query or unset any such title. This command is equivalent to
423 \pset title title. (The name of this command derives from ``cap‐
424 tion'', as it was previously only used to set the caption in an
425 HTML table.)
426 \connect (or \c) [ dbname [ username ] [ host ] [ port ] ]
428 Establishes a new connection to a PostgreSQL server. If the new
429 connection is successfully made, the previous connection is
430 closed. If any of dbname, username, host or port are omitted or
431 specified as -, the value of that parameter from the previous
432 connection is used. If there is no previous connection, the
433 libpq default for the parameter's value is used.
434 If the connection attempt failed (wrong user name, access
436 denied, etc.), the previous connection will only be kept if psql
437 is in interactive mode. When executing a non-interactive script,
438 processing will immediately stop with an error. This distinction
439 was chosen as a user convenience against typos on the one hand,
440 and a safety mechanism that scripts are not accidentally acting
441 on the wrong database on the other hand.
442 \copy { table [ ( column_list ) ] | ( query ) }
444 Performs a frontend (client) copy. This is an operation that
445 runs an SQL COPY [copy(7)] command, but instead of the server
446 reading or writing the specified file, psql reads or writes the
447 file and routes the data between the server and the local file
448 system. This means that file accessibility and privileges are
449 those of the local user, not the server, and no SQL superuser
450 privileges are required.
451 The syntax of the command is similar to that of the SQL COPY
453 [copy(7)] command. Note that, because of this, special parsing
454 rules apply to the \copy command. In particular, the variable
455 substitution rules and backslash escapes do not apply.
456 \copy ... from stdin | to stdout reads/writes based on the com‐
458 mand input and output respectively. All rows are read from the
459 same source that issued the command, continuing until \. is read
460 or the stream reaches EOF. Output is sent to the same place as
461 command output. To read/write from psql's standard input or out‐
462 put, use pstdin or pstdout. This option is useful for populating
463 tables in-line within a SQL script file.
464 Tip: This operation is not as efficient as the SQL COPY command
466 because all data must pass through the client/server connection.
467 For large amounts of data the SQL command might be preferable.
468 \copyright
471 Shows the copyright and distribution terms of PostgreSQL.
472 \d[S+] [ pattern ]
474 For each relation (table, view, index, or sequence) matching the
475 pattern, show all columns, their types, the tablespace (if not
476 the default) and any special attributes such as NOT NULL or
477 defaults, if any. Associated indexes, constraints, rules, and
478 triggers are also shown, as is the view definition if the rela‐
479 tion is a view. (``Matching the pattern'' is defined below.)
480 The command form \d+ is identical, except that more information
482 is displayed: any comments associated with the columns of the
483 table are shown, as is the presence of OIDs in the table.
484 By default, only user-created objects are shown; supply a pat‐
486 tern or the S modifier to include system objects.
487 Note: If \d is used without a pattern argument, it is equivalent
489 to \dtvs which will show a list of all tables, views, and
490 sequences. This is purely a convenience measure.
491 \da[S] [ pattern ]
494 Lists all available aggregate functions, together with their
495 return type and the data types they operate on. If pattern is
496 specified, only aggregates whose names match the pattern are
497 shown. By default, only user-created objects are shown; supply
498 a pattern or the S modifier to include system objects.
499 \db[+] [ pattern ]
501 Lists all available tablespaces. If pattern is specified, only
502 tablespaces whose names match the pattern are shown. If + is
503 appended to the command name, each object is listed with its
504 associated permissions.
505 \dc[S] [ pattern ]
507 Lists all available conversions between character-set encodings.
508 If pattern is specified, only conversions whose names match the
509 pattern are listed. By default, only user-created objects are
510 shown; supply a pattern or the S modifier to include system
511 objects.
512 \dC [ pattern ]
514 Lists all available type casts. If pattern is specified, only
515 casts whose source or target types match the pattern are listed.
516 \dd[S] [ pattern ]
518 Shows the descriptions of objects matching the pattern, or of
519 all visible objects if no argument is given. But in either case,
520 only objects that have a description are listed. By default,
521 only user-created objects are shown; supply a pattern or the S
522 modifier to include system objects. ``Object'' covers aggre‐
523 gates, functions, operators, types, relations (tables, views,
524 indexes, sequences), large objects, rules, and triggers. For
525 example:
526 => \dd version
528 Object descriptions
529 Schema | Name | Object | Description
530 ------------+---------+----------+---------------------------
531 pg_catalog | version | function | PostgreSQL version string
532 (1 row)
533 Descriptions for objects can be created with the COMMENT [com‐
536 ment(7)] SQL command.
537 \dD[S] [ pattern ]
539 Lists all available domains. If pattern is specified, only
540 matching domains are shown. By default, only user-created
541 objects are shown; supply a pattern or the S modifier to include
542 system objects.
543 \des[+] [ pattern ]
545 Lists all foreign servers (mnemonic: ``external servers''). If
546 pattern is specified, only those servers whose name matches the
547 pattern are listed. If the form \des+ is used, a full descrip‐
548 tion of each server is shown, including the server's ACL, type,
549 version, and options.
550 \deu[+] [ pattern ]
552 Lists all user mappings (mnemonic: ``external users''). If pat‐
553 tern is specified, only those mappings whose user names match
554 the pattern are listed. If the form \deu+ is used, additional
555 information about each mapping is shown.
556 Caution: \deu+ might also display the user name and password of
558 the remote user, so care should be taken not to disclose them.
559 \dew[+] [ pattern ]
562 Lists all foreign-data wrappers (mnemonic: ``external wrap‐
563 pers''). If pattern is specified, only those foreign-data wrap‐
564 pers whose name matches the pattern are listed. If the form
565 \dew+ is used, the ACL and options of the foreign-data wrapper
566 are also shown.
567 \df[antwS+] [ pattern ]
569 Lists available functions, together with their arguments, return
570 types, and their function types: 'agg' (aggregate), 'normal',
571 'trigger', and 'window'. To display only functions of a specific
572 type, use the corresponding letters a, n, t, or w. If pattern is
573 specified, only functions whose names match the pattern are
574 shown. If the form \df+ is used, additional information about
575 each function, including volatility, language, source code and
576 description, is shown. By default, only user-created objects are
577 shown; supply a pattern or the S modifier to include system
578 objects.
579 Note: To look up functions taking arguments or returning values
581 of a specific type, use your pager's search capability to scroll
582 through the \df output.
583 \dF[+] [ pattern ]
586 Lists available text search configurations. If pattern is spec‐
587 ified, only configurations whose names match the pattern are
588 shown. If the form \dF+ is used, a full description of each
589 configuration is shown, including the underlying text search
590 parser and the dictionary list for each parser token type.
591 \dFd[+] [ pattern ]
593 Lists available text search dictionaries. If pattern is speci‐
594 fied, only dictionaries whose names match the pattern are shown.
595 If the form \dFd+ is used, additional information is shown about
596 each selected dictionary, including the underlying text search
597 template and the option values.
598 \dFp[+] [ pattern ]
600 Lists available text search parsers. If pattern is specified,
601 only parsers whose names match the pattern are shown. If the
602 form \dFp+ is used, a full description of each parser is shown,
603 including the underlying functions and the list of recognized
604 token types.
605 \dFt[+] [ pattern ]
607 Lists available text search templates. If pattern is specified,
608 only templates whose names match the pattern are shown. If the
609 form \dFt+ is used, additional information is shown about each
610 template, including the underlying function names.
611 \dg[+] [ pattern ]
613 Lists all database roles. If pattern is specified, only those
614 roles whose names match the pattern are listed. (This command
615 is now effectively the same as \du). If the form \dg+ is used,
616 additional information is shown about each role, including the
617 comment for each role.
618 \di[S+] [ pattern ]
620 \ds[S+] [ pattern ]
622 \dt[S+] [ pattern ]
624 \dv[S+] [ pattern ]
626 In this group of commands, the letters i, s, t, and v stand for
627 index, sequence, table, and view, respectively. You can specify
628 any or all of these letters, in any order, to obtain a listing
629 of all the matching objects. For example, \dit lists indexes and
630 tables. If + is appended to the command name, each object is
631 listed with its physical size on disk and its associated
632 description, if any. By default, only user-created objects are
633 shown; supply a pattern or the S modifier to include system
634 objects.
635 If pattern is specified, only objects whose names match the pat‐
637 tern are listed.
638 \dl This is an alias for \lo_list, which shows a list of large
640 objects.
641 \dn[+] [ pattern ]
643 Lists available schemas (namespaces). If pattern (a regular
644 expression) is specified, only schemas whose names match the
645 pattern are listed. Non-local temporary schemas are suppressed.
646 If + is appended to the command name, each object is listed with
647 its associated permissions and description, if any.
648 \do[S] [ pattern ]
650 Lists available operators with their operand and return types.
651 If pattern is specified, only operators whose names match the
652 pattern are listed. By default, only user-created objects are
653 shown; supply a pattern or the S modifier to include system
654 objects.
655 \dp [ pattern ]
657 Lists available tables, views and sequences with their associ‐
658 ated access privileges. If pattern is specified, only tables,
659 views and sequences whose names match the pattern are listed.
660 The GRANT [grant(7)] and REVOKE [revoke(7)] commands are used to
662 set access privileges.
663 \dT[S+] [ pattern ]
665 Lists available data types. If pattern is specified, only types
666 whose names match the pattern are listed. If + is appended to
667 the command name, each type is listed with its internal name and
668 size, as well as its allowed values if it is an enum type. By
669 default, only user-created objects are shown; supply a pattern
670 or the S modifier to include system objects.
671 \du[+] [ pattern ]
673 Lists all database roles. If pattern is specified, only those
674 roles whose names match the pattern are listed. If the form
675 \du+ is used, additional information is shown about each role,
676 including the comment for each role.
677 \edit (or \e) [ filename ]
679 If filename is specified, the file is edited; after the editor
680 exits, its content is copied back to the query buffer. If no
681 argument is given, the current query buffer is copied to a tem‐
682 porary file which is then edited in the same fashion.
683 The new query buffer is then re-parsed according to the normal
685 rules of psql, where the whole buffer is treated as a single
686 line. (Thus you cannot make scripts this way. Use \i for that.)
687 This means also that if the query ends with (or rather contains)
688 a semicolon, it is immediately executed. In other cases it will
689 merely wait in the query buffer.
690 Tip: psql searches the environment variables PSQL_EDITOR, EDI‐
692 TOR, and VISUAL (in that order) for an editor to use. If all of
693 them are unset, vi is used on Unix systems, notepad.exe on Win‐
694 dows systems.
695 \ef [ function_description ]
698 This command fetches and edits the definition of the named func‐
699 tion, in the form of a CREATE OR REPLACE FUNCTION command.
700 Editing is done in the same way as for \e. After the editor
701 exits, the updated command waits in the query buffer; type semi‐
702 colon or \g to send it, or \r to cancel.
703 The target function can be specified by name alone, or by name
705 and arguments, for example foo(integer, text). The argument
706 types must be given if there is more than one function of the
707 same name.
708 If no function is specified, a blank CREATE FUNCTION template is
710 presented for editing.
711 \echo text [ ... ]
713 Prints the arguments to the standard output, separated by one
714 space and followed by a newline. This can be useful to inter‐
715 sperse information in the output of scripts. For example:
716 => \echo `date`
718 Tue Oct 26 21:40:57 CEST 1999
719 If the first argument is an unquoted -n the trailing newline is
721 not written.
722 Tip: If you use the \o command to redirect your query output you
724 might wish to use \qecho instead of this command.
725 \encoding [ encoding ]
728 Sets the client character set encoding. Without an argument,
729 this command shows the current encoding.
730 \f [ string ]
732 Sets the field separator for unaligned query output. The default
733 is the vertical bar (|). See also \pset for a generic way of
734 setting output options.
735 \g [ { filename | |command } ]
737 Sends the current query input buffer to the server and option‐
738 ally stores the query's output in filename or pipes the output
739 into a separate Unix shell executing command. A bare \g is vir‐
740 tually equivalent to a semicolon. A \g with argument is a ``one-
741 shot'' alternative to the \o command.
742 \help (or \h) [ command ]
744 Gives syntax help on the specified SQL command. If command is
745 not specified, then psql will list all the commands for which
746 syntax help is available. If command is an asterisk (*), then
747 syntax help on all SQL commands is shown.
748 Note: To simplify typing, commands that consists of several
750 words do not have to be quoted. Thus it is fine to type \help
751 alter table.
752 \H Turns on HTML query output format. If the HTML format is already
755 on, it is switched back to the default aligned text format. This
756 command is for compatibility and convenience, but see \pset
757 about setting other output options.
758 \i filename
760 Reads input from the file filename and executes it as though it
761 had been typed on the keyboard.
762 Note: If you want to see the lines on the screen as they are
764 read you must set the variable ECHO to all.
765 \l (or \list)
768 \l+ (or \list+)
770 List the names, owners, character set encodings, and access
771 privileges of all the databases in the server. If + is appended
772 to the command name, database sizes, default tablespaces, and
773 descriptions are also displayed. (Size information is only
774 available for databases that the current user can connect to.)
775 \lo_export loid filename
777 Reads the large object with OID loid from the database and
778 writes it to filename. Note that this is subtly different from
779 the server function lo_export, which acts with the permissions
780 of the user that the database server runs as and on the server's
781 file system.
782 Tip: Use \lo_list to find out the large object's OID.
784 \lo_import filename [ comment ]
787 Stores the file into a PostgreSQL large object. Optionally, it
788 associates the given comment with the object. Example:
789 foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'
791 lo_import 152801
792 The response indicates that the large object received object ID
794 152801, which can be used to access the newly-created large
795 object in the future. For the sake of readability, it is recom‐
796 mended to always associate a human-readable comment with every
797 object. Both OIDs and comments can be viewed with the \lo_list
798 command.
799 Note that this command is subtly different from the server-side
801 lo_import because it acts as the local user on the local file
802 system, rather than the server's user and file system.
803 \lo_list
805 Shows a list of all PostgreSQL large objects currently stored in
806 the database, along with any comments provided for them.
807 \lo_unlink loid
809 Deletes the large object with OID loid from the database.
810 Tip: Use \lo_list to find out the large object's OID.
812 \o [ {filename | |command} ]
815 Saves future query results to the file filename or pipes future
816 results into a separate Unix shell to execute command. If no
817 arguments are specified, the query output will be reset to the
818 standard output.
819 ``Query results'' includes all tables, command responses, and
821 notices obtained from the database server, as well as output of
822 various backslash commands that query the database (such as \d),
823 but not error messages.
824 Tip: To intersperse text output in between query results, use
826 \qecho.
827 \p Print the current query buffer to the standard output.
830 \password [ username ]
832 Changes the password of the specified user (by default, the cur‐
833 rent user). This command prompts for the new password, encrypts
834 it, and sends it to the server as an ALTER ROLE command. This
835 makes sure that the new password does not appear in cleartext in
836 the command history, the server log, or elsewhere.
837 \prompt [ text ] name
839 Prompts the user to set variable name. An optional prompt, text,
840 can be specified. (For multi-word prompts, use single-quotes.)
841 By default, \prompt uses the terminal for input and output. How‐
843 ever, if the -f command line switch is used, \prompt uses stan‐
844 dard input and standard output.
845 \pset parameter [ value ]
847 This command sets options affecting the output of query result
848 tables. parameter describes which option is to be set. The
849 semantics of value depend thereon.
850 Adjustable printing options are:
852 format Sets the output format to one of unaligned, aligned,
854 wrapped, html, latex, or troff-ms. Unique abbreviations
855 are allowed. (That would mean one letter is enough.)
856 ``Unaligned'' writes all columns of a row on a line, sep‐
858 arated by the currently active field separator. This is
859 intended to create output that might be intended to be
860 read in by other programs (tab-separated, comma-sepa‐
861 rated). ``Aligned'' mode is the standard, human-read‐
862 able, nicely formatted text output that is default.
863 ``Wrapped'' is like aligned but wraps output to the spec‐
865 ified width. If \pset columns is zero (the default),
866 wrapped mode only affects screen output and wrapped width
867 is controlled by the environment variable COLUMNS or the
868 detected screen width. If \pset columns is set to a non-
869 zero value, all output is wrapped, including file and
870 pipe output.
871 The ``HTML'' and ``LaTeX'' modes put out tables that are
873 intended to be included in documents using the respective
874 mark-up language. They are not complete documents! (This
875 might not be so dramatic in HTML, but in LaTeX you must
876 have a complete document wrapper.)
877 columns
879 Controls the target width for the wrapped format, and
880 width for determining if wide output requires the pager.
881 Zero (the default) causes the wrapped format to affect
882 only screen output.
883 border The second argument must be a number. In general, the
885 higher the number the more borders and lines the tables
886 will have, but this depends on the particular format. In
887 HTML mode, this will translate directly into the bor‐
888 der=... attribute, in the others only values 0 (no bor‐
889 der), 1 (internal dividing lines), and 2 (table frame)
890 make sense.
891 expanded (or x)
893 You can specify an optional second argument, if it is
894 provided it may be either on or off which will enable or
895 disable expanded mode. If the second argument is not pro‐
896 vided then we will toggle between regular and expanded
897 format. When expanded format is enabled, query results
898 are displayed in two columns, with the column name on the
899 left and the data on the right. This mode is useful if
900 the data wouldn't fit on the screen in the normal ``hori‐
901 zontal'' mode.
902 Expanded mode is supported by all four output formats.
904 null The second argument is a string that should be printed
906 whenever a column is null. The default is not to print
907 anything, which can easily be mistaken for, say, an empty
908 string. Thus, one might choose to write \pset null
909 '(null)'.
910 fieldsep
912 Specifies the field separator to be used in unaligned
913 output mode. That way one can create, for example, tab-
914 or comma-separated output, which other programs might
915 prefer. To set a tab as field separator, type \pset
916 fieldsep '\t'. The default field separator is '|' (a ver‐
917 tical bar).
918 footer You can specify an optional second argument, if it is
920 provided it may be either on or off which will enable or
921 disable display of the default footer (x rows). If the
922 second argument is not provided then we will toggle
923 between on and off.
924 numericlocale
926 You can specify an optional second argument, if it is
927 provided it may be either on or off which will enable or
928 disable display of a locale-aware character to separate
929 groups of digits to the left of the decimal marker. If
930 the second argument is not provided then we will toggle
931 between on and off.
932 recordsep
934 Specifies the record (line) separator to use in unaligned
935 output mode. The default is a newline character.
936 tuples_only (or t)
938 You can specify an optional second argument, if it is
939 provided it may be either on or off which will enable or
940 disable the tuples only mode. If the second argument is
941 not provided then we will toggle between tuples only and
942 full display. Full display shows extra information such
943 as column headers, titles, and various footers. In tuples
944 only mode, only actual table data is shown.
945 title [ text ]
947 Sets the table title for any subsequently printed tables.
948 This can be used to give your output descriptive tags. If
949 no argument is given, the title is unset.
950 tableattr (or T) [ text ]
952 Allows you to specify any attributes to be placed inside
953 the HTML table tag. This could for example be cellpadding
954 or bgcolor. Note that you probably don't want to specify
955 border here, as that is already taken care of by \pset
956 border.
957 pager Controls use of a pager for query and psql help output.
959 If the environment variable PAGER is set, the output is
960 piped to the specified program. Otherwise a platform-
961 dependent default (such as more) is used.
962 When the pager is off, the pager is not used. When the
964 pager is on, the pager is used only when appropriate,
965 i.e. the output is to a terminal and will not fit on the
966 screen. \pset pager turns the pager on and off. Pager
967 can also be set to always, which causes the pager to be
968 always used.
969 Illustrations on how these different formats look can be seen in the
971 Examples [psql(1)] section.
972 Tip: There are various shortcut commands for \pset. See \a, \C,
974 \H, \t, \T, and \x.
975 Note: It is an error to call \pset without arguments. In the
978 future this call might show the current status of all printing
979 options.
980 \q or \quit
983 Quits the psql program. In a script file, only execution of
984 that script is terminated.
985 \qecho text [ ... ]
987 This command is identical to \echo except that the output will
988 be written to the query output channel, as set by \o.
989 \r Resets (clears) the query buffer.
991 \s [ filename ]
993 Print or save the command line history to filename. If filename
994 is omitted, the history is written to the standard output. This
995 option is only available if psql is configured to use the GNU
996 Readline library.
997 \set [ name [ value [ ... ] ] ]
999 Sets the internal variable name to value or, if more than one
1000 value is given, to the concatenation of all of them. If no sec‐
1001 ond argument is given, the variable is just set with no value.
1002 To unset a variable, use the \unset command.
1003 Valid variable names can contain characters, digits, and under‐
1005 scores. See the section Variables [psql(1)] below for details.
1006 Variable names are case-sensitive.
1007 Although you are welcome to set any variable to anything you
1009 want, psql treats several variables as special. They are docu‐
1010 mented in the section about variables.
1011 Note: This command is totally separate from the SQL command SET
1013 [set(7)].
1014 \t Toggles the display of output column name headings and row count
1017 footer. This command is equivalent to \pset tuples_only and is
1018 provided for convenience.
1019 \T table_options
1021 Allows you to specify attributes to be placed within the table
1022 tag in HTML tabular output mode. This command is equivalent to
1023 \pset tableattr table_options.
1024 \timing [ on | off ]
1026 Without parameter, toggles a display of how long each SQL state‐
1027 ment takes, in milliseconds. With parameter, sets same.
1028 \w {filename | |command}
1030 Outputs the current query buffer to the file filename or pipes
1031 it to the Unix command command.
1032 \x Toggles expanded table formatting mode. As such it is equivalent
1034 to \pset expanded.
1035 \z [ pattern ]
1037 Produces a list of all available tables, views and sequences
1038 with their associated access privileges. If a pattern is speci‐
1039 fied, only tables,views and sequences whose names match the pat‐
1040 tern are listed.
1041 The GRANT [grant(7)] and REVOKE [revoke(7)] commands are used to
1043 set access privileges.
1044 This is an alias for \dp (``display privileges'').
1046 \! [ command ]
1048 Escapes to a separate Unix shell or executes the Unix command
1049 command. The arguments are not further interpreted, the shell
1050 will see them as is.
1051 \? Shows help information about the backslash commands.
1053 PATTERNS
1055 The various \d commands accept a pattern parameter to specify the
1056 object name(s) to be displayed. In the simplest case, a pattern is just
1057 the exact name of the object. The characters within a pattern are nor‐
1058 mally folded to lower case, just as in SQL names; for example, \dt FOO
1059 will display the table named foo. As in SQL names, placing double
1060 quotes around a pattern stops folding to lower case. Should you need to
1061 include an actual double quote character in a pattern, write it as a
1062 pair of double quotes within a double-quote sequence; again this is in
1063 accord with the rules for SQL quoted identifiers. For example, \dt
1064 "FOO""BAR" will display the table named FOO"BAR (not foo"bar). Unlike
1065 the normal rules for SQL names, you can put double quotes around just
1066 part of a pattern, for instance \dt FOO"FOO"BAR will display the table
1067 named fooFOObar.
1068 Within a pattern, * matches any sequence of characters (including no
1070 characters) and ? matches any single character. (This notation is com‐
1071 parable to Unix shell file name patterns.) For example, \dt int* dis‐
1072 plays all tables whose names begin with int. But within double quotes,
1073 * and ? lose these special meanings and are just matched literally.
1074 A pattern that contains a dot (.) is interpreted as a schema name pat‐
1076 tern followed by an object name pattern. For example, \dt foo*.*bar*
1077 displays all tables whose table name includes bar that are in schemas
1078 whose schema name starts with foo. When no dot appears, then the pat‐
1079 tern matches only objects that are visible in the current schema search
1080 path. Again, a dot within double quotes loses its special meaning and
1081 is matched literally.
1082 Advanced users can use regular-expression notations such as character
1084 classes, for example [0-9] to match any digit. All regular expression
1085 special characters work as specified in in the documentation, except
1086 for . which is taken as a separator as mentioned above, * which is
1087 translated to the regular-expression notation .*, ? which is translated
1088 to ., and $ which is matched literally. You can emulate these pattern
1089 characters at need by writing ? for ., (R+|) for R*, or (R|) for R?. $
1090 is not needed as a regular-expression character since the pattern must
1091 match the whole name, unlike the usual interpretation of regular
1092 expressions (in other words, $ is automatically appended to your pat‐
1093 tern). Write * at the beginning and/or end if you don't wish the pat‐
1094 tern to be anchored. Note that within double quotes, all regular
1095 expression special characters lose their special meanings and are
1096 matched literally. Also, the regular expression special characters are
1097 matched literally in operator name patterns (i.e., the argument of
1098 \do).
1099 Whenever the pattern parameter is omitted completely, the \d commands
1101 display all objects that are visible in the current schema search path
1102 — this is equivalent to using the pattern *. To see all objects in the
1103 database, use the pattern *.*.
1104 ADVANCED FEATURES
1106 VARIABLES
1107 psql provides variable substitution features similar to common Unix
1108 command shells. Variables are simply name/value pairs, where the value
1109 can be any string of any length. To set variables, use the psql meta-
1110 command \set:
1111 testdb=> \set foo bar
1113 sets the variable foo to the value bar. To retrieve the content of the
1115 variable, precede the name with a colon and use it as the argument of
1116 any slash command:
1117 testdb=> \echo :foo
1119 bar
1120 Note: The arguments of \set are subject to the same substitution
1123 rules as with other commands. Thus you can construct interesting
1124 references such as \set :foo 'something' and get ``soft links''
1125 or ``variable variables'' of Perl or PHP fame, respectively.
1126 Unfortunately (or fortunately?), there is no way to do anything
1127 useful with these constructs. On the other hand, \set bar :foo
1128 is a perfectly valid way to copy a variable.
1129 If you call \set without a second argument, the variable is set, with
1132 an empty string as value. To unset (or delete) a variable, use the com‐
1133 mand \unset.
1134 psql's internal variable names can consist of letters, numbers, and
1136 underscores in any order and any number of them. A number of these
1137 variables are treated specially by psql. They indicate certain option
1138 settings that can be changed at run time by altering the value of the
1139 variable or represent some state of the application. Although you can
1140 use these variables for any other purpose, this is not recommended, as
1141 the program behavior might grow really strange really quickly. By con‐
1142 vention, all specially treated variables consist of all upper-case let‐
1143 ters (and possibly numbers and underscores). To ensure maximum compati‐
1144 bility in the future, avoid using such variable names for your own pur‐
1145 poses. A list of all specially treated variables follows.
1146 AUTOCOMMIT
1149 When on (the default), each SQL command is automatically commit‐
1150 ted upon successful completion. To postpone commit in this mode,
1151 you must enter a BEGIN or START TRANSACTION SQL command. When
1152 off or unset, SQL commands are not committed until you explic‐
1153 itly issue COMMIT or END. The autocommit-off mode works by issu‐
1154 ing an implicit BEGIN for you, just before any command that is
1155 not already in a transaction block and is not itself a BEGIN or
1156 other transaction-control command, nor a command that cannot be
1157 executed inside a transaction block (such as VACUUM).
1158 Note: In autocommit-off mode, you must explicitly abandon any
1160 failed transaction by entering ABORT or ROLLBACK. Also keep in
1161 mind that if you exit the session without committing, your work
1162 will be lost.
1163 Note: The autocommit-on mode is PostgreSQL's traditional behav‐
1166 ior, but autocommit-off is closer to the SQL spec. If you prefer
1167 autocommit-off, you might wish to set it in the system-wide
1168 psqlrc file or your ~/.psqlrc file.
1169 DBNAME The name of the database you are currently connected to. This is
1172 set every time you connect to a database (including program
1173 start-up), but can be unset.
1174 ECHO If set to all, all lines entered from the keyboard or from a
1176 script are written to the standard output before they are parsed
1177 or executed. To select this behavior on program start-up, use
1178 the switch -a. If set to queries, psql merely prints all queries
1179 as they are sent to the server. The switch for this is -e.
1180 ECHO_HIDDEN
1182 When this variable is set and a backslash command queries the
1183 database, the query is first shown. This way you can study the
1184 PostgreSQL internals and provide similar functionality in your
1185 own programs. (To select this behavior on program start-up, use
1186 the switch -E.) If you set the variable to the value noexec, the
1187 queries are just shown but are not actually sent to the server
1188 and executed.
1189 ENCODING
1191 The current client character set encoding.
1192 FETCH_COUNT
1194 If this variable is set to an integer value > 0, the results of
1195 SELECT queries are fetched and displayed in groups of that many
1196 rows, rather than the default behavior of collecting the entire
1197 result set before display. Therefore only a limited amount of
1198 memory is used, regardless of the size of the result set. Set‐
1199 tings of 100 to 1000 are commonly used when enabling this fea‐
1200 ture. Keep in mind that when using this feature, a query might
1201 fail after having already displayed some rows.
1202 Tip: Although you can use any output format with this feature,
1204 the default aligned format tends to look bad because each group
1205 of FETCH_COUNT rows will be formatted separately, leading to
1206 varying column widths across the row groups. The other output
1207 formats work better.
1208 HISTCONTROL
1211 If this variable is set to ignorespace, lines which begin with a
1212 space are not entered into the history list. If set to a value
1213 of ignoredups, lines matching the previous history line are not
1214 entered. A value of ignoreboth combines the two options. If
1215 unset, or if set to any other value than those above, all lines
1216 read in interactive mode are saved on the history list.
1217 Note: This feature was shamelessly plagiarized from Bash.
1219 HISTFILE
1222 The file name that will be used to store the history list. The
1223 default value is ~/.psql_history. For example, putting:
1224 \set HISTFILE ~/.psql_history- :DBNAME
1226 in ~/.psqlrc will cause psql to maintain a separate history for
1228 each database.
1229 Note: This feature was shamelessly plagiarized from Bash.
1231 HISTSIZE
1234 The number of commands to store in the command history. The
1235 default value is 500.
1236 Note: This feature was shamelessly plagiarized from Bash.
1238 HOST The database server host you are currently connected to. This is
1241 set every time you connect to a database (including program
1242 start-up), but can be unset.
1243 IGNOREEOF
1245 If unset, sending an EOF character (usually Control+D) to an
1246 interactive session of psql will terminate the application. If
1247 set to a numeric value, that many EOF characters are ignored
1248 before the application terminates. If the variable is set but
1249 has no numeric value, the default is 10.
1250 Note: This feature was shamelessly plagiarized from Bash.
1252 LASTOID
1255 The value of the last affected OID, as returned from an INSERT
1256 or lo_insert command. This variable is only guaranteed to be
1257 valid until after the result of the next SQL command has been
1258 displayed.
1259 ON_ERROR_ROLLBACK
1262 When on, if a statement in a transaction block generates an
1263 error, the error is ignored and the transaction continues. When
1264 interactive, such errors are only ignored in interactive ses‐
1265 sions, and not when reading script files. When off (the
1266 default), a statement in a transaction block that generates an
1267 error aborts the entire transaction. The on_error_rollback-on
1268 mode works by issuing an implicit SAVEPOINT for you, just before
1269 each command that is in a transaction block, and rolls back to
1270 the savepoint on error.
1271 ON_ERROR_STOP
1273 By default, if non-interactive scripts encounter an error, such
1274 as a malformed SQL command or internal meta-command, processing
1275 continues. This has been the traditional behavior of psql but it
1276 is sometimes not desirable. If this variable is set, script pro‐
1277 cessing will immediately terminate. If the script was called
1278 from another script it will terminate in the same fashion. If
1279 the outermost script was not called from an interactive psql
1280 session but rather using the -f option, psql will return error
1281 code 3, to distinguish this case from fatal error conditions
1282 (error code 1).
1283 PORT The database server port to which you are currently connected.
1285 This is set every time you connect to a database (including pro‐
1286 gram start-up), but can be unset.
1287 PROMPT1
1289 PROMPT2
1291 PROMPT3
1293 These specify what the prompts psql issues should look like. See
1294 Prompting [psql(1)] below.
1295 QUIET This variable is equivalent to the command line option -q. It is
1297 probably not too useful in interactive mode.
1298 SINGLELINE
1300 This variable is equivalent to the command line option -S.
1301 SINGLESTEP
1303 This variable is equivalent to the command line option -s.
1304 USER The database user you are currently connected as. This is set
1306 every time you connect to a database (including program start-
1307 up), but can be unset.
1308 VERBOSITY
1310 This variable can be set to the values default, verbose, or
1311 terse to control the verbosity of error reports.
1312 SQL INTERPOLATION
1314 An additional useful feature of psql variables is that you can substi‐
1315 tute (``interpolate'') them into regular SQL statements. The syntax for
1316 this is again to prepend the variable name with a colon (:):
1317 testdb=> \set foo 'my_table'
1319 testdb=> SELECT * FROM :foo;
1320 would then query the table my_table. The value of the variable is
1322 copied literally, so it can even contain unbalanced quotes or backslash
1323 commands. You must make sure that it makes sense where you put it.
1324 Variable interpolation will not be performed into quoted SQL entities.
1325 One possible use of this mechanism is to copy the contents of a file
1327 into a table column. First load the file into a variable and then pro‐
1328 ceed as above:
1329 testdb=> \set content '''' `cat my_file.txt` ''''
1331 testdb=> INSERT INTO my_table VALUES (:content);
1332 One problem with this approach is that my_file.txt might contain single
1334 quotes. These need to be escaped so that they don't cause a syntax
1335 error when the second line is processed. This could be done with the
1336 program sed:
1337 testdb=> \set content '''' `sed -e "s/'/''/g" < my_file.txt` ''''
1339 If you are using non-standard-conforming strings then you'll also need
1341 to double backslashes. This is a bit tricky:
1342 testdb=> \set content '''' `sed -e "s/'/''/g" -e 's/\\/\\\\/g' < my_file.txt` ''''
1344 Note the use of different shell quoting conventions so that neither the
1346 single quote marks nor the backslashes are special to the shell. Back‐
1347 slashes are still special to sed, however, so we need to double them.
1348 (Perhaps at one point you thought it was great that all Unix commands
1349 use the same escape character.)
1350 Since colons can legally appear in SQL commands, the following rule
1352 applies: the character sequence ``:name'' is not changed unless
1353 ``name'' is the name of a variable that is currently set. In any case
1354 you can escape a colon with a backslash to protect it from substitu‐
1355 tion. (The colon syntax for variables is standard SQL for embedded
1356 query languages, such as ECPG. The colon syntax for array slices and
1357 type casts are PostgreSQL extensions, hence the conflict.)
1358 PROMPTING
1360 The prompts psql issues can be customized to your preference. The three
1361 variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special
1362 escape sequences that describe the appearance of the prompt. Prompt 1
1363 is the normal prompt that is issued when psql requests a new command.
1364 Prompt 2 is issued when more input is expected during command input
1365 because the command was not terminated with a semicolon or a quote was
1366 not closed. Prompt 3 is issued when you run an SQL COPY command and
1367 you are expected to type in the row values on the terminal.
1368 The value of the selected prompt variable is printed literally, except
1370 where a percent sign (%) is encountered. Depending on the next charac‐
1371 ter, certain other text is substituted instead. Defined substitutions
1372 are:
1373 %M The full host name (with domain name) of the database server, or
1375 [local] if the connection is over a Unix domain socket, or
1376 [local:/dir/name], if the Unix domain socket is not at the com‐
1377 piled in default location.
1378 %m The host name of the database server, truncated at the first
1380 dot, or [local] if the connection is over a Unix domain socket.
1381 %> The port number at which the database server is listening.
1383 %n The database session user name. (The expansion of this value
1385 might change during a database session as the result of the com‐
1386 mand SET SESSION AUTHORIZATION.)
1387 %/ The name of the current database.
1389 %~ Like %/, but the output is ~ (tilde) if the database is your
1391 default database.
1392 %# If the session user is a database superuser, then a #, otherwise
1394 a >. (The expansion of this value might change during a data‐
1395 base session as the result of the command SET SESSION AUTHORIZA‐
1396 TION.)
1397 %R In prompt 1 normally =, but ^ if in single-line mode, and ! if
1399 the session is disconnected from the database (which can happen
1400 if \connect fails). In prompt 2 the sequence is replaced by -,
1401 *, a single quote, a double quote, or a dollar sign, depending
1402 on whether psql expects more input because the command wasn't
1403 terminated yet, because you are inside a /* ... */ comment, or
1404 because you are inside a quoted or dollar-escaped string. In
1405 prompt 3 the sequence doesn't produce anything.
1406 %x Transaction status: an empty string when not in a transaction
1408 block, or * when in a transaction block, or ! when in a failed
1409 transaction block, or ? when the transaction state is indeter‐
1410 minate (for example, because there is no connection).
1411 %digits
1413 The character with the indicated octal code is substituted.
1414 %:name:
1416 The value of the psql variable name. See the section Variables
1417 [psql(1)] for details.
1418 %`command`
1420 The output of command, similar to ordinary ``back-tick'' substi‐
1421 tution.
1422 %[ ... %]
1424 Prompts can contain terminal control characters which, for exam‐
1425 ple, change the color, background, or style of the prompt text,
1426 or change the title of the terminal window. In order for the
1427 line editing features of Readline to work properly, these non-
1428 printing control characters must be designated as invisible by
1429 surrounding them with %[ and %]. Multiple pairs of these can
1430 occur within the prompt. For example:
1431 testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
1433 results in a boldfaced (1;) yellow-on-black (33;40) prompt on
1435 VT100-compatible, color-capable terminals.
1436 To insert a percent sign into your prompt, write %%. The default
1438 prompts are '%/%R%# ' for prompts 1 and 2, and '>> ' for prompt 3.
1439 Note: This feature was shamelessly plagiarized from tcsh.
1441 COMMAND-LINE EDITING
1444 psql supports the Readline library for convenient line editing and
1445 retrieval. The command history is automatically saved when psql exits
1446 and is reloaded when psql starts up. Tab-completion is also supported,
1447 although the completion logic makes no claim to be an SQL parser. If
1448 for some reason you do not like the tab completion, you can turn it off
1449 by putting this in a file named .inputrc in your home directory:
1450 $if psql
1452 set disable-completion on
1453 $endif
1454 (This is not a psql but a Readline feature. Read its documentation for
1456 further details.)
1457
1459 COLUMNS
1460 If \pset columns is zero, controls the width for the wrapped
1461 format and width for determining if wide output requires the
1462 pager.
1463 PAGER If the query results do not fit on the screen, they are piped
1465 through this command. Typical values are more or less. The
1466 default is platform-dependent. The use of the pager can be dis‐
1467 abled by using the \pset command.
1468 PGDATABASE
1470 PGHOST
1472 PGPORT
1474 PGUSER Default connection parameters (see in the documentation).
1476 PSQL_EDITOR
1478 EDITOR
1480 VISUAL Editor used by the \e command. The variables are examined in the
1482 order listed; the first that is set is used.
1483 SHELL Command executed by the \! command.
1485 TMPDIR Directory for storing temporary files. The default is /tmp.
1487 This utility, like most other PostgreSQL utilities, also uses the envi‐
1489 ronment variables supported by libpq (see in the documentation).
1490
1492 · Before starting up, psql attempts to read and execute commands from
1493 the system-wide psqlrc file and the user's ~/.psqlrc file. (On Win‐
1494 dows, the user's startup file is named %APPDATA%\post‐
1495 gresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information
1496 on setting up the system-wide file. It could be used to set up the
1497 client or the server to taste (using the \set and SET commands).
1498 · Both the system-wide psqlrc file and the user's ~/.psqlrc file can be
1500 made version-specific by appending a dash and the PostgreSQL release
1501 number, for example ~/.psqlrc-8.4.20. A matching version-specific
1502 file will be read in preference to a non-version-specific file.
1503 · The command-line history is stored in the file ~/.psql_history, or
1505 %APPDATA%\postgresql\psql_history on Windows.
1506
1508 · In an earlier life psql allowed the first argument of a single-letter
1509 backslash command to start directly after the command, without inter‐
1510 vening whitespace. As of PostgreSQL 8.4 this is no longer allowed.
1511 · psql is only guaranteed to work smoothly with servers of the same
1513 version. That does not mean other combinations will fail outright,
1514 but subtle and not-so-subtle problems might come up. Backslash com‐
1515 mands are particularly likely to fail if the server is of a newer
1516 version than psql itself. However, backslash commands of the \d fam‐
1517 ily should work with servers of versions back to 7.4, though not nec‐
1518 essarily with servers newer than psql itself.
1519
1521 psql is built as a ``console application''. Since the Windows console
1522 windows use a different encoding than the rest of the system, you must
1523 take special care when using 8-bit characters within psql. If psql
1524 detects a problematic console code page, it will warn you at startup.
1525 To change the console code page, two things are necessary:
1526 · Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code
1528 page that is appropriate for German; replace it with your value.) If
1529 you are using Cygwin, you can put this command in /etc/profile.
1530 · Set the console font to Lucida Console, because the raster font does
1532 not work with the ANSI code page.
1533
1535 The first example shows how to spread a command over several lines of
1536 input. Notice the changing prompt:
1537 testdb=> CREATE TABLE my_table (
1539 testdb(> first integer not null default 0,
1540 testdb(> second text)
1541 testdb-> ;
1542 CREATE TABLE
1543 Now look at the table definition again:
1545 testdb=> \d my_table
1547 Table "my_table"
1548 Attribute | Type | Modifier
1549 -----------+---------+--------------------
1550 first | integer | not null default 0
1551 second | text |
1552 Now we change the prompt to something more interesting:
1554 testdb=> \set PROMPT1 '%n@%m %~%R%# '
1556 peter@localhost testdb=>
1557 Let's assume you have filled the table with data and want to take a
1559 look at it:
1560 peter@localhost testdb=> SELECT * FROM my_table;
1562 first | second
1563 -------+--------
1564 1 | one
1565 2 | two
1566 3 | three
1567 4 | four
1568 (4 rows)
1569 You can display tables in different ways by using the \pset command:
1571 peter@localhost testdb=> \pset border 2
1573 Border style is 2.
1574 peter@localhost testdb=> SELECT * FROM my_table;
1575 +-------+--------+
1576 | first | second |
1577 +-------+--------+
1578 | 1 | one |
1579 | 2 | two |
1580 | 3 | three |
1581 | 4 | four |
1582 +-------+--------+
1583 (4 rows)
1584 peter@localhost testdb=> \pset border 0
1586 Border style is 0.
1587 peter@localhost testdb=> SELECT * FROM my_table;
1588 first second
1589 ----- ------
1590 1 one
1591 2 two
1592 3 three
1593 4 four
1594 (4 rows)
1595 peter@localhost testdb=> \pset border 1
1597 Border style is 1.
1598 peter@localhost testdb=> \pset format unaligned
1599 Output format is unaligned.
1600 peter@localhost testdb=> \pset fieldsep ","
1601 Field separator is ",".
1602 peter@localhost testdb=> \pset tuples_only
1603 Showing only tuples.
1604 peter@localhost testdb=> SELECT second, first FROM my_table;
1605 one,1
1606 two,2
1607 three,3
1608 four,4
1609 Alternatively, use the short commands:
1611 peter@localhost testdb=> \a \t \x
1613 Output format is aligned.
1614 Tuples only is off.
1615 Expanded display is on.
1616 peter@localhost testdb=> SELECT * FROM my_table;
1617 -[ RECORD 1 ]-
1618 first | 1
1619 second | one
1620 -[ RECORD 2 ]-
1621 first | 2
1622 second | two
1623 -[ RECORD 3 ]-
1624 first | 3
1625 second | three
1626 -[ RECORD 4 ]-
1627 first | 4
1628 second | four
1629Application 2014-02-17 PSQL(1)