| blob | cd94af11f653dcd1c872454ef17344e09033a247 |
1 <!--
2 $PostgreSQL$
3 PostgreSQL documentation
4 -->
7 <refmeta>
11 </refmeta>
13 <refnamediv>
15 <refpurpose>
17 </refpurpose>
18 </refnamediv>
22 </indexterm>
24 <refsynopsisdiv>
25 <cmdsynopsis>
30 </cmdsynopsis>
31 </refsynopsisdiv>
33 <refsect1>
36 <para>
39 queries interactively, issue them to
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.
44 </para>
45 </refsect1>
50 <variablelist>
51 <varlistentry>
54 <listitem>
55 <para>
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
60 </para>
61 </listitem>
62 </varlistentry>
64 <varlistentry>
67 <listitem>
68 <para>
69 Switches to unaligned output mode. (The default output mode is
70 otherwise aligned.)
71 </para>
72 </listitem>
73 </varlistentry>
75 <varlistentry>
78 <listitem>
79 <para>
82 and then exit. This is useful in shell scripts.
83 </para>
84 <para>
86 a command string that is completely parsable by the server (i.e.,
88 or a single backslash command. Thus you cannot mix
90 meta-commands with this option. To achieve that, you could
94 </para>
95 <para>
96 If the command string contains multiple SQL commands, they are
97 processed in a single transaction, unless there are explicit
99 string to divide it into multiple transactions. This is
100 different from the behavior when the same string is fed to
102 </para>
103 </listitem>
104 </varlistentry>
106 <varlistentry>
109 <listitem>
110 <para>
111 Specifies the name of the database to connect to. This is
112 equivalent to specifying <replaceable
114 argument on the command line.
115 </para>
116 <para>
118 <parameter>conninfo</parameter> string. See <xref linkend="libpq-connect"> for more information.
119 </para>
120 </listitem>
121 </varlistentry>
123 <varlistentry>
126 <listitem>
127 <para>
128 Copy all SQL commands sent to the server to standard output as well.
129 This is equivalent
132 </para>
133 </listitem>
134 </varlistentry>
136 <varlistentry>
139 <listitem>
140 <para>
143 internal operations. This is equivalent to
146 </para>
147 </listitem>
148 </varlistentry>
150 <varlistentry>
153 <listitem>
154 <para>
156 as the source of commands instead of reading commands interactively.
158 terminates. This is in many ways equivalent to the internal
160 </para>
162 <para>
164 (hyphen), then standard input is read.
165 </para>
167 <para>
168 Using this option is subtly different from writing <literal>psql
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
177 everything by hand.
178 </para>
179 </listitem>
180 </varlistentry>
182 <varlistentry>
184 <term><option>--field-separator <replaceable class="parameter">separator</replaceable></></term>
185 <listitem>
186 <para>
188 field separator for unaligned output. This is equivalent to
190 </para>
191 </listitem>
192 </varlistentry>
194 <varlistentry>
197 <listitem>
198 <para>
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
202 socket.
203 </para>
204 </listitem>
205 </varlistentry>
207 <varlistentry>
210 <listitem>
211 <para>
215 </para>
216 </listitem>
217 </varlistentry>
219 <varlistentry>
222 <listitem>
223 <para>
224 List all available databases, then exit. Other non-connection
225 options are ignored. This is similar to the internal command
227 </para>
228 </listitem>
229 </varlistentry>
231 <varlistentry>
234 <listitem>
235 <para>
236 Write all query output into file <replaceable
238 normal output destination.
239 </para>
240 </listitem>
241 </varlistentry>
243 <varlistentry>
246 <listitem>
247 <para>
248 Put all query output into file <replaceable
251 </para>
252 </listitem>
253 </varlistentry>
255 <varlistentry>
258 <listitem>
259 <para>
260 Specifies the TCP port or the local Unix-domain
261 socket file extension on which the server is listening for
263 environment variable or, if not set, to the port specified at
264 compile time, usually 5432.
265 </para>
266 </listitem>
267 </varlistentry>
269 <varlistentry>
272 <listitem>
273 <para>
274 Allows you to specify printing options in the style of
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
279 </para>
280 </listitem>
281 </varlistentry>
283 <varlistentry>
286 <listitem>
287 <para>
289 quietly. By default, it prints welcome messages and various
290 informational output. If this option is used, none of this
294 </para>
295 </listitem>
296 </varlistentry>
298 <varlistentry>
300 <term><option>--record-separator <replaceable class="parameter">separator</replaceable></></term>
301 <listitem>
302 <para>
304 record separator for unaligned output. This is equivalent to the
306 </para>
307 </listitem>
308 </varlistentry>
310 <varlistentry>
313 <listitem>
314 <para>
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.
318 </para>
319 </listitem>
320 </varlistentry>
322 <varlistentry>
325 <listitem>
326 <para>
327 Runs in single-line mode where a newline terminates an SQL command, as a
328 semicolon does.
329 </para>
331 <note>
332 <para>
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
336 execution might not always be clear to the inexperienced user.
337 </para>
338 </note>
339 </listitem>
340 </varlistentry>
342 <varlistentry>
345 <listitem>
346 <para>
347 Turn off printing of column names and result row count footers,
349 </para>
350 </listitem>
351 </varlistentry>
353 <varlistentry>
356 <listitem>
357 <para>
358 Allows you to specify options to be placed within the
361 </para>
362 </listitem>
363 </varlistentry>
365 <varlistentry>
368 <listitem>
369 <para>
370 Connect to the database as the user <replaceable
372 (You must have permission to do so, of course.)
373 </para>
374 </listitem>
375 </varlistentry>
377 <varlistentry>
381 <listitem>
382 <para>
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.
390 </para>
391 </listitem>
392 </varlistentry>
394 <varlistentry>
397 <listitem>
398 <para>
400 </para>
401 </listitem>
402 </varlistentry>
404 <varlistentry>
407 <listitem>
408 <para>
409 Never issue a password prompt. If the server requires password
410 authentication and a password is not available by other means
412 attempt will fail. This option can be useful in batch jobs and
413 scripts where no user is present to enter a password.
414 </para>
416 <para>
417 Note that this option will remain set for the entire session,
418 and so it affects uses of the meta-command
420 </para>
421 </listitem>
422 </varlistentry>
424 <varlistentry>
427 <listitem>
428 <para>
430 password before connecting to a database.
431 </para>
433 <para>
435 will automatically prompt for a password if the server demands
437 will waste a connection attempt finding out that the server wants a
439 the extra connection attempt.
440 </para>
442 <para>
443 Note that this option will remain set for the entire session,
444 and so it affects uses of the meta-command
446 </para>
447 </listitem>
448 </varlistentry>
450 <varlistentry>
453 <listitem>
454 <para>
455 Turn on the expanded table formatting mode. This is equivalent to the
457 </para>
458 </listitem>
459 </varlistentry>
461 <varlistentry>
464 <listitem>
465 <para>
466 Do not read the start-up file (neither the system-wide
469 </para>
470 </listitem>
471 </varlistentry>
473 <varlistentry>
476 <listitem>
477 <para>
481 as a single transaction. This ensures that either all the commands
482 complete successfully, or no changes are applied.
483 </para>
485 <para>
488 effects.
489 Also, if the script contains any command that cannot be executed
490 inside a transaction block, specifying this option will cause that
491 command (and hence the whole transaction) to fail.
492 </para>
493 </listitem>
494 </varlistentry>
496 <varlistentry>
499 <listitem>
500 <para>
502 arguments, and exit.
503 </para>
504 </listitem>
505 </varlistentry>
506 </variablelist>
507 </refsect1>
510 <refsect1>
513 <para>
515 finished normally, 1 if a fatal error of its own (out of memory,
516 file not found) occurs, 2 if the connection to the server went bad
517 and the session was not interactive, and 3 if an error occurred in a
519 </para>
520 </refsect1>
523 <refsect1>
529 <para>
532 to connect to a database you need to know the name of your target
533 database, the host name and port number of the server and what user
535 told about those parameters via command line options, namely
538 not belong to any option it will be interpreted as the database name
539 (or the user name, if the database name is already given). Not all
540 these options are required; there are useful defaults. If you omit the host
543 machines that don't have Unix-domain sockets. The default port number is
544 determined at compile time.
545 Since the database server uses the same default, you will not have
546 to specify the port in most cases. The default user name is your
547 Unix user name, as is the default database name. Note that you cannot
548 just connect to any database under any user name. Your database
549 administrator should have informed you about your access rights.
550 </para>
552 <para>
553 When the defaults aren't quite right, you can save yourself
554 some typing by setting the environment variables
557 values. (For additional environment variables, see <xref
561 </para>
563 <para>
564 An alternative way to specify connection parameters is in a
566 database name. This mechanism give you very wide control over the
567 connection. For example:
568 <programlisting>
570 </programlisting>
571 This way you can also use LDAP for connection parameter lookup as
574 available connection options.
575 </para>
577 <para>
578 If the connection could not be made for any reason (e.g., insufficient
579 privileges, server is not running on the targeted host, etc.),
581 </para>
582 </refsect2>
587 <para>
589 prompt with the name of the database to which
592 <programlisting>
594 psql (&version;)
595 Type "help" for help.
597 testdb=>
598 </programlisting>
599 </para>
601 <para>
603 Ordinarily, input lines are sent to the server when a
604 command-terminating semicolon is reached. An end of line does not
605 terminate a command. Thus commands can be spread over several lines for
606 clarity. If the command was sent and executed without error, the results
607 of the command are displayed on the screen.
608 </para>
610 <para>
612 for asynchronous notification events generated by
615 </para>
616 </refsect2>
618 <refsect2>
621 <para>
625 itself. These commands help make
627 scripting. Meta-commands are more commonly called slash or backslash
628 commands.
629 </para>
631 <para>
633 followed immediately by a command verb, then any arguments. The arguments
634 are separated from the command verb and each other by any number of
635 whitespace characters.
636 </para>
638 <para>
639 To include whitespace into an argument you can quote it with a
640 single quote. To include a single quote into such an argument,
641 use two single quotes. Anything contained in single quotes is
642 furthermore subject to C-like substitutions for
646 </para>
648 <para>
651 variable is used as the argument instead.
652 </para>
654 <para>
656 are taken as a command line that is passed to the shell. The
657 output of the command (with any trailing newline removed) is taken
658 as the argument value. The above escape sequences also apply in
659 backquotes.
660 </para>
662 <para>
664 table name) as argument. These arguments follow the syntax rules
667 from case conversion and allow incorporation of whitespace into
668 the identifier. Within double quotes, paired double quotes reduce
669 to a single double quote in the resulting name. For example,
672 name</>.
673 </para>
675 <para>
676 Parsing for arguments stops when another unquoted backslash occurs.
677 This is taken as the beginning of a new meta-command. The special
682 line. But in any case, the arguments of a meta-command cannot
683 continue beyond the end of the line.
684 </para>
686 <para>
687 The following meta-commands are defined:
689 <variablelist>
690 <varlistentry>
692 <listitem>
693 <para>
694 If the current table output format is unaligned, it is switched to aligned.
695 If it is not unaligned, it is set to unaligned. This command is
697 more general solution.
698 </para>
699 </listitem>
700 </varlistentry>
702 <varlistentry>
704 <listitem>
705 <para>
706 Changes the current working directory to
708 to the current user's home directory.
709 </para>
711 <tip>
712 <para>
714 </para>
715 </tip>
716 </listitem>
717 </varlistentry>
719 <varlistentry>
721 <listitem>
722 <para>
723 Sets the title of any tables being printed as the result of a
724 query or unset any such title. This command is equivalent to
728 previously only used to set the caption in an
730 </para>
731 </listitem>
732 </varlistentry>
734 <varlistentry>
735 <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>
736 <listitem>
737 <para>
739 server. If the new connection is successfully made, the
740 previous connection is closed. If any of <replaceable
746 previous connection is used. If there is no previous
748 the parameter's value is used.
749 </para>
751 <para>
752 If the connection attempt failed (wrong user name, access
753 denied, etc.), the previous connection will only be kept if
755 executing a non-interactive script, processing will
756 immediately stop with an error. This distinction was chosen as
757 a user convenience against typos on the one hand, and a safety
758 mechanism that scripts are not accidentally acting on the
759 wrong database on the other hand.
760 </para>
761 </listitem>
762 </varlistentry>
764 <varlistentry>
765 <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
768 [ with ]
769 [ binary ]
770 [ oids ]
773 [ csv
774 [ header ]
779 </term>
781 <listitem>
782 <para>
783 Performs a frontend (client) copy. This is an operation that
786 reading or writing the specified file,
788 routes the data between the server and the local file system.
789 This means that file accessibility and privileges are those of
790 the local user, not the server, and no SQL superuser
791 privileges are required.
792 </para>
794 <para>
795 The syntax of the command is similar to that of the
799 command. In particular, the variable substitution rules and
800 backslash escapes do not apply.
801 </para>
803 <para>
805 reads/writes based on the command input and output respectively.
806 All rows are read from the same source that issued the command,
809 command output. To read/write from
812 for populating tables in-line within a SQL script file.
813 </para>
815 <tip>
816 <para>
819 through the client/server connection. For large
821 </para>
822 </tip>
824 </listitem>
825 </varlistentry>
827 <varlistentry>
829 <listitem>
830 <para>
831 Shows the copyright and distribution terms of
833 </para>
834 </listitem>
835 </varlistentry>
837 <varlistentry>
840 <listitem>
841 <para>
842 For each relation (table, view, index, or sequence) matching the
844 columns, their types, the tablespace (if not the default) and any special
846 any. Associated indexes, constraints, rules, and triggers are
847 also shown, as is the view definition if the relation is a view.
849 </para>
851 <para>
853 more information is displayed: any comments associated with the
854 columns of the table are shown, as is the presence of OIDs in the
855 table.
856 </para>
858 <para>
859 By default, only user-created objects are shown; supply a
861 objects.
862 </para>
864 <note>
865 <para>
869 all tables, views, and sequences. This is purely a convenience
870 measure.
871 </para>
872 </note>
873 </listitem>
874 </varlistentry>
876 <varlistentry>
879 <listitem>
880 <para>
881 Lists all available aggregate functions, together with their
882 return type and the data types they operate on. If <replaceable
884 is specified, only aggregates whose names match the pattern are shown.
885 By default, only user-created objects are shown; supply a
887 objects.
888 </para>
889 </listitem>
890 </varlistentry>
893 <varlistentry>
896 <listitem>
897 <para>
898 Lists all available tablespaces. If <replaceable
900 is specified, only tablespaces whose names match the pattern are shown.
902 is listed with its associated permissions.
903 </para>
904 </listitem>
905 </varlistentry>
908 <varlistentry>
910 <listitem>
911 <para>
912 Lists all available conversions between character-set encodings.
914 is specified, only conversions whose names match the pattern are
915 listed.
916 By default, only user-created objects are shown; supply a
918 objects.
919 </para>
920 </listitem>
921 </varlistentry>
924 <varlistentry>
926 <listitem>
927 <para>
928 Lists all available type casts.
930 is specified, only casts whose source or target types match the
931 pattern are listed.
932 </para>
933 </listitem>
934 </varlistentry>
937 <varlistentry>
939 <listitem>
940 <para>
941 Shows the descriptions of objects matching the <replaceable
943 no argument is given. But in either case, only objects that have
944 a description are listed.
945 By default, only user-created objects are shown; supply a
947 objects.
949 types, relations (tables, views, indexes, sequences), large
950 objects, rules, and triggers. For example:
951 <programlisting>
953 Object descriptions
954 Schema | Name | Object | Description
955 ------------+---------+----------+---------------------------
956 pg_catalog | version | function | PostgreSQL version string
957 (1 row)
958 </programlisting>
959 </para>
961 <para>
962 Descriptions for objects can be created with the <xref
965 </para>
966 </listitem>
967 </varlistentry>
970 <varlistentry>
972 <listitem>
973 <para>
974 Lists all available domains. If <replaceable
976 is specified, only matching domains are shown.
977 By default, only user-created objects are shown; supply a
979 objects.
980 </para>
981 </listitem>
982 </varlistentry>
985 <varlistentry>
986 <term><literal>\des[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
987 <listitem>
988 <para>
989 Lists all foreign servers (mnemonic: <quote>external
990 servers</quote>).
992 specified, only those servers whose name matches the pattern
994 full desription of each server is shown, including the
995 server's ACL, type, version, and options.
996 </para>
997 </listitem>
998 </varlistentry>
1001 <varlistentry>
1002 <term><literal>\deu[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1003 <listitem>
1004 <para>
1005 Lists all user mappings (mnemonic: <quote>external
1006 users</quote>).
1008 specified, only those mappings whose user names match the
1010 used, additional information about each mapping is shown.
1011 </para>
1013 <caution>
1014 <para>
1016 password of the remote user, so care should be taken not to
1017 disclose them.
1018 </para>
1019 </caution>
1020 </listitem>
1021 </varlistentry>
1024 <varlistentry>
1025 <term><literal>\dew[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1026 <listitem>
1027 <para>
1028 Lists all foreign-data wrappers (mnemonic: <quote>external
1029 wrappers</quote>).
1031 specified, only those foreign-data wrappers whose name matches
1033 is used, the ACL and options of the foreign-data wrapper are
1034 also shown.
1035 </para>
1036 </listitem>
1037 </varlistentry>
1040 <varlistentry>
1041 <term><literal>\df[antwS+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1043 <listitem>
1044 <para>
1045 Lists available functions, together with their arguments,
1046 return types, and their function types: 'agg' (aggregate),
1047 'normal', 'trigger', and 'window'. To display only functions
1051 functions whose names match the pattern are shown. If the
1053 about each function, including volatility, language, source
1054 code and description, is shown. By default, only user-created
1056 modifier to include system objects.
1057 </para>
1059 <note>
1060 <para>
1061 To look up functions taking arguments or returning values of a specific
1063 output.
1064 </para>
1065 </note>
1067 </listitem>
1068 </varlistentry>
1070 <varlistentry>
1071 <term><literal>\dF[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1072 <listitem>
1073 <para>
1074 Lists available text search configurations.
1076 only configurations whose names match the pattern are shown.
1078 each configuration is shown, including the underlying text search
1079 parser and the dictionary list for each parser token type.
1080 </para>
1081 </listitem>
1082 </varlistentry>
1084 <varlistentry>
1085 <term><literal>\dFd[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1086 <listitem>
1087 <para>
1088 Lists available text search dictionaries.
1090 only dictionaries whose names match the pattern are shown.
1092 is shown about each selected dictionary, including the underlying
1093 text search template and the option values.
1094 </para>
1095 </listitem>
1096 </varlistentry>
1098 <varlistentry>
1099 <term><literal>\dFp[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1100 <listitem>
1101 <para>
1102 Lists available text search parsers.
1104 only parsers whose names match the pattern are shown.
1106 each parser is shown, including the underlying functions and the
1107 list of recognized token types.
1108 </para>
1109 </listitem>
1110 </varlistentry>
1112 <varlistentry>
1113 <term><literal>\dFt[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1114 <listitem>
1115 <para>
1116 Lists available text search templates.
1118 only templates whose names match the pattern are shown.
1120 is shown about each template, including the underlying function names.
1121 </para>
1122 </listitem>
1123 </varlistentry>
1126 <varlistentry>
1128 <listitem>
1129 <para>
1130 Lists all database roles. If <replaceable
1132 those roles whose names match the pattern are listed.
1134 </para>
1135 </listitem>
1136 </varlistentry>
1139 <varlistentry>
1140 <term><literal>\di[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1141 <term><literal>\ds[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1142 <term><literal>\dt[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1143 <term><literal>\dv[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1145 <listitem>
1146 <para>
1147 In this group of commands, the letters
1150 stand for index, sequence, table, and view, respectively.
1151 You can specify any or all of
1152 these letters, in any order, to obtain a listing of all the
1155 appended to the command name, each object is listed with its
1156 physical size on disk and its associated description, if any.
1157 By default, only user-created objects are shown; supply a
1159 objects.
1160 </para>
1162 <para>
1164 specified, only objects whose names match the pattern are listed.
1165 </para>
1166 </listitem>
1167 </varlistentry>
1170 <varlistentry>
1172 <listitem>
1173 <para>
1175 list of large objects.
1176 </para>
1177 </listitem>
1178 </varlistentry>
1181 <varlistentry>
1182 <term><literal>\dn[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1184 <listitem>
1185 <para>
1186 Lists available schemas (namespaces). If <replaceable
1188 is specified, only schemas whose names match the pattern are listed.
1190 is appended to the command name, each object is listed with its associated
1191 permissions and description, if any.
1192 </para>
1193 </listitem>
1194 </varlistentry>
1197 <varlistentry>
1198 <term><literal>\do[S] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1199 <listitem>
1200 <para>
1201 Lists available operators with their operand and return types.
1203 specified, only operators whose names match the pattern are listed.
1204 By default, only user-created objects are shown; supply a
1206 objects.
1207 </para>
1208 </listitem>
1209 </varlistentry>
1212 <varlistentry>
1214 <listitem>
1215 <para>
1216 Lists available tables, views and sequences with their
1217 associated access privileges.
1219 specified, only tables, views and sequences whose names match the pattern are listed.
1220 </para>
1222 <para>
1225 commands are used to set access privileges.
1226 </para>
1227 </listitem>
1228 </varlistentry>
1231 <varlistentry>
1232 <term><literal>\dT[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1233 <listitem>
1234 <para>
1235 Lists available data types.
1237 specified, only types whose names match the pattern are listed.
1239 listed with its internal name and size, as well as its allowed values
1241 By default, only user-created objects are shown; supply a
1243 objects.
1244 </para>
1245 </listitem>
1246 </varlistentry>
1249 <varlistentry>
1251 <listitem>
1252 <para>
1253 Lists all database roles. If <replaceable
1255 those roles whose names match the pattern are listed.
1256 </para>
1257 </listitem>
1258 </varlistentry>
1261 <varlistentry>
1262 <term><literal>\edit</literal> (or <literal>\e</literal>) <literal><optional> <replaceable class="parameter">filename</replaceable> </optional></literal></term>
1264 <listitem>
1265 <para>
1267 specified, the file is edited; after the editor exits, its
1268 content is copied back to the query buffer. If no argument is
1269 given, the current query buffer is copied to a temporary file
1270 which is then edited in the same fashion.
1271 </para>
1273 <para>
1274 The new query buffer is then re-parsed according to the normal
1276 is treated as a single line. (Thus you cannot make scripts this
1278 if the query ends with (or rather contains) a semicolon, it is
1279 immediately executed. In other cases it will merely wait in the
1280 query buffer.
1281 </para>
1283 <tip>
1284 <para>
1290 </para>
1291 </tip>
1292 </listitem>
1293 </varlistentry>
1296 <varlistentry>
1297 <term><literal>\ef <optional> <replaceable class="parameter">function_description</replaceable> </optional></literal></term>
1299 <listitem>
1300 <para>
1301 This command fetches and edits the definition of the named function,
1304 After the editor exits, the updated command waits in the query buffer;
1306 to cancel.
1307 </para>
1309 <para>
1310 The target function can be specified by name alone, or by name
1312 The argument types must be given if there is more
1313 than one function of the same name.
1314 </para>
1316 <para>
1318 template is presented for editing.
1319 </para>
1320 </listitem>
1321 </varlistentry>
1324 <varlistentry>
1325 <term><literal>\echo <replaceable class="parameter">text</replaceable> [ ... ]</literal></term>
1326 <listitem>
1327 <para>
1328 Prints the arguments to the standard output, separated by one
1329 space and followed by a newline. This can be useful to
1330 intersperse information in the output of scripts. For example:
1331 <programlisting>
1334 </programlisting>
1336 newline is not written.
1337 </para>
1339 <tip>
1340 <para>
1343 instead of this command.
1344 </para>
1345 </tip>
1346 </listitem>
1347 </varlistentry>
1350 <varlistentry>
1351 <term><literal>\encoding [ <replaceable class="parameter">encoding</replaceable> ]</literal></term>
1353 <listitem>
1354 <para>
1355 Sets the client character set encoding. Without an argument, this command
1356 shows the current encoding.
1357 </para>
1358 </listitem>
1359 </varlistentry>
1362 <varlistentry>
1365 <listitem>
1366 <para>
1367 Sets the field separator for unaligned query output. The default
1370 options.
1371 </para>
1372 </listitem>
1373 </varlistentry>
1376 <varlistentry>
1377 <term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
1379 <listitem>
1380 <para>
1381 Sends the current query input buffer to the server and
1382 optionally stores the query's output in <replaceable
1384 into a separate Unix shell executing <replaceable
1389 </para>
1390 </listitem>
1391 </varlistentry>
1393 <varlistentry>
1394 <term><literal>\help</literal> (or <literal>\h</literal>) <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
1395 <listitem>
1396 <para>
1400 all the commands for which syntax help is available. If
1404 </para>
1406 <note>
1407 <para>
1408 To simplify typing, commands that consists of several words do
1409 not have to be quoted. Thus it is fine to type <userinput>\help
1410 alter table</userinput>.
1411 </para>
1412 </note>
1413 </listitem>
1414 </varlistentry>
1417 <varlistentry>
1419 <listitem>
1420 <para>
1423 back to the default aligned text format. This command is for
1425 about setting other output options.
1426 </para>
1427 </listitem>
1428 </varlistentry>
1431 <varlistentry>
1433 <listitem>
1434 <para>
1435 Reads input from the file <replaceable
1437 though it had been typed on the keyboard.
1438 </para>
1439 <note>
1440 <para>
1441 If you want to see the lines on the screen as they are read you
1444 </para>
1445 </note>
1446 </listitem>
1447 </varlistentry>
1450 <varlistentry>
1453 <listitem>
1454 <para>
1455 List the names, owners, character set encodings, and access privileges
1456 of all the databases in the server.
1458 sizes, default tablespaces, and descriptions are also displayed.
1459 (Size information is only available for databases that the current
1460 user can connect to.)
1461 </para>
1462 </listitem>
1463 </varlistentry>
1466 <varlistentry>
1467 <term><literal>\lo_export <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></literal></term>
1469 <listitem>
1470 <para>
1473 writes it to <replaceable
1475 subtly different from the server function
1477 of the user that the database server runs as and on the server's
1478 file system.
1479 </para>
1480 <tip>
1481 <para>
1484 </para>
1485 </tip>
1486 </listitem>
1487 </varlistentry>
1490 <varlistentry>
1491 <term><literal>\lo_import <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</literal></term>
1493 <listitem>
1494 <para>
1496 large object. Optionally, it associates the given
1497 comment with the object. Example:
1498 <programlisting>
1500 lo_import 152801
1501 </programlisting>
1502 The response indicates that the large object received object
1503 ID 152801, which can be used to access the newly-created large
1504 object in the future. For the sake of readability, it is
1505 recommended to always associate a human-readable comment with
1506 every object. Both OIDs and comments can be viewed with the
1508 </para>
1510 <para>
1511 Note that this command is subtly different from the server-side
1513 on the local file system, rather than the server's user and file
1514 system.
1515 </para>
1516 </listitem>
1517 </varlistentry>
1519 <varlistentry>
1521 <listitem>
1522 <para>
1524 large objects currently stored in the database,
1525 along with any comments provided for them.
1526 </para>
1527 </listitem>
1528 </varlistentry>
1530 <varlistentry>
1533 <listitem>
1534 <para>
1537 database.
1538 </para>
1540 <tip>
1541 <para>
1544 </para>
1545 </tip>
1546 </listitem>
1547 </varlistentry>
1550 <varlistentry>
1551 <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
1553 <listitem>
1554 <para>
1555 Saves future query results to the file <replaceable
1557 into a separate Unix shell to execute <replaceable
1559 specified, the query output will be reset to the standard output.
1560 </para>
1562 <para>
1564 responses, and notices obtained from the database server, as
1565 well as output of various backslash commands that query the
1567 messages.
1568 </para>
1570 <tip>
1571 <para>
1572 To intersperse text output in between query results, use
1574 </para>
1575 </tip>
1576 </listitem>
1577 </varlistentry>
1580 <varlistentry>
1582 <listitem>
1583 <para>
1584 Print the current query buffer to the standard output.
1585 </para>
1586 </listitem>
1587 </varlistentry>
1589 <varlistentry>
1590 <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term>
1591 <listitem>
1592 <para>
1593 Changes the password of the specified user (by default, the current
1594 user). This command prompts for the new password, encrypts it, and
1596 makes sure that the new password does not appear in cleartext in the
1597 command history, the server log, or elsewhere.
1598 </para>
1599 </listitem>
1600 </varlistentry>
1602 <varlistentry>
1603 <term><literal>\prompt [ <replaceable class="parameter">text</replaceable> ] <replaceable class="parameter">name</replaceable></literal></term>
1604 <listitem>
1605 <para>
1606 Prompts the user to set variable <replaceable
1609 prompts, use single-quotes.)
1610 </para>
1612 <para>
1616 </para>
1617 </listitem>
1618 </varlistentry>
1620 <varlistentry>
1621 <term><literal>\pset <replaceable class="parameter">parameter</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
1623 <listitem>
1624 <para>
1625 This command sets options affecting the output of query result
1627 describes which option is to be set. The semantics of
1629 thereon.
1630 </para>
1632 <para>
1633 Adjustable printing options are:
1634 <variablelist>
1635 <varlistentry>
1637 <listitem>
1638 <para>
1643 Unique abbreviations are allowed. (That would mean one letter
1644 is enough.)
1645 </para>
1647 <para>
1649 line, separated by the currently active field separator. This
1650 is intended to create output that might be intended to be read
1651 in by other programs (tab-separated, comma-separated).
1653 nicely formatted text output that is default.
1654 </para>
1656 <para>
1660 output and wrapped width is controlled by the environment
1663 is wrapped, including file and pipe output.
1664 </para>
1666 <para>
1669 be included in documents using the respective mark-up
1670 language. They are not complete documents! (This might not be
1672 have a complete document wrapper.)
1673 </para>
1674 </listitem>
1675 </varlistentry>
1677 <varlistentry>
1679 <listitem>
1680 <para>
1682 and width for determining if wide output requires the pager.
1684 affect only screen output.
1685 </para>
1686 </listitem>
1687 </varlistentry>
1689 <varlistentry>
1691 <listitem>
1692 <para>
1693 The second argument must be a number. In general, the higher
1694 the number the more borders and lines the tables will have,
1695 but this depends on the particular format. In
1699 and 2 (table frame) make sense.
1700 </para>
1701 </listitem>
1702 </varlistentry>
1704 <varlistentry>
1706 <listitem>
1707 <para>
1708 You can specify an optional second argument, if it is provided it
1710 which will enable or disable expanded mode. If the second
1711 argument is not provided then we will toggle between regular and
1712 expanded format. When expanded format is enabled, query results
1713 are displayed in two columns, with the column name on the left and
1714 the data on the right. This mode is useful if the data wouldn't fit
1716 </para>
1718 <para>
1719 Expanded mode is supported by all four output formats.
1720 </para>
1721 </listitem>
1722 </varlistentry>
1724 <varlistentry>
1726 <listitem>
1727 <para>
1728 The second argument is a string that should be printed
1729 whenever a column is null. The default is not to print
1730 anything, which can easily be mistaken for, say, an empty
1731 string. Thus, one might choose to write <literal>\pset null
1732 '(null)'</literal>.
1733 </para>
1734 </listitem>
1735 </varlistentry>
1737 <varlistentry>
1739 <listitem>
1740 <para>
1741 Specifies the field separator to be used in unaligned output
1742 mode. That way one can create, for example, tab- or
1743 comma-separated output, which other programs might prefer. To
1744 set a tab as field separator, type <literal>\pset fieldsep
1745 '\t'</literal>. The default field separator is
1747 </para>
1748 </listitem>
1749 </varlistentry>
1751 <varlistentry>
1753 <listitem>
1754 <para>
1755 You can specify an optional second argument, if it is provided it
1757 which will enable or disable display of the default footer
1759 provided then we will toggle between on and off.
1760 </para>
1761 </listitem>
1762 </varlistentry>
1764 <varlistentry>
1766 <listitem>
1767 <para>
1768 You can specify an optional second argument, if it is provided it
1770 which will enable or disable display of a locale-aware character
1771 to separate groups of digits to the left of the decimal marker. If
1772 the second argument is not provided then we will toggle between
1773 on and off.
1774 </para>
1775 </listitem>
1776 </varlistentry>
1778 <varlistentry>
1780 <listitem>
1781 <para>
1782 Specifies the record (line) separator to use in unaligned
1783 output mode. The default is a newline character.
1784 </para>
1785 </listitem>
1786 </varlistentry>
1788 <varlistentry>
1790 <listitem>
1791 <para>
1792 You can specify an optional second argument, if it is provided it
1794 which will enable or disable the tuples only mode. If the
1795 second argument is not provided then we will toggle between tuples
1796 only and full display. Full display shows extra information such
1797 as column headers, titles, and various footers. In tuples only
1798 mode, only actual table data is shown.
1799 </para>
1800 </listitem>
1801 </varlistentry>
1803 <varlistentry>
1805 <listitem>
1806 <para>
1807 Sets the table title for any subsequently printed tables. This
1808 can be used to give your output descriptive tags. If no
1809 argument is given, the title is unset.
1810 </para>
1811 </listitem>
1812 </varlistentry>
1814 <varlistentry>
1815 <term><literal>tableattr</literal> (or <literal>T</literal>) <literal>[ <replaceable class="parameter">text</replaceable> ]</literal></term>
1816 <listitem>
1817 <para>
1818 Allows you to specify any attributes to be placed inside the
1824 </para>
1825 </listitem>
1826 </varlistentry>
1829 <varlistentry>
1831 <listitem>
1832 <para>
1835 is set, the output is piped to the specified program.
1836 Otherwise a platform-dependent default (such as
1838 </para>
1840 <para>
1843 output is to a terminal and will not fit on the screen.
1846 always used.
1847 </para>
1848 </listitem>
1849 </varlistentry>
1850 </variablelist>
1851 </para>
1853 <para>
1854 Illustrations on how these different formats look can be seen in
1857 </para>
1859 <tip>
1860 <para>
1864 </para>
1865 </tip>
1867 <note>
1868 <para>
1870 arguments. In the future this call might show the current status
1871 of all printing options.
1872 </para>
1873 </note>
1875 </listitem>
1876 </varlistentry>
1879 <varlistentry>
1881 <listitem>
1882 <para>
1884 </para>
1885 </listitem>
1886 </varlistentry>
1889 <varlistentry>
1890 <term><literal>\qecho <replaceable class="parameter">text</replaceable> [ ... ] </literal></term>
1891 <listitem>
1892 <para>
1894 that the output will be written to the query output channel, as
1896 </para>
1897 </listitem>
1898 </varlistentry>
1901 <varlistentry>
1903 <listitem>
1904 <para>
1905 Resets (clears) the query buffer.
1906 </para>
1907 </listitem>
1908 </varlistentry>
1911 <varlistentry>
1913 <listitem>
1914 <para>
1915 Print or save the command line history to <replaceable
1918 is written to the standard output. This option is only available
1921 </para>
1922 </listitem>
1923 </varlistentry>
1926 <varlistentry>
1927 <term><literal>\set [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ] ] ]</literal></term>
1929 <listitem>
1930 <para>
1931 Sets the internal variable <replaceable
1934 is given, to the concatenation of all of them. If no second
1935 argument is given, the variable is just set with no value. To
1937 </para>
1939 <para>
1940 Valid variable names can contain characters, digits, and
1941 underscores. See the section <xref
1944 Variable names are case-sensitive.
1945 </para>
1947 <para>
1948 Although you are welcome to set any variable to anything you
1950 as special. They are documented in the section about variables.
1951 </para>
1953 <note>
1954 <para>
1957 </para>
1958 </note>
1959 </listitem>
1960 </varlistentry>
1963 <varlistentry>
1965 <listitem>
1966 <para>
1967 Toggles the display of output column name headings and row count
1968 footer. This command is equivalent to <literal>\pset
1969 tuples_only</literal> and is provided for convenience.
1970 </para>
1971 </listitem>
1972 </varlistentry>
1975 <varlistentry>
1977 <listitem>
1978 <para>
1979 Allows you to specify attributes to be placed within the
1981 output mode. This command is equivalent to <literal>\pset
1982 tableattr <replaceable
1984 </para>
1985 </listitem>
1986 </varlistentry>
1989 <varlistentry>
1990 <term><literal>\timing [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> ]</literal></term>
1991 <listitem>
1992 <para>
1993 Without parameter, toggles a display of how long each SQL statement
1994 takes, in milliseconds. With parameter, sets same.
1995 </para>
1996 </listitem>
1997 </varlistentry>
2000 <varlistentry>
2001 <term><literal>\w</literal> {<replaceable class="parameter">filename</replaceable> | <replaceable class="parameter">|command</replaceable>}</term>
2002 <listitem>
2003 <para>
2004 Outputs the current query buffer to the file <replaceable
2007 </para>
2008 </listitem>
2009 </varlistentry>
2012 <varlistentry>
2014 <listitem>
2015 <para>
2016 Toggles expanded table formatting mode. As such it is equivalent to
2018 </para>
2019 </listitem>
2020 </varlistentry>
2023 <varlistentry>
2025 <listitem>
2026 <para>
2027 Produces a list of all available tables, views and sequences with their
2028 associated access privileges.
2030 specified, only tables,views and sequences whose names match the pattern are listed.
2031 </para>
2033 <para>
2036 commands are used to set access privileges.
2037 </para>
2039 <para>
2041 privileges</quote>).
2042 </para>
2043 </listitem>
2044 </varlistentry>
2047 <varlistentry>
2049 <listitem>
2050 <para>
2051 Escapes to a separate Unix shell or executes the Unix command
2053 arguments are not further interpreted, the shell will see them
2054 as is.
2055 </para>
2056 </listitem>
2057 </varlistentry>
2060 <varlistentry>
2062 <listitem>
2063 <para>
2064 Shows help information about the backslash commands.
2065 </para>
2066 </listitem>
2067 </varlistentry>
2069 </variablelist>
2070 </para>
2075 <indexterm>
2078 </indexterm>
2080 <para>
2083 object name(s) to be displayed. In the simplest case, a pattern
2084 is just the exact name of the object. The characters within a
2085 pattern are normally folded to lower case, just as in SQL names;
2088 a pattern stops folding to lower case. Should you need to include
2089 an actual double quote character in a pattern, write it as a pair
2090 of double quotes within a double-quote sequence; again this is in
2091 accord with the rules for SQL quoted identifiers. For example,
2094 rules for SQL names, you can put double quotes around just part
2097 </para>
2099 <para>
2102 (This notation is comparable to Unix shell file name patterns.)
2106 literally.
2107 </para>
2109 <para>
2111 name pattern followed by an object name pattern. For example,
2115 matches only objects that are visible in the current schema search path.
2116 Again, a dot within double quotes loses its special meaning and is matched
2117 literally.
2118 </para>
2120 <para>
2121 Advanced users can use regular-expression notations such as character
2123 expression special characters work as specified in
2129 these pattern characters at need by writing
2136 the pattern must match the whole name, unlike the usual
2139 beginning and/or end if you don't wish the pattern to be anchored.
2140 Note that within double quotes, all regular expression special characters
2141 lose their special meanings and are matched literally. Also, the regular
2142 expression special characters are matched literally in operator name
2144 </para>
2146 <para>
2149 that are visible in the current schema search path — this is
2152 </para>
2153 </refsect3>
2154 </refsect2>
2156 <refsect2>
2162 <para>
2164 features similar to common Unix command shells.
2165 Variables are simply name/value pairs, where the value
2166 can be any string of any length. To set variables, use the
2169 <programlisting>
2171 </programlisting>
2174 the name with a colon and use it as the argument of any slash
2175 command:
2176 <programlisting>
2178 bar
2179 </programlisting>
2180 </para>
2182 <note>
2183 <para>
2185 substitution rules as with other commands. Thus you can construct
2186 interesting references such as <literal>\set :foo
2190 respectively. Unfortunately (or fortunately?), there is no way to do
2191 anything useful with these constructs. On the other hand,
2193 variable.
2194 </para>
2195 </note>
2197 <para>
2199 variable is set, with an empty string as value. To unset (or delete) a
2201 </para>
2203 <para>
2205 consist of letters, numbers, and underscores in any order and any
2206 number of them. A number of these variables are treated specially
2208 settings that can be changed at run time by altering the value of
2209 the variable or represent some state of the application. Although
2210 you can use these variables for any other purpose, this is not
2211 recommended, as the program behavior might grow really strange
2212 really quickly. By convention, all specially treated variables
2213 consist of all upper-case letters (and possibly numbers and
2214 underscores). To ensure maximum compatibility in the future, avoid
2215 using such variable names for your own purposes. A list of all specially
2216 treated variables follows.
2217 </para>
2219 <variablelist>
2220 <varlistentry>
2221 <indexterm>
2224 </indexterm>
2226 <listitem>
2227 <para>
2229 committed upon successful completion. To postpone commit in this
2232 commands are not committed until you explicitly issue
2235 before any command that is not already in a transaction block and
2237 command, nor a command that cannot be executed inside a transaction
2239 </para>
2241 <note>
2242 <para>
2243 In autocommit-off mode, you must explicitly abandon any failed
2245 Also keep in mind that if you exit the session
2246 without committing, your work will be lost.
2247 </para>
2248 </note>
2250 <note>
2251 <para>
2253 behavior, but autocommit-off is closer to the SQL spec. If you
2254 prefer autocommit-off, you might wish to set it in the system-wide
2257 </para>
2258 </note>
2259 </listitem>
2260 </varlistentry>
2262 <varlistentry>
2264 <listitem>
2265 <para>
2266 The name of the database you are currently connected to. This is
2267 set every time you connect to a database (including program
2268 start-up), but can be unset.
2269 </para>
2270 </listitem>
2271 </varlistentry>
2273 <varlistentry>
2275 <listitem>
2276 <para>
2278 entered from the keyboard or from a script are written to the standard output
2279 before they are parsed or executed. To select this behavior on program
2283 they are sent to the server. The switch for this is
2285 </para>
2286 </listitem>
2287 </varlistentry>
2289 <varlistentry>
2291 <listitem>
2292 <para>
2293 When this variable is set and a backslash command queries the
2294 database, the query is first shown. This way you can study the
2296 similar functionality in your own programs. (To select this behavior
2299 just shown but are not actually sent to the server and executed.
2300 </para>
2301 </listitem>
2302 </varlistentry>
2304 <varlistentry>
2306 <listitem>
2307 <para>
2308 The current client character set encoding.
2309 </para>
2310 </listitem>
2311 </varlistentry>
2313 <varlistentry>
2315 <listitem>
2316 <para>
2319 and displayed in groups of that many rows, rather than the
2320 default behavior of collecting the entire result set before
2321 display. Therefore only a
2322 limited amount of memory is used, regardless of the size of
2324 when enabling this feature.
2325 Keep in mind that when using this feature, a query might
2326 fail after having already displayed some rows.
2327 </para>
2328 <tip>
2329 <para>
2330 Although you can use any output format with this feature,
2333 will be formatted separately, leading to varying column
2334 widths across the row groups. The other output formats work better.
2335 </para>
2336 </tip>
2337 </listitem>
2338 </varlistentry>
2340 <varlistentry>
2342 <listitem>
2343 <para>
2345 lines which begin with a space are not entered into the history
2347 matching the previous history line are not entered. A value of
2349 unset, or if set to any other value than those above, all lines
2350 read in interactive mode are saved on the history list.
2351 </para>
2352 <note>
2353 <para>
2354 This feature was shamelessly plagiarized from
2356 </para>
2357 </note>
2358 </listitem>
2359 </varlistentry>
2361 <varlistentry>
2363 <listitem>
2364 <para>
2365 The file name that will be used to store the history list. The default
2367 <programlisting>
2368 \set HISTFILE ~/.psql_history- :DBNAME
2369 </programlisting>
2372 each database.
2373 </para>
2374 <note>
2375 <para>
2376 This feature was shamelessly plagiarized from
2378 </para>
2379 </note>
2380 </listitem>
2381 </varlistentry>
2383 <varlistentry>
2385 <listitem>
2386 <para>
2387 The number of commands to store in the command history. The
2388 default value is 500.
2389 </para>
2390 <note>
2391 <para>
2392 This feature was shamelessly plagiarized from
2394 </para>
2395 </note>
2396 </listitem>
2397 </varlistentry>
2399 <varlistentry>
2401 <listitem>
2402 <para>
2403 The database server host you are currently connected to. This is
2404 set every time you connect to a database (including program
2405 start-up), but can be unset.
2406 </para>
2407 </listitem>
2408 </varlistentry>
2410 <varlistentry>
2412 <listitem>
2413 <para>
2417 will terminate the application. If set to a numeric value,
2419 application terminates. If the variable is set but has no
2420 numeric value, the default is 10.
2421 </para>
2422 <note>
2423 <para>
2424 This feature was shamelessly plagiarized from
2426 </para>
2427 </note>
2428 </listitem>
2429 </varlistentry>
2431 <varlistentry>
2433 <listitem>
2434 <para>
2435 The value of the last affected OID, as returned from an
2437 command. This variable is only guaranteed to be valid until
2439 been displayed.
2440 </para>
2441 </listitem>
2442 </varlistentry>
2444 <varlistentry>
2445 <indexterm>
2448 </indexterm>
2450 <listitem>
2451 <para>
2453 generates an error, the error is ignored and the transaction
2455 ignored in interactive sessions, and not when reading script
2457 transaction block that generates an error aborts the entire
2458 transaction. The on_error_rollback-on mode works by issuing an
2460 that is in a transaction block, and rolls back to the savepoint
2461 on error.
2462 </para>
2463 </listitem>
2464 </varlistentry>
2466 <varlistentry>
2468 <listitem>
2469 <para>
2470 By default, if non-interactive scripts encounter an error, such
2472 meta-command, processing continues. This has been the
2474 is sometimes not desirable. If this variable is set, script
2475 processing will immediately terminate. If the script was called
2476 from another script it will terminate in the same fashion. If
2477 the outermost script was not called from an interactive
2480 return error code 3, to distinguish this case from fatal error
2481 conditions (error code 1).
2482 </para>
2483 </listitem>
2484 </varlistentry>
2486 <varlistentry>
2488 <listitem>
2489 <para>
2490 The database server port to which you are currently connected.
2491 This is set every time you connect to a database (including
2492 program start-up), but can be unset.
2493 </para>
2494 </listitem>
2495 </varlistentry>
2497 <varlistentry>
2501 <listitem>
2502 <para>
2504 issues should look like. See <xref
2507 </para>
2508 </listitem>
2509 </varlistentry>
2511 <varlistentry>
2513 <listitem>
2514 <para>
2515 This variable is equivalent to the command line option
2517 interactive mode.
2518 </para>
2519 </listitem>
2520 </varlistentry>
2522 <varlistentry>
2524 <listitem>
2525 <para>
2526 This variable is equivalent to the command line option
2528 </para>
2529 </listitem>
2530 </varlistentry>
2532 <varlistentry>
2534 <listitem>
2535 <para>
2536 This variable is equivalent to the command line option
2538 </para>
2539 </listitem>
2540 </varlistentry>
2542 <varlistentry>
2544 <listitem>
2545 <para>
2546 The database user you are currently connected as. This is set
2547 every time you connect to a database (including program
2548 start-up), but can be unset.
2549 </para>
2550 </listitem>
2551 </varlistentry>
2553 <varlistentry>
2555 <listitem>
2556 <para>
2559 of error reports.
2560 </para>
2561 </listitem>
2562 </varlistentry>
2564 </variablelist>
2566 </refsect3>
2568 <refsect3>
2571 <para>
2575 this is again to prepend the variable name with a colon
2577 <programlisting>
2580 </programlisting>
2582 the variable is copied literally, so it can even contain unbalanced
2583 quotes or backslash commands. You must make sure that it makes sense
2584 where you put it. Variable interpolation will not be performed into
2586 </para>
2588 <para>
2589 One possible use of this mechanism is to
2590 copy the contents of a file into a table column. First load the file into a
2591 variable and then proceed as above:
2592 <programlisting>
2595 </programlisting>
2597 might contain single quotes. These need to be escaped so that
2598 they don't cause a syntax error when the second line is processed. This
2600 <programlisting>
2602 </programlisting>
2603 If you are using non-standard-conforming strings then you'll also need
2604 to double backslashes. This is a bit tricky:
2605 <programlisting>
2606 testdb=> <userinput>\set content '''' `sed -e "s/'/''/g" -e 's/\\/\\\\/g' < my_file.txt` ''''</userinput>
2607 </programlisting>
2608 Note the use of different shell quoting conventions so that neither
2609 the single quote marks nor the backslashes are special to the shell.
2611 we need to double them. (Perhaps
2612 at one point you thought it was great that all Unix commands use the
2613 same escape character.)
2614 </para>
2616 <para>
2617 Since colons can legally appear in SQL commands, the following rule
2618 applies: the character sequence
2620 of a variable that is currently set. In any case you can escape
2621 a colon with a backslash to protect it from substitution. (The
2624 The colon syntax for array slices and type casts are
2626 conflict.)
2627 </para>
2629 </refsect3>
2634 <para>
2638 and special escape sequences that describe the appearance of the
2639 prompt. Prompt 1 is the normal prompt that is issued when
2641 issued when more input is expected during command input because the
2642 command was not terminated with a semicolon or a quote was not closed.
2645 row values on the terminal.
2646 </para>
2648 <para>
2649 The value of the selected prompt variable is printed literally,
2651 Depending on the next character, certain other text is substituted
2652 instead. Defined substitutions are:
2654 <variablelist>
2655 <varlistentry>
2657 <listitem>
2658 <para>
2659 The full host name (with domain name) of the database server,
2661 domain socket, or
2663 if the Unix domain socket is not at the compiled in default
2664 location.
2665 </para>
2666 </listitem>
2667 </varlistentry>
2669 <varlistentry>
2671 <listitem>
2672 <para>
2673 The host name of the database server, truncated at the
2675 over a Unix domain socket.
2676 </para>
2677 </listitem>
2678 </varlistentry>
2680 <varlistentry>
2683 </varlistentry>
2685 <varlistentry>
2687 <listitem>
2688 <para>
2689 The database session user name. (The expansion of this
2690 value might change during a database session as the result
2691 of the command <command>SET SESSION
2692 AUTHORIZATION</command>.)
2693 </para>
2694 </listitem>
2695 </varlistentry>
2697 <varlistentry>
2700 </varlistentry>
2702 <varlistentry>
2705 (tilde) if the database is your default database.</para></listitem>
2706 </varlistentry>
2708 <varlistentry>
2710 <listitem>
2711 <para>
2712 If the session user is a database superuser, then a
2714 (The expansion of this value might change during a database
2715 session as the result of the command <command>SET SESSION
2716 AUTHORIZATION</command>.)
2717 </para>
2718 </listitem>
2719 </varlistentry>
2721 <varlistentry>
2723 <listitem>
2724 <para>
2727 disconnected from the database (which can happen if
2730 a double quote, or a dollar sign, depending on whether
2732 command wasn't terminated yet, because you are inside a
2734 a quoted or dollar-escaped string. In prompt 3 the sequence doesn't
2735 produce anything.
2736 </para>
2737 </listitem>
2738 </varlistentry>
2740 <varlistentry>
2742 <listitem>
2743 <para>
2744 Transaction status: an empty string when not in a transaction
2747 when the transaction state is indeterminate (for example, because
2748 there is no connection).
2749 </para>
2750 </listitem>
2751 </varlistentry>
2753 <varlistentry>
2755 <listitem>
2756 <para>
2757 The character with the indicated octal code is substituted.
2758 </para>
2759 </listitem>
2760 </varlistentry>
2762 <varlistentry>
2763 <term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term>
2764 <listitem>
2765 <para>
2770 </para>
2771 </listitem>
2772 </varlistentry>
2774 <varlistentry>
2775 <term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term>
2776 <listitem>
2777 <para>
2778 The output of <replaceable
2781 </para>
2782 </listitem>
2783 </varlistentry>
2785 <varlistentry>
2787 <listitem>
2788 <para>
2789 Prompts can contain terminal control characters which, for
2790 example, change the color, background, or style of the prompt
2791 text, or change the title of the terminal window. In order for
2793 non-printing control characters must be designated as invisible
2796 the prompt. For example:
2797 <programlisting>
2799 </programlisting>
2802 terminals.
2803 </para>
2804 </listitem>
2805 </varlistentry>
2807 </variablelist>
2809 To insert a percent sign into your prompt, write
2813 </para>
2815 <note>
2816 <para>
2817 This feature was shamelessly plagiarized from
2819 </para>
2820 </note>
2822 </refsect3>
2824 <refsect3>
2827 <para>
2829 library for convenient line editing and retrieval. The command
2831 exits and is reloaded when
2833 supported, although the completion logic makes no claim to be an
2835 can turn it off by putting this in a file named
2837 <programlisting>
2838 $if psql
2839 set disable-completion on
2840 $endif
2841 </programlisting>
2844 for further details.)
2845 </para>
2846 </refsect3>
2847 </refsect2>
2848 </refsect1>
2851 <refsect1>
2854 <variablelist>
2856 <varlistentry>
2859 <listitem>
2860 <para>
2863 if wide output requires the pager.
2864 </para>
2865 </listitem>
2866 </varlistentry>
2868 <varlistentry>
2871 <listitem>
2872 <para>
2873 If the query results do not fit on the screen, they are piped
2874 through this command. Typical values are
2876 is platform-dependent. The use of the pager can be disabled by
2878 </para>
2879 </listitem>
2880 </varlistentry>
2882 <varlistentry>
2888 <listitem>
2889 <para>
2891 </para>
2892 </listitem>
2893 </varlistentry>
2895 <varlistentry>
2900 <listitem>
2901 <para>
2903 are examined in the order listed; the first that is set is used.
2904 </para>
2905 </listitem>
2906 </varlistentry>
2908 <varlistentry>
2911 <listitem>
2912 <para>
2914 </para>
2915 </listitem>
2916 </varlistentry>
2918 <varlistentry>
2921 <listitem>
2922 <para>
2923 Directory for storing temporary files. The default is
2925 </para>
2926 </listitem>
2927 </varlistentry>
2928 </variablelist>
2930 <para>
2934 </para>
2936 </refsect1>
2939 <refsect1>
2942 <itemizedlist>
2943 <listitem>
2944 <para>
2946 read and execute commands from the system-wide
2949 (On Windows, the user's startup file is named
2952 for information on setting up the system-wide file. It could be used
2953 to set up the client or the server to taste (using the <command>\set
2955 </para>
2956 </listitem>
2958 <listitem>
2959 <para>
2964 A matching version-specific file will be read in preference to a
2965 non-version-specific file.
2966 </para>
2967 </listitem>
2969 <listitem>
2970 <para>
2971 The command-line history is stored in the file
2974 </para>
2975 </listitem>
2976 </itemizedlist>
2977 </refsect1>
2980 <refsect1>
2983 <itemizedlist>
2984 <listitem>
2985 <para>
2987 first argument of a single-letter backslash command to start
2988 directly after the command, without intervening whitespace.
2990 longer allowed.
2991 </para>
2992 </listitem>
2994 <listitem>
2995 <para>
2997 with servers of the same version. That does not mean other combinations
2998 will fail outright, but subtle and not-so-subtle problems might come
2999 up. Backslash commands are particularly likely to fail if the
3002 servers of versions back to 7.4, though not necessarily with servers
3004 </para>
3005 </listitem>
3007 </itemizedlist>
3008 </refsect1>
3011 <refsect1>
3014 <para>
3016 application</>. Since the Windows console windows use a different
3017 encoding than the rest of the system, you must take special care
3020 console code page, it will warn you at startup. To change the
3021 console code page, two things are necessary:
3023 <itemizedlist>
3024 <listitem>
3025 <para>
3026 Set the code page by entering <userinput>cmd.exe /c chcp
3028 German; replace it with your value.) If you are using Cygwin,
3030 </para>
3031 </listitem>
3033 <listitem>
3034 <para>
3036 raster font does not work with the ANSI code page.
3037 </para>
3038 </listitem>
3039 </itemizedlist>
3040 </para>
3042 </refsect1>
3048 <para>
3049 The first example shows how to spread a command over several lines of
3050 input. Notice the changing prompt:
3051 <programlisting>
3056 CREATE TABLE
3057 </programlisting>
3058 Now look at the table definition again:
3059 <programlisting>
3061 Table "my_table"
3062 Attribute | Type | Modifier
3063 -----------+---------+--------------------
3064 first | integer | not null default 0
3065 second | text |
3067 </programlisting>
3068 Now we change the prompt to something more interesting:
3069 <programlisting>
3071 peter@localhost testdb=>
3072 </programlisting>
3073 Let's assume you have filled the table with data and want to take a
3074 look at it:
3075 <programlisting>
3076 peter@localhost testdb=> SELECT * FROM my_table;
3077 first | second
3078 -------+--------
3079 1 | one
3080 2 | two
3081 3 | three
3082 4 | four
3083 (4 rows)
3085 </programlisting>
3086 You can display tables in different ways by using the
3088 <programlisting>
3090 Border style is 2.
3092 +-------+--------+
3093 | first | second |
3094 +-------+--------+
3095 | 1 | one |
3096 | 2 | two |
3097 | 3 | three |
3098 | 4 | four |
3099 +-------+--------+
3100 (4 rows)
3103 Border style is 0.
3105 first second
3106 ----- ------
3107 1 one
3108 2 two
3109 3 three
3110 4 four
3111 (4 rows)
3114 Border style is 1.
3116 Output format is unaligned.
3118 Field separator is ",".
3120 Showing only tuples.
3122 one,1
3123 two,2
3124 three,3
3125 four,4
3126 </programlisting>
3127 Alternatively, use the short commands:
3128 <programlisting>
3130 Output format is aligned.
3131 Tuples only is off.
3132 Expanded display is on.
3134 -[ RECORD 1 ]-
3135 first | 1
3136 second | one
3137 -[ RECORD 2 ]-
3138 first | 2
3139 second | two
3140 -[ RECORD 3 ]-
3141 first | 3
3142 second | three
3143 -[ RECORD 4 ]-
3144 first | 4
3145 second | four
3146 </programlisting>
3147 </para>
3149 </refsect1>
3151 </refentry>