2 $PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.210 2008/09/06 00:01:21 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="APP-PSQL">
8 <refentrytitle id="app-psql-title"><application>psql</application></refentrytitle>
9 <manvolnum>1</manvolnum>
10 <refmiscinfo>Application</refmiscinfo>
14 <refname><application>psql</application></refname>
16 <productname>PostgreSQL</productname> interactive terminal
20 <indexterm zone="app-psql">
21 <primary>psql</primary>
26 <command>psql</command>
27 <arg rep="repeat"><replaceable class="parameter">option</replaceable></arg>
28 <arg><replaceable class="parameter">dbname</replaceable>
29 <arg><replaceable class="parameter">username</replaceable></arg></arg>
34 <title>Description</title>
37 <application>psql</application> is a terminal-based front-end to
38 <productname>PostgreSQL</productname>. It enables you to type in
39 queries interactively, issue them to
40 <productname>PostgreSQL</productname>, and see the query results.
41 Alternatively, input can be from a file. In addition, it provides a
42 number of meta-commands and various shell-like features to
43 facilitate writing scripts and automating a wide variety of tasks.
47 <refsect1 id="R1-APP-PSQL-3">
48 <title>Options</title>
52 <term><option>-a</></term>
53 <term><option>--echo-all</></term>
56 Print all input lines to standard output as they are read. This is more
57 useful for script processing rather than interactive mode. This is
58 equivalent to setting the variable <varname>ECHO</varname> to
59 <literal>all</literal>.
65 <term><option>-A</></term>
66 <term><option>--no-align</></term>
69 Switches to unaligned output mode. (The default output mode is
76 <term><option>-c <replaceable class="parameter">command</replaceable></></term>
77 <term><option>--command <replaceable class="parameter">command</replaceable></></term>
80 Specifies that <application>psql</application> is to execute one
81 command string, <replaceable class="parameter">command</replaceable>,
82 and then exit. This is useful in shell scripts.
85 <replaceable class="parameter">command</replaceable> must be either
86 a command string that is completely parsable by the server (i.e.,
87 it contains no <application>psql</application> specific features),
88 or a single backslash command. Thus you cannot mix
89 <acronym>SQL</acronym> and <application>psql</application>
90 meta-commands with this option. To achieve that, you could
91 pipe the string into <application>psql</application>, like
92 this: <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>.
93 (<literal>\\</> is the separator meta-command.)
96 If the command string contains multiple SQL commands, they are
97 processed in a single transaction, unless there are explicit
98 <command>BEGIN</>/<command>COMMIT</> commands included in the
99 string to divide it into multiple transactions. This is
100 different from the behavior when the same string is fed to
101 <application>psql</application>'s standard input.
107 <term><option>-d <replaceable class="parameter">dbname</replaceable></></term>
108 <term><option>--dbname <replaceable class="parameter">dbname</replaceable></></term>
111 Specifies the name of the database to connect to. This is
112 equivalent to specifying <replaceable
113 class="parameter">dbname</replaceable> as the first non-option
114 argument on the command line.
117 If this parameter contains an <symbol>=</symbol> sign, it is treated as a
118 <parameter>conninfo</parameter> string. See <xref linkend="libpq-connect"> for more information.
124 <term><option>-e</></term>
125 <term><option>--echo-queries</></term>
128 Copy all SQL commands sent to the server to standard output as well.
130 to setting the variable <varname>ECHO</varname> to
131 <literal>queries</literal>.
137 <term><option>-E</></term>
138 <term><option>--echo-hidden</></term>
141 Echo the actual queries generated by <command>\d</command> and other backslash
142 commands. You can use this to study <application>psql</application>'s
143 internal operations. This is equivalent to
144 setting the variable <varname>ECHO_HIDDEN</varname> from within
145 <application>psql</application>.
151 <term><option>-f <replaceable class="parameter">filename</replaceable></></term>
152 <term><option>--file <replaceable class="parameter">filename</replaceable></></term>
155 Use the file <replaceable class="parameter">filename</replaceable>
156 as the source of commands instead of reading commands interactively.
157 After the file is processed, <application>psql</application>
158 terminates. This is in many ways equivalent to the internal
159 command <command>\i</command>.
163 If <replaceable>filename</replaceable> is <literal>-</literal>
164 (hyphen), then standard input is read.
168 Using this option is subtly different from writing <literal>psql
170 class="parameter">filename</replaceable></literal>. In general,
171 both will do what you expect, but using <literal>-f</literal>
172 enables some nice features such as error messages with line
173 numbers. There is also a slight chance that using this option will
174 reduce the start-up overhead. On the other hand, the variant using
175 the shell's input redirection is (in theory) guaranteed to yield
176 exactly the same output that you would have gotten had you entered
183 <term><option>-F <replaceable class="parameter">separator</replaceable></></term>
184 <term><option>--field-separator <replaceable class="parameter">separator</replaceable></></term>
187 Use <replaceable class="parameter">separator</replaceable> as the
188 field separator for unaligned output. This is equivalent to
189 <command>\pset fieldsep</command> or <command>\f</command>.
195 <term><option>-h <replaceable class="parameter">hostname</replaceable></></term>
196 <term><option>--host <replaceable class="parameter">hostname</replaceable></></term>
199 Specifies the host name of the machine on which the
200 server is running. If the value begins
201 with a slash, it is used as the directory for the Unix-domain
208 <term><option>-H</></term>
209 <term><option>--html</></term>
212 Turn on <acronym>HTML</acronym> tabular output. This is
213 equivalent to <literal>\pset format html</literal> or the
214 <command>\H</command> command.
220 <term><option>-l</></term>
221 <term><option>--list</></term>
224 List all available databases, then exit. Other non-connection
225 options are ignored. This is similar to the internal command
226 <command>\list</command>.
232 <term><option>-L <replaceable class="parameter">filename</replaceable></></term>
233 <term><option>--log-file <replaceable class="parameter">filename</replaceable></></term>
236 Write all query output into file <replaceable
237 class="parameter">filename</replaceable>, in addition to the
238 normal output destination.
244 <term><option>-o <replaceable class="parameter">filename</replaceable></></term>
245 <term><option>--output <replaceable class="parameter">filename</replaceable></></term>
248 Put all query output into file <replaceable
249 class="parameter">filename</replaceable>. This is equivalent to
250 the command <command>\o</command>.
256 <term><option>-p <replaceable class="parameter">port</replaceable></></term>
257 <term><option>--port <replaceable class="parameter">port</replaceable></></term>
260 Specifies the TCP port or the local Unix-domain
261 socket file extension on which the server is listening for
262 connections. Defaults to the value of the <envar>PGPORT</envar>
263 environment variable or, if not set, to the port specified at
264 compile time, usually 5432.
270 <term><option>-P <replaceable class="parameter">assignment</replaceable></></term>
271 <term><option>--pset <replaceable class="parameter">assignment</replaceable></></term>
274 Allows you to specify printing options in the style of
275 <command>\pset</command> on the command line. Note that here you
276 have to separate name and value with an equal sign instead of a
277 space. Thus to set the output format to LaTeX, you could write
278 <literal>-P format=latex</literal>.
284 <term><option>-q</></term>
285 <term><option>--quiet</></term>
288 Specifies that <application>psql</application> should do its work
289 quietly. By default, it prints welcome messages and various
290 informational output. If this option is used, none of this
291 happens. This is useful with the <option>-c</option> option.
292 Within <application>psql</application> you can also set the
293 <varname>QUIET</varname> variable to achieve the same effect.
299 <term><option>-R <replaceable class="parameter">separator</replaceable></></term>
300 <term><option>--record-separator <replaceable class="parameter">separator</replaceable></></term>
303 Use <replaceable class="parameter">separator</replaceable> as the
304 record separator for unaligned output. This is equivalent to the
305 <command>\pset recordsep</command> command.
311 <term><option>-s</></term>
312 <term><option>--single-step</></term>
315 Run in single-step mode. That means the user is prompted before
316 each command is sent to the server, with the option to cancel
317 execution as well. Use this to debug scripts.
323 <term><option>-S</></term>
324 <term><option>--single-line</></term>
327 Runs in single-line mode where a newline terminates an SQL command, as a
333 This mode is provided for those who insist on it, but you are not
334 necessarily encouraged to use it. In particular, if you mix
335 <acronym>SQL</acronym> and meta-commands on a line the order of
336 execution might not always be clear to the inexperienced user.
343 <term><option>-t</></term>
344 <term><option>--tuples-only</></term>
347 Turn off printing of column names and result row count footers,
348 etc. This is equivalent to the <command>\t</command> command.
354 <term><option>-T <replaceable class="parameter">table_options</replaceable></></term>
355 <term><option>--table-attr <replaceable class="parameter">table_options</replaceable></></term>
358 Allows you to specify options to be placed within the
359 <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. See
360 <command>\pset</command> for details.
366 <term><option>-U <replaceable class="parameter">username</replaceable></></term>
367 <term><option>--username <replaceable class="parameter">username</replaceable></></term>
370 Connect to the database as the user <replaceable
371 class="parameter">username</replaceable> instead of the default.
372 (You must have permission to do so, of course.)
378 <term><option>-v <replaceable class="parameter">assignment</replaceable></></term>
379 <term><option>--set <replaceable class="parameter">assignment</replaceable></></term>
380 <term><option>--variable <replaceable class="parameter">assignment</replaceable></></term>
383 Perform a variable assignment, like the <command>\set</command>
384 internal command. Note that you must separate name and value, if
385 any, by an equal sign on the command line. To unset a variable,
386 leave off the equal sign. To just set a variable without a value,
387 use the equal sign but leave off the value. These assignments are
388 done during a very early stage of start-up, so variables reserved
389 for internal purposes might get overwritten later.
395 <term><option>-V</></term>
396 <term><option>--version</></term>
399 Print the <application>psql</application> version and exit.
405 <term><option>-W</></term>
406 <term><option>--password</></term>
409 Force <application>psql</application> to prompt for a
410 password before connecting to a database.
414 This option is never essential, since <application>psql</application>
415 will automatically prompt for a password if the server demands
416 password authentication. However, <application>psql</application>
417 will waste a connection attempt finding out that the server wants a
418 password. In some cases it is worth typing <option>-W</> to avoid
419 the extra connection attempt.
423 Note that this option will remain set for the entire session,
424 and so it affects uses of the meta-command
425 <command>\connect</command> as well as the initial connection attempt.
431 <term><option>-x</></term>
432 <term><option>--expanded</></term>
435 Turn on the expanded table formatting mode. This is equivalent to the
436 <command>\x</command> command.
442 <term><option>-X,</></term>
443 <term><option>--no-psqlrc</></term>
446 Do not read the start-up file (neither the system-wide
447 <filename>psqlrc</filename> file nor the user's
448 <filename>~/.psqlrc</filename> file).
454 <term><option>-1</option></term>
455 <term><option>--single-transaction</option></term>
458 When <application>psql</application> executes a script with the
459 <option>-f</> option, adding this option wraps
460 <command>BEGIN</>/<command>COMMIT</> around the script to execute it
461 as a single transaction. This ensures that either all the commands
462 complete successfully, or no changes are applied.
466 If the script itself uses <command>BEGIN</>, <command>COMMIT</>,
467 or <command>ROLLBACK</>, this option will not have the desired
469 Also, if the script contains any command that cannot be executed
470 inside a transaction block, specifying this option will cause that
471 command (and hence the whole transaction) to fail.
477 <term><option>-?</></term>
478 <term><option>--help</></term>
481 Show help about <application>psql</application> command line
491 <title>Exit Status</title>
494 <application>psql</application> returns 0 to the shell if it
495 finished normally, 1 if a fatal error of its own (out of memory,
496 file not found) occurs, 2 if the connection to the server went bad
497 and the session was not interactive, and 3 if an error occurred in a
498 script and the variable <varname>ON_ERROR_STOP</varname> was set.
506 <refsect2 id="R2-APP-PSQL-connecting">
507 <title>Connecting To A Database</title>
510 <application>psql</application> is a regular
511 <productname>PostgreSQL</productname> client application. In order
512 to connect to a database you need to know the name of your target
513 database, the host name and port number of the server and what user
514 name you want to connect as. <application>psql</application> can be
515 told about those parameters via command line options, namely
516 <option>-d</option>, <option>-h</option>, <option>-p</option>, and
517 <option>-U</option> respectively. If an argument is found that does
518 not belong to any option it will be interpreted as the database name
519 (or the user name, if the database name is already given). Not all
520 these options are required; there are useful defaults. If you omit the host
521 name, <application>psql</> will connect via a Unix-domain socket
522 to a server on the local host, or via TCP/IP to <literal>localhost</> on
523 machines that don't have Unix-domain sockets. The default port number is
524 determined at compile time.
525 Since the database server uses the same default, you will not have
526 to specify the port in most cases. The default user name is your
527 Unix user name, as is the default database name. Note that you cannot
528 just connect to any database under any user name. Your database
529 administrator should have informed you about your access rights.
533 When the defaults aren't quite right, you can save yourself
534 some typing by setting the environment variables
535 <envar>PGDATABASE</envar>, <envar>PGHOST</envar>,
536 <envar>PGPORT</envar> and/or <envar>PGUSER</envar> to appropriate
537 values. (For additional environment variables, see <xref
538 linkend="libpq-envars">.) It is also convenient to have a
539 <filename>~/.pgpass</> file to avoid regularly having to type in
540 passwords. See <xref linkend="libpq-pgpass"> for more information.
544 An alternative way to specify connection parameters is in a
545 <parameter>conninfo</parameter> string, which is used instead of a
546 database name. This mechanism give you very wide control over the
547 connection. For example:
549 $ <userinput>psql "service=myservice sslmode=require"</userinput>
551 This way you can also use LDAP for connection parameter lookup as
552 described in <xref linkend="libpq-ldap">.
553 See <xref linkend="libpq-connect"> for more information on all the
554 available connection options.
558 If the connection could not be made for any reason (e.g., insufficient
559 privileges, server is not running on the targeted host, etc.),
560 <application>psql</application> will return an error and terminate.
564 <refsect2 id="R2-APP-PSQL-4">
565 <title>Entering SQL Commands</title>
568 In normal operation, <application>psql</application> provides a
569 prompt with the name of the database to which
570 <application>psql</application> is currently connected, followed by
571 the string <literal>=></literal>. For example:
573 $ <userinput>psql testdb</userinput>
575 Type "help" for help.
584 At the prompt, the user can type in <acronym>SQL</acronym> commands.
585 Ordinarily, input lines are sent to the server when a
586 command-terminating semicolon is reached. An end of line does not
587 terminate a command. Thus commands can be spread over several lines for
588 clarity. If the command was sent and executed without error, the results
589 of the command are displayed on the screen.
593 Whenever a command is executed, <application>psql</application> also polls
594 for asynchronous notification events generated by
595 <xref linkend="SQL-LISTEN" endterm="SQL-LISTEN-title"> and
596 <xref linkend="SQL-NOTIFY" endterm="SQL-NOTIFY-title">.
601 <title>Meta-Commands</title>
604 Anything you enter in <application>psql</application> that begins
605 with an unquoted backslash is a <application>psql</application>
606 meta-command that is processed by <application>psql</application>
607 itself. These commands help make
608 <application>psql</application> more useful for administration or
609 scripting. Meta-commands are more commonly called slash or backslash
614 The format of a <application>psql</application> command is the backslash,
615 followed immediately by a command verb, then any arguments. The arguments
616 are separated from the command verb and each other by any number of
617 whitespace characters.
621 To include whitespace into an argument you can quote it with a
622 single quote. To include a single quote into such an argument,
623 use two single quotes. Anything contained in single quotes is
624 furthermore subject to C-like substitutions for
625 <literal>\n</literal> (new line), <literal>\t</literal> (tab),
626 <literal>\</literal><replaceable>digits</replaceable> (octal), and
627 <literal>\x</literal><replaceable>digits</replaceable> (hexadecimal).
631 If an unquoted argument begins with a colon (<literal>:</literal>),
632 it is taken as a <application>psql</> variable and the value of the
633 variable is used as the argument instead.
637 Arguments that are enclosed in backquotes (<literal>`</literal>)
638 are taken as a command line that is passed to the shell. The
639 output of the command (with any trailing newline removed) is taken
640 as the argument value. The above escape sequences also apply in
645 Some commands take an <acronym>SQL</acronym> identifier (such as a
646 table name) as argument. These arguments follow the syntax rules
647 of <acronym>SQL</acronym>: Unquoted letters are forced to
648 lowercase, while double quotes (<literal>"</>) protect letters
649 from case conversion and allow incorporation of whitespace into
650 the identifier. Within double quotes, paired double quotes reduce
651 to a single double quote in the resulting name. For example,
652 <literal>FOO"BAR"BAZ</> is interpreted as <literal>fooBARbaz</>,
653 and <literal>"A weird"" name"</> becomes <literal>A weird"
658 Parsing for arguments stops when another unquoted backslash occurs.
659 This is taken as the beginning of a new meta-command. The special
660 sequence <literal>\\</literal> (two backslashes) marks the end of
661 arguments and continues parsing <acronym>SQL</acronym> commands, if
662 any. That way <acronym>SQL</acronym> and
663 <application>psql</application> commands can be freely mixed on a
664 line. But in any case, the arguments of a meta-command cannot
665 continue beyond the end of the line.
669 The following meta-commands are defined:
673 <term><literal>\a</literal></term>
676 If the current table output format is unaligned, it is switched to aligned.
677 If it is not unaligned, it is set to unaligned. This command is
678 kept for backwards compatibility. See <command>\pset</command> for a
679 more general solution.
685 <term><literal>\cd [ <replaceable>directory</replaceable> ]</literal></term>
688 Changes the current working directory to
689 <replaceable>directory</replaceable>. Without argument, changes
690 to the current user's home directory.
695 To print your current working directory, use <literal>\!pwd</literal>.
702 <term><literal>\C [ <replaceable class="parameter">title</replaceable> ]</literal></term>
705 Sets the title of any tables being printed as the result of a
706 query or unset any such title. This command is equivalent to
707 <literal>\pset title <replaceable
708 class="parameter">title</replaceable></literal>. (The name of
709 this command derives from <quote>caption</quote>, as it was
710 previously only used to set the caption in an
711 <acronym>HTML</acronym> table.)
717 <term><literal>\connect</literal> (or <literal>\c</literal>) <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] ]</literal></term>
720 Establishes a new connection to a <productname>PostgreSQL</>
721 server. If the new connection is successfully made, the
722 previous connection is closed. If any of <replaceable
723 class="parameter">dbname</replaceable>, <replaceable
724 class="parameter">username</replaceable>, <replaceable
725 class="parameter">host</replaceable> or <replaceable
726 class="parameter">port</replaceable> are omitted or specified
727 as <literal>-</literal>, the value of that parameter from the
728 previous connection is used. If there is no previous
729 connection, the <application>libpq</application> default for
730 the parameter's value is used.
734 If the connection attempt failed (wrong user name, access
735 denied, etc.), the previous connection will only be kept if
736 <application>psql</application> is in interactive mode. When
737 executing a non-interactive script, processing will
738 immediately stop with an error. This distinction was chosen as
739 a user convenience against typos on the one hand, and a safety
740 mechanism that scripts are not accidentally acting on the
741 wrong database on the other hand.
747 <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
748 { <literal>from</literal> | <literal>to</literal> }
749 { <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
753 [ delimiter [ as ] '<replaceable class="parameter">character</replaceable>' ]
754 [ null [ as ] '<replaceable class="parameter">string</replaceable>' ]
757 [ quote [ as ] '<replaceable class="parameter">character</replaceable>' ]
758 [ escape [ as ] '<replaceable class="parameter">character</replaceable>' ]
759 [ force quote <replaceable class="parameter">column_list</replaceable> ]
760 [ force not null <replaceable class="parameter">column_list</replaceable> ] ]</literal>
765 Performs a frontend (client) copy. This is an operation that
766 runs an <acronym>SQL</acronym> <xref linkend="SQL-COPY"
767 endterm="SQL-COPY-title"> command, but instead of the server
768 reading or writing the specified file,
769 <application>psql</application> reads or writes the file and
770 routes the data between the server and the local file system.
771 This means that file accessibility and privileges are those of
772 the local user, not the server, and no SQL superuser
773 privileges are required.
777 The syntax of the command is similar to that of the
778 <acronym>SQL</acronym> <xref linkend="sql-copy"
779 endterm="sql-copy-title"> command. Note that, because of this,
780 special parsing rules apply to the <command>\copy</command>
781 command. In particular, the variable substitution rules and
782 backslash escapes do not apply.
786 <literal>\copy ... from stdin | to stdout</literal>
787 reads/writes based on the command input and output respectively.
788 All rows are read from the same source that issued the command,
789 continuing until <literal>\.</literal> is read or the stream
790 reaches <acronym>EOF</>. Output is sent to the same place as
791 command output. To read/write from
792 <application>psql</application>'s standard input or output, use
793 <literal>pstdin</> or <literal>pstdout</>. This option is useful
794 for populating tables in-line within a SQL script file.
799 This operation is not as efficient as the <acronym>SQL</acronym>
800 <command>COPY</command> command because all data must pass
801 through the client/server connection. For large
802 amounts of data the <acronym>SQL</acronym> command might be preferable.
810 <term><literal>\copyright</literal></term>
813 Shows the copyright and distribution terms of
814 <productname>PostgreSQL</productname>.
820 <term><literal>\d [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
821 <term><literal>\d+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
825 For each relation (table, view, index, or sequence) matching the
826 <replaceable class="parameter">pattern</replaceable>, show all
827 columns, their types, the tablespace (if not the default) and any special
828 attributes such as <literal>NOT NULL</literal> or defaults, if
829 any. Associated indexes, constraints, rules, and triggers are
830 also shown, as is the view definition if the relation is a view.
831 (<quote>Matching the pattern</> is defined below.)
835 The command form <literal>\d+</literal> is identical, except that
836 more information is displayed: any comments associated with the
837 columns of the table are shown, as is the presence of OIDs in the
843 If <command>\d</command> is used without a
844 <replaceable class="parameter">pattern</replaceable> argument, it is
845 equivalent to <command>\dtvs</command> which will show a list of
846 all tables, views, and sequences. This is purely a convenience
854 <term><literal>\da [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
858 Lists all available aggregate functions, together with their
859 return type and the data types they operate on. If <replaceable
860 class="parameter">pattern</replaceable>
861 is specified, only aggregates whose names match the pattern are shown.
868 <term><literal>\db [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
869 <term><literal>\db+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
873 Lists all available tablespaces. If <replaceable
874 class="parameter">pattern</replaceable>
875 is specified, only tablespaces whose names match the pattern are shown.
876 If <literal>+</literal> is appended to the command name, each object
877 is listed with its associated permissions.
884 <term><literal>\dc [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
887 Lists all available conversions between character-set encodings.
888 If <replaceable class="parameter">pattern</replaceable>
889 is specified, only conversions whose names match the pattern are
897 <term><literal>\dC</literal></term>
900 Lists all available type casts.
907 <term><literal>\dd [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
910 Shows the descriptions of objects matching the <replaceable
911 class="parameter">pattern</replaceable>, or of all visible objects if
912 no argument is given. But in either case, only objects that have
913 a description are listed.
914 (<quote>Object</quote> covers aggregates, functions, operators,
915 types, relations (tables, views, indexes, sequences, large
916 objects), rules, and triggers.) For example:
918 => <userinput>\dd version</userinput>
920 Schema | Name | Object | Description
921 ------------+---------+----------+---------------------------
922 pg_catalog | version | function | PostgreSQL version string
928 Descriptions for objects can be created with the <xref
929 linkend="sql-comment" endterm="sql-comment-title">
930 <acronym>SQL</acronym> command.
937 <term><literal>\dD [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
940 Lists all available domains. If <replaceable
941 class="parameter">pattern</replaceable>
942 is specified, only matching domains are shown.
949 <term><literal>\df [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
950 <term><literal>\df+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
954 Lists available functions, together with their argument and
955 return types. If <replaceable
956 class="parameter">pattern</replaceable>
957 is specified, only functions whose names match the pattern are shown.
958 If the form <literal>\df+</literal> is used, additional information about
959 each function, including volatility, language, source code and description, is shown.
964 To look up functions taking argument or returning values of a specific
965 type, use your pager's search capability to scroll through the <literal>\df</>
970 To reduce clutter, <literal>\df</> does not show data type I/O
971 functions. This is implemented by ignoring functions that accept
972 or return type <type>cstring</>.
981 <term><literal>\dF [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
982 <term><literal>\dF+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
985 Lists available text search configurations.
986 If <replaceable class="parameter">pattern</replaceable> is specified,
987 only configurations whose names match the pattern are shown.
988 If the form <literal>\dF+</literal> is used, a full description of
989 each configuration is shown, including the underlying text search
990 parser and the dictionary list for each parser token type.
996 <term><literal>\dFd [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
997 <term><literal>\dFd+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1000 Lists available text search dictionaries.
1001 If <replaceable class="parameter">pattern</replaceable> is specified,
1002 only dictionaries whose names match the pattern are shown.
1003 If the form <literal>\dFd+</literal> is used, additional information
1004 is shown about each selected dictionary, including the underlying
1005 text search template and the option values.
1011 <term><literal>\dFp [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1012 <term><literal>\dFp+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1015 Lists available text search parsers.
1016 If <replaceable class="parameter">pattern</replaceable> is specified,
1017 only parsers whose names match the pattern are shown.
1018 If the form <literal>\dFp+</literal> is used, a full description of
1019 each parser is shown, including the underlying functions and the
1020 list of recognized token types.
1026 <term><literal>\dFt [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1027 <term><literal>\dFt+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1030 Lists available text search templates.
1031 If <replaceable class="parameter">pattern</replaceable> is specified,
1032 only templates whose names match the pattern are shown.
1033 If the form <literal>\dFt+</literal> is used, additional information
1034 is shown about each template, including the underlying function names.
1041 <term><literal>\dg [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1044 Lists all database roles. If <replaceable
1045 class="parameter">pattern</replaceable> is specified, only
1046 those roles whose names match the pattern are listed.
1047 (This command is now effectively the same as <literal>\du</>.)
1054 <term><literal>\distvS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1058 This is not the actual command name: the letters
1059 <literal>i</literal>, <literal>s</literal>,
1060 <literal>t</literal>, <literal>v</literal>,
1061 <literal>S</literal> stand for index, sequence, table, view,
1062 and system table, respectively. You can specify any or all of
1063 these letters, in any order, to obtain a listing of all the
1064 matching objects. The letter <literal>S</literal> restricts
1065 the listing to system objects; without <literal>S</literal>,
1066 only non-system objects are shown. If <literal>+</literal> is
1067 appended to the command name, each object is listed with its
1068 physical size on disk and its associated description, if any.
1072 If <replaceable class="parameter">pattern</replaceable> is
1073 specified, only objects whose names match the pattern are listed.
1080 <term><literal>\dl</literal></term>
1083 This is an alias for <command>\lo_list</command>, which shows a
1084 list of large objects.
1091 <term><literal>\dn [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1092 <term><literal>\dn+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1096 Lists all available schemas (namespaces). If <replaceable
1097 class="parameter">pattern</replaceable> (a regular expression)
1098 is specified, only schemas whose names match the pattern are listed.
1099 Non-local temporary schemas are suppressed. If <literal>+</literal>
1100 is appended to the command name, each object is listed with its associated
1101 permissions and description, if any.
1108 <term><literal>\do [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1111 Lists available operators with their operand and return types.
1112 If <replaceable class="parameter">pattern</replaceable> is
1113 specified, only operators whose names match the pattern are listed.
1120 <term><literal>\dp [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1123 Produces a list of all available tables, views and sequences with their
1124 associated access privileges.
1125 If <replaceable class="parameter">pattern</replaceable> is
1126 specified, only tables, views and sequences whose names match the pattern are listed.
1130 The <xref linkend="sql-grant" endterm="sql-grant-title"> and
1131 <xref linkend="sql-revoke" endterm="sql-revoke-title">
1132 commands are used to set access privileges.
1139 <term><literal>\dT [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1140 <term><literal>\dT+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1143 Lists all data types or only those that match <replaceable
1144 class="parameter">pattern</replaceable>. The command form
1145 <literal>\dT+</literal> shows extra information, namely the type's internal name, size, and
1146 allowed values for <type>enum</> types.
1153 <term><literal>\du [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1156 Lists all database roles, or only those that match <replaceable
1157 class="parameter">pattern</replaceable>.
1164 <term><literal>\edit</literal> (or <literal>\e</literal>) <literal>[ <replaceable class="parameter">filename</replaceable> ]</literal></term>
1168 If <replaceable class="parameter">filename</replaceable> is
1169 specified, the file is edited; after the editor exits, its
1170 content is copied back to the query buffer. If no argument is
1171 given, the current query buffer is copied to a temporary file
1172 which is then edited in the same fashion.
1176 The new query buffer is then re-parsed according to the normal
1177 rules of <application>psql</application>, where the whole buffer
1178 is treated as a single line. (Thus you cannot make scripts this
1179 way. Use <command>\i</command> for that.) This means also that
1180 if the query ends with (or rather contains) a semicolon, it is
1181 immediately executed. In other cases it will merely wait in the
1187 <application>psql</application> searches the environment
1188 variables <envar>PSQL_EDITOR</envar>, <envar>EDITOR</envar>, and
1189 <envar>VISUAL</envar> (in that order) for an editor to use. If
1190 all of them are unset, <filename>vi</filename> is used on Unix
1191 systems, <filename>notepad.exe</filename> on Windows systems.
1199 <term><literal>\ef <replaceable class="parameter">function_description</replaceable> </literal></term>
1203 This command fetches and edits the definition of the named function,
1204 in the form of a <command>CREATE OR REPLACE FUNCTION</> command.
1205 Editing is done in the same way as for <literal>\e</>.
1206 After the editor exits, the updated command waits in the query buffer;
1207 type semicolon or <literal>\g</> to send it, or <literal>\r</>
1212 The target function can be specified by name alone, or by name
1213 and arguments, for example <literal>foo(integer, text)</>.
1214 The argument types must be given if there is more
1215 than one function of the same name.
1222 <term><literal>\echo <replaceable class="parameter">text</replaceable> [ ... ]</literal></term>
1225 Prints the arguments to the standard output, separated by one
1226 space and followed by a newline. This can be useful to
1227 intersperse information in the output of scripts. For example:
1229 => <userinput>\echo `date`</userinput>
1230 Tue Oct 26 21:40:57 CEST 1999
1232 If the first argument is an unquoted <literal>-n</literal> the trailing
1233 newline is not written.
1238 If you use the <command>\o</command> command to redirect your
1239 query output you might wish to use <command>\qecho</command>
1240 instead of this command.
1248 <term><literal>\encoding [ <replaceable class="parameter">encoding</replaceable> ]</literal></term>
1252 Sets the client character set encoding. Without an argument, this command
1253 shows the current encoding.
1260 <term><literal>\f [ <replaceable class="parameter">string</replaceable> ]</literal></term>
1264 Sets the field separator for unaligned query output. The default
1265 is the vertical bar (<literal>|</literal>). See also
1266 <command>\pset</command> for a generic way of setting output
1274 <term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
1278 Sends the current query input buffer to the server and
1279 optionally stores the query's output in <replaceable
1280 class="parameter">filename</replaceable> or pipes the output
1281 into a separate Unix shell executing <replaceable
1282 class="parameter">command</replaceable>. A bare
1283 <literal>\g</literal> is virtually equivalent to a semicolon. A
1284 <literal>\g</literal> with argument is a <quote>one-shot</quote>
1285 alternative to the <command>\o</command> command.
1291 <term><literal>\help</literal> (or <literal>\h</literal>) <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
1294 Gives syntax help on the specified <acronym>SQL</acronym>
1295 command. If <replaceable class="parameter">command</replaceable>
1296 is not specified, then <application>psql</application> will list
1297 all the commands for which syntax help is available. If
1298 <replaceable class="parameter">command</replaceable> is an
1299 asterisk (<literal>*</literal>), then syntax help on all
1300 <acronym>SQL</acronym> commands is shown.
1305 To simplify typing, commands that consists of several words do
1306 not have to be quoted. Thus it is fine to type <userinput>\help
1307 alter table</userinput>.
1315 <term><literal>\H</literal></term>
1318 Turns on <acronym>HTML</acronym> query output format. If the
1319 <acronym>HTML</acronym> format is already on, it is switched
1320 back to the default aligned text format. This command is for
1321 compatibility and convenience, but see <command>\pset</command>
1322 about setting other output options.
1329 <term><literal>\i <replaceable class="parameter">filename</replaceable></literal></term>
1332 Reads input from the file <replaceable
1333 class="parameter">filename</replaceable> and executes it as
1334 though it had been typed on the keyboard.
1338 If you want to see the lines on the screen as they are read you
1339 must set the variable <varname>ECHO</varname> to
1340 <literal>all</literal>.
1348 <term><literal>\l</literal> (or <literal>\list</literal>)</term>
1349 <term><literal>\l+</literal> (or <literal>\list+</literal>)</term>
1352 List the names, owners, character set encodings, and access privileges
1353 of all the databases in the server.
1354 If <literal>+</literal> is appended to the command name, database
1355 sizes, default tablespaces, and descriptions are also displayed.
1356 (Size information is only available for databases that the current
1357 user can connect to.)
1364 <term><literal>\lo_export <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></literal></term>
1368 Reads the large object with <acronym>OID</acronym> <replaceable
1369 class="parameter">loid</replaceable> from the database and
1370 writes it to <replaceable
1371 class="parameter">filename</replaceable>. Note that this is
1372 subtly different from the server function
1373 <function>lo_export</function>, which acts with the permissions
1374 of the user that the database server runs as and on the server's
1379 Use <command>\lo_list</command> to find out the large object's
1380 <acronym>OID</acronym>.
1388 <term><literal>\lo_import <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</literal></term>
1392 Stores the file into a <productname>PostgreSQL</productname>
1393 large object. Optionally, it associates the given
1394 comment with the object. Example:
1396 foo=> <userinput>\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'</userinput>
1399 The response indicates that the large object received object
1400 ID 152801, which can be used to access the newly-created large
1401 object in the future. For the sake of readability, it is
1402 recommended to always associate a human-readable comment with
1403 every object. Both OIDs and comments can be viewed with the
1404 <command>\lo_list</command> command.
1408 Note that this command is subtly different from the server-side
1409 <function>lo_import</function> because it acts as the local user
1410 on the local file system, rather than the server's user and file
1417 <term><literal>\lo_list</literal></term>
1420 Shows a list of all <productname>PostgreSQL</productname>
1421 large objects currently stored in the database,
1422 along with any comments provided for them.
1428 <term><literal>\lo_unlink <replaceable class="parameter">loid</replaceable></literal></term>
1432 Deletes the large object with <acronym>OID</acronym>
1433 <replaceable class="parameter">loid</replaceable> from the
1439 Use <command>\lo_list</command> to find out the large object's
1440 <acronym>OID</acronym>.
1448 <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
1452 Saves future query results to the file <replaceable
1453 class="parameter">filename</replaceable> or pipes future results
1454 into a separate Unix shell to execute <replaceable
1455 class="parameter">command</replaceable>. If no arguments are
1456 specified, the query output will be reset to the standard output.
1460 <quote>Query results</quote> includes all tables, command
1461 responses, and notices obtained from the database server, as
1462 well as output of various backslash commands that query the
1463 database (such as <command>\d</command>), but not error
1469 To intersperse text output in between query results, use
1470 <command>\qecho</command>.
1478 <term><literal>\p</literal></term>
1481 Print the current query buffer to the standard output.
1487 <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
1490 Changes the password of the specified user (by default, the current
1491 user). This command prompts for the new password, encrypts it, and
1492 sends it to the server as an <command>ALTER ROLE</> command. This
1493 makes sure that the new password does not appear in cleartext in the
1494 command history, the server log, or elsewhere.
1500 <term><literal>\prompt [ <replaceable class="parameter">text</replaceable> ] <replaceable class="parameter">name</replaceable></literal></term>
1503 Prompts the user to set variable <replaceable
1504 class="parameter">name</>. An optional prompt, <replaceable
1505 class="parameter">text</>, can be specified. (For multi-word
1506 prompts, use single-quotes.)
1510 By default, <literal>\prompt</> uses the terminal for input and
1511 output. However, if the <option>-f</> command line switch is
1512 used, <literal>\prompt</> uses standard input and standard output.
1518 <term><literal>\pset <replaceable class="parameter">parameter</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
1522 This command sets options affecting the output of query result
1523 tables. <replaceable class="parameter">parameter</replaceable>
1524 describes which option is to be set. The semantics of
1525 <replaceable class="parameter">value</replaceable> depend
1530 Adjustable printing options are:
1533 <term><literal>format</literal></term>
1536 Sets the output format to one of <literal>unaligned</literal>,
1537 <literal>aligned</literal>, <literal>wrapped</literal>,
1538 <literal>html</literal>,
1539 <literal>latex</literal>, or <literal>troff-ms</literal>.
1540 Unique abbreviations are allowed. (That would mean one letter
1545 <quote>Unaligned</quote> writes all columns of a row on a
1546 line, separated by the currently active field separator. This
1547 is intended to create output that might be intended to be read
1548 in by other programs (tab-separated, comma-separated).
1549 <quote>Aligned</quote> mode is the standard, human-readable,
1550 nicely formatted text output that is default.
1554 <quote>Wrapped</quote> is like <literal>aligned</> but wraps
1555 output to the specified width. If <literal>\pset columns</> is
1556 zero (the default), <literal>wrapped</> mode only affects screen
1557 output and wrapped width is controlled by the environment
1558 variable <envar>COLUMNS</> or the detected screen width. If
1559 <literal>\pset columns</> is set to a non-zero value, all output
1560 is wrapped, including file and pipe output.
1564 The <quote><acronym>HTML</acronym></quote> and
1565 <quote>LaTeX</quote> modes put out tables that are intended to
1566 be included in documents using the respective mark-up
1567 language. They are not complete documents! (This might not be
1568 so dramatic in <acronym>HTML</acronym>, but in LaTeX you must
1569 have a complete document wrapper.)
1575 <term><literal>columns</literal></term>
1578 Controls the target width for the <literal>wrapped</> format,
1579 and width for determining if wide output requires the pager.
1580 Zero (the default) causes the <literal>wrapped</> format to
1581 affect only screen output.
1587 <term><literal>border</literal></term>
1590 The second argument must be a number. In general, the higher
1591 the number the more borders and lines the tables will have,
1592 but this depends on the particular format. In
1593 <acronym>HTML</acronym> mode, this will translate directly
1594 into the <literal>border=...</literal> attribute, in the
1595 others only values 0 (no border), 1 (internal dividing lines),
1596 and 2 (table frame) make sense.
1602 <term><literal>expanded</literal> (or <literal>x</literal>)</term>
1605 You can specify an optional second argument, if it is provided it
1606 may be either <literal>on</literal> or <literal>off</literal>
1607 which will enable or disable expanded mode. If the second
1608 argument is not provided then we will toggle between regular and
1609 expanded format. When expanded format is enabled, query results
1610 are displayed in two columns, with the column name on the left and
1611 the data on the right. This mode is useful if the data wouldn't fit
1612 on the screen in the normal <quote>horizontal</quote> mode.
1616 Expanded mode is supported by all four output formats.
1622 <term><literal>null</literal></term>
1625 The second argument is a string that should be printed
1626 whenever a column is null. The default is not to print
1627 anything, which can easily be mistaken for, say, an empty
1628 string. Thus, one might choose to write <literal>\pset null
1635 <term><literal>fieldsep</literal></term>
1638 Specifies the field separator to be used in unaligned output
1639 mode. That way one can create, for example, tab- or
1640 comma-separated output, which other programs might prefer. To
1641 set a tab as field separator, type <literal>\pset fieldsep
1642 '\t'</literal>. The default field separator is
1643 <literal>'|'</literal> (a vertical bar).
1649 <term><literal>footer</literal></term>
1652 You can specify an optional second argument, if it is provided it
1653 may be either <literal>on</literal> or <literal>off</literal>
1654 which will enable or disable display of the default footer
1655 <literal>(x rows)</literal>. If the second argument is not
1656 provided then we will toggle between on and off.
1662 <term><literal>numericlocale</literal></term>
1665 You can specify an optional second argument, if it is provided it
1666 may be either <literal>on</literal> or <literal>off</literal>
1667 which will enable or disable display of a locale-aware character
1668 to seperate groups of digits to the left of the decimal marker. If
1669 the second argument is not provided then we will toggle between
1676 <term><literal>recordsep</literal></term>
1679 Specifies the record (line) separator to use in unaligned
1680 output mode. The default is a newline character.
1686 <term><literal>tuples_only</literal> (or <literal>t</literal>)</term>
1689 You can specify an optional second argument, if it is provided it
1690 may be either <literal>on</literal> or <literal>off</literal>
1691 which will enable or disable the tuples only mode. If the
1692 second argument is not provided then we will toggle between tuples
1693 only and full display. Full display shows extra information such
1694 as column headers, titles, and various footers. In tuples only
1695 mode, only actual table data is shown.
1701 <term><literal>title [ <replaceable class="parameter">text</replaceable> ]</literal></term>
1704 Sets the table title for any subsequently printed tables. This
1705 can be used to give your output descriptive tags. If no
1706 argument is given, the title is unset.
1712 <term><literal>tableattr</literal> (or <literal>T</literal>) <literal>[ <replaceable class="parameter">text</replaceable> ]</literal></term>
1715 Allows you to specify any attributes to be placed inside the
1716 <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. This
1717 could for example be <literal>cellpadding</literal> or
1718 <literal>bgcolor</literal>. Note that you probably don't want
1719 to specify <literal>border</literal> here, as that is already
1720 taken care of by <literal>\pset border</literal>.
1727 <term><literal>pager</literal></term>
1730 Controls use of a pager for query and <application>psql</>
1731 help output. If the environment variable <envar>PAGER</envar>
1732 is set, the output is piped to the specified program.
1733 Otherwise a platform-dependent default (such as
1734 <filename>more</filename>) is used.
1738 When the pager is <literal>off</>, the pager is not used. When the pager
1739 is <literal>on</>, the pager is used only when appropriate, i.e. the
1740 output is to a terminal and will not fit on the screen.
1741 <literal>\pset pager</> turns the pager on and off. Pager can
1742 also be set to <literal>always</>, which causes the pager to be
1751 Illustrations on how these different formats look can be seen in
1752 the <xref linkend="APP-PSQL-examples"
1753 endterm="APP-PSQL-examples-title"> section.
1758 There are various shortcut commands for <command>\pset</command>. See
1759 <command>\a</command>, <command>\C</command>, <command>\H</command>,
1760 <command>\t</command>, <command>\T</command>, and <command>\x</command>.
1766 It is an error to call <command>\pset</command> without
1767 arguments. In the future this call might show the current status
1768 of all printing options.
1777 <term><literal>\q</literal></term>
1780 Quits the <application>psql</application> program.
1787 <term><literal>\qecho <replaceable class="parameter">text</replaceable> [ ... ] </literal></term>
1790 This command is identical to <command>\echo</command> except
1791 that the output will be written to the query output channel, as
1792 set by <command>\o</command>.
1799 <term><literal>\r</literal></term>
1802 Resets (clears) the query buffer.
1809 <term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
1812 Print or save the command line history to <replaceable
1813 class="parameter">filename</replaceable>. If <replaceable
1814 class="parameter">filename</replaceable> is omitted, the history
1815 is written to the standard output. This option is only available
1816 if <application>psql</application> is configured to use the
1817 <acronym>GNU</acronym> <application>Readline</application> library.
1824 <term><literal>\set [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ] ] ]</literal></term>
1828 Sets the internal variable <replaceable
1829 class="parameter">name</replaceable> to <replaceable
1830 class="parameter">value</replaceable> or, if more than one value
1831 is given, to the concatenation of all of them. If no second
1832 argument is given, the variable is just set with no value. To
1833 unset a variable, use the <command>\unset</command> command.
1837 Valid variable names can contain characters, digits, and
1838 underscores. See the section <xref
1839 linkend="APP-PSQL-variables"
1840 endterm="APP-PSQL-variables-title"> below for details.
1841 Variable names are case-sensitive.
1845 Although you are welcome to set any variable to anything you
1846 want, <application>psql</application> treats several variables
1847 as special. They are documented in the section about variables.
1852 This command is totally separate from the <acronym>SQL</acronym>
1853 command <xref linkend="SQL-SET" endterm="SQL-SET-title">.
1861 <term><literal>\t</literal></term>
1864 Toggles the display of output column name headings and row count
1865 footer. This command is equivalent to <literal>\pset
1866 tuples_only</literal> and is provided for convenience.
1873 <term><literal>\T <replaceable class="parameter">table_options</replaceable></literal></term>
1876 Allows you to specify attributes to be placed within the
1877 <sgmltag>table</sgmltag> tag in <acronym>HTML</acronym> tabular
1878 output mode. This command is equivalent to <literal>\pset
1879 tableattr <replaceable
1880 class="parameter">table_options</replaceable></literal>.
1887 <term><literal>\timing [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> ]</literal></term>
1890 Without parameter, toggles a display of how long each SQL statement
1891 takes, in milliseconds. With parameter, sets same.
1898 <term><literal>\w</literal> {<replaceable class="parameter">filename</replaceable> | <replaceable class="parameter">|command</replaceable>}</term>
1901 Outputs the current query buffer to the file <replaceable
1902 class="parameter">filename</replaceable> or pipes it to the Unix
1903 command <replaceable class="parameter">command</replaceable>.
1910 <term><literal>\x</literal></term>
1913 Toggles expanded table formatting mode. As such it is equivalent to
1914 <literal>\pset expanded</literal>.
1921 <term><literal>\z [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1924 Produces a list of all available tables, views and sequences with their
1925 associated access privileges.
1926 If a <replaceable class="parameter">pattern</replaceable> is
1927 specified, only tables,views and sequences whose names match the pattern are listed.
1931 The <xref linkend="sql-grant" endterm="sql-grant-title"> and
1932 <xref linkend="sql-revoke" endterm="sql-revoke-title">
1933 commands are used to set access privileges.
1937 This is an alias for <command>\dp</command> (<quote>display
1938 privileges</quote>).
1945 <term><literal>\! [ <replaceable class="parameter">command</replaceable> ]</literal></term>
1948 Escapes to a separate Unix shell or executes the Unix command
1949 <replaceable class="parameter">command</replaceable>. The
1950 arguments are not further interpreted, the shell will see them
1958 <term><literal>\?</literal></term>
1961 Shows help information about the backslash commands.
1969 <refsect3 id="APP-PSQL-patterns">
1970 <title id="APP-PSQL-patterns-title">Patterns</title>
1973 <primary>patterns</primary>
1974 <secondary>in psql and pg_dump</secondary>
1978 The various <literal>\d</> commands accept a <replaceable
1979 class="parameter">pattern</replaceable> parameter to specify the
1980 object name(s) to be displayed. In the simplest case, a pattern
1981 is just the exact name of the object. The characters within a
1982 pattern are normally folded to lower case, just as in SQL names;
1983 for example, <literal>\dt FOO</> will display the table named
1984 <literal>foo</>. As in SQL names, placing double quotes around
1985 a pattern stops folding to lower case. Should you need to include
1986 an actual double quote character in a pattern, write it as a pair
1987 of double quotes within a double-quote sequence; again this is in
1988 accord with the rules for SQL quoted identifiers. For example,
1989 <literal>\dt "FOO""BAR"</> will display the table named
1990 <literal>FOO"BAR</> (not <literal>foo"bar</>). Unlike the normal
1991 rules for SQL names, you can put double quotes around just part
1992 of a pattern, for instance <literal>\dt FOO"FOO"BAR</> will display
1993 the table named <literal>fooFOObar</>.
1997 Within a pattern, <literal>*</> matches any sequence of characters
1998 (including no characters) and <literal>?</> matches any single character.
1999 (This notation is comparable to Unix shell file name patterns.)
2000 For example, <literal>\dt int*</> displays all tables whose names
2001 begin with <literal>int</>. But within double quotes, <literal>*</>
2002 and <literal>?</> lose these special meanings and are just matched
2007 A pattern that contains a dot (<literal>.</>) is interpreted as a schema
2008 name pattern followed by an object name pattern. For example,
2009 <literal>\dt foo*.*bar*</> displays all tables whose table name
2010 includes <literal>bar</> that are in schemas whose schema name
2011 starts with <literal>foo</>. When no dot appears, then the pattern
2012 matches only objects that are visible in the current schema search path.
2013 Again, a dot within double quotes loses its special meaning and is matched
2018 Advanced users can use regular-expression notations such as character
2019 classes, for example <literal>[0-9]</> to match any digit. All regular
2020 expression special characters work as specified in
2021 <xref linkend="functions-posix-regexp">, except for <literal>.</> which
2022 is taken as a separator as mentioned above, <literal>*</> which is
2023 translated to the regular-expression notation <literal>.*</>,
2024 <literal>?</> which is translated to <literal>.</>, and
2025 <literal>$</> which is matched literally. You can emulate
2026 these pattern characters at need by writing
2027 <literal>?</> for <literal>.</>,
2028 <literal>(<replaceable class="parameter">R</replaceable>+|)</literal> for
2029 <literal><replaceable class="parameter">R</replaceable>*</literal>, or
2030 <literal>(<replaceable class="parameter">R</replaceable>|)</literal> for
2031 <literal><replaceable class="parameter">R</replaceable>?</literal>.
2032 <literal>$</> is not needed as a regular-expression character since
2033 the pattern must match the whole name, unlike the usual
2034 interpretation of regular expressions (in other words, <literal>$</>
2035 is automatically appended to your pattern). Write <literal>*</> at the
2036 beginning and/or end if you don't wish the pattern to be anchored.
2037 Note that within double quotes, all regular expression special characters
2038 lose their special meanings and are matched literally. Also, the regular
2039 expression special characters are matched literally in operator name
2040 patterns (i.e., the argument of <literal>\do</>).
2044 Whenever the <replaceable class="parameter">pattern</replaceable> parameter
2045 is omitted completely, the <literal>\d</> commands display all objects
2046 that are visible in the current schema search path — this is
2047 equivalent to using the pattern <literal>*</>.
2048 To see all objects in the database, use the pattern <literal>*.*</>.
2054 <title>Advanced features</title>
2056 <refsect3 id="APP-PSQL-variables">
2057 <title id="APP-PSQL-variables-title">Variables</title>
2060 <application>psql</application> provides variable substitution
2061 features similar to common Unix command shells.
2062 Variables are simply name/value pairs, where the value
2063 can be any string of any length. To set variables, use the
2064 <application>psql</application> meta-command
2065 <command>\set</command>:
2067 testdb=> <userinput>\set foo bar</userinput>
2069 sets the variable <literal>foo</literal> to the value
2070 <literal>bar</literal>. To retrieve the content of the variable, precede
2071 the name with a colon and use it as the argument of any slash
2074 testdb=> <userinput>\echo :foo</userinput>
2081 The arguments of <command>\set</command> are subject to the same
2082 substitution rules as with other commands. Thus you can construct
2083 interesting references such as <literal>\set :foo
2084 'something'</literal> and get <quote>soft links</quote> or
2085 <quote>variable variables</quote> of <productname>Perl</productname>
2086 or <productname><acronym>PHP</acronym></productname> fame,
2087 respectively. Unfortunately (or fortunately?), there is no way to do
2088 anything useful with these constructs. On the other hand,
2089 <literal>\set bar :foo</literal> is a perfectly valid way to copy a
2095 If you call <command>\set</command> without a second argument, the
2096 variable is set, with an empty string as value. To unset (or delete) a
2097 variable, use the command <command>\unset</command>.
2101 <application>psql</application>'s internal variable names can
2102 consist of letters, numbers, and underscores in any order and any
2103 number of them. A number of these variables are treated specially
2104 by <application>psql</application>. They indicate certain option
2105 settings that can be changed at run time by altering the value of
2106 the variable or represent some state of the application. Although
2107 you can use these variables for any other purpose, this is not
2108 recommended, as the program behavior might grow really strange
2109 really quickly. By convention, all specially treated variables
2110 consist of all upper-case letters (and possibly numbers and
2111 underscores). To ensure maximum compatibility in the future, avoid
2112 using such variable names for your own purposes. A list of all specially
2113 treated variables follows.
2119 <primary>autocommit</primary>
2120 <secondary>psql</secondary>
2122 <term><varname>AUTOCOMMIT</varname></term>
2125 When <literal>on</> (the default), each SQL command is automatically
2126 committed upon successful completion. To postpone commit in this
2127 mode, you must enter a <command>BEGIN</> or <command>START
2128 TRANSACTION</> SQL command. When <literal>off</> or unset, SQL
2129 commands are not committed until you explicitly issue
2130 <command>COMMIT</> or <command>END</>. The autocommit-off
2131 mode works by issuing an implicit <command>BEGIN</> for you, just
2132 before any command that is not already in a transaction block and
2133 is not itself a <command>BEGIN</> or other transaction-control
2134 command, nor a command that cannot be executed inside a transaction
2135 block (such as <command>VACUUM</>).
2140 In autocommit-off mode, you must explicitly abandon any failed
2141 transaction by entering <command>ABORT</> or <command>ROLLBACK</>.
2142 Also keep in mind that if you exit the session
2143 without committing, your work will be lost.
2149 The autocommit-on mode is <productname>PostgreSQL</>'s traditional
2150 behavior, but autocommit-off is closer to the SQL spec. If you
2151 prefer autocommit-off, you might wish to set it in the system-wide
2152 <filename>psqlrc</filename> file or your
2153 <filename>~/.psqlrc</filename> file.
2160 <term><varname>DBNAME</varname></term>
2163 The name of the database you are currently connected to. This is
2164 set every time you connect to a database (including program
2165 start-up), but can be unset.
2171 <term><varname>ECHO</varname></term>
2174 If set to <literal>all</literal>, all lines
2175 entered from the keyboard or from a script are written to the standard output
2176 before they are parsed or executed. To select this behavior on program
2177 start-up, use the switch <option>-a</option>. If set to
2178 <literal>queries</literal>,
2179 <application>psql</application> merely prints all queries as
2180 they are sent to the server. The switch for this is
2181 <option>-e</option>.
2187 <term><varname>ECHO_HIDDEN</varname></term>
2190 When this variable is set and a backslash command queries the
2191 database, the query is first shown. This way you can study the
2192 <productname>PostgreSQL</productname> internals and provide
2193 similar functionality in your own programs. (To select this behavior
2194 on program start-up, use the switch <option>-E</option>.) If you set
2195 the variable to the value <literal>noexec</literal>, the queries are
2196 just shown but are not actually sent to the server and executed.
2202 <term><varname>ENCODING</varname></term>
2205 The current client character set encoding.
2211 <term><varname>FETCH_COUNT</varname></term>
2214 If this variable is set to an integer value > 0,
2215 the results of <command>SELECT</command> queries are fetched
2216 and displayed in groups of that many rows, rather than the
2217 default behavior of collecting the entire result set before
2218 display. Therefore only a
2219 limited amount of memory is used, regardless of the size of
2220 the result set. Settings of 100 to 1000 are commonly used
2221 when enabling this feature.
2222 Keep in mind that when using this feature, a query might
2223 fail after having already displayed some rows.
2227 Although you can use any output format with this feature,
2228 the default <literal>aligned</> format tends to look bad
2229 because each group of <varname>FETCH_COUNT</varname> rows
2230 will be formatted separately, leading to varying column
2231 widths across the row groups. The other output formats work better.
2238 <term><varname>HISTCONTROL</varname></term>
2241 If this variable is set to <literal>ignorespace</literal>,
2242 lines which begin with a space are not entered into the history
2243 list. If set to a value of <literal>ignoredups</literal>, lines
2244 matching the previous history line are not entered. A value of
2245 <literal>ignoreboth</literal> combines the two options. If
2246 unset, or if set to any other value than those above, all lines
2247 read in interactive mode are saved on the history list.
2251 This feature was shamelessly plagiarized from
2252 <application>Bash</application>.
2259 <term><varname>HISTFILE</varname></term>
2262 The file name that will be used to store the history list. The default
2263 value is <filename>~/.psql_history</filename>. For example, putting:
2265 \set HISTFILE ~/.psql_history- :DBNAME
2267 in <filename>~/.psqlrc</filename> will cause
2268 <application>psql</application> to maintain a separate history for
2273 This feature was shamelessly plagiarized from
2274 <application>Bash</application>.
2281 <term><varname>HISTSIZE</varname></term>
2284 The number of commands to store in the command history. The
2285 default value is 500.
2289 This feature was shamelessly plagiarized from
2290 <application>Bash</application>.
2297 <term><varname>HOST</varname></term>
2300 The database server host you are currently connected to. This is
2301 set every time you connect to a database (including program
2302 start-up), but can be unset.
2308 <term><varname>IGNOREEOF</varname></term>
2311 If unset, sending an <acronym>EOF</> character (usually
2312 <keycombo action="simul"><keycap>Control</><keycap>D</></>)
2313 to an interactive session of <application>psql</application>
2314 will terminate the application. If set to a numeric value,
2315 that many <acronym>EOF</> characters are ignored before the
2316 application terminates. If the variable is set but has no
2317 numeric value, the default is 10.
2321 This feature was shamelessly plagiarized from
2322 <application>Bash</application>.
2329 <term><varname>LASTOID</varname></term>
2332 The value of the last affected OID, as returned from an
2333 <command>INSERT</command> or <command>lo_insert</command>
2334 command. This variable is only guaranteed to be valid until
2335 after the result of the next <acronym>SQL</acronym> command has
2343 <primary>rollback</primary>
2344 <secondary>psql</secondary>
2346 <term><varname>ON_ERROR_ROLLBACK</varname></term>
2349 When <literal>on</>, if a statement in a transaction block
2350 generates an error, the error is ignored and the transaction
2351 continues. When <literal>interactive</>, such errors are only
2352 ignored in interactive sessions, and not when reading script
2353 files. When <literal>off</> (the default), a statement in a
2354 transaction block that generates an error aborts the entire
2355 transaction. The on_error_rollback-on mode works by issuing an
2356 implicit <command>SAVEPOINT</> for you, just before each command
2357 that is in a transaction block, and rolls back to the savepoint
2364 <term><varname>ON_ERROR_STOP</varname></term>
2367 By default, if non-interactive scripts encounter an error, such
2368 as a malformed <acronym>SQL</acronym> command or internal
2369 meta-command, processing continues. This has been the
2370 traditional behavior of <application>psql</application> but it
2371 is sometimes not desirable. If this variable is set, script
2372 processing will immediately terminate. If the script was called
2373 from another script it will terminate in the same fashion. If
2374 the outermost script was not called from an interactive
2375 <application>psql</application> session but rather using the
2376 <option>-f</option> option, <application>psql</application> will
2377 return error code 3, to distinguish this case from fatal error
2378 conditions (error code 1).
2384 <term><varname>PORT</varname></term>
2387 The database server port to which you are currently connected.
2388 This is set every time you connect to a database (including
2389 program start-up), but can be unset.
2395 <term><varname>PROMPT1</varname></term>
2396 <term><varname>PROMPT2</varname></term>
2397 <term><varname>PROMPT3</varname></term>
2400 These specify what the prompts <application>psql</application>
2401 issues should look like. See <xref
2402 linkend="APP-PSQL-prompting"
2403 endterm="APP-PSQL-prompting-title"> below.
2409 <term><varname>QUIET</varname></term>
2412 This variable is equivalent to the command line option
2413 <option>-q</option>. It is probably not too useful in
2420 <term><varname>SINGLELINE</varname></term>
2423 This variable is equivalent to the command line option
2424 <option>-S</option>.
2430 <term><varname>SINGLESTEP</varname></term>
2433 This variable is equivalent to the command line option
2434 <option>-s</option>.
2440 <term><varname>USER</varname></term>
2443 The database user you are currently connected as. This is set
2444 every time you connect to a database (including program
2445 start-up), but can be unset.
2451 <term><varname>VERBOSITY</varname></term>
2454 This variable can be set to the values <literal>default</>,
2455 <literal>verbose</>, or <literal>terse</> to control the verbosity
2466 <title><acronym>SQL</acronym> Interpolation</title>
2469 An additional useful feature of <application>psql</application>
2470 variables is that you can substitute (<quote>interpolate</quote>)
2471 them into regular <acronym>SQL</acronym> statements. The syntax for
2472 this is again to prepend the variable name with a colon
2473 (<literal>:</literal>):
2475 testdb=> <userinput>\set foo 'my_table'</userinput>
2476 testdb=> <userinput>SELECT * FROM :foo;</userinput>
2478 would then query the table <literal>my_table</literal>. The value of
2479 the variable is copied literally, so it can even contain unbalanced
2480 quotes or backslash commands. You must make sure that it makes sense
2481 where you put it. Variable interpolation will not be performed into
2482 quoted <acronym>SQL</acronym> entities.
2486 One possible use of this mechanism is to
2487 copy the contents of a file into a table column. First load the file into a
2488 variable and then proceed as above:
2490 testdb=> <userinput>\set content '''' `cat my_file.txt` ''''</userinput>
2491 testdb=> <userinput>INSERT INTO my_table VALUES (:content);</userinput>
2493 One problem with this approach is that <filename>my_file.txt</filename>
2494 might contain single quotes. These need to be escaped so that
2495 they don't cause a syntax error when the second line is processed. This
2496 could be done with the program <command>sed</command>:
2498 testdb=> <userinput>\set content '''' `sed -e "s/'/''/g" < my_file.txt` ''''</userinput>
2500 If you are using non-standard-conforming strings then you'll also need
2501 to double backslashes. This is a bit tricky:
2503 testdb=> <userinput>\set content '''' `sed -e "s/'/''/g" -e 's/\\/\\\\/g' < my_file.txt` ''''</userinput>
2505 Note the use of different shell quoting conventions so that neither
2506 the single quote marks nor the backslashes are special to the shell.
2507 Backslashes are still special to <command>sed</command>, however, so
2508 we need to double them. (Perhaps
2509 at one point you thought it was great that all Unix commands use the
2510 same escape character.)
2514 Since colons can legally appear in SQL commands, the following rule
2515 applies: the character sequence
2516 <quote>:name</quote> is not changed unless <quote>name</> is the name
2517 of a variable that is currently set. In any case you can escape
2518 a colon with a backslash to protect it from substitution. (The
2519 colon syntax for variables is standard <acronym>SQL</acronym> for
2520 embedded query languages, such as <application>ECPG</application>.
2521 The colon syntax for array slices and type casts are
2522 <productname>PostgreSQL</productname> extensions, hence the
2528 <refsect3 id="APP-PSQL-prompting">
2529 <title id="APP-PSQL-prompting-title">Prompting</title>
2532 The prompts <application>psql</application> issues can be customized
2533 to your preference. The three variables <varname>PROMPT1</varname>,
2534 <varname>PROMPT2</varname>, and <varname>PROMPT3</varname> contain strings
2535 and special escape sequences that describe the appearance of the
2536 prompt. Prompt 1 is the normal prompt that is issued when
2537 <application>psql</application> requests a new command. Prompt 2 is
2538 issued when more input is expected during command input because the
2539 command was not terminated with a semicolon or a quote was not closed.
2540 Prompt 3 is issued when you run an <acronym>SQL</acronym>
2541 <command>COPY</command> command and you are expected to type in the
2542 row values on the terminal.
2546 The value of the selected prompt variable is printed literally,
2547 except where a percent sign (<literal>%</literal>) is encountered.
2548 Depending on the next character, certain other text is substituted
2549 instead. Defined substitutions are:
2553 <term><literal>%M</literal></term>
2556 The full host name (with domain name) of the database server,
2557 or <literal>[local]</literal> if the connection is over a Unix
2559 <literal>[local:<replaceable>/dir/name</replaceable>]</literal>,
2560 if the Unix domain socket is not at the compiled in default
2567 <term><literal>%m</literal></term>
2570 The host name of the database server, truncated at the
2571 first dot, or <literal>[local]</literal> if the connection is
2572 over a Unix domain socket.
2578 <term><literal>%></literal></term>
2579 <listitem><para>The port number at which the database server is listening.</para></listitem>
2583 <term><literal>%n</literal></term>
2586 The database session user name. (The expansion of this
2587 value might change during a database session as the result
2588 of the command <command>SET SESSION
2589 AUTHORIZATION</command>.)
2595 <term><literal>%/</literal></term>
2596 <listitem><para>The name of the current database.</para></listitem>
2600 <term><literal>%~</literal></term>
2601 <listitem><para>Like <literal>%/</literal>, but the output is <literal>~</literal>
2602 (tilde) if the database is your default database.</para></listitem>
2606 <term><literal>%#</literal></term>
2609 If the session user is a database superuser, then a
2610 <literal>#</literal>, otherwise a <literal>></literal>.
2611 (The expansion of this value might change during a database
2612 session as the result of the command <command>SET SESSION
2613 AUTHORIZATION</command>.)
2619 <term><literal>%R</literal></term>
2622 In prompt 1 normally <literal>=</literal>, but <literal>^</literal> if
2623 in single-line mode, and <literal>!</literal> if the session is
2624 disconnected from the database (which can happen if
2625 <command>\connect</command> fails). In prompt 2 the sequence is
2626 replaced by <literal>-</literal>, <literal>*</literal>, a single quote,
2627 a double quote, or a dollar sign, depending on whether
2628 <application>psql</application> expects more input because the
2629 command wasn't terminated yet, because you are inside a
2630 <literal>/* ... */</literal> comment, or because you are inside
2631 a quoted or dollar-escaped string. In prompt 3 the sequence doesn't
2638 <term><literal>%x</literal></term>
2641 Transaction status: an empty string when not in a transaction
2642 block, or <literal>*</> when in a transaction block, or
2643 <literal>!</> when in a failed transaction block, or <literal>?</>
2644 when the transaction state is indeterminate (for example, because
2645 there is no connection).
2651 <term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
2654 The character with the indicated octal code is substituted.
2660 <term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term>
2663 The value of the <application>psql</application> variable
2664 <replaceable class="parameter">name</replaceable>. See the
2665 section <xref linkend="APP-PSQL-variables"
2666 endterm="APP-PSQL-variables-title"> for details.
2672 <term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term>
2675 The output of <replaceable
2676 class="parameter">command</replaceable>, similar to ordinary
2677 <quote>back-tick</quote> substitution.
2683 <term><literal>%[</literal> ... <literal>%]</literal></term>
2686 Prompts can contain terminal control characters which, for
2687 example, change the color, background, or style of the prompt
2688 text, or change the title of the terminal window. In order for
2689 the line editing features of <application>Readline</application> to work properly, these
2690 non-printing control characters must be designated as invisible
2691 by surrounding them with <literal>%[</literal> and
2692 <literal>%]</literal>. Multiple pairs of these can occur within
2693 the prompt. For example:
2695 testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
2697 results in a boldfaced (<literal>1;</literal>) yellow-on-black
2698 (<literal>33;40</literal>) prompt on VT100-compatible, color-capable
2706 To insert a percent sign into your prompt, write
2707 <literal>%%</literal>. The default prompts are
2708 <literal>'%/%R%# '</literal> for prompts 1 and 2, and
2709 <literal>'>> '</literal> for prompt 3.
2714 This feature was shamelessly plagiarized from
2715 <application>tcsh</application>.
2722 <title>Command-Line Editing</title>
2725 <application>psql</application> supports the <application>Readline</application>
2726 library for convenient line editing and retrieval. The command
2727 history is automatically saved when <application>psql</application>
2728 exits and is reloaded when
2729 <application>psql</application> starts up. Tab-completion is also
2730 supported, although the completion logic makes no claim to be an
2731 <acronym>SQL</acronym> parser. If for some reason you do not like the tab completion, you
2732 can turn it off by putting this in a file named
2733 <filename>.inputrc</filename> in your home directory:
2736 set disable-completion on
2739 (This is not a <application>psql</application> but a
2740 <application>Readline</application> feature. Read its documentation
2741 for further details.)
2749 <title>Environment</title>
2754 <term><envar>COLUMNS</envar></term>
2758 If <literal>\pset columns</> is zero, controls the
2759 width for the <literal>wrapped</> format and width for determining
2760 if wide output requires the pager.
2766 <term><envar>PAGER</envar></term>
2770 If the query results do not fit on the screen, they are piped
2771 through this command. Typical values are
2772 <literal>more</literal> or <literal>less</literal>. The default
2773 is platform-dependent. The use of the pager can be disabled by
2774 using the <command>\pset</command> command.
2780 <term><envar>PGDATABASE</envar></term>
2781 <term><envar>PGHOST</envar></term>
2782 <term><envar>PGPORT</envar></term>
2783 <term><envar>PGUSER</envar></term>
2787 Default connection parameters (see <xref linkend="libpq-envars">).
2793 <term><envar>PSQL_EDITOR</envar></term>
2794 <term><envar>EDITOR</envar></term>
2795 <term><envar>VISUAL</envar></term>
2799 Editor used by the <command>\e</command> command. The variables
2800 are examined in the order listed; the first that is set is used.
2806 <term><envar>SHELL</envar></term>
2810 Command executed by the <command>\!</command> command.
2816 <term><envar>TMPDIR</envar></term>
2820 Directory for storing temporary files. The default is
2821 <filename>/tmp</filename>.
2828 This utility, like most other <productname>PostgreSQL</> utilities,
2829 also uses the environment variables supported by <application>libpq</>
2830 (see <xref linkend="libpq-envars">).
2837 <title>Files</title>
2842 Before starting up, <application>psql</application> attempts to
2843 read and execute commands from the system-wide
2844 <filename>psqlrc</filename> file and the user's
2845 <filename>~/.psqlrc</filename> file.
2846 (On Windows, the user's startup file is named
2847 <filename>%APPDATA%\postgresql\psqlrc.conf</filename>.)
2848 See <filename><replaceable>PREFIX</>/share/psqlrc.sample</>
2849 for information on setting up the system-wide file. It could be used
2850 to set up the client or the server to taste (using the <command>\set
2851 </command> and <command>SET</command> commands).
2857 Both the system-wide <filename>psqlrc</filename> file and the user's
2858 <filename>~/.psqlrc</filename> file can be made version-specific
2859 by appending a dash and the <productname>PostgreSQL</productname>
2860 release number, for example <filename>~/.psqlrc-&version;</filename>.
2861 A matching version-specific file will be read in preference to a
2862 non-version-specific file.
2868 The command-line history is stored in the file
2869 <filename>~/.psql_history</filename>, or
2870 <filename>%APPDATA%\postgresql\psql_history</filename> on Windows.
2878 <title>Notes</title>
2883 In an earlier life <application>psql</application> allowed the
2884 first argument of a single-letter backslash command to start
2885 directly after the command, without intervening whitespace.
2886 As of <productname>PostgreSQL</productname> 8.4 this is no
2893 <application>psql</application> is only guaranteed to work smoothly
2894 with servers of the same version. That does not mean other combinations
2895 will fail outright, but subtle and not-so-subtle problems might come
2896 up. Backslash commands are particularly likely to fail if the
2897 server is of a newer version than <application>psql</> itself. However,
2898 backslash commands of the <literal>\d</> family should work with
2899 servers of versions back to 7.4, though not necessarily with servers
2900 newer than <application>psql</> itself.
2909 <title>Notes for Windows users</title>
2912 <application>psql</application> is built as a <quote>console
2913 application</>. Since the Windows console windows use a different
2914 encoding than the rest of the system, you must take special care
2915 when using 8-bit characters within <application>psql</application>.
2916 If <application>psql</application> detects a problematic
2917 console code page, it will warn you at startup. To change the
2918 console code page, two things are necessary:
2923 Set the code page by entering <userinput>cmd.exe /c chcp
2924 1252</userinput>. (1252 is a code page that is appropriate for
2925 German; replace it with your value.) If you are using Cygwin,
2926 you can put this command in <filename>/etc/profile</filename>.
2932 Set the console font to <literal>Lucida Console</>, because the
2933 raster font does not work with the ANSI code page.
2942 <refsect1 id="APP-PSQL-examples">
2943 <title id="APP-PSQL-examples-title">Examples</title>
2946 The first example shows how to spread a command over several lines of
2947 input. Notice the changing prompt:
2949 testdb=> <userinput>CREATE TABLE my_table (</userinput>
2950 testdb(> <userinput> first integer not null default 0,</userinput>
2951 testdb(> <userinput> second text)</userinput>
2952 testdb-> <userinput>;</userinput>
2955 Now look at the table definition again:
2957 testdb=> <userinput>\d my_table</userinput>
2959 Attribute | Type | Modifier
2960 -----------+---------+--------------------
2961 first | integer | not null default 0
2965 Now we change the prompt to something more interesting:
2967 testdb=> <userinput>\set PROMPT1 '%n@%m %~%R%# '</userinput>
2968 peter@localhost testdb=>
2970 Let's assume you have filled the table with data and want to take a
2973 peter@localhost testdb=> SELECT * FROM my_table;
2983 You can display tables in different ways by using the
2984 <command>\pset</command> command:
2986 peter@localhost testdb=> <userinput>\pset border 2</userinput>
2988 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2999 peter@localhost testdb=> <userinput>\pset border 0</userinput>
3001 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
3010 peter@localhost testdb=> <userinput>\pset border 1</userinput>
3012 peter@localhost testdb=> <userinput>\pset format unaligned</userinput>
3013 Output format is unaligned.
3014 peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput>
3015 Field separator is ",".
3016 peter@localhost testdb=> <userinput>\pset tuples_only</userinput>
3017 Showing only tuples.
3018 peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
3024 Alternatively, use the short commands:
3026 peter@localhost testdb=> <userinput>\a \t \x</userinput>
3027 Output format is aligned.
3029 Expanded display is on.
3030 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
OSDN Git Service
