2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.36 2000/08/29 20:02:09 momjian Exp $
6 <refentry id="APP-PSQL">
8 <refentrytitle id="app-psql-title">
9 <application>psql</application>
11 <refmiscinfo>Application</refmiscinfo>
16 <application>psql</application>
19 <productname>Postgres</productname> interactive terminal
25 <date>1999-10-26</date>
28 <synopsis>psql [ <replaceable class="parameter">options</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">user</replaceable> ] ]</synopsis>
30 <refsect2 id="R2-APP-PSQL-1">
32 <date>1998-09-26</date>
35 <title>Summary</title>
38 <application>psql</application> is a terminal-based front-end to
39 <productname>Postgres</productname>. It enables you to type in queries
40 interactively, issue them to <productname>Postgres</productname>, and see
41 the query results. Alternatively, input can be from a file.
42 In addition, it provides a number of meta-commands and
43 various shell-like features to facilitate writing scripts and automating a wide
51 <refsect1 id="R1-APP-PSQL-1">
53 <date>1998-10-26</date>
56 <title>Description</title>
58 <refsect2 id="R2-APP-PSQL-connecting">
60 <date>2000-01-14</date>
63 <title>Connecting To A Database</title>
66 <application>psql</application> is a regular <productname>Postgres</productname>
67 client application. In order to connect to a database you need to know the
68 name of your target database, the hostname and port number of the server
69 and what user name you want to connect as. <application>psql</application> can be
70 told about those parameters via command line options, namely <option>-d</option>,
71 <option>-h</option>, <option>-p</option>, and <option>-U</option> respectively.
72 If an argument is found that does not belong to any option it will be interpreted
73 as the database name (or the user name, if the database name is also
74 given). Not all these options are required, defaults do apply.
75 If you omit the host name psql will connect via a UNIX domain socket
77 local host. The default port number is compile-time determined. Since the database
78 server uses the same default, you will not have to specify the port in most
79 cases. The default user name is your Unix username, as is the default
81 Note that you can't just connect to any database under any username. Your database
82 administrator should have informed you about your access rights. To save you some typing
83 you can also set the environment variables <envar>PGDATABASE</envar>,
84 <envar>PGHOST</envar>, <envar>PGPORT</envar> and <envar>PGUSER</envar>
85 to appropriate values.
89 If the connection could not be made for any reason (e.g., insufficient
90 privileges, postmaster is not running on the server, etc.),
91 <application>psql</application> will return an error and terminate.
95 <refsect2 id="R2-APP-PSQL-4">
97 <date>1998-09-26</date>
100 <title>Entering Queries</title>
103 In normal operation, <application>psql</application> provides a prompt with
104 the name of the database to which <application>psql</application> is currently
105 connected, followed by the string "=>". For example,
107 $ <userinput>psql testdb</userinput>
108 Welcome to psql, the PostgreSQL interactive terminal.
110 Type: \copyright for distribution terms
111 \h for help with SQL commands
112 \? for help on internal slash commands
113 \g or terminate with semicolon to execute query
121 At the prompt, the user may type in <acronym>SQL</acronym> queries.
122 Ordinarily, input lines are sent to the backend when a query-terminating
123 semicolon is reached. An end of line does not terminate a query! Thus queries
124 can be spread over several lines for clarity. If the query was sent and without
125 error, the query results are displayed on the screen.
129 Whenever a query is executed, <application>psql</application> also polls
130 for asynchronous notification events generated by
131 <xref linkend="SQL-LISTEN" endterm="SQL-LISTEN-title"> and
132 <xref linkend="SQL-NOTIFY" endterm="SQL-NOTIFY-title">.
137 <refsect1 id="R1-APP-PSQL-2">
139 <date>1998-09-26</date>
142 <title><application>psql</application> Meta-Commands</title>
145 Anything you enter in <application>psql</application> that begins with an
146 unquoted backslash is a <application>psql</application> meta-command that is
147 processed by <application>psql</application> itself.
148 These commands are what makes
149 <application>psql</application> interesting for administration or scripting.
150 Meta-commands are more commonly called slash or backslash commands.
154 The format of a <application>psql</application> command is the backslash,
155 followed immediately by a command verb, then any arguments. The arguments
156 are separated from the command verb and each other by any number of white
161 To include whitespace into an argument you must quote it with a single
162 quote. To include a single quote into such an argument, precede it by
163 a backslash. Anything contained in single quotes is furthermore subject to
164 C-like substitutions for <literal>\n</literal> (new line), <literal>\t</literal>
165 (tab), <literal>\</literal><replaceable>digits</replaceable>,
166 <literal>\0</literal><replaceable>digits</replaceable>, and
167 <literal>\0x</literal><replaceable>digits</replaceable>
168 (the character with the given decimal, octal, or hexadecimal code).
172 If an unquoted argument begins with a colon (<literal>:</literal>),
173 it is taken as a variable and the value of the variable is taken as the
178 Arguments that are quoted in <quote>backticks</quote> (<literal>`</literal>)
179 are taken as a command line that is passed to the shell. The output of the
180 command (with a trailing newline removed) is taken as the argument value.
181 The above escape sequences also apply in backticks.
185 Some commands take the name of an <acronym>SQL</acronym> identifier (such as
186 a table name) as argument. These arguments follow the syntax rules of
187 <acronym>SQL</acronym> regarding double quotes: an identifier without
188 double quotes is coerced to lower-case. For all other commands
189 double quotes are not special and will become part of the argument.
193 Parsing for arguments stops when another unquoted backslash occurs. This
194 is taken as the beginning of a new meta-command. The special sequence
195 <literal>\\</literal>
196 (two backslashes) marks the end of arguments and continues parsing
197 <acronym>SQL</acronym> queries, if any. That way <acronym>SQL</acronym> and
198 <application>psql</application> commands can be freely mixed on a line.
199 But in any case, the arguments of a meta-command cannot continue beyond the end
204 The following meta-commands are defined:
208 <term><literal>\a</literal></term>
211 If the current table output format is unaligned, switch to aligned.
212 If it is not unaligned, set it to unaligned. This command is
213 kept for backwards compatibility. See <command>\pset</command> for a
220 <term><literal>\C</literal> [ <replaceable class="parameter">title</replaceable> ]</term>
223 Set the title of any tables being printed as the result of a query or
224 unset any such title. This command is equivalent to
225 <literal>\pset title <replaceable class="parameter">title</replaceable></literal>.
227 command derives from <quote>caption</quote>, as it was previously only
228 used to set the caption in an <acronym>HTML</acronym> table.)
234 <term><literal>\connect</literal> (or <literal>\c</literal>) [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] ]</term>
237 Establishes a connection to a new database and/or under a user name. The
238 previous connection is closed.
239 If <replaceable class="parameter">dbname</replaceable> is <literal>-</literal>
240 the current database name is assumed.
244 If <replaceable class="parameter">username</replaceable> is omitted
245 the current user name is assumed.
249 As a special rule, <command>\connect</command> without any arguments will connect
250 to the default database as the default user (as you would have gotten
251 by starting <application>psql</application> without any arguments).
255 If the connection attempt failed (wrong username, access denied, etc.) the
256 previous connection will be kept if and only if <application>psql</application> is
257 in interactive mode. When executing a non-interactive script, processing
258 will immediately stop with an error. This distinction was chosen as a user
259 convenience against typos on the one hand, and a safety mechanism that
260 scripts are not accidentally acting on the wrong database on the other hand.
266 <term><literal>\copy</literal> <replaceable class="parameter">table</replaceable>
267 [ <literal>with oids</literal> ] { <literal>from</literal> | <literal>to</literal> }
268 <replaceable class="parameter">filename</replaceable> | stdin | stdout
269 [ <literal>with delimiters</literal> '<replaceable class="parameter">characters</replaceable>' ]
270 [ <literal>with null as</literal> '<replaceable class="parameter">string</replaceable>' ]
275 Performs a frontend (client) copy. This is an operation that runs an
276 <acronym>SQL</acronym> <xref linkend="SQL-COPY" endterm="SQL-COPY-title"> command,
277 but instead of the backend's reading or writing the specified file, and
278 consequently requiring backend access and special user privilege,
279 as well as being bound to the file system accessible by the backend,
280 <application>psql</application> reads or writes the
281 file and routes the data between the backend and the local file system.
285 The syntax of the command is similar to that of the <acronym>SQL</acronym>
286 <command>COPY</command> command (see its description for the details).
287 Note that, because of this, special parsing rules apply to the
288 <command>\copy</command> command. In particular, the variable
289 substitution rules and backslash escapes do not apply.
294 This operation is not as efficient as the <acronym>SQL</acronym>
295 <command>COPY</command> command because all data must pass through the
296 client/server IP or socket connection. For large amounts of data the other
297 technique may be preferable.
303 Note the difference in interpretation of <literal>stdin</literal> and <literal>stdout</literal>
304 between frontend and backend copies: in a frontend copy these always refer
305 to <application>psql</application>'s input and output stream. On a backend
306 copy <literal>stdin</literal> comes from wherever the <command>COPY</command>
307 itself came from (for example, a script run with the <option>-f</option> option),
308 and <literal>stdout</literal> refers to the query output stream (see
309 <command>\o</command> meta-command below).
316 <term><literal>\copyright</literal></term>
319 Shows the copyright and distribution terms of <application>Postgres</application>.
325 <term><literal>\d</literal> <replaceable class="parameter">relation</replaceable> </term>
329 Shows all columns of <replaceable class="parameter">relation</replaceable>
330 (which could be a table, view, index, or sequence),
331 their types, and any special attributes such as <literal>NOT NULL</literal>
333 If the relation is, in fact, a table, any defined indices are also listed.
334 If the relation is a view, the view definition is also shown.
338 The command form <literal>\d+</literal> is identical, but any comments
339 associated with the table columns are shown as well.
344 If <command>\d</command> is called without any arguments, it is
345 equivalent to <command>\dtvs</command> which will show a list
346 of all tables, views, and sequences. This is purely a convenience
354 <term><literal>\da</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
358 Lists all available aggregate functions, together with the data type they operate on.
359 If <replaceable class="parameter">pattern</replaceable>
360 (a regular expression) is specified, only matching aggregates are shown.
366 <term><literal>\dd</literal> [ <replaceable class="parameter">object</replaceable> ]</term>
369 Shows the descriptions of <replaceable class="parameter">object</replaceable>
370 (which can be a regular expression), or of all objects if no argument is given.
371 (<quote>Object</quote> covers aggregates, functions, operators, types, relations
372 (tables, views, indices, sequences, large objects), rules, and triggers.) For example:
374 => <userinput>\dd version</userinput>
376 Name | What | Description
377 ---------+----------+---------------------------
378 version | function | PostgreSQL version string
384 Descriptions for objects can be generated with the <command>COMMENT ON</command>
385 <acronym>SQL</acronym> command.
390 <productname>Postgres</productname> stores the object descriptions in the
391 pg_description system table.
400 <term><literal>\df [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
404 Lists available functions, together with their argument and return types.
405 If <replaceable class="parameter">pattern</replaceable>
406 (a regular expression) is specified, only matching functions are shown.
407 If the form <literal>\df+</literal> is used, additional information about
408 each function, including language and description is shown.
415 <term><literal>\distvS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
419 This is not the actual command name: The letters i, s, t, v, S stand for
420 index, sequence, table, view, and system table, respectively. You can specify
421 any or all of them in any order to obtain a listing of them, together with
426 If <replaceable class="parameter">pattern</replaceable> is specified,
427 it is a regular expression restricts the listing to those objects
428 whose name matches. If one appends a <quote>+</quote> to the command name,
429 each object is listed with its associated description, if any.
436 <term><literal>\dl</literal></term>
439 This is an alias for <command>\lo_list</command>, which shows a list of large objects.
446 <term><literal>\do [ <replaceable class="parameter">name</replaceable> ]</literal></term>
449 Lists available operators with their operand and return types.
450 If <replaceable class="parameter">name</replaceable>
451 is specified, only operators with that name will be shown.
458 <term><literal>\dp</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
461 This is an alias for <command>\z</command> which was included for its
462 greater mnemonic value (<quote>display permissions</quote>).
469 <term><literal>\dT [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
472 Lists all data types or only those that match <replaceable class="parameter">pattern</replaceable>.
473 The command form <literal>\dT+</literal> shows extra information.
480 <term><literal>\edit</literal> (or <literal>\e</literal>) [ <replaceable class="parameter">filename</replaceable> ]</term>
484 If <replaceable class="parameter">filename</replaceable> is specified,
485 the file is edited; after the editor exits, its content is copied
486 back to the query buffer. If no argument is given, the current query
487 buffer is copied to a temporary file which is then edited in the same
492 The new query buffer is then re-parsed according to the normal rules of
493 <application>psql</application>, where the whole buffer is treated as
494 a single line. (Thus you cannot make scripts this way.
495 Use <command>\i</command> for that.) This means also that
496 if the query ends with (or rather contains) a semicolon, it is immediately
497 executed. In other cases it will merely wait in the query buffer.
502 <application>psql</application> searches the environment variables
503 <envar>PSQL_EDITOR</envar>, <envar>EDITOR</envar>, and <envar>VISUAL</envar>
504 (in that order) for an editor to use. If all of them are unset,
505 <filename>/bin/vi</filename> is run.
513 <term><literal>\echo</literal> <replaceable class="parameter">text</replaceable> [ ... ]</term>
516 Prints the arguments to the standard output, separated by one space and
517 followed by a newline. This can be useful to
518 intersperse information in the output of scripts. For example:
520 => <userinput>\echo `date`</userinput>
521 Tue Oct 26 21:40:57 CEST 1999
523 If the first argument is an unquoted <literal>-n</literal> the the trailing
524 newline is not written.
529 If you use the <command>\o</command> command to redirect your query output
530 you may wish to use <command>\qecho</command> instead of this command.
538 <term><literal>\encoding</literal> [ <replaceable class="parameter">encoding</replaceable> ]</term>
542 Sets the client encoding, if you are using multibyte encodings.
543 Without an argument, this command shows the current encoding.
550 <term><literal>\f</literal> [ <replaceable class="parameter">string</replaceable> ]</term>
554 Sets the field separator for unaligned query output. The default is
555 pipe (<literal>|</literal>). See also <command>\pset</command> for a generic way
556 of setting output options.
563 <term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
567 Sends the current query input buffer to the backend and optionally
568 saves the output in <replaceable class="parameter">filename</replaceable>
569 or pipes the output into a separate Unix shell to execute
570 <replaceable class="parameter">command</replaceable>. A bare <literal>\g</literal>
571 is virtually equivalent to a semicolon. A <literal>\g</literal> with argument
572 is a <quote>one-shot</quote> alternative to the <command>\o</command> command.
578 <term><literal>\help</literal> (or <literal>\h</literal>) [ <replaceable class="parameter">command</replaceable> ]</term>
581 Give syntax help on the specified <acronym>SQL</acronym> command.
582 If <replaceable class="parameter">command</replaceable> is not specified,
583 then <application>psql</application> will
584 list all the commands for which syntax help is
585 available. If <replaceable class="parameter">command</replaceable>
586 is an asterisk (<quote>*</quote>), then
587 syntax help on all <acronym>SQL</acronym> commands is shown.
592 To simplify typing, commands that consists of several words do not have to be quoted.
593 Thus it is fine to type <userinput>\help alter table</userinput>.
601 <term><literal>\H</literal></term>
604 Turns on <acronym>HTML</acronym> query output format. If the <acronym>HTML</acronym>
605 format is already on, it is switched back to the default aligned text format. This
606 command is for compatibility and convenience, but see <command>\pset</command> about
607 setting other output options.
614 <term><literal>\i</literal> <replaceable class="parameter">filename</replaceable></term>
617 Reads input from the file <replaceable class="parameter">filename</replaceable>
618 and executes it as though it had been typed on the keyboard.
622 If you want to see the lines on the screen as they are read you must set
623 the variable <envar>ECHO</envar> to <literal>all</literal>.
631 <term><literal>\l</literal> (or <literal>\list</literal>)</term>
634 List all the databases in the server as well as their owners. Append a
635 <quote>+</quote> to the command name to see any descriptions
636 for the databases as well. If your <productname>Postgres</productname>
638 compiled with multibyte encoding support, the encoding scheme of each
639 database is shown as well.
646 <term><literal>\lo_export</literal> <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></term>
650 Reads the large object with <acronym>OID</acronym> <replaceable class="parameter">loid</replaceable>
651 from the database and writes it to <replaceable class="parameter">filename</replaceable>.
652 Note that this is subtly different from the server function <function>lo_export</function>,
653 which acts with the permissions of the user that the database server runs as and
654 on the server's file system.
658 Use <command>\lo_list</command> to find out the large object's <acronym>OID</acronym>.
663 See the description of the <envar>LO_TRANSACTION</envar> variable for
664 important information concerning all large object operations.
672 <term><literal>\lo_import</literal> <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</term>
676 Stores the file into a <productname>Postgres</productname> <quote>large object</quote>.
677 Optionally, it associates the given comment with the object. Example:
679 foo=> <userinput>\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'</userinput>
682 The response indicates that the large object received object id 152801
683 which one ought to remember if one wants to access the object ever again.
684 For that reason it is recommended to always associate a human-readable
685 comment with every object. Those can then be seen with the
686 <command>\lo_list</command> command.
690 Note that this command is subtly different from the server-side <function>lo_import</function>
691 because it acts as the local user on the local file system, rather than the server's
692 user and file system.
697 See the description of the <envar>LO_TRANSACTION</envar> variable for
698 important information concerning all large object operations.
705 <term><literal>\lo_list</literal></term>
708 Shows a list of all <productname>Postgres</productname> <quote>large
709 objects</quote> currently stored in the database along with their owners.
715 <term><literal>\lo_unlink</literal> <replaceable class="parameter">loid</replaceable></term>
719 Deletes the large object with <acronym>OID</acronym> <replaceable class="parameter">loid</replaceable>
725 Use <command>\lo_list</command> to find out the large object's <acronym>OID</acronym>.
730 See the description of the <envar>LO_TRANSACTION</envar> variable for
731 important information concerning all large object operations.
739 <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
743 Saves future query results to the file
744 <replaceable class="parameter">filename</replaceable> or pipe future
745 results into a separate Unix shell to execute
746 <replaceable class="parameter">command</replaceable>.
747 If no arguments are specified, the query output will be reset to
748 <filename>stdout</filename>.
752 <quote>Query results</quote> includes all tables, command responses,
754 from the database server, as well as output of various backslash
755 commands that query the database (such as <command>\d</command>),
756 but not error messages.
761 To intersperse text output in between query results, use <command>\qecho</command>.
769 <term><literal>\p</literal></term>
772 Print the current query buffer to the standard output.
779 <term><literal>\pset</literal> <replaceable class="parameter">parameter</replaceable> [ <replaceable class="parameter">value</replaceable> ]</term>
783 This command sets options affecting the output of query result tables.
784 <replaceable class="parameter">parameter</replaceable> describes which option
785 is to be set. The semantics of <replaceable class="parameter">value</replaceable>
790 Adjustable printing options are:
793 <term><literal>format</literal></term>
796 Sets the output format to one of <literal>unaligned</literal>,
797 <literal>aligned</literal>, <literal>html</literal>, or <literal>latex</literal>.
798 Unique abbreviations are allowed. (That would mean one letter is enough.)
802 <quote>Unaligned</quote> writes all fields of a tuple on a line, separated
803 by the currently active field separator. This is intended to create output
804 that might be intended to be read in by other programs (tab-separated,
806 <quote>Aligned</quote> mode is the
807 standard, human-readable, nicely formatted text output that is default.
808 The <quote><acronym>HTML</acronym></quote> and <quote>LaTeX</quote> modes
809 put out tables that are intended to be included in documents using the
810 respective mark-up language. They are not complete documents! (This might
811 not be so dramatic in <acronym>HTML</acronym>, but in LaTeX you must
812 have a complete document wrapper.)
818 <term><literal>border</literal></term>
821 The second argument must be a number. In general, the higher the number
822 the more borders and lines the tables will have, but this depends on
823 the particular format. In <acronym>HTML</acronym> mode, this will
824 translate directly into the <literal>border=...</literal> attribute, in
825 the others only values 0 (no border), 1 (internal dividing lines), and 2
826 (table frame) make sense.
832 <term><literal>expanded</literal> (or <literal>x</literal>)</term>
835 Toggles between regular and expanded format. When expanded format is
836 enabled, all output has two columns with the field name on the left
837 and the data on the right. This mode is useful if the data wouldn't
838 fit on the screen in the normal <quote>horizontal</quote> mode.
842 Expanded mode is supported by all four output modes.
848 <term><literal>null</literal></term>
851 The second argument is a string that should be printed whenever a field
852 is null. The default is not to print anything, which can easily be mistaken
853 for, say, an empty string. Thus, one might choose to write
854 <literal>\pset null '(null)'</literal>.
860 <term><literal>fieldsep</literal></term>
863 Specifies the field separator to be used in unaligned output mode. That way
864 one can create, for example, tab- or comma-separated output, which other
865 programs might prefer. To set a tab as field separator, type
866 <literal>\pset fieldsep '\t'</literal>. The default field separator is
867 <literal>'|'</literal> (a <quote>pipe</quote> symbol).
873 <term><literal>recordsep</literal></term>
876 Specifies the record (line) separator to use in unaligned output mode. The default
877 is a newline character.
883 <term><literal>tuples_only</literal> (or <literal>t</literal>)</term>
886 Toggles between tuples only and full display. Full display may show
887 extra information such as column headers, titles, and various footers.
888 In tuples only mode, only actual table data is shown.
894 <term><literal>title</literal> [ <replaceable class="parameter">text</replaceable> ]</term>
897 Sets the table title for any subsequently printed tables. This can be
898 used to give your output descriptive tags. If no argument is given,
904 This formerly only affected <acronym>HTML</acronym> mode. You can now
905 set titles in any output format.
912 <term><literal>tableattr</literal> (or <literal>T</literal>) [ <replaceable class="parameter">text</replaceable> ]</term>
915 Allows you to specify any attributes to be placed inside the <acronym>HTML</acronym>
916 <sgmltag>table</sgmltag> tag. This could for example be
917 <literal>cellpadding</literal> or <literal>bgcolor</literal>. Note that you
918 probably don't want to specify <literal>border</literal> here, as
919 that is already taken care of by <literal>\pset border</literal>.
926 <term><literal>pager</literal></term>
929 Toggles the list of a pager to do table output. If the environment variable
930 <envar>PAGER</envar> is set, the output is piped to the specified program.
931 Otherwise <filename>more</filename> is used.
935 In any case, <application>psql</application> only uses the pager if it
936 seems appropriate. That means among other things that the output is to
937 a terminal and that the table would normally not fit on the screen.
938 Because of the modular nature of the printing routines it is not always
939 possible to predict the number of lines that will actually be printed.
940 For that reason <application>psql</application> might not appear very
941 discriminating about when to use the pager and when not to.
946 Illustrations on how these different formats look can be seen in
947 the <xref linkend="APP-PSQL-examples" endterm="APP-PSQL-examples-title"> section.
952 There are various shortcut commands for <command>\pset</command>. See
953 <command>\a</command>, <command>\C</command>, <command>\H</command>,
954 <command>\t</command>, <command>\T</command>, and <command>\x</command>.
960 It is an error to call <command>\pset</command> without arguments. In the future
961 this call might show the current status of all printing options.
970 <term><literal>\q</literal></term>
973 Quit the <application>psql</application> program.
980 <term><literal>\qecho</literal> <replaceable class="parameter">text</replaceable> [ ... ] </term>
983 This command is identical to <command>\echo</command> except that
984 all output will be written to the query output channel, as set by
985 <command>\o</command>.
992 <term><literal>\r</literal></term>
995 Resets (clears) the query buffer.
1002 <term><literal>\s</literal> [ <replaceable class="parameter">filename</replaceable> ]</term>
1005 Print or save the command line history to
1006 <replaceable class="parameter">filename</replaceable>.
1007 If <replaceable class="parameter">filename</replaceable> is omitted,
1008 the history is written to the standard output.
1009 This option is only available if <application>psql</application> is
1010 configured to use the <acronym>GNU</acronym> history library.
1015 As of <application>psql</application> version 7.0 it is no longer
1016 necessary to save the command history, since that will be done
1017 automatically on program termination. The history is
1018 also loaded automatically every time <application>psql</application>
1027 <term><literal>\set</literal> [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ]]]</term>
1031 Sets the internal variable <replaceable class="parameter">name</replaceable>
1032 to <replaceable class="parameter">value</replaceable> or, if more than one
1033 value is given, to the concatenation of all of them. If no second argument
1034 is given, the variable is just set with no value. To unset a variable, use
1035 the <command>\unset</command> command.
1039 Valid variable names can contain characters, digits, and underscores.
1040 See the section about <application>psql</application> variables for details.
1044 Although you are welcome to set any variable to anything you want,
1045 <application>psql</application> treats several variables as special.
1046 They are documented in the section about variables.
1051 This command is totally separate from the <acronym>SQL</acronym> command
1052 <xref linkend="SQL-SET" endterm="SQL-SET-title">.
1060 <term><literal>\t</literal></term>
1063 Toggles the display of output column name headings and row count footer.
1064 This command is equivalent to <literal>\pset tuples_only</literal> and
1065 is provided for convenience.
1072 <term><literal>\T</literal> <replaceable class="parameter">table_options</replaceable></term>
1075 Allows you to specify options to be placed within the <sgmltag>table</sgmltag>
1076 tag in <acronym>HTML</acronym> tabular output mode. This command is
1077 equivalent to <literal>\pset tableattr <replaceable class="parameter">table_options</replaceable></literal>.
1084 <term><literal>\w</literal> {<replaceable class="parameter">filename</replaceable> | <replaceable class="parameter">|command</replaceable>}</term>
1087 Outputs the current query buffer to the file <replaceable class="parameter">filename</replaceable>
1088 or pipes it to the Unix command <replaceable class="parameter">command</replaceable>.
1095 <term><literal>\x</literal></term>
1098 Toggles extended row format mode. As such it is equivalent to
1099 <literal>\pset expanded</literal>.
1106 <term><literal>\z</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
1109 Produces a list of all tables in the database with their appropriate
1110 access permissions listed. If an argument is given it is taken as a regular
1111 expression which limits the listing to those tables which match it.
1116 test=> <userinput>\z</userinput>
1117 Access permissions for database "test"
1118 Relation | Access permissions
1119 ----------+-------------------------------------
1120 my_table | {"=r","joe=arwR", "group staff=ar"}
1123 Read this as follows:
1128 <literal>"=r"</literal>: <literal>PUBLIC</literal> has read
1129 (<command>SELECT</command>) permission on the table.
1135 <literal>"joe=arwR"</literal>: User <literal>joe</literal> has read,
1136 write (<command>UPDATE</command>, <command>DELETE</command>),
1137 <quote>append</quote> (<command>INSERT</command>) permissions,
1138 and permission to create rules on the table.
1144 <literal>"group staff=ar"</literal>: Group <literal>staff</literal>
1145 has <command>SELECT</command> and <command>INSERT</command> permission.
1152 The commands <xref linkend="SQL-GRANT" endterm="SQL-GRANT-title"> and
1153 <xref linkend="SQL-REVOKE" endterm="SQL-REVOKE-title">
1154 are used to set access permissions.
1162 <term><literal>\!</literal> [ <replaceable class="parameter">command</replaceable> ]</term>
1165 Escapes to a separate Unix shell or executes the Unix command
1166 <replaceable class="parameter">command</replaceable>. The arguments
1167 are not further interpreted, the shell will see them as is.
1174 <term><literal>\?</literal></term>
1177 Get help information about the slash (<quote>\</quote>) commands.
1188 <refsect1 id="R1-APP-PSQL-3">
1190 <date>1998-09-26</date>
1193 <title>Command-line Options</title>
1196 If so configured, <application>psql</application> understands both standard
1197 Unix short options, and <acronym>GNU</acronym>-style long options. The latter
1198 are not available on all systems.
1204 <term>-a, --echo-all</term>
1207 Print all the lines to the screen as they are read. This is more useful for
1208 script processing rather than interactive mode.
1209 This is equivalent to setting the variable <envar>ECHO</envar> to <literal>all</literal>.
1216 <term>-A, --no-align</term>
1219 Switches to unaligned output mode. (The default output mode is otherwise
1227 <term>-c, --command <replaceable class="parameter">query</replaceable></term>
1230 Specifies that <application>psql</application>
1231 is to execute one query string, <replaceable class="parameter">query</replaceable>,
1232 and then exit. This is useful in shell scripts.
1235 <replaceable class="parameter">query</replaceable> must be either a query string
1236 that is completely parseable by the backend (i.e., it contains no <application>psql</application>
1237 specific features), or it is a single backslash command. Thus
1238 you cannot mix <acronym>SQL</acronym> and <application>psql</application>
1239 meta-commands. To achieve that, you could pipe the string into
1240 <application>psql</application>, like this:
1241 <literal>echo "\x \\ select * from foo;" | psql</literal>.
1248 <term>-d, --dbname <replaceable class="parameter">dbname</replaceable></term>
1251 Specifies the name of the database to connect to. This is equivalent to specifying
1252 <replaceable class="parameter">dbname</replaceable> as the first non-option
1253 argument on the command line.
1260 <term>-e, --echo-queries</term>
1263 Show all queries that are sent to the backend.
1264 This is equivalent to setting the variable <envar>ECHO</envar>
1265 to <literal>queries</literal>.
1272 <term>-E, --echo-hidden</term>
1275 Echoes the actual queries generated by \d and other backslash commands.
1276 You can use this if you wish to include similar functionality into
1277 your own programs. This is equivalent to setting the variable
1278 <envar>ECHO_HIDDEN</envar> from within <application>psql</application>.
1285 <term>-f, --file <replaceable class="parameter">filename</replaceable></term>
1288 Use the file <replaceable class="parameter">filename</replaceable>
1289 as the source of queries instead of reading queries interactively.
1290 After the file is processed, <application>psql</application> terminates.
1291 This in many ways equivalent to the internal command <command>\i</command>.
1294 Using this option is subtly different from writing
1295 <literal>psql < <replaceable class="parameter">filename</replaceable></literal>.
1296 In general, both will do what you expect, but using <literal>-f</literal>
1297 enables some nice features such as error messages with line numbers.
1298 There is also a slight chance that using this option will reduce
1299 the start-up overhead. On the other hand, the variant using the shell's
1300 input redirection is (in theory) guaranteed to yield exactly the same
1301 output that you would have gotten had you entered everything by hand.
1308 <term>-F, --field-separator <replaceable class="parameter">separator</replaceable></term>
1311 Use <replaceable class="parameter">separator</replaceable> as the field separator.
1312 This is equivalent to <command>\pset fieldsep</command> or <command>\f</command>.
1319 <term>-h, --host <replaceable class="parameter">hostname</replaceable></term>
1322 Specifies the host name of the machine on which the
1323 <application>postmaster</application> is running.
1324 Without this option, communication is performed using
1325 local Unix domain sockets.
1332 <term>-H, --html</term>
1335 Turns on <acronym>HTML</acronym> tabular output. This is equivalent
1336 to <literal>\pset format html</literal> or the <command>\H</command>
1344 <term>-l, --list</term>
1347 Lists all available databases, then exits. Other non-connection options
1348 are ignored. This is similar to the internal command <command>\list</command>.
1355 <term>-o, --output <replaceable class="parameter">filename</replaceable></term>
1358 Put all query output into file <replaceable class="parameter">filename</replaceable>.
1359 This is equivalent to the command <command>\o</command>.
1366 <term>-p, --port <replaceable class="parameter">port</replaceable></term>
1369 Specifies the TCP/IP port or, by omission, the local Unix domain socket file
1370 extension on which the <application>postmaster</application>
1371 is listening for connections. Defaults to the value of the
1372 <envar>PGPORT</envar> environment variable or, if not set, to the port
1373 specified at compile time, usually 5432.
1380 <term>-P, --pset <replaceable class="parameter">assignment</replaceable></term>
1383 Allows you to specify printing options in the style of <command>\pset</command>
1384 on the command line. Note that here you have to separate name and value with
1385 an equal sign instead of a space. Thus to set the output format to LaTeX, you
1386 could write <literal>-P format=latex</literal>.
1396 Specifies that <application>psql</application> should do its work quietly.
1397 By default, it prints welcome messages and various informational output.
1398 If this option is used, none of this happens. This is useful with the
1399 <option>-c</option> option. Within <application>psql</application> you can
1400 also set the <envar>QUIET</envar> variable to achieve the same effect.
1407 <term>-R, --record-separator <replaceable class="parameter">separator</replaceable></term>
1410 Use <replaceable class="parameter">separator</replaceable> as the record separator.
1411 This is equivalent to the <command>\pset recordsep</command> command.
1418 <term>-s, --single-step</term>
1421 Run in single-step mode. That means the user is prompted before each query
1422 is sent to the backend, with the option to cancel execution as well.
1423 Use this to debug scripts.
1430 <term>-S, --single-line</term>
1433 Runs in single-line mode where a newline terminates a query, as a semicolon does.
1438 This mode is provided for those who insist on it, but you are not necessarily
1439 encouraged to use it. In particular, if you mix <acronym>SQL</acronym> and
1440 meta-commands on a line the order of execution might not always be clear to
1441 the inexperienced user.
1449 <term>-t, --tuples-only</term>
1452 Turn off printing of column names and result row count footers, etc.
1453 It is completely equivalent to the <command>\t</command> meta-command.
1460 <term>-T, --table-attr <replaceable class="parameter">table_options</replaceable></term>
1463 Allows you to specify options to be placed within the <acronym>HTML</acronym>
1464 <sgmltag>table</sgmltag> tag. See <command>\pset</command> for details.
1474 Makes <application>psql</application> prompt for the user name and password
1475 before connecting to the database.
1479 This option is deprecated, as it is conceptually flawed. (Prompting for
1480 a non-default user name and prompting for a password because the
1481 backend requires it are really two different things.) You are encouraged
1482 to look at the <option>-U</option> and <option>-W</option> options instead.
1489 <term>-U, --username <replaceable class="parameter">username</replaceable></term>
1492 Connects to the database as the user <replaceable class="parameter">username</replaceable>
1493 instead of the default. (You must have permission to do so, of course.)
1500 <term>-v, --variable, --set <replaceable class="parameter">assignment</replaceable></term>
1503 Performs a variable assignment, like the <command>\set</command> internal command.
1504 Note that you must separate name and value, if any, by an equal sign on the command
1505 line. To unset a variable, leave off the equal sign. These assignments are done
1506 during a very early state of start-up, so variables reserved for internal purposes
1507 might get overwritten later.
1514 <term>-V, --version</term>
1517 Shows the <application>psql</application> version.
1524 <term>-W, --password</term>
1527 Requests that <application>psql</application> should prompt for a password
1528 before connecting to a database. This will remain set for the entire
1529 session, even if you change the database connection with the meta-command
1530 <command>\connect</command>.
1534 As of version 7.0, <application>psql</application> automatically issues a
1535 password prompt whenever the backend requests password authentication.
1536 Because this is currently based on a hack, the automatic
1537 recognition might mysteriously fail, hence this option to force a prompt.
1538 If no password prompt is issued and the backend requires password authentication
1539 the connection attempt will fail.
1546 <term>-x, --expanded</term>
1549 Turns on extended row format mode. This is equivalent to the command
1550 <command>\x</command>.
1557 <term>-X, --no-psqlrc</term>
1560 Do not read the start-up file <filename>~/.psqlrc</filename>.
1567 <term>-?, --help</term>
1570 Shows help about <application>psql</application> command line arguments.
1581 <refsect1 id="R1-APP-PSQL-4">
1583 <date>1998-09-27</date>
1586 <title>Advanced features</title>
1588 <refsect2 id="APP-PSQL-variables">
1589 <title id="APP-PSQL-variables-title">Variables</title>
1592 <application>psql</application> provides variable substitution features
1593 similar to common Unix command shells. This feature is new and not very
1594 sophisticated, yet, but there are plans to expand it in the future.
1595 Variables are simply name/value
1596 pairs, where the value can be any string of any length. To set variables,
1597 use the <application>psql</application> meta-command <command>\set</command>:
1599 testdb=> <userinput>\set foo bar</userinput>
1601 sets the variable <quote>foo</quote> to the value <quote>bar</quote>. To retrieve
1602 the content of the variable, precede the name with a colon and use it
1603 as the argument of any slash command:
1605 testdb=> <userinput>\echo :foo</userinput>
1612 The arguments of <command>\set</command> are subject to the same substitution
1613 rules as with other commands. Thus you can construct interesting references
1614 such as <literal>\set :foo 'something'</literal> and get <quote>soft
1615 links</quote> or <quote>variable variables</quote> of <productname>Perl</productname>
1616 or <productname><acronym>PHP</acronym></productname> fame, respectively.
1617 Unfortunately (or fortunately?), there is no way to do anything useful
1618 with these constructs. On the
1619 other hand, <literal>\set bar :foo</literal> is a perfectly valid way to copy
1625 If you call <command>\set</command> without a second argument, the variable is simply
1626 set, but has no value. To unset (or delete) a variable, use the command
1627 <command>\unset</command>.
1631 <application>psql</application>'s internal variable names can consist of
1632 letters, numbers, and underscores in any order and any number of them.
1633 A number of regular variables are treated specially by <application>psql</application>.
1634 They indicate certain option settings that can be changed at runtime
1635 by altering the value of the variable or represent some state of the application.
1636 Although you can use these
1637 variables for any other purpose, this is not recommended, as the
1638 program behavior might grow really strange really quickly.
1639 By convention, all specially treated variables consist of all upper-case letters
1640 (and possibly numbers and underscores). To ensure maximum compatibility in the
1641 future, avoid such variables.
1642 A list of all specially treated variables follows.
1645 <term><envar>DBNAME</envar></term>
1648 The name of the database you are currently connected to. This is set every time
1649 you connect to a database (including program start-up), but can be unset.
1655 <term><envar>ECHO</envar></term>
1658 If set to <quote><literal>all</literal></quote>, all lines entered or from a script
1659 are written to the standard output before they
1660 are parsed or executed. To specify this on program start-up, use the switch
1661 <option>-a</option>. If set to <quote><literal>queries</literal></quote>,
1662 <application>psql</application> merely prints all queries as they are sent to the
1663 backend. The option for this is <option>-e</option>.
1669 <term><envar>ECHO_HIDDEN</envar></term>
1672 When this variable is set and a backslash command queries the database, the query
1673 is first shown. This way you can study the <productname>Postgres</productname>
1674 internals and provide similar functionality in your own programs. If you set the
1675 variable to the value <quote>noexec</quote>, the queries are just shown but are
1676 not actually sent to the backend and executed.
1682 <term><envar>ENCODING</envar></term>
1685 The current client multibyte encoding. If you are not set up to use
1686 multibyte characters, this variable will always contain
1687 <quote>SQL_ASCII</quote>.
1693 <term><envar>HISTCONTROL</envar></term>
1696 If this variable is set to <literal>ignorespace</literal>, lines which begin with a
1697 space are not entered into the history list. If set to a value of
1698 <literal>ignoredups</literal>, lines matching the previous history line are not
1699 entered. A value of <literal>ignoreboth</literal> combines the two
1700 options. If unset, or if set to any other value than those above, all lines read
1701 in interactive mode are saved on the history list.
1705 This feature was shamelessly plagiarized from <application>bash</application>.
1712 <term><envar>HISTSIZE</envar></term>
1715 The number of commands to store in the command history.
1716 The default value is 500.
1720 This feature was shamelessly plagiarized from <application>bash</application>.
1727 <term><envar>HOST</envar></term>
1730 The database server host you are currently connected to. This is set every time
1731 you connect to a database (including program start-up), but can be unset.
1737 <term><envar>IGNOREEOF</envar></term>
1740 If unset, sending an EOF character (usually Control-D) to an interactive session of
1741 <application>psql</application> will terminate the application.
1742 If set to a numeric value, that many EOF characters are ignored before the application
1743 terminates. If the variable is set but has no numeric value, the default is 10.
1747 This feature was shamelessly plagiarized from <application>bash</application>.
1754 <term><envar>LASTOID</envar></term>
1757 The value of the last affected oid, as returned from an <command>INSERT</command>
1758 or <command>lo_insert</command> command. This variable is only guaranteed to be
1759 valid until after the result of the next <acronym>SQL</acronym> command has been
1766 <term><envar>LO_TRANSACTION</envar></term>
1769 If you use the <productname>Postgres</productname> large object
1770 interface to specially store data that does not fit into one tuple,
1771 all the operations must be contained in a transaction block. (See the
1772 documentation of the large object interface for more information.) Since
1773 <application>psql</application> has no way to tell if you already
1774 have a transaction in progress when you call one of its internal
1775 commands <command>\lo_export</command>, <command>\lo_import</command>,
1776 <command>\lo_unlink</command> it must take some arbitrary action. This
1777 action could either be to roll back any transaction that might already
1778 be in progress, or to commit any such transaction, or to do nothing at
1779 all. In the last case you must provide your own
1780 <command>BEGIN TRANSACTION</command>/<command>COMMIT</command> block or
1781 the results will be unpredictable (usually resulting in the desired
1782 action's not being performed in any case).
1786 To choose what you want to do you set this variable to one of
1787 <quote>rollback</quote>, <quote>commit</quote>, or <quote>nothing</quote>.
1788 The default is to roll back the transaction. If you just want to load one
1789 or a few objects this is fine. However, if you intend to transfer many
1790 large objects, it might be advisable to provide one explicit transaction
1791 block around all commands.
1797 <term><envar>ON_ERROR_STOP</envar></term>
1800 By default, if non-interactive scripts encounter an error, such as a
1801 malformed <acronym>SQL</acronym> query or internal meta-command,
1802 processing continues. This has been the traditional behavior of
1803 <application>psql</application> but it is sometimes not desirable. If this variable
1804 is set, script processing will immediately terminate. If the script was
1805 called from another script it will terminate in the same fashion.
1806 If the outermost script was not called from an interactive <application>psql</application>
1807 session but rather using the <option>-f</option> option, <application>psql</application>
1808 will return error code 3, to distinguish this case from fatal
1809 error conditions (error code 1).
1815 <term><envar>PORT</envar></term>
1818 The database server port to which you are currently connected. This is set every time
1819 you connect to a database (including program start-up), but can be unset.
1825 <term><envar>PROMPT1</envar>, <envar>PROMPT2</envar>, <envar>PROMPT3</envar></term>
1828 These specify what the prompt <application>psql</application> issues is
1829 supposed to look like. See
1830 <quote><xref linkend="APP-PSQL-prompting" endterm="APP-PSQL-prompting-title"></quote>
1837 <term><envar>QUIET</envar></term>
1840 This variable is equivalent to the command line option <option>-q</option>.
1841 It is probably not too useful in interactive mode.
1847 <term><envar>SINGLELINE</envar></term>
1850 This variable is set by the command line option <option>-S</option>. You
1851 can unset or reset it at run time.
1857 <term><envar>SINGLESTEP</envar></term>
1860 This variable is equivalent to the command line option <option>-s</option>.
1866 <term><envar>USER</envar></term>
1869 The database user you are currently connected as. This is set every time
1870 you connect to a database (including program start-up), but can be unset.
1882 <refsect2 id="APP-PSQL-sql-interpol">
1883 <title id="APP-PSQL-sql-interpol-title"><acronym>SQL</acronym> Interpolation</title>
1886 An additional useful feature of <application>psql</application> variables
1887 is that you can substitute (<quote>interpolate</quote>) them into
1888 regular <acronym>SQL</acronym> statements. The syntax for this is again to prepend
1889 the variable name with a colon (<literal>:</literal>).
1891 testdb=> <userinput>\set foo 'my_table'</userinput>
1892 testdb=> <userinput>SELECT * FROM :foo;</userinput>
1894 would then query the table <literal>my_table</literal>. The value of the
1895 variable is copied literally, so it can even contain unbalanced quotes or
1896 backslash commands. You must make sure that it makes sense where you put it.
1897 Variable interpolation will not be performed into quoted <acronym>SQL</acronym>
1902 A popular application of this facility is to refer to the last inserted
1903 <acronym>OID</acronym> in subsequent statement to build a foreign key
1905 Another possible use of this mechanism is to copy the contents of a file
1906 into a field. First load the file into a variable and then proceed as above.
1908 testdb=> <userinput>\set content '\'' `cat my_file.txt` '\''</userinput>
1909 testdb=> <userinput>INSERT INTO my_table VALUES (:content);</userinput>
1911 One possible problem with this approach is that <filename>my_file.txt</filename>
1912 might contain single quotes. These need to be escaped so that
1913 they don't cause a syntax error when the third line is processed. This
1914 could be done with the program <application>sed</application>:
1916 testdb=> <userinput>\set content `sed -e "s/'/\\\\\\'/g" < my_file.txt`</userinput>
1918 Observe the correct number of backslashes (6)! You can resolve it this way: After
1919 <application>psql</application> has parsed this line, it passes
1920 <literal>sed -e "s/'/\\\'/g" < my_file.txt</literal> to the shell. The shell
1921 will do it's own thing inside the double quotes and execute <filename>sed</filename>
1922 with the arguments <literal>-e</literal> and <literal>s/'/\\'/g</literal>.
1923 When <application>sed</application> parses this it will replace the two
1924 backslashes with a single one and then do the substitution. Perhaps at
1925 one point you thought it was great that all Unix commands use the same
1926 escape character. And this is ignoring the fact that you might have to
1927 escape all backslashes as well because <acronym>SQL</acronym> text constants
1928 are also subject to certain interpretations. In that case you might
1929 be better off preparing the file externally.
1933 Since colons may legally appear in queries, the following rule applies: If the variable
1934 is not set, the character sequence <quote>colon+name</quote> is not changed. In any
1935 case you can escape a colon with a backslash to protect it from interpretation.
1936 (The colon syntax for variables is standard <acronym>SQL</acronym> for embedded
1937 query languages, such as <application>ecpg</application>. The colon syntax for
1938 array slices and type casts are <productname>Postgres</productname> extensions,
1939 hence the conflict.)
1945 <refsect2 id="APP-PSQL-prompting">
1946 <title id="APP-PSQL-prompting-title">Prompting</title>
1949 The prompts <application>psql</application> issues can be customized to
1950 your preference. The three variables <envar>PROMPT1</envar>, <envar>PROMPT2</envar>,
1951 and <envar>PROMPT3</envar> contain strings and special escape sequences
1952 that describe the appearance of the prompt. Prompt 1 is the normal prompt
1953 that is issued when <application>psql</application> requests a new query.
1954 Prompt 2 is issued when more input is expected during query input because
1955 the query was not terminated with a semicolon or a quote was not closed.
1956 Prompt 3 is issued when you run an <acronym>SQL</acronym> <command>COPY</command>
1957 command and you are expected to type in the tuples on the terminal.
1961 The value of the respective prompt variable is printed literally, except where
1962 a percent sign (<quote>%</quote>) is encountered. Depending on the next
1963 character, certain other text is substituted instead. Defined substitutions are:
1967 <term><literal>%M</literal></term>
1968 <listitem><para>The full hostname (with domainname) of the database server (or
1969 <quote>localhost</quote> if hostname information is not available).</para></listitem>
1973 <term><literal>%m</literal></term>
1974 <listitem><para>The hostname of the database server, truncated after the
1975 first dot.</para></listitem>
1979 <term><literal>%></literal></term>
1980 <listitem><para>The port number at which the database server is listening.</para></listitem>
1984 <term><literal>%n</literal></term>
1985 <listitem><para>The username you are connected as (not your local system
1986 user name).</para></listitem>
1990 <term><literal>%/</literal></term>
1991 <listitem><para>The name of the current database.</para></listitem>
1995 <term><literal>%~</literal></term>
1996 <listitem><para>Like <literal>%/</literal>, but the output is <quote>~</quote>
1997 (tilde) if the database is your default database.</para></listitem>
2001 <term><literal>%#</literal></term>
2002 <listitem><para>If the current user is a database superuser, then a
2003 <quote>#</quote>, otherwise a <quote>></quote>.</para></listitem>
2007 <term><literal>%R</literal></term>
2009 In prompt 1 normally <quote>=</quote>, but <quote>^</quote> if in single-line
2010 mode, and <quote>!</quote> if the session is disconnected from the database
2011 (which can happen if <command>\connect</command> fails). In prompt 2 the
2012 sequence is replaced by <quote>-</quote>, <quote>*</quote>, a single quote,
2013 or a double quote, depending on whether <application>psql</application>
2014 expects more input because the query wasn't terminated yet, because you are
2015 inside a <literal>/* ... */</literal> comment, or because you are inside
2016 a quote. In prompt 3 the sequence doesn't resolve to anything.</para>
2021 <term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
2023 If <replaceable class="parameter">digits</replaceable> starts with
2024 <literal>0x</literal> the rest of the characters are interpreted at a
2025 hexadecimal digit and the character with the corresponding code is
2026 substituted. If the first digit is <literal>0</literal> the characters are
2027 interpreted as on octal number and the corresponding character is
2028 substituted. Otherwise a decimal number is assumed.</para>
2033 <term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term>
2035 The value of the <application>psql</application>, variable <replaceable
2036 class="parameter">name</replaceable>. See the section
2037 <quote><xref linkend="APP-PSQL-variables" endterm="APP-PSQL-variables-title"></quote>
2043 <term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term>
2045 The output of <replaceable class="parameter">command</replaceable>, similar to
2046 ordinary <quote>back-tick</quote> substitution.</para>
2052 To insert a percent sign into your prompt, write <literal>%%</literal>. The
2053 default prompts are equivalent to <literal>'%/%R%# '</literal> for prompts 1
2054 and 2, and <literal>'>> '</literal> for prompt 3.
2059 This feature was shamelessly plagiarized from <application>tcsh</application>.
2065 <refsect2 id="APP-PSQL-MISC">
2066 <title id="APP-PSQL-MISC-title">Miscellaneous</title>
2069 <application>psql</application> returns 0 to the shell if it finished normally,
2070 1 if a fatal error of its own (out of memory, file not found) occurs, 2 if the
2071 connection to the backend went bad and the session is not interactive, and 3 if
2072 an error occurred in a script and the variable <envar>ON_ERROR_STOP</envar> was
2077 Before starting up, <application>psql</application> attempts
2078 to read and execute commands from the file <filename>$HOME/.psqlrc</filename>. It
2079 could be used to set up the client or the server to taste (using the <command>\set
2080 </command> and <command>SET</command> commands).
2086 <title><acronym>GNU</acronym> readline</title>
2089 <application>psql</application> supports the readline and history libraries for
2090 convenient line editing and retrieval. The command history is stored in a file
2091 named <filename>.psql_history</filename> in your home directory and is reloaded when
2092 <application>psql</application> starts up.
2093 Tab-completion is also supported, although
2094 the completion logic makes no claim to be an <acronym>SQL</acronym> parser.
2095 When available, <application>psql</application> is automatically built to use these
2096 features. If for some reason you do not like the tab completion, you can turn if off
2097 by putting this in a file named <filename>.inputrc</filename> in your
2101 set disable-completion on
2104 (This is not a <application>psql</application> but a <application>readline</application>
2105 feature. Read its documentation for further details.)
2109 If you have the readline library installed but <application>psql</application>
2110 does not seem to use it, you must make sure that <productname>Postgres</productname>'s
2111 top-level <filename>configure</filename> script finds it. <filename>configure</filename>
2112 needs to find both the library <filename>libreadline.a</filename>
2113 (or a shared library equivalent)
2114 <emphasis>and</emphasis> the header files <filename>readline.h</filename> and
2115 <filename>history.h</filename> (or <filename>readline/readline.h</filename> and
2116 <filename>readline/history.h</filename>) in appropriate directories. If
2117 you have the library and header files installed in an obscure place you
2118 must tell <filename>configure</filename> about them, for example:
2120 $ ./configure --with-includes=/opt/gnu/include --with-libs=/opt/gnu/lib ...
2122 Then you have to recompile <application>psql</application> (not necessarily
2123 the entire code tree).
2127 The <acronym>GNU</acronym> readline library can be obtained from the <acronym>GNU</acronym>
2128 project's <acronym>FTP</acronym> server at <ulink URL="ftp://ftp.gnu.org">ftp://ftp.gnu.org</ulink>.
2137 <refsect1 id="APP-PSQL-examples">
2138 <title id="APP-PSQL-examples-title">Examples</title>
2142 This section only shows a few examples specific to <application>psql</application>.
2143 If you want to learn <acronym>SQL</acronym> or get familiar with
2144 <productname>Postgres</productname>, you might wish to read the Tutorial that
2145 is included in the distribution.
2150 The first example shows how to spread a query over several lines of input.
2151 Notice the changing prompt:
2153 testdb=> <userinput>CREATE TABLE my_table (</userinput>
2154 testdb(> <userinput> first integer not null default 0,</userinput>
2155 testdb(> <userinput> second text</userinput>
2156 testdb-> <userinput>);</userinput>
2159 Now look at the table definition again:
2161 testdb=> <userinput>\d my_table</userinput>
2163 Attribute | Type | Modifier
2164 -----------+---------+--------------------
2165 first | integer | not null default 0
2169 At this point you decide to change the prompt to something more
2172 testdb=> <userinput>\set PROMPT1 '%n@%m %~%R%# '</userinput>
2173 peter@localhost testdb=>
2175 Let's assume you have filled the table with data and want to take a look at it:
2177 peter@localhost testdb=> SELECT * FROM my_table;
2187 Notice how the int4 columns in right aligned while the text column in left aligned.
2188 You can make this table look differently by using the <command>\pset</command>
2191 peter@localhost testdb=> <userinput>\pset border 2</userinput>
2193 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2204 peter@localhost testdb=> <userinput>\pset border 0</userinput>
2206 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2215 peter@localhost testdb=> <userinput>\pset border 1</userinput>
2217 peter@localhost testdb=> <userinput>\pset format unaligned</userinput>
2218 Output format is unaligned.
2219 peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput>
2220 Field separator is ",".
2221 peter@localhost testdb=> <userinput>\pset tuples_only</userinput>
2222 Showing only tuples.
2223 peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
2229 Alternatively, use the short commands:
2231 peter@localhost testdb=> <userinput>\a \t \x</userinput>
2232 Output format is aligned.
2234 Expanded display is on.
2235 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2256 <date>1999-10-27</date>
2259 <title>Appendix</title>
2262 <title>Bugs and Issues</title>
2267 In some earlier life <application>psql</application> allowed the first
2268 argument to start directly after the (single-letter) command. For
2269 compatibility this is still supported to some extent but I am not
2270 going to explain the details here as this use is discouraged. But
2271 if you get strange messages, keep this in mind. For example
2273 testdb=> <userinput>\foo</userinput>
2274 Field separator is "oo".
2276 is perhaps not what one would expect.
2282 <application>psql</application> only works smoothly with servers of the
2283 same version. That does not mean other combinations will fail outright,
2284 but subtle and not-so-subtle problems might come up.
2290 Pressing Control-C during a <quote>copy in</quote> (data sent to the
2291 server) doesn't show the most ideal of behaviors. If you get a message
2292 such as <quote>PQexec: you gotta get out of a COPY state yourself</quote>,
2293 simply reset the connection by entering <literal>\c - -</literal>.
2305 <!-- Keep this comment at the end of the file
2310 sgml-minimize-attributes:nil
2311 sgml-always-quote-attributes:t
2314 sgml-parent-document:nil
2315 sgml-default-dtd-file:"../reference.ced"
2316 sgml-exposed-tags:nil
2317 sgml-local-catalogs:"/usr/lib/sgml/catalog"
2318 sgml-local-ecat-files:nil