2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.51 2001/05/09 17:29:10 momjian Exp $
6 <refentry id="APP-PSQL">
8 <date>2000-12-25</date>
12 <refentrytitle id="app-psql-title"><application>psql</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
18 <refname><application>psql</application></refname>
20 <productname>Postgres</productname> interactive terminal
26 <date>1999-10-26</date>
29 <synopsis>psql [ <replaceable class="parameter">options</replaceable> ] [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">user</replaceable> ] ]</synopsis>
31 <refsect2 id="R2-APP-PSQL-1">
33 <date>1998-09-26</date>
36 <title>Summary</title>
39 <application>psql</application> is a terminal-based front-end to
40 <productname>Postgres</productname>. It enables you to type in queries
41 interactively, issue them to <productname>Postgres</productname>, and see
42 the query results. Alternatively, input can be from a file.
43 In addition, it provides a number of meta-commands and
44 various shell-like features to facilitate writing scripts and automating a wide
52 <refsect1 id="R1-APP-PSQL-1">
54 <date>1998-10-26</date>
57 <title>Description</title>
59 <refsect2 id="R2-APP-PSQL-connecting">
61 <date>2000-01-14</date>
64 <title>Connecting To A Database</title>
67 <application>psql</application> is a regular <productname>Postgres</productname>
68 client application. In order to connect to a database you need to know the
69 name of your target database, the hostname and port number of the server
70 and what user name you want to connect as. <application>psql</application> can be
71 told about those parameters via command line options, namely <option>-d</option>,
72 <option>-h</option>, <option>-p</option>, and <option>-U</option> respectively.
73 If an argument is found that does not belong to any option it will be interpreted
74 as the database name (or the user name, if the database name is also
75 given). Not all these options are required, defaults do apply.
76 If you omit the host name psql will connect via a Unix domain socket
78 local host. The default port number is compile-time determined. Since the database
79 server uses the same default, you will not have to specify the port in most
80 cases. The default user name is your Unix username, as is the default
82 Note that you can't just connect to any database under any username. Your database
83 administrator should have informed you about your access rights. To save you some typing
84 you can also set the environment variables <envar>PGDATABASE</envar>,
85 <envar>PGHOST</envar>, <envar>PGPORT</envar> and <envar>PGUSER</envar>
86 to appropriate values.
90 If the connection could not be made for any reason (e.g., insufficient
91 privileges, postmaster is not running on the server, etc.),
92 <application>psql</application> will return an error and terminate.
96 <refsect2 id="R2-APP-PSQL-4">
98 <date>1998-09-26</date>
101 <title>Entering Queries</title>
104 In normal operation, <application>psql</application> provides a prompt with
105 the name of the database to which <application>psql</application> is currently
106 connected, followed by the string "=>". For example,
108 $ <userinput>psql testdb</userinput>
109 Welcome to psql, the PostgreSQL interactive terminal.
111 Type: \copyright for distribution terms
112 \h for help with SQL commands
113 \? for help on internal slash commands
114 \g or terminate with semicolon to execute query
122 At the prompt, the user may type in <acronym>SQL</acronym> queries.
123 Ordinarily, input lines are sent to the backend when a query-terminating
124 semicolon is reached. An end of line does not terminate a query! Thus queries
125 can be spread over several lines for clarity. If the query was sent and without
126 error, the query results are displayed on the screen.
130 Whenever a query is executed, <application>psql</application> also polls
131 for asynchronous notification events generated by
132 <xref linkend="SQL-LISTEN" endterm="SQL-LISTEN-title"> and
133 <xref linkend="SQL-NOTIFY" endterm="SQL-NOTIFY-title">.
138 <refsect1 id="R1-APP-PSQL-2">
140 <date>1998-09-26</date>
143 <title><application>psql</application> Meta-Commands</title>
146 Anything you enter in <application>psql</application> that begins with an
147 unquoted backslash is a <application>psql</application> meta-command that is
148 processed by <application>psql</application> itself.
149 These commands are what makes
150 <application>psql</application> interesting for administration or scripting.
151 Meta-commands are more commonly called slash or backslash commands.
155 The format of a <application>psql</application> command is the backslash,
156 followed immediately by a command verb, then any arguments. The arguments
157 are separated from the command verb and each other by any number of
158 whitespace characters.
162 To include whitespace into an argument you must quote it with a single
163 quote. To include a single quote into such an argument, precede it by
164 a backslash. Anything contained in single quotes is furthermore subject to
165 C-like substitutions for <literal>\n</literal> (new line), <literal>\t</literal>
166 (tab), <literal>\</literal><replaceable>digits</replaceable>,
167 <literal>\0</literal><replaceable>digits</replaceable>, and
168 <literal>\0x</literal><replaceable>digits</replaceable>
169 (the character with the given decimal, octal, or hexadecimal code).
173 If an unquoted argument begins with a colon (<literal>:</literal>),
174 it is taken as a variable and the value of the variable is taken as the
179 Arguments that are quoted in <quote>backticks</quote> (<literal>`</literal>)
180 are taken as a command line that is passed to the shell. The output of the
181 command (with a trailing newline removed) is taken as the argument value.
182 The above escape sequences also apply in backticks.
186 Some commands take the name of an <acronym>SQL</acronym> identifier (such as
187 a table name) as argument. These arguments follow the syntax rules of
188 <acronym>SQL</acronym> regarding double quotes: an identifier without
189 double quotes is coerced to lower-case. For all other commands
190 double quotes are not special and will become part of the argument.
194 Parsing for arguments stops when another unquoted backslash occurs. This
195 is taken as the beginning of a new meta-command. The special sequence
196 <literal>\\</literal>
197 (two backslashes) marks the end of arguments and continues parsing
198 <acronym>SQL</acronym> queries, if any. That way <acronym>SQL</acronym> and
199 <application>psql</application> commands can be freely mixed on a line.
200 But in any case, the arguments of a meta-command cannot continue beyond the end
205 The following meta-commands are defined:
209 <term><literal>\a</literal></term>
212 If the current table output format is unaligned, switch to aligned.
213 If it is not unaligned, set it to unaligned. This command is
214 kept for backwards compatibility. See <command>\pset</command> for a
221 <term><literal>\cd</literal> <optional><replaceable>directory</replaceable></optional></term>
224 Change the current working directory to
225 <replaceable>directory</replaceable>. Without argument,
226 change to the current user's home directory.
231 To print your current working directory, use <literal>\!pwd</literal>.
238 <term><literal>\C</literal> [ <replaceable class="parameter">title</replaceable> ]</term>
241 Set the title of any tables being printed as the result of a query or
242 unset any such title. This command is equivalent to
243 <literal>\pset title <replaceable class="parameter">title</replaceable></literal>.
245 command derives from <quote>caption</quote>, as it was previously only
246 used to set the caption in an <acronym>HTML</acronym> table.)
252 <term><literal>\connect</literal> (or <literal>\c</literal>) [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] ]</term>
255 Establishes a connection to a new database and/or under a user name. The
256 previous connection is closed.
257 If <replaceable class="parameter">dbname</replaceable> is <literal>-</literal>
258 the current database name is assumed.
262 If <replaceable class="parameter">username</replaceable> is omitted
263 the current user name is assumed.
267 As a special rule, <command>\connect</command> without any arguments will connect
268 to the default database as the default user (as you would have gotten
269 by starting <application>psql</application> without any arguments).
273 If the connection attempt failed (wrong username, access denied, etc.), the
274 previous connection will be kept if and only if <application>psql</application> is
275 in interactive mode. When executing a non-interactive script, processing
276 will immediately stop with an error. This distinction was chosen as a user
277 convenience against typos on the one hand, and a safety mechanism that
278 scripts are not accidentally acting on the wrong database on the other hand.
284 <term><literal>\copy</literal> <replaceable class="parameter">table</replaceable>
285 [ <literal>with oids</literal> ] { <literal>from</literal> | <literal>to</literal> }
286 <replaceable class="parameter">filename</replaceable> | stdin | stdout
287 [ <literal>using delimiters</literal> '<replaceable class="parameter">characters</replaceable>' ]
288 [ <literal>with null as</literal> '<replaceable class="parameter">string</replaceable>' ]
293 Performs a frontend (client) copy. This is an operation that runs an
294 <acronym>SQL</acronym> <xref linkend="SQL-COPY" endterm="SQL-COPY-title"> command,
295 but instead of the backend's reading or writing the specified file, and
296 consequently requiring backend access and special user privilege,
297 as well as being bound to the file system accessible by the backend,
298 <application>psql</application> reads or writes the
299 file and routes the data between the backend and the local file system.
303 The syntax of the command is similar to that of the <acronym>SQL</acronym>
304 <command>COPY</command> command (see its description for the details).
305 Note that, because of this, special parsing rules apply to the
306 <command>\copy</command> command. In particular, the variable
307 substitution rules and backslash escapes do not apply.
312 This operation is not as efficient as the <acronym>SQL</acronym>
313 <command>COPY</command> command because all data must pass through the
314 client/server IP or socket connection. For large amounts of data the other
315 technique may be preferable.
321 Note the difference in interpretation of <literal>stdin</literal> and <literal>stdout</literal>
322 between frontend and backend copies: in a frontend copy these always refer
323 to <application>psql</application>'s input and output stream. On a backend
324 copy <literal>stdin</literal> comes from wherever the <command>COPY</command>
325 itself came from (for example, a script run with the <option>-f</option> option),
326 and <literal>stdout</literal> refers to the query output stream (see
327 <command>\o</command> meta-command below).
334 <term><literal>\copyright</literal></term>
337 Shows the copyright and distribution terms of <application>Postgres</application>.
343 <term><literal>\d</literal> <replaceable class="parameter">relation</replaceable> </term>
347 Shows all columns of <replaceable class="parameter">relation</replaceable>
348 (which could be a table, view, index, or sequence),
349 their types, and any special attributes such as <literal>NOT NULL</literal>
351 If the relation is, in fact, a table, any defined indices are also listed.
352 If the relation is a view, the view definition is also shown.
356 The command form <literal>\d+</literal> is identical, but any comments
357 associated with the table columns are shown as well.
362 If <command>\d</command> is called without any arguments, it is
363 equivalent to <command>\dtvs</command> which will show a list
364 of all tables, views, and sequences. This is purely a convenience
372 <term><literal>\da</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
376 Lists all available aggregate functions, together with the data type they operate on.
377 If <replaceable class="parameter">pattern</replaceable>
378 (a regular expression) is specified, only matching aggregates are shown.
384 <term><literal>\dd</literal> [ <replaceable class="parameter">object</replaceable> ]</term>
387 Shows the descriptions of <replaceable class="parameter">object</replaceable>
388 (which can be a regular expression), or of all objects if no argument is given.
389 (<quote>Object</quote> covers aggregates, functions, operators, types, relations
390 (tables, views, indices, sequences, large objects), rules, and triggers.) For example:
392 => <userinput>\dd version</userinput>
394 Name | What | Description
395 ---------+----------+---------------------------
396 version | function | PostgreSQL version string
402 Descriptions for objects can be generated with the <command>COMMENT ON</command>
403 <acronym>SQL</acronym> command.
408 <productname>Postgres</productname> stores the object descriptions in the
409 pg_description system table.
418 <term><literal>\df [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
422 Lists available functions, together with their argument and return types.
423 If <replaceable class="parameter">pattern</replaceable>
424 (a regular expression) is specified, only matching functions are shown.
425 If the form <literal>\df+</literal> is used, additional information about
426 each function, including language and description, is shown.
433 <term><literal>\distvS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
437 This is not the actual command name: The letters i, s, t, v, S stand for
438 index, sequence, table, view, and system table, respectively. You can specify
439 any or all of them in any order to obtain a listing of them, together with
444 If <replaceable class="parameter">pattern</replaceable> is specified,
445 it is a regular expression that restricts the listing to those objects
446 whose name matches. If one appends a <quote>+</quote> to the command name,
447 each object is listed with its associated description, if any.
454 <term><literal>\dl</literal></term>
457 This is an alias for <command>\lo_list</command>, which shows a list of large objects.
464 <term><literal>\do [ <replaceable class="parameter">name</replaceable> ]</literal></term>
467 Lists available operators with their operand and return types.
468 If <replaceable class="parameter">name</replaceable>
469 is specified, only operators with that name will be shown.
476 <term><literal>\dp</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
479 This is an alias for <command>\z</command> which was included for its
480 greater mnemonic value (<quote>display permissions</quote>).
487 <term><literal>\dT [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
490 Lists all data types or only those that match <replaceable class="parameter">pattern</replaceable>.
491 The command form <literal>\dT+</literal> shows extra information.
498 <term><literal>\du [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
501 Lists all configured users or only those that match <replaceable class="parameter">pattern</replaceable>.
508 <term><literal>\edit</literal> (or <literal>\e</literal>) [ <replaceable class="parameter">filename</replaceable> ]</term>
512 If <replaceable class="parameter">filename</replaceable> is specified,
513 the file is edited; after the editor exits, its content is copied
514 back to the query buffer. If no argument is given, the current query
515 buffer is copied to a temporary file which is then edited in the same
520 The new query buffer is then re-parsed according to the normal rules of
521 <application>psql</application>, where the whole buffer is treated as
522 a single line. (Thus you cannot make scripts this way.
523 Use <command>\i</command> for that.) This means also that
524 if the query ends with (or rather contains) a semicolon, it is immediately
525 executed. In other cases it will merely wait in the query buffer.
530 <application>psql</application> searches the environment variables
531 <envar>PSQL_EDITOR</envar>, <envar>EDITOR</envar>, and <envar>VISUAL</envar>
532 (in that order) for an editor to use. If all of them are unset,
533 <filename>/bin/vi</filename> is run.
541 <term><literal>\echo</literal> <replaceable class="parameter">text</replaceable> [ ... ]</term>
544 Prints the arguments to the standard output, separated by one space and
545 followed by a newline. This can be useful to
546 intersperse information in the output of scripts. For example:
548 => <userinput>\echo `date`</userinput>
549 Tue Oct 26 21:40:57 CEST 1999
551 If the first argument is an unquoted <literal>-n</literal> the the trailing
552 newline is not written.
557 If you use the <command>\o</command> command to redirect your query output
558 you may wish to use <command>\qecho</command> instead of this command.
566 <term><literal>\encoding</literal> [ <replaceable class="parameter">encoding</replaceable> ]</term>
570 Sets the client encoding, if you are using multibyte encodings.
571 Without an argument, this command shows the current encoding.
578 <term><literal>\f</literal> [ <replaceable class="parameter">string</replaceable> ]</term>
582 Sets the field separator for unaligned query output. The default is
583 pipe (<literal>|</literal>). See also <command>\pset</command> for a generic way
584 of setting output options.
591 <term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
595 Sends the current query input buffer to the backend and optionally
596 saves the output in <replaceable class="parameter">filename</replaceable>
597 or pipes the output into a separate Unix shell to execute
598 <replaceable class="parameter">command</replaceable>. A bare <literal>\g</literal>
599 is virtually equivalent to a semicolon. A <literal>\g</literal> with argument
600 is a <quote>one-shot</quote> alternative to the <command>\o</command> command.
606 <term><literal>\help</literal> (or <literal>\h</literal>) [ <replaceable class="parameter">command</replaceable> ]</term>
609 Give syntax help on the specified <acronym>SQL</acronym> command.
610 If <replaceable class="parameter">command</replaceable> is not specified,
611 then <application>psql</application> will
612 list all the commands for which syntax help is
613 available. If <replaceable class="parameter">command</replaceable>
614 is an asterisk (<quote>*</quote>), then
615 syntax help on all <acronym>SQL</acronym> commands is shown.
620 To simplify typing, commands that consists of several words do not have to be quoted.
621 Thus it is fine to type <userinput>\help alter table</userinput>.
629 <term><literal>\H</literal></term>
632 Turns on <acronym>HTML</acronym> query output format. If the <acronym>HTML</acronym>
633 format is already on, it is switched back to the default aligned text format. This
634 command is for compatibility and convenience, but see <command>\pset</command> about
635 setting other output options.
642 <term><literal>\i</literal> <replaceable class="parameter">filename</replaceable></term>
645 Reads input from the file <replaceable class="parameter">filename</replaceable>
646 and executes it as though it had been typed on the keyboard.
650 If you want to see the lines on the screen as they are read you must set
651 the variable <envar>ECHO</envar> to <literal>all</literal>.
659 <term><literal>\l</literal> (or <literal>\list</literal>)</term>
662 List all the databases in the server as well as their owners. Append a
663 <quote>+</quote> to the command name to see any descriptions
664 for the databases as well. If your <productname>Postgres</productname>
666 compiled with multibyte encoding support, the encoding scheme of each
667 database is shown as well.
674 <term><literal>\lo_export</literal> <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></term>
678 Reads the large object with <acronym>OID</acronym> <replaceable class="parameter">loid</replaceable>
679 from the database and writes it to <replaceable class="parameter">filename</replaceable>.
680 Note that this is subtly different from the server function <function>lo_export</function>,
681 which acts with the permissions of the user that the database server runs as and
682 on the server's file system.
686 Use <command>\lo_list</command> to find out the large object's <acronym>OID</acronym>.
691 See the description of the <envar>LO_TRANSACTION</envar> variable for
692 important information concerning all large object operations.
700 <term><literal>\lo_import</literal> <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</term>
704 Stores the file into a <productname>Postgres</productname> <quote>large object</quote>.
705 Optionally, it associates the given comment with the object. Example:
707 foo=> <userinput>\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'</userinput>
710 The response indicates that the large object received object id 152801
711 which one ought to remember if one wants to access the object ever again.
712 For that reason it is recommended to always associate a human-readable
713 comment with every object. Those can then be seen with the
714 <command>\lo_list</command> command.
718 Note that this command is subtly different from the server-side <function>lo_import</function>
719 because it acts as the local user on the local file system, rather than the server's
720 user and file system.
725 See the description of the <envar>LO_TRANSACTION</envar> variable for
726 important information concerning all large object operations.
733 <term><literal>\lo_list</literal></term>
736 Shows a list of all <productname>Postgres</productname> <quote>large
737 objects</quote> currently stored in the database, along with any
738 comments provided for them.
744 <term><literal>\lo_unlink</literal> <replaceable class="parameter">loid</replaceable></term>
748 Deletes the large object with <acronym>OID</acronym> <replaceable class="parameter">loid</replaceable>
754 Use <command>\lo_list</command> to find out the large object's <acronym>OID</acronym>.
759 See the description of the <envar>LO_TRANSACTION</envar> variable for
760 important information concerning all large object operations.
768 <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
772 Saves future query results to the file
773 <replaceable class="parameter">filename</replaceable> or pipes future
774 results into a separate Unix shell to execute
775 <replaceable class="parameter">command</replaceable>.
776 If no arguments are specified, the query output will be reset to
777 <filename>stdout</filename>.
781 <quote>Query results</quote> includes all tables, command responses,
783 from the database server, as well as output of various backslash
784 commands that query the database (such as <command>\d</command>),
785 but not error messages.
790 To intersperse text output in between query results, use <command>\qecho</command>.
798 <term><literal>\p</literal></term>
801 Print the current query buffer to the standard output.
808 <term><literal>\pset</literal> <replaceable class="parameter">parameter</replaceable> [ <replaceable class="parameter">value</replaceable> ]</term>
812 This command sets options affecting the output of query result tables.
813 <replaceable class="parameter">parameter</replaceable> describes which option
814 is to be set. The semantics of <replaceable class="parameter">value</replaceable>
819 Adjustable printing options are:
822 <term><literal>format</literal></term>
825 Sets the output format to one of <literal>unaligned</literal>,
826 <literal>aligned</literal>, <literal>html</literal>, or <literal>latex</literal>.
827 Unique abbreviations are allowed. (That would mean one letter is enough.)
831 <quote>Unaligned</quote> writes all fields of a tuple on a line, separated
832 by the currently active field separator. This is intended to create output
833 that might be intended to be read in by other programs (tab-separated,
835 <quote>Aligned</quote> mode is the
836 standard, human-readable, nicely formatted text output that is default.
837 The <quote><acronym>HTML</acronym></quote> and <quote>LaTeX</quote> modes
838 put out tables that are intended to be included in documents using the
839 respective mark-up language. They are not complete documents! (This might
840 not be so dramatic in <acronym>HTML</acronym>, but in LaTeX you must
841 have a complete document wrapper.)
847 <term><literal>border</literal></term>
850 The second argument must be a number. In general, the higher the number
851 the more borders and lines the tables will have, but this depends on
852 the particular format. In <acronym>HTML</acronym> mode, this will
853 translate directly into the <literal>border=...</literal> attribute, in
854 the others only values 0 (no border), 1 (internal dividing lines), and 2
855 (table frame) make sense.
861 <term><literal>expanded</literal> (or <literal>x</literal>)</term>
864 Toggles between regular and expanded format. When expanded format is
865 enabled, all output has two columns with the field name on the left
866 and the data on the right. This mode is useful if the data wouldn't
867 fit on the screen in the normal <quote>horizontal</quote> mode.
871 Expanded mode is supported by all four output modes.
877 <term><literal>null</literal></term>
880 The second argument is a string that should be printed whenever a field
881 is null. The default is not to print anything, which can easily be mistaken
882 for, say, an empty string. Thus, one might choose to write
883 <literal>\pset null '(null)'</literal>.
889 <term><literal>fieldsep</literal></term>
892 Specifies the field separator to be used in unaligned output mode. That way
893 one can create, for example, tab- or comma-separated output, which other
894 programs might prefer. To set a tab as field separator, type
895 <literal>\pset fieldsep '\t'</literal>. The default field separator is
896 <literal>'|'</literal> (a <quote>pipe</quote> symbol).
902 <term><literal>recordsep</literal></term>
905 Specifies the record (line) separator to use in unaligned output mode. The default
906 is a newline character.
912 <term><literal>tuples_only</literal> (or <literal>t</literal>)</term>
915 Toggles between tuples only and full display. Full display may show
916 extra information such as column headers, titles, and various footers.
917 In tuples only mode, only actual table data is shown.
923 <term><literal>title</literal> [ <replaceable class="parameter">text</replaceable> ]</term>
926 Sets the table title for any subsequently printed tables. This can be
927 used to give your output descriptive tags. If no argument is given,
933 This formerly only affected <acronym>HTML</acronym> mode. You can now
934 set titles in any output format.
941 <term><literal>tableattr</literal> (or <literal>T</literal>) [ <replaceable class="parameter">text</replaceable> ]</term>
944 Allows you to specify any attributes to be placed inside the <acronym>HTML</acronym>
945 <sgmltag>table</sgmltag> tag. This could for example be
946 <literal>cellpadding</literal> or <literal>bgcolor</literal>. Note that you
947 probably don't want to specify <literal>border</literal> here, as
948 that is already taken care of by <literal>\pset border</literal>.
955 <term><literal>pager</literal></term>
958 Toggles the list of a pager to do table output. If the environment variable
959 <envar>PAGER</envar> is set, the output is piped to the specified program.
960 Otherwise <filename>more</filename> is used.
964 In any case, <application>psql</application> only uses the pager if it
965 seems appropriate. That means among other things that the output is to
966 a terminal and that the table would normally not fit on the screen.
967 Because of the modular nature of the printing routines it is not always
968 possible to predict the number of lines that will actually be printed.
969 For that reason <application>psql</application> might not appear very
970 discriminating about when to use the pager and when not to.
975 Illustrations on how these different formats look can be seen in
976 the <xref linkend="APP-PSQL-examples" endterm="APP-PSQL-examples-title"> section.
981 There are various shortcut commands for <command>\pset</command>. See
982 <command>\a</command>, <command>\C</command>, <command>\H</command>,
983 <command>\t</command>, <command>\T</command>, and <command>\x</command>.
989 It is an error to call <command>\pset</command> without arguments. In the future
990 this call might show the current status of all printing options.
999 <term><literal>\q</literal></term>
1002 Quit the <application>psql</application> program.
1009 <term><literal>\qecho</literal> <replaceable class="parameter">text</replaceable> [ ... ] </term>
1012 This command is identical to <command>\echo</command> except that
1013 all output will be written to the query output channel, as set by
1014 <command>\o</command>.
1021 <term><literal>\r</literal></term>
1024 Resets (clears) the query buffer.
1031 <term><literal>\s</literal> [ <replaceable class="parameter">filename</replaceable> ]</term>
1034 Print or save the command line history to
1035 <replaceable class="parameter">filename</replaceable>.
1036 If <replaceable class="parameter">filename</replaceable> is omitted,
1037 the history is written to the standard output.
1038 This option is only available if <application>psql</application> is
1039 configured to use the <acronym>GNU</acronym> history library.
1044 As of <application>psql</application> version 7.0 it is no longer
1045 necessary to save the command history, since that will be done
1046 automatically on program termination. The history is
1047 also loaded automatically every time <application>psql</application>
1056 <term><literal>\set</literal> [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ]]]</term>
1060 Sets the internal variable <replaceable class="parameter">name</replaceable>
1061 to <replaceable class="parameter">value</replaceable> or, if more than one
1062 value is given, to the concatenation of all of them. If no second argument
1063 is given, the variable is just set with no value. To unset a variable, use
1064 the <command>\unset</command> command.
1068 Valid variable names can contain characters, digits, and underscores.
1069 See the section about <application>psql</application> variables for details.
1073 Although you are welcome to set any variable to anything you want,
1074 <application>psql</application> treats several variables as special.
1075 They are documented in the section about variables.
1080 This command is totally separate from the <acronym>SQL</acronym> command
1081 <xref linkend="SQL-SET" endterm="SQL-SET-title">.
1089 <term><literal>\t</literal></term>
1092 Toggles the display of output column name headings and row count footer.
1093 This command is equivalent to <literal>\pset tuples_only</literal> and
1094 is provided for convenience.
1101 <term><literal>\T</literal> <replaceable class="parameter">table_options</replaceable></term>
1104 Allows you to specify options to be placed within the <sgmltag>table</sgmltag>
1105 tag in <acronym>HTML</acronym> tabular output mode. This command is
1106 equivalent to <literal>\pset tableattr <replaceable class="parameter">table_options</replaceable></literal>.
1113 <term><literal>\w</literal> {<replaceable class="parameter">filename</replaceable> | <replaceable class="parameter">|command</replaceable>}</term>
1116 Outputs the current query buffer to the file <replaceable class="parameter">filename</replaceable>
1117 or pipes it to the Unix command <replaceable class="parameter">command</replaceable>.
1124 <term><literal>\x</literal></term>
1127 Toggles extended row format mode. As such it is equivalent to
1128 <literal>\pset expanded</literal>.
1135 <term><literal>\z</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
1138 Produces a list of all tables in the database with their appropriate
1139 access permissions listed. If an argument is given it is taken as a regular
1140 expression which limits the listing to those tables which match it.
1145 test=> <userinput>\z</userinput>
1146 Access permissions for database "test"
1147 Relation | Access permissions
1148 ----------+-------------------------------------
1149 my_table | {"=r","joe=arwR", "group staff=ar"}
1152 Read this as follows:
1157 <literal>"=r"</literal>: <literal>PUBLIC</literal> has read
1158 (<command>SELECT</command>) permission on the table.
1164 <literal>"joe=arwR"</literal>: User <literal>joe</literal> has read,
1165 write (<command>UPDATE</command>, <command>DELETE</command>),
1166 <quote>append</quote> (<command>INSERT</command>) permissions,
1167 and permission to create rules on the table.
1173 <literal>"group staff=ar"</literal>: Group <literal>staff</literal>
1174 has <command>SELECT</command> and <command>INSERT</command> permission.
1181 The commands <xref linkend="SQL-GRANT" endterm="SQL-GRANT-title"> and
1182 <xref linkend="SQL-REVOKE" endterm="SQL-REVOKE-title">
1183 are used to set access permissions.
1191 <term><literal>\!</literal> [ <replaceable class="parameter">command</replaceable> ]</term>
1194 Escapes to a separate Unix shell or executes the Unix command
1195 <replaceable class="parameter">command</replaceable>. The arguments
1196 are not further interpreted, the shell will see them as is.
1203 <term><literal>\?</literal></term>
1206 Get help information about the backslash (<quote>\</quote>) commands.
1217 <refsect1 id="R1-APP-PSQL-3">
1219 <date>1998-09-26</date>
1222 <title>Command-line Options</title>
1225 If so configured, <application>psql</application> understands both standard
1226 Unix short options, and <acronym>GNU</acronym>-style long options. The latter
1227 are not available on all systems.
1233 <term>-a, --echo-all</term>
1236 Print all the lines to the screen as they are read. This is more useful for
1237 script processing rather than interactive mode.
1238 This is equivalent to setting the variable <envar>ECHO</envar> to <literal>all</literal>.
1245 <term>-A, --no-align</term>
1248 Switches to unaligned output mode. (The default output mode is otherwise
1256 <term>-c, --command <replaceable class="parameter">query</replaceable></term>
1259 Specifies that <application>psql</application>
1260 is to execute one query string, <replaceable class="parameter">query</replaceable>,
1261 and then exit. This is useful in shell scripts.
1264 <replaceable class="parameter">query</replaceable> must be either a query string
1265 that is completely parseable by the backend (i.e., it contains no <application>psql</application>
1266 specific features), or it is a single backslash command. Thus
1267 you cannot mix <acronym>SQL</acronym> and <application>psql</application>
1268 meta-commands. To achieve that, you could pipe the string into
1269 <application>psql</application>, like this:
1270 <literal>echo "\x \\ select * from foo;" | psql</literal>.
1277 <term>-d, --dbname <replaceable class="parameter">dbname</replaceable></term>
1280 Specifies the name of the database to connect to. This is equivalent to specifying
1281 <replaceable class="parameter">dbname</replaceable> as the first non-option
1282 argument on the command line.
1289 <term>-e, --echo-queries</term>
1292 Show all queries that are sent to the backend.
1293 This is equivalent to setting the variable <envar>ECHO</envar>
1294 to <literal>queries</literal>.
1301 <term>-E, --echo-hidden</term>
1304 Echoes the actual queries generated by \d and other backslash commands.
1305 You can use this if you wish to include similar functionality into
1306 your own programs. This is equivalent to setting the variable
1307 <envar>ECHO_HIDDEN</envar> from within <application>psql</application>.
1314 <term>-f, --file <replaceable class="parameter">filename</replaceable></term>
1317 Use the file <replaceable class="parameter">filename</replaceable>
1318 as the source of queries instead of reading queries interactively.
1319 After the file is processed, <application>psql</application> terminates.
1320 This is in many ways equivalent to the internal command <command>\i</command>.
1324 If <replaceable>filename</replaceable> is <literal>-</literal>
1325 (hyphen), then standard input is read.
1329 Using this option is subtly different from writing
1330 <literal>psql < <replaceable class="parameter">filename</replaceable></literal>.
1331 In general, both will do what you expect, but using <literal>-f</literal>
1332 enables some nice features such as error messages with line numbers.
1333 There is also a slight chance that using this option will reduce
1334 the start-up overhead. On the other hand, the variant using the shell's
1335 input redirection is (in theory) guaranteed to yield exactly the same
1336 output that you would have gotten had you entered everything by hand.
1343 <term>-F, --field-separator <replaceable class="parameter">separator</replaceable></term>
1346 Use <replaceable class="parameter">separator</replaceable> as the field separator.
1347 This is equivalent to <command>\pset fieldsep</command> or <command>\f</command>.
1354 <term>-h, --host <replaceable class="parameter">hostname</replaceable></term>
1357 Specifies the host name of the machine on which the
1358 <application>postmaster</application> is running.
1359 If host begins with a slash, it is used
1360 as the directory for the unix domain socket.
1367 <term>-H, --html</term>
1370 Turns on <acronym>HTML</acronym> tabular output. This is equivalent
1371 to <literal>\pset format html</literal> or the <command>\H</command>
1379 <term>-l, --list</term>
1382 Lists all available databases, then exits. Other non-connection options
1383 are ignored. This is similar to the internal command <command>\list</command>.
1390 <term>-o, --output <replaceable class="parameter">filename</replaceable></term>
1393 Put all query output into file <replaceable class="parameter">filename</replaceable>.
1394 This is equivalent to the command <command>\o</command>.
1401 <term>-p, --port <replaceable class="parameter">port</replaceable></term>
1404 Specifies the TCP/IP port or, by omission, the local Unix domain socket file
1405 extension on which the <application>postmaster</application>
1406 is listening for connections. Defaults to the value of the
1407 <envar>PGPORT</envar> environment variable or, if not set, to the port
1408 specified at compile time, usually 5432.
1415 <term>-P, --pset <replaceable class="parameter">assignment</replaceable></term>
1418 Allows you to specify printing options in the style of <command>\pset</command>
1419 on the command line. Note that here you have to separate name and value with
1420 an equal sign instead of a space. Thus to set the output format to LaTeX, you
1421 could write <literal>-P format=latex</literal>.
1431 Specifies that <application>psql</application> should do its work quietly.
1432 By default, it prints welcome messages and various informational output.
1433 If this option is used, none of this happens. This is useful with the
1434 <option>-c</option> option. Within <application>psql</application> you can
1435 also set the <envar>QUIET</envar> variable to achieve the same effect.
1442 <term>-R, --record-separator <replaceable class="parameter">separator</replaceable></term>
1445 Use <replaceable class="parameter">separator</replaceable> as the record separator.
1446 This is equivalent to the <command>\pset recordsep</command> command.
1453 <term>-s, --single-step</term>
1456 Run in single-step mode. That means the user is prompted before each query
1457 is sent to the backend, with the option to cancel execution as well.
1458 Use this to debug scripts.
1465 <term>-S, --single-line</term>
1468 Runs in single-line mode where a newline terminates a query, as a semicolon does.
1473 This mode is provided for those who insist on it, but you are not necessarily
1474 encouraged to use it. In particular, if you mix <acronym>SQL</acronym> and
1475 meta-commands on a line the order of execution might not always be clear to
1476 the inexperienced user.
1484 <term>-t, --tuples-only</term>
1487 Turn off printing of column names and result row count footers, etc.
1488 It is completely equivalent to the <command>\t</command> meta-command.
1495 <term>-T, --table-attr <replaceable class="parameter">table_options</replaceable></term>
1498 Allows you to specify options to be placed within the <acronym>HTML</acronym>
1499 <sgmltag>table</sgmltag> tag. See <command>\pset</command> for details.
1509 Makes <application>psql</application> prompt for the user name and password
1510 before connecting to the database.
1514 This option is deprecated, as it is conceptually flawed. (Prompting for
1515 a non-default user name and prompting for a password because the
1516 backend requires it are really two different things.) You are encouraged
1517 to look at the <option>-U</option> and <option>-W</option> options instead.
1524 <term>-U, --username <replaceable class="parameter">username</replaceable></term>
1527 Connects to the database as the user <replaceable class="parameter">username</replaceable>
1528 instead of the default. (You must have permission to do so, of course.)
1535 <term>-v, --variable, --set <replaceable class="parameter">assignment</replaceable></term>
1538 Performs a variable assignment, like the <command>\set</command>
1539 internal command. Note that you must separate name and value,
1540 if any, by an equal sign on the command line. To unset a
1541 variable, leave off the equal sign. To just set a variable
1542 without a value, use the equal sign but leave off the value.
1543 These assignments are done during a very early stage of
1544 start-up, so variables reserved for internal purposes might get
1552 <term>-V, --version</term>
1555 Shows the <application>psql</application> version.
1562 <term>-W, --password</term>
1565 Requests that <application>psql</application> should prompt for a password
1566 before connecting to a database. This will remain set for the entire
1567 session, even if you change the database connection with the meta-command
1568 <command>\connect</command>.
1572 As of version 7.0, <application>psql</application> automatically issues a
1573 password prompt whenever the backend requests password authentication.
1574 Because this is currently based on a hack, the automatic
1575 recognition might mysteriously fail, hence this option to force a prompt.
1576 If no password prompt is issued and the backend requires password authentication
1577 the connection attempt will fail.
1584 <term>-x, --expanded</term>
1587 Turns on extended row format mode. This is equivalent to the command
1588 <command>\x</command>.
1595 <term>-X, --no-psqlrc</term>
1598 Do not read the start-up file <filename>~/.psqlrc</filename>.
1605 <term>-?, --help</term>
1608 Shows help about <application>psql</application> command line arguments.
1619 <refsect1 id="R1-APP-PSQL-4">
1621 <date>1998-09-27</date>
1624 <title>Advanced features</title>
1626 <refsect2 id="APP-PSQL-variables">
1627 <title id="APP-PSQL-variables-title">Variables</title>
1630 <application>psql</application> provides variable substitution features
1631 similar to common Unix command shells. This feature is new and not very
1632 sophisticated, yet, but there are plans to expand it in the future.
1633 Variables are simply name/value
1634 pairs, where the value can be any string of any length. To set variables,
1635 use the <application>psql</application> meta-command <command>\set</command>:
1637 testdb=> <userinput>\set foo bar</userinput>
1639 sets the variable <quote>foo</quote> to the value <quote>bar</quote>. To retrieve
1640 the content of the variable, precede the name with a colon and use it
1641 as the argument of any slash command:
1643 testdb=> <userinput>\echo :foo</userinput>
1650 The arguments of <command>\set</command> are subject to the same substitution
1651 rules as with other commands. Thus you can construct interesting references
1652 such as <literal>\set :foo 'something'</literal> and get <quote>soft
1653 links</quote> or <quote>variable variables</quote> of <productname>Perl</productname>
1654 or <productname><acronym>PHP</acronym></productname> fame, respectively.
1655 Unfortunately (or fortunately?), there is no way to do anything useful
1656 with these constructs. On the
1657 other hand, <literal>\set bar :foo</literal> is a perfectly valid way to copy
1663 If you call <command>\set</command> without a second argument, the variable is simply
1664 set, but has no value. To unset (or delete) a variable, use the command
1665 <command>\unset</command>.
1669 <application>psql</application>'s internal variable names can consist of
1670 letters, numbers, and underscores in any order and any number of them.
1671 A number of regular variables are treated specially by <application>psql</application>.
1672 They indicate certain option settings that can be changed at runtime
1673 by altering the value of the variable or represent some state of the application.
1674 Although you can use these
1675 variables for any other purpose, this is not recommended, as the
1676 program behavior might grow really strange really quickly.
1677 By convention, all specially treated variables consist of all upper-case letters
1678 (and possibly numbers and underscores). To ensure maximum compatibility in the
1679 future, avoid such variables.
1680 A list of all specially treated variables follows.
1683 <term><envar>DBNAME</envar></term>
1686 The name of the database you are currently connected to. This is set every time
1687 you connect to a database (including program start-up), but can be unset.
1693 <term><envar>ECHO</envar></term>
1696 If set to <quote><literal>all</literal></quote>, all lines entered or from a script
1697 are written to the standard output before they
1698 are parsed or executed. To specify this on program start-up, use the switch
1699 <option>-a</option>. If set to <quote><literal>queries</literal></quote>,
1700 <application>psql</application> merely prints all queries as they are sent to the
1701 backend. The option for this is <option>-e</option>.
1707 <term><envar>ECHO_HIDDEN</envar></term>
1710 When this variable is set and a backslash command queries the database, the query
1711 is first shown. This way you can study the <productname>Postgres</productname>
1712 internals and provide similar functionality in your own programs. If you set the
1713 variable to the value <quote>noexec</quote>, the queries are just shown but are
1714 not actually sent to the backend and executed.
1720 <term><envar>ENCODING</envar></term>
1723 The current client multibyte encoding. If you are not set up to use
1724 multibyte characters, this variable will always contain
1725 <quote>SQL_ASCII</quote>.
1731 <term><envar>HISTCONTROL</envar></term>
1734 If this variable is set to <literal>ignorespace</literal>, lines which begin with a
1735 space are not entered into the history list. If set to a value of
1736 <literal>ignoredups</literal>, lines matching the previous history line are not
1737 entered. A value of <literal>ignoreboth</literal> combines the two
1738 options. If unset, or if set to any other value than those above, all lines read
1739 in interactive mode are saved on the history list.
1743 This feature was shamelessly plagiarized from <application>bash</application>.
1750 <term><envar>HISTSIZE</envar></term>
1753 The number of commands to store in the command history.
1754 The default value is 500.
1758 This feature was shamelessly plagiarized from <application>bash</application>.
1765 <term><envar>HOST</envar></term>
1768 The database server host you are currently connected to. This is set every time
1769 you connect to a database (including program start-up), but can be unset.
1775 <term><envar>IGNOREEOF</envar></term>
1778 If unset, sending an EOF character (usually Control-D) to an interactive session of
1779 <application>psql</application> will terminate the application.
1780 If set to a numeric value, that many EOF characters are ignored before the application
1781 terminates. If the variable is set but has no numeric value, the default is 10.
1785 This feature was shamelessly plagiarized from <application>bash</application>.
1792 <term><envar>LASTOID</envar></term>
1795 The value of the last affected oid, as returned from an <command>INSERT</command>
1796 or <command>lo_insert</command> command. This variable is only guaranteed to be
1797 valid until after the result of the next <acronym>SQL</acronym> command has been
1804 <term><envar>LO_TRANSACTION</envar></term>
1807 If you use the <productname>Postgres</productname> large object
1808 interface to specially store data that does not fit into one tuple,
1809 all the operations must be contained in a transaction block. (See the
1810 documentation of the large object interface for more information.) Since
1811 <application>psql</application> has no way to tell if you already
1812 have a transaction in progress when you call one of its internal
1813 commands (<command>\lo_export</command>, <command>\lo_import</command>,
1814 <command>\lo_unlink</command>) it must take some arbitrary action. This
1815 action could either be to roll back any transaction that might already
1816 be in progress, or to commit any such transaction, or to do nothing at
1817 all. In the last case you must provide your own
1818 <command>BEGIN TRANSACTION</command>/<command>COMMIT</command> block or
1819 the results will be unpredictable (usually resulting in the desired
1820 action's not being performed in any case).
1824 To choose what you want to do you set this variable to one of
1825 <quote>rollback</quote>, <quote>commit</quote>, or <quote>nothing</quote>.
1826 The default is to roll back the transaction. If you just want to load one
1827 or a few objects this is fine. However, if you intend to transfer many
1828 large objects, it might be advisable to provide one explicit transaction
1829 block around all commands.
1835 <term><envar>ON_ERROR_STOP</envar></term>
1838 By default, if non-interactive scripts encounter an error, such as a
1839 malformed <acronym>SQL</acronym> query or internal meta-command,
1840 processing continues. This has been the traditional behavior of
1841 <application>psql</application> but it is sometimes not desirable. If this variable
1842 is set, script processing will immediately terminate. If the script was
1843 called from another script it will terminate in the same fashion.
1844 If the outermost script was not called from an interactive <application>psql</application>
1845 session but rather using the <option>-f</option> option, <application>psql</application>
1846 will return error code 3, to distinguish this case from fatal
1847 error conditions (error code 1).
1853 <term><envar>PORT</envar></term>
1856 The database server port to which you are currently connected. This is set every time
1857 you connect to a database (including program start-up), but can be unset.
1863 <term><envar>PROMPT1</envar>, <envar>PROMPT2</envar>, <envar>PROMPT3</envar></term>
1866 These specify what the prompt <application>psql</application> issues is
1867 supposed to look like. See
1868 <quote><xref linkend="APP-PSQL-prompting" endterm="APP-PSQL-prompting-title"></quote>
1875 <term><envar>QUIET</envar></term>
1878 This variable is equivalent to the command line option <option>-q</option>.
1879 It is probably not too useful in interactive mode.
1885 <term><envar>SINGLELINE</envar></term>
1888 This variable is set by the command line option <option>-S</option>. You
1889 can unset or reset it at run time.
1895 <term><envar>SINGLESTEP</envar></term>
1898 This variable is equivalent to the command line option <option>-s</option>.
1904 <term><envar>USER</envar></term>
1907 The database user you are currently connected as. This is set every time
1908 you connect to a database (including program start-up), but can be unset.
1920 <refsect2 id="APP-PSQL-sql-interpol">
1921 <title id="APP-PSQL-sql-interpol-title"><acronym>SQL</acronym> Interpolation</title>
1924 An additional useful feature of <application>psql</application> variables
1925 is that you can substitute (<quote>interpolate</quote>) them into
1926 regular <acronym>SQL</acronym> statements. The syntax for this is again to prepend
1927 the variable name with a colon (<literal>:</literal>).
1929 testdb=> <userinput>\set foo 'my_table'</userinput>
1930 testdb=> <userinput>SELECT * FROM :foo;</userinput>
1932 would then query the table <literal>my_table</literal>. The value of the
1933 variable is copied literally, so it can even contain unbalanced quotes or
1934 backslash commands. You must make sure that it makes sense where you put it.
1935 Variable interpolation will not be performed into quoted <acronym>SQL</acronym>
1940 A popular application of this facility is to refer to the last inserted
1941 <acronym>OID</acronym> in subsequent statements to build a foreign key
1943 Another possible use of this mechanism is to copy the contents of a file
1944 into a field. First load the file into a variable and then proceed as above.
1946 testdb=> <userinput>\set content '\'' `cat my_file.txt` '\''</userinput>
1947 testdb=> <userinput>INSERT INTO my_table VALUES (:content);</userinput>
1949 One possible problem with this approach is that <filename>my_file.txt</filename>
1950 might contain single quotes. These need to be escaped so that
1951 they don't cause a syntax error when the third line is processed. This
1952 could be done with the program <application>sed</application>:
1954 testdb=> <userinput>\set content `sed -e "s/'/\\\\\\'/g" < my_file.txt`</userinput>
1956 Observe the correct number of backslashes (6)! You can resolve it this way: After
1957 <application>psql</application> has parsed this line, it passes
1958 <literal>sed -e "s/'/\\\'/g" < my_file.txt</literal> to the shell. The shell
1959 will do its own thing inside the double quotes and execute <filename>sed</filename>
1960 with the arguments <literal>-e</literal> and <literal>s/'/\\'/g</literal>.
1961 When <application>sed</application> parses this it will replace the two
1962 backslashes with a single one and then do the substitution. Perhaps at
1963 one point you thought it was great that all Unix commands use the same
1964 escape character. And this is ignoring the fact that you might have to
1965 escape all backslashes as well because <acronym>SQL</acronym> text constants
1966 are also subject to certain interpretations. In that case you might
1967 be better off preparing the file externally.
1971 Since colons may legally appear in queries, the following rule applies: If the variable
1972 is not set, the character sequence <quote>colon+name</quote> is not changed. In any
1973 case you can escape a colon with a backslash to protect it from interpretation.
1974 (The colon syntax for variables is standard <acronym>SQL</acronym> for embedded
1975 query languages, such as <application>ecpg</application>. The colon syntax for
1976 array slices and type casts are <productname>Postgres</productname> extensions,
1977 hence the conflict.)
1983 <refsect2 id="APP-PSQL-prompting">
1984 <title id="APP-PSQL-prompting-title">Prompting</title>
1987 The prompts <application>psql</application> issues can be customized to
1988 your preference. The three variables <envar>PROMPT1</envar>, <envar>PROMPT2</envar>,
1989 and <envar>PROMPT3</envar> contain strings and special escape sequences
1990 that describe the appearance of the prompt. Prompt 1 is the normal prompt
1991 that is issued when <application>psql</application> requests a new query.
1992 Prompt 2 is issued when more input is expected during query input because
1993 the query was not terminated with a semicolon or a quote was not closed.
1994 Prompt 3 is issued when you run an <acronym>SQL</acronym> <command>COPY</command>
1995 command and you are expected to type in the tuples on the terminal.
1999 The value of the respective prompt variable is printed literally, except where
2000 a percent sign (<quote>%</quote>) is encountered. Depending on the next
2001 character, certain other text is substituted instead. Defined substitutions are:
2005 <term><literal>%M</literal></term>
2008 The full hostname (with domain name) of the database server,
2009 or <literal>[local]</literal> if the connection is over a
2010 Unix domain socket, or
2011 <literal>[local:<replaceable>/dir/name</replaceable>]</literal>,
2012 if the Unix domain socket is not at the compiled in default
2019 <term><literal>%m</literal></term>
2022 The hostname of the database server, truncated after the
2023 first dot, or <literal>[local]</literal> if the connection
2024 is over a Unix domain socket.
2030 <term><literal>%></literal></term>
2031 <listitem><para>The port number at which the database server is listening.</para></listitem>
2035 <term><literal>%n</literal></term>
2036 <listitem><para>The username you are connected as (not your local system
2037 user name).</para></listitem>
2041 <term><literal>%/</literal></term>
2042 <listitem><para>The name of the current database.</para></listitem>
2046 <term><literal>%~</literal></term>
2047 <listitem><para>Like <literal>%/</literal>, but the output is <quote>~</quote>
2048 (tilde) if the database is your default database.</para></listitem>
2052 <term><literal>%#</literal></term>
2053 <listitem><para>If the current user is a database superuser, then a
2054 <quote>#</quote>, otherwise a <quote>></quote>.</para></listitem>
2058 <term><literal>%R</literal></term>
2060 In prompt 1 normally <quote>=</quote>, but <quote>^</quote> if in single-line
2061 mode, and <quote>!</quote> if the session is disconnected from the database
2062 (which can happen if <command>\connect</command> fails). In prompt 2 the
2063 sequence is replaced by <quote>-</quote>, <quote>*</quote>, a single quote,
2064 or a double quote, depending on whether <application>psql</application>
2065 expects more input because the query wasn't terminated yet, because you are
2066 inside a <literal>/* ... */</literal> comment, or because you are inside
2067 a quote. In prompt 3 the sequence doesn't resolve to anything.</para>
2072 <term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
2074 If <replaceable class="parameter">digits</replaceable> starts with
2075 <literal>0x</literal> the rest of the characters are interpreted as a
2076 hexadecimal digit and the character with the corresponding code is
2077 substituted. If the first digit is <literal>0</literal> the characters are
2078 interpreted as on octal number and the corresponding character is
2079 substituted. Otherwise a decimal number is assumed.</para>
2084 <term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term>
2086 The value of the <application>psql</application>, variable <replaceable
2087 class="parameter">name</replaceable>. See the section
2088 <quote><xref linkend="APP-PSQL-variables" endterm="APP-PSQL-variables-title"></quote>
2094 <term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term>
2096 The output of <replaceable class="parameter">command</replaceable>, similar to
2097 ordinary <quote>back-tick</quote> substitution.</para>
2103 To insert a percent sign into your prompt, write <literal>%%</literal>. The
2104 default prompts are equivalent to <literal>'%/%R%# '</literal> for prompts 1
2105 and 2, and <literal>'>> '</literal> for prompt 3.
2110 This feature was shamelessly plagiarized from <application>tcsh</application>.
2116 <refsect2 id="APP-PSQL-MISC">
2117 <title id="APP-PSQL-MISC-title">Miscellaneous</title>
2120 <application>psql</application> returns 0 to the shell if it finished normally,
2121 1 if a fatal error of its own (out of memory, file not found) occurs, 2 if the
2122 connection to the backend went bad and the session is not interactive, and 3 if
2123 an error occurred in a script and the variable <envar>ON_ERROR_STOP</envar> was
2128 Before starting up, <application>psql</application> attempts
2129 to read and execute commands from the file <filename>$HOME/.psqlrc</filename>. It
2130 could be used to set up the client or the server to taste (using the <command>\set
2131 </command> and <command>SET</command> commands).
2137 <title><acronym>GNU</acronym> readline</title>
2140 <application>psql</application> supports the readline and history libraries for
2141 convenient line editing and retrieval. The command history is stored in a file
2142 named <filename>.psql_history</filename> in your home directory and is reloaded when
2143 <application>psql</application> starts up.
2144 Tab-completion is also supported, although
2145 the completion logic makes no claim to be an <acronym>SQL</acronym> parser.
2146 When available, <application>psql</application> is automatically built to use these
2147 features. If for some reason you do not like the tab completion, you can turn if off
2148 by putting this in a file named <filename>.inputrc</filename> in your
2152 set disable-completion on
2155 (This is not a <application>psql</application> but a <application>readline</application>
2156 feature. Read its documentation for further details.)
2160 If you have the readline library installed but <application>psql</application>
2161 does not seem to use it, you must make sure that <productname>Postgres</productname>'s
2162 top-level <filename>configure</filename> script finds it. <filename>configure</filename>
2163 needs to find both the library <filename>libreadline.a</filename>
2164 (or a shared library equivalent)
2165 <emphasis>and</emphasis> the header files <filename>readline.h</filename> and
2166 <filename>history.h</filename> (or <filename>readline/readline.h</filename> and
2167 <filename>readline/history.h</filename>) in appropriate directories. If
2168 you have the library and header files installed in an obscure place you
2169 must tell <filename>configure</filename> about them, for example:
2171 $ ./configure --with-includes=/opt/gnu/include --with-libs=/opt/gnu/lib ...
2173 Then you have to recompile <application>psql</application> (not necessarily
2174 the entire code tree).
2178 The <acronym>GNU</acronym> readline library can be obtained from the <acronym>GNU</acronym>
2179 project's <acronym>FTP</acronym> server at <ulink URL="ftp://ftp.gnu.org">ftp://ftp.gnu.org</ulink>.
2188 <refsect1 id="APP-PSQL-examples">
2189 <title id="APP-PSQL-examples-title">Examples</title>
2193 This section only shows a few examples specific to <application>psql</application>.
2194 If you want to learn <acronym>SQL</acronym> or get familiar with
2195 <productname>Postgres</productname>, you might wish to read the Tutorial that
2196 is included in the distribution.
2201 The first example shows how to spread a query over several lines of input.
2202 Notice the changing prompt:
2204 testdb=> <userinput>CREATE TABLE my_table (</userinput>
2205 testdb(> <userinput> first integer not null default 0,</userinput>
2206 testdb(> <userinput> second text</userinput>
2207 testdb-> <userinput>);</userinput>
2210 Now look at the table definition again:
2212 testdb=> <userinput>\d my_table</userinput>
2214 Attribute | Type | Modifier
2215 -----------+---------+--------------------
2216 first | integer | not null default 0
2220 At this point you decide to change the prompt to something more
2223 testdb=> <userinput>\set PROMPT1 '%n@%m %~%R%# '</userinput>
2224 peter@localhost testdb=>
2226 Let's assume you have filled the table with data and want to take a look at it:
2228 peter@localhost testdb=> SELECT * FROM my_table;
2238 You can make this table look differently by using the <command>\pset</command>
2241 peter@localhost testdb=> <userinput>\pset border 2</userinput>
2243 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2254 peter@localhost testdb=> <userinput>\pset border 0</userinput>
2256 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2265 peter@localhost testdb=> <userinput>\pset border 1</userinput>
2267 peter@localhost testdb=> <userinput>\pset format unaligned</userinput>
2268 Output format is unaligned.
2269 peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput>
2270 Field separator is ",".
2271 peter@localhost testdb=> <userinput>\pset tuples_only</userinput>
2272 Showing only tuples.
2273 peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
2279 Alternatively, use the short commands:
2281 peter@localhost testdb=> <userinput>\a \t \x</userinput>
2282 Output format is aligned.
2284 Expanded display is on.
2285 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2306 <date>1999-10-27</date>
2309 <title>Appendix</title>
2312 <title>Bugs and Issues</title>
2317 In some earlier life <application>psql</application> allowed the first
2318 argument to start directly after the (single-letter) command. For
2319 compatibility this is still supported to some extent but I am not
2320 going to explain the details here as this use is discouraged. But
2321 if you get strange messages, keep this in mind. For example
2323 testdb=> <userinput>\foo</userinput>
2324 Field separator is "oo",
2326 which is perhaps not what one would expect.
2332 <application>psql</application> only works smoothly with servers of the
2333 same version. That does not mean other combinations will fail outright,
2334 but subtle and not-so-subtle problems might come up.
2340 Pressing Control-C during a <quote>copy in</quote> (data sent to the
2341 server) doesn't show the most ideal of behaviors. If you get a message
2342 such as <quote>PQexec: you gotta get out of a COPY state yourself</quote>,
2343 simply reset the connection by entering <literal>\c - -</literal>.
2355 <!-- Keep this comment at the end of the file
2360 sgml-minimize-attributes:nil
2361 sgml-always-quote-attributes:t
2364 sgml-parent-document:nil
2365 sgml-default-dtd-file:"../reference.ced"
2366 sgml-exposed-tags:nil
2367 sgml-local-catalogs:"/usr/lib/sgml/catalog"
2368 sgml-local-ecat-files:nil