2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.81 2002/12/12 21:02:19 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="APP-PSQL">
8 <refentrytitle id="app-psql-title"><application>psql</application></refentrytitle>
9 <manvolnum>1</manvolnum>
10 <refmiscinfo>Application</refmiscinfo>
14 <refname><application>psql</application></refname>
16 <productname>PostgreSQL</productname> interactive terminal
22 <command>psql</command>
23 <arg><replaceable class="parameter">options</replaceable></arg>
24 <arg><replaceable class="parameter">dbname</replaceable>
25 <arg><replaceable class="parameter">user</replaceable></arg></arg>
30 <title>Description</title>
33 <application>psql</application> is a terminal-based front-end to
34 <productname>PostgreSQL</productname>. It enables you to type in
35 queries interactively, issue them to
36 <productname>PostgreSQL</productname>, and see the query results.
37 Alternatively, input can be from a file. In addition, it provides a
38 number of meta-commands and various shell-like features to
39 facilitate writing scripts and automating a wide variety of tasks.
43 <refsect1 id="R1-APP-PSQL-3">
44 <title>Options</title>
48 <term><option>-a</></term>
49 <term><option>--echo-all</></term>
52 Print all the lines to the screen as they are read. This is more
53 useful for script processing rather than interactive mode. This is
54 equivalent to setting the variable <varname>ECHO</varname> to
55 <literal>all</literal>.
61 <term><option>-A</></term>
62 <term><option>--no-align</></term>
65 Switches to unaligned output mode. (The default output mode is
72 <term><option>-c <replaceable class="parameter">query</replaceable></></term>
73 <term><option>--command <replaceable class="parameter">query</replaceable></></term>
76 Specifies that <application>psql</application> is to execute one
77 query string, <replaceable class="parameter">query</replaceable>,
78 and then exit. This is useful in shell scripts.
81 <replaceable class="parameter">query</replaceable> must be either
82 a query string that is completely parsable by the backend (i.e.,
83 it contains no <application>psql</application> specific features),
84 or it is a single backslash command. Thus you cannot mix
85 <acronym>SQL</acronym> and <application>psql</application>
86 meta-commands. To achieve that, you could pipe the string into
87 <application>psql</application>, like this: <literal>echo "\x \\
88 select * from foo;" | psql</literal>.
94 <term><option>-d <replaceable class="parameter">dbname</replaceable></></term>
95 <term><option>--dbname <replaceable class="parameter">dbname</replaceable></></term>
98 Specifies the name of the database to connect to. This is
99 equivalent to specifying <replaceable
100 class="parameter">dbname</replaceable> as the first non-option
101 argument on the command line.
107 <term><option>-e</></term>
108 <term><option>--echo-queries</></term>
111 Show all queries that are sent to the backend. This is equivalent
112 to setting the variable <varname>ECHO</varname> to
113 <literal>queries</literal>.
119 <term><option>-E</></term>
120 <term><option>--echo-hidden</></term>
123 Echoes the actual queries generated by \d and other backslash
124 commands. You can use this if you wish to include similar
125 functionality into your own programs. This is equivalent to
126 setting the variable <varname>ECHO_HIDDEN</varname> from within
127 <application>psql</application>.
133 <term><option>-f <replaceable class="parameter">filename</replaceable></></term>
134 <term><option>--file <replaceable class="parameter">filename</replaceable></></term>
137 Use the file <replaceable class="parameter">filename</replaceable>
138 as the source of queries instead of reading queries interactively.
139 After the file is processed, <application>psql</application>
140 terminates. This is in many ways equivalent to the internal
141 command <command>\i</command>.
145 If <replaceable>filename</replaceable> is <literal>-</literal>
146 (hyphen), then standard input is read.
150 Using this option is subtly different from writing <literal>psql
152 class="parameter">filename</replaceable></literal>. In general,
153 both will do what you expect, but using <literal>-f</literal>
154 enables some nice features such as error messages with line
155 numbers. There is also a slight chance that using this option will
156 reduce the start-up overhead. On the other hand, the variant using
157 the shell's input redirection is (in theory) guaranteed to yield
158 exactly the same output that you would have gotten had you entered
165 <term><option>-F <replaceable class="parameter">separator</replaceable></></term>
166 <term><option>--field-separator <replaceable class="parameter">separator</replaceable></></term>
169 Use <replaceable class="parameter">separator</replaceable> as the
170 field separator. This is equivalent to <command>\pset
171 fieldsep</command> or <command>\f</command>.
177 <term><option>-h <replaceable class="parameter">hostname</replaceable></></term>
178 <term><option>--host <replaceable class="parameter">hostname</replaceable></></term>
181 Specifies the host name of the machine on which the
182 <application>postmaster</application> is running. If host begins
183 with a slash, it is used as the directory for the Unix-domain
190 <term><option>-H</></term>
191 <term><option>--html</></term>
194 Turns on <acronym>HTML</acronym> tabular output. This is
195 equivalent to <literal>\pset format html</literal> or the
196 <command>\H</command> command.
202 <term><option>-l</></term>
203 <term><option>--list</></term>
206 Lists all available databases, then exits. Other non-connection
207 options are ignored. This is similar to the internal command
208 <command>\list</command>.
214 <term><option>-o <replaceable class="parameter">filename</replaceable></></term>
215 <term><option>--output <replaceable class="parameter">filename</replaceable></></term>
218 Put all query output into file <replaceable
219 class="parameter">filename</replaceable>. This is equivalent to
220 the command <command>\o</command>.
226 <term><option>-p <replaceable class="parameter">port</replaceable></></term>
227 <term><option>--port <replaceable class="parameter">port</replaceable></></term>
230 Specifies the TCP/IP port or, by omission, the local Unix domain
231 socket file extension on which the
232 <application>postmaster</application> is listening for
233 connections. Defaults to the value of the <envar>PGPORT</envar>
234 environment variable or, if not set, to the port specified at
235 compile time, usually 5432.
241 <term><option>-P <replaceable class="parameter">assignment</replaceable></></term>
242 <term><option>--pset <replaceable class="parameter">assignment</replaceable></></term>
245 Allows you to specify printing options in the style of
246 <command>\pset</command> on the command line. Note that here you
247 have to separate name and value with an equal sign instead of a
248 space. Thus to set the output format to LaTeX, you could write
249 <literal>-P format=latex</literal>.
255 <term><option>-q</></term>
256 <term><option>--quiet</></term>
259 Specifies that <application>psql</application> should do its work
260 quietly. By default, it prints welcome messages and various
261 informational output. If this option is used, none of this
262 happens. This is useful with the <option>-c</option> option.
263 Within <application>psql</application> you can also set the
264 <varname>QUIET</varname> variable to achieve the same effect.
270 <term><option>-R <replaceable class="parameter">separator</replaceable></></term>
271 <term><option>--record-separator <replaceable class="parameter">separator</replaceable></></term>
274 Use <replaceable class="parameter">separator</replaceable> as the
275 record separator. This is equivalent to the <command>\pset
276 recordsep</command> command.
282 <term><option>-s</></term>
283 <term><option>--single-step</></term>
286 Run in single-step mode. That means the user is prompted before
287 each query is sent to the backend, with the option to cancel
288 execution as well. Use this to debug scripts.
294 <term><option>-S</></term>
295 <term><option>--single-line</></term>
298 Runs in single-line mode where a newline terminates a query, as a
304 This mode is provided for those who insist on it, but you are not
305 necessarily encouraged to use it. In particular, if you mix
306 <acronym>SQL</acronym> and meta-commands on a line the order of
307 execution might not always be clear to the inexperienced user.
314 <term><option>-t</></term>
315 <term><option>--tuples-only</></term>
318 Turn off printing of column names and result row count footers,
319 etc. It is completely equivalent to the <command>\t</command>
326 <term><option>-T <replaceable class="parameter">table_options</replaceable></></term>
327 <term><option>--table-attr <replaceable class="parameter">table_options</replaceable></></term>
330 Allows you to specify options to be placed within the
331 <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. See
332 <command>\pset</command> for details.
338 <term><option>-u</></term>
341 Makes <application>psql</application> prompt for the user name and
342 password before connecting to the database.
346 This option is deprecated, as it is conceptually flawed.
347 (Prompting for a non-default user name and prompting for a
348 password because the backend requires it are really two different
349 things.) You are encouraged to look at the <option>-U</option> and
350 <option>-W</option> options instead.
356 <term><option>-U <replaceable class="parameter">username</replaceable></></term>
357 <term><option>--username <replaceable class="parameter">username</replaceable></></term>
360 Connects to the database as the user <replaceable
361 class="parameter">username</replaceable> instead of the default.
362 (You must have permission to do so, of course.)
368 <term><option>-v <replaceable class="parameter">assignment</replaceable></></term>
369 <term><option>--set <replaceable class="parameter">assignment</replaceable></></term>
370 <term><option>--variable <replaceable class="parameter">assignment</replaceable></></term>
373 Performs a variable assignment, like the <command>\set</command>
374 internal command. Note that you must separate name and value, if
375 any, by an equal sign on the command line. To unset a variable,
376 leave off the equal sign. To just set a variable without a value,
377 use the equal sign but leave off the value. These assignments are
378 done during a very early stage of start-up, so variables reserved
379 for internal purposes might get overwritten later.
385 <term><option>-V</></term>
386 <term><option>--version</></term>
389 Shows the <application>psql</application> version.
395 <term><option>-W</></term>
396 <term><option>--password</></term>
399 Requests that <application>psql</application> should prompt for a
400 password before connecting to a database. This will remain set for
401 the entire session, even if you change the database connection
402 with the meta-command <command>\connect</command>.
406 In the current version, <application>psql</application>
407 automatically issues a password prompt whenever the backend
408 requests password authentication. Because this is currently based
409 on a hack, the automatic recognition might mysteriously fail,
410 hence this option to force a prompt. If no password prompt is
411 issued and the backend requires password authentication the
412 connection attempt will fail.
418 <term><option>-x</></term>
419 <term><option>--expanded</></term>
422 Turns on extended row format mode. This is equivalent to the
423 command <command>\x</command>.
429 <term><option>-X,</></term>
430 <term><option>--no-psqlrc</></term>
433 Do not read the start-up file <filename>~/.psqlrc</filename>.
439 <term><option>-?</></term>
440 <term><option>--help</></term>
443 Shows help about <application>psql</application> command line
451 Long options are not available on all platforms.
457 <title>Exit Status</title>
460 <application>psql</application> returns 0 to the shell if it
461 finished normally, 1 if a fatal error of its own (out of memory,
462 file not found) occurs, 2 if the connection to the backend went bad
463 and the session is not interactive, and 3 if an error occurred in a
464 script and the variable <varname>ON_ERROR_STOP</varname> was set.
472 <refsect2 id="R2-APP-PSQL-connecting">
473 <title>Connecting To A Database</title>
476 <application>psql</application> is a regular
477 <productname>PostgreSQL</productname> client application. In order
478 to connect to a database you need to know the name of your target
479 database, the host name and port number of the server and what user
480 name you want to connect as. <application>psql</application> can be
481 told about those parameters via command line options, namely
482 <option>-d</option>, <option>-h</option>, <option>-p</option>, and
483 <option>-U</option> respectively. If an argument is found that does
484 not belong to any option it will be interpreted as the database name
485 (or the user name, if the database name is also given). Not all
486 these options are required, defaults do apply. If you omit the host
487 name, <application>psql</> will connect via a Unix domain socket to a server on the
488 local host. The default port number is compile-time determined.
489 Since the database server uses the same default, you will not have
490 to specify the port in most cases. The default user name is your
491 Unix user name, as is the default database name. Note that you can't
492 just connect to any database under any user name. Your database
493 administrator should have informed you about your access rights. To
494 save you some typing you can also set the environment variables
495 <envar>PGDATABASE</envar>, <envar>PGHOST</envar>,
496 <envar>PGPORT</envar> and <envar>PGUSER</envar> to appropriate
501 If the connection could not be made for any reason (e.g., insufficient
502 privileges, postmaster is not running on the server, etc.),
503 <application>psql</application> will return an error and terminate.
507 <refsect2 id="R2-APP-PSQL-4">
508 <title>Entering Queries</title>
511 In normal operation, <application>psql</application> provides a
512 prompt with the name of the database to which
513 <application>psql</application> is currently connected, followed by
514 the string <literal>=></literal>. For example,
516 $ <userinput>psql testdb</userinput>
517 Welcome to psql &version;, the PostgreSQL interactive terminal.
519 Type: \copyright for distribution terms
520 \h for help with SQL commands
521 \? for help on internal slash commands
522 \g or terminate with semicolon to execute query
530 At the prompt, the user may type in <acronym>SQL</acronym> queries.
531 Ordinarily, input lines are sent to the backend when a
532 query-terminating semicolon is reached. An end of line does not
533 terminate a query! Thus queries can be spread over several lines for
534 clarity. If the query was sent and without error, the query results
535 are displayed on the screen.
539 Whenever a query is executed, <application>psql</application> also polls
540 for asynchronous notification events generated by
541 <xref linkend="SQL-LISTEN" endterm="SQL-LISTEN-title"> and
542 <xref linkend="SQL-NOTIFY" endterm="SQL-NOTIFY-title">.
547 <title>Meta-Commands</title>
550 Anything you enter in <application>psql</application> that begins
551 with an unquoted backslash is a <application>psql</application>
552 meta-command that is processed by <application>psql</application>
553 itself. These commands are what makes
554 <application>psql</application> interesting for administration or
555 scripting. Meta-commands are more commonly called slash or backslash
560 The format of a <application>psql</application> command is the backslash,
561 followed immediately by a command verb, then any arguments. The arguments
562 are separated from the command verb and each other by any number of
563 whitespace characters.
567 To include whitespace into an argument you may quote it with a
568 single quote. To include a single quote into such an argument,
569 precede it by a backslash. Anything contained in single quotes is
570 furthermore subject to C-like substitutions for
571 <literal>\n</literal> (new line), <literal>\t</literal> (tab),
572 <literal>\</literal><replaceable>digits</replaceable>,
573 <literal>\0</literal><replaceable>digits</replaceable>, and
574 <literal>\0x</literal><replaceable>digits</replaceable> (the
575 character with the given decimal, octal, or hexadecimal code).
579 If an unquoted argument begins with a colon (<literal>:</literal>),
580 it is taken as a <application>psql</> variable and the value of the
581 variable is used as the argument instead.
585 Arguments that are enclosed in backquotes (<literal>`</literal>)
586 are taken as a command line that is passed to the shell. The
587 output of the command (with any trailing newline removed) is taken
588 as the argument value. The above escape sequences also apply in
593 Some commands take an <acronym>SQL</acronym> identifier
594 (such as a table name) as argument. These arguments follow the
595 syntax rules of <acronym>SQL</acronym> regarding double quotes: an
596 identifier without double quotes is coerced to lower-case, while
597 whitespace within double quotes is included in the argument.
601 Parsing for arguments stops when another unquoted backslash occurs.
602 This is taken as the beginning of a new meta-command. The special
603 sequence <literal>\\</literal> (two backslashes) marks the end of
604 arguments and continues parsing <acronym>SQL</acronym> queries, if
605 any. That way <acronym>SQL</acronym> and
606 <application>psql</application> commands can be freely mixed on a
607 line. But in any case, the arguments of a meta-command cannot
608 continue beyond the end of the line.
612 The following meta-commands are defined:
616 <term><literal>\a</literal></term>
619 If the current table output format is unaligned, switch to aligned.
620 If it is not unaligned, set it to unaligned. This command is
621 kept for backwards compatibility. See <command>\pset</command> for a
628 <term><literal>\cd</literal> <optional><replaceable>directory</replaceable></optional></term>
631 Change the current working directory to
632 <replaceable>directory</replaceable>. Without argument, change
633 to the current user's home directory.
638 To print your current working directory, use <literal>\!pwd</literal>.
645 <term><literal>\C</literal> [ <replaceable class="parameter">title</replaceable> ]</term>
648 Set the title of any tables being printed as the result of a
649 query or unset any such title. This command is equivalent to
650 <literal>\pset title <replaceable
651 class="parameter">title</replaceable></literal>. (The name of
652 this command derives from <quote>caption</quote>, as it was
653 previously only used to set the caption in an
654 <acronym>HTML</acronym> table.)
660 <term><literal>\connect</literal> (or <literal>\c</literal>) [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] ]</term>
663 Establishes a connection to a new database and/or under a user
664 name. The previous connection is closed. If <replaceable
665 class="parameter">dbname</replaceable> is <literal>-</literal>
666 the current database name is assumed.
670 If <replaceable class="parameter">username</replaceable> is
671 omitted the current user name is assumed. </para>
674 As a special rule, <command>\connect</command> without any
675 arguments will connect to the default database as the default
676 user (as you would have gotten by starting
677 <application>psql</application> without any arguments).
681 If the connection attempt failed (wrong user name, access
682 denied, etc.), the previous connection will be kept if and only
683 if <application>psql</application> is in interactive mode. When
684 executing a non-interactive script, processing will immediately
685 stop with an error. This distinction was chosen as a user
686 convenience against typos on the one hand, and a safety
687 mechanism that scripts are not accidentally acting on the wrong
688 database on the other hand.
694 <term><literal>\copy <replaceable class="parameter">table</replaceable>
695 [ ( <replaceable class="parameter">column_list</replaceable> ) ]
696 { <literal>from</literal> | <literal>to</literal> }
697 <replaceable class="parameter">filename</replaceable> | stdin | stdout
698 [ <literal>with</literal> ]
699 [ <literal>oids</literal> ]
700 [ <literal>delimiter [as] </literal> '<replaceable class="parameter">character</replaceable>' ]
701 [ <literal>null [as] </literal> '<replaceable class="parameter">string</replaceable>' ]</literal>
706 Performs a frontend (client) copy. This is an operation that
707 runs an <acronym>SQL</acronym> <xref linkend="SQL-COPY"
708 endterm="SQL-COPY-title"> command, but instead of the backend's
709 reading or writing the specified file,
710 <application>psql</application> reads or writes the file and
711 routes the data between the backend and the local file system.
712 This means that file accessibility and privileges are those
713 of the local user, not the server, and no SQL superuser
714 privileges are required.
718 The syntax of the command is similar to that of the
719 <acronym>SQL</acronym> <command>COPY</command> command (see its
720 description for the details). Note that, because of this,
721 special parsing rules apply to the <command>\copy</command>
722 command. In particular, the variable substitution rules and
723 backslash escapes do not apply.
728 This operation is not as efficient as the <acronym>SQL</acronym>
729 <command>COPY</command> command because all data must pass
730 through the client/server IP or socket connection. For large
731 amounts of data the other technique may be preferable.
737 Note the difference in interpretation of
738 <literal>stdin</literal> and <literal>stdout</literal> between
739 frontend and backend copies: in a frontend copy these always
740 refer to <application>psql</application>'s input and output
741 stream. On a backend copy <literal>stdin</literal> comes from
742 wherever the <command>COPY</command> itself came from (for
743 example, a script run with the <option>-f</option> option), and
744 <literal>stdout</literal> refers to the query output stream (see
745 <command>\o</command> meta-command below).
752 <term><literal>\copyright</literal></term>
755 Shows the copyright and distribution terms of
756 <application>PostgreSQL</application>.
762 <term><literal>\d</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
766 For each relation (table, view, index, or sequence) matching the
767 <replaceable class="parameter">pattern</replaceable>, show all
768 columns, their types, and any special
769 attributes such as <literal>NOT NULL</literal> or defaults, if
770 any. Associated indexes, constraints, rules, and triggers are
771 also shown, as is the view definition if the relation is a view.
772 (<quote>Matching the pattern</> is defined below.)
776 The command form <literal>\d+</literal> is identical, but any
777 comments associated with the table columns are shown as well.
782 If <command>\d</command> is used without a
783 <replaceable class="parameter">pattern</replaceable> argument, it is
784 equivalent to <command>\dtvs</command> which will show a list of
785 all tables, views, and sequences. This is purely a convenience
793 <term><literal>\da</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
797 Lists all available aggregate functions, together with the data
798 type they operate on. If <replaceable
799 class="parameter">pattern</replaceable> (a regular expression)
800 is specified, only matching aggregates are shown.
806 <term><literal>\dd</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
809 Shows the descriptions of objects matching the <replaceable
810 class="parameter">pattern</replaceable>, or of all visible objects if
811 no argument is given. But in either case, only objects that have
812 a description are listed.
813 (<quote>Object</quote> covers aggregates, functions, operators,
814 types, relations (tables, views, indexes, sequences, large
815 objects), rules, and triggers.) For example:
817 => <userinput>\dd version</userinput>
819 Schema | Name | Object | Description
820 ------------+---------+----------+---------------------------
821 pg_catalog | version | function | PostgreSQL version string
827 Descriptions for objects can be created with the
828 <command>COMMENT ON</command> <acronym>SQL</acronym> command.
833 <productname>PostgreSQL</productname> stores the object
834 descriptions in the <structname>pg_description</> system table.
843 <term><literal>\dD</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
846 Lists all available domains (derived types). If <replaceable
847 class="parameter">pattern</replaceable>
848 is specified, only matching domains are shown.
855 <term><literal>\dc</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
858 Lists all available conversions (between encodings). If <replaceable
859 class="parameter">pattern</replaceable>
860 is specified, only matching conversions are shown.
867 <term><literal>\dC</literal></term>
870 Lists all available type casts. Casts can be explicit, explicit and assignment
871 or implicit, and are used to change a variable from one type to another.
878 <term><literal>\df [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
882 Lists available functions, together with their argument and
883 return types. If <replaceable
884 class="parameter">pattern</replaceable>
885 is specified, only matching functions are shown. If the form
886 <literal>\df+</literal> is used, additional information about
887 each function, including language and description, is shown.
892 To reduce clutter, <literal>\df</> does not show data type I/O
893 functions. This is implemented by ignoring functions that accept
894 or return type <type>cstring</>.
903 <term><literal>\distvS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
907 This is not the actual command name: the letters i, s, t, v, S
908 stand for index, sequence, table, view, and system table,
909 respectively. You can specify any or all of these letters, in any
910 order, to obtain a listing of all the matching objects. The letter
911 S restricts the listing to system objects; without S, only non-system
913 If <quote>+</quote> is appended to the command name, each object is
914 listed with its associated description, if any.
918 If a <replaceable class="parameter">pattern</replaceable> is
919 specified, only objects whose name matches the pattern are listed.
926 <term><literal>\dl</literal></term>
929 This is an alias for <command>\lo_list</command>, which shows a
930 list of large objects.
937 <term><literal>\do [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
940 Lists available operators with their operand and return types.
941 If a <replaceable class="parameter">pattern</replaceable> is
942 specified, only operators whose name matches the pattern are listed.
949 <term><literal>\dp</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
952 Produces a list of all available tables with their
953 associated access permissions.
954 If a <replaceable class="parameter">pattern</replaceable> is
955 specified, only tables whose name matches the pattern are listed.
959 The commands <xref linkend="SQL-GRANT"> and
960 <xref linkend="SQL-REVOKE">
961 are used to set access permissions. See <xref linkend="SQL-GRANT">
962 for more information.
969 <term><literal>\dT [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
972 Lists all data types or only those that match <replaceable
973 class="parameter">pattern</replaceable>. The command form
974 <literal>\dT+</literal> shows extra information.
981 <term><literal>\du [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
984 Lists all database users, or only those that match <replaceable
985 class="parameter">pattern</replaceable>.
992 <term><literal>\edit</literal> (or <literal>\e</literal>) [ <replaceable class="parameter">filename</replaceable> ]</term>
996 If <replaceable class="parameter">filename</replaceable> is
997 specified, the file is edited; after the editor exits, its
998 content is copied back to the query buffer. If no argument is
999 given, the current query buffer is copied to a temporary file
1000 which is then edited in the same fashion.
1004 The new query buffer is then re-parsed according to the normal
1005 rules of <application>psql</application>, where the whole buffer
1006 is treated as a single line. (Thus you cannot make scripts this
1007 way. Use <command>\i</command> for that.) This means also that
1008 if the query ends with (or rather contains) a semicolon, it is
1009 immediately executed. In other cases it will merely wait in the
1015 <application>psql</application> searches the environment
1016 variables <envar>PSQL_EDITOR</envar>, <envar>EDITOR</envar>, and
1017 <envar>VISUAL</envar> (in that order) for an editor to use. If
1018 all of them are unset, <filename>/bin/vi</filename> is run.
1026 <term><literal>\echo</literal> <replaceable class="parameter">text</replaceable> [ ... ]</term>
1029 Prints the arguments to the standard output, separated by one
1030 space and followed by a newline. This can be useful to
1031 intersperse information in the output of scripts. For example:
1033 => <userinput>\echo `date`</userinput>
1034 Tue Oct 26 21:40:57 CEST 1999
1036 If the first argument is an unquoted <literal>-n</literal> the the trailing
1037 newline is not written.
1042 If you use the <command>\o</command> command to redirect your
1043 query output you may wish to use <command>\qecho</command>
1044 instead of this command.
1052 <term><literal>\encoding</literal> [ <replaceable class="parameter">encoding</replaceable> ]</term>
1056 Sets the client encoding, if you are using multibyte encodings.
1057 Without an argument, this command shows the current encoding.
1064 <term><literal>\f</literal> [ <replaceable class="parameter">string</replaceable> ]</term>
1068 Sets the field separator for unaligned query output. The default
1069 is pipe (<literal>|</literal>). See also
1070 <command>\pset</command> for a generic way of setting output
1078 <term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
1082 Sends the current query input buffer to the backend and
1083 optionally saves the output in <replaceable
1084 class="parameter">filename</replaceable> or pipes the output
1085 into a separate Unix shell to execute <replaceable
1086 class="parameter">command</replaceable>. A bare
1087 <literal>\g</literal> is virtually equivalent to a semicolon. A
1088 <literal>\g</literal> with argument is a <quote>one-shot</quote>
1089 alternative to the <command>\o</command> command.
1095 <term><literal>\help</literal> (or <literal>\h</literal>) [ <replaceable class="parameter">command</replaceable> ]</term>
1098 Give syntax help on the specified <acronym>SQL</acronym>
1099 command. If <replaceable class="parameter">command</replaceable>
1100 is not specified, then <application>psql</application> will list
1101 all the commands for which syntax help is available. If
1102 <replaceable class="parameter">command</replaceable> is an
1103 asterisk (<quote>*</quote>), then syntax help on all
1104 <acronym>SQL</acronym> commands is shown.
1109 To simplify typing, commands that consists of several words do
1110 not have to be quoted. Thus it is fine to type <userinput>\help
1111 alter table</userinput>.
1119 <term><literal>\H</literal></term>
1122 Turns on <acronym>HTML</acronym> query output format. If the
1123 <acronym>HTML</acronym> format is already on, it is switched
1124 back to the default aligned text format. This command is for
1125 compatibility and convenience, but see <command>\pset</command>
1126 about setting other output options.
1133 <term><literal>\i</literal> <replaceable class="parameter">filename</replaceable></term>
1136 Reads input from the file <replaceable
1137 class="parameter">filename</replaceable> and executes it as
1138 though it had been typed on the keyboard.
1142 If you want to see the lines on the screen as they are read you
1143 must set the variable <varname>ECHO</varname> to
1144 <literal>all</literal>.
1152 <term><literal>\l</literal> (or <literal>\list</literal>)</term>
1155 List all the databases in the server as well as their owners.
1156 Append a <quote>+</quote> to the command name to see any
1157 descriptions for the databases as well. If your
1158 <productname>PostgreSQL</productname> installation was compiled
1159 with multibyte encoding support, the encoding scheme of each
1160 database is shown as well.
1167 <term><literal>\lo_export</literal> <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></term>
1171 Reads the large object with <acronym>OID</acronym> <replaceable
1172 class="parameter">loid</replaceable> from the database and
1173 writes it to <replaceable
1174 class="parameter">filename</replaceable>. Note that this is
1175 subtly different from the server function
1176 <function>lo_export</function>, which acts with the permissions
1177 of the user that the database server runs as and on the server's
1182 Use <command>\lo_list</command> to find out the large object's
1183 <acronym>OID</acronym>.
1188 See the description of the <varname>LO_TRANSACTION</varname>
1189 variable for important information concerning all large object
1198 <term><literal>\lo_import</literal> <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</term>
1202 Stores the file into a <productname>PostgreSQL</productname>
1203 <quote>large object</quote>. Optionally, it associates the given
1204 comment with the object. Example:
1206 foo=> <userinput>\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'</userinput>
1209 The response indicates that the large object received object id
1210 152801 which one ought to remember if one wants to access the
1211 object ever again. For that reason it is recommended to always
1212 associate a human-readable comment with every object. Those can
1213 then be seen with the <command>\lo_list</command> command.
1217 Note that this command is subtly different from the server-side
1218 <function>lo_import</function> because it acts as the local user
1219 on the local file system, rather than the server's user and file
1225 See the description of the <varname>LO_TRANSACTION</varname>
1226 variable for important information concerning all large object
1234 <term><literal>\lo_list</literal></term>
1237 Shows a list of all <productname>PostgreSQL</productname>
1238 <quote>large objects</quote> currently stored in the database,
1239 along with any comments provided for them.
1245 <term><literal>\lo_unlink</literal> <replaceable class="parameter">loid</replaceable></term>
1249 Deletes the large object with <acronym>OID</acronym>
1250 <replaceable class="parameter">loid</replaceable> from the
1256 Use <command>\lo_list</command> to find out the large object's
1257 <acronym>OID</acronym>.
1262 See the description of the <varname>LO_TRANSACTION</varname>
1263 variable for important information concerning all large object
1272 <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
1276 Saves future query results to the file <replaceable
1277 class="parameter">filename</replaceable> or pipes future results
1278 into a separate Unix shell to execute <replaceable
1279 class="parameter">command</replaceable>. If no arguments are
1280 specified, the query output will be reset to
1281 <filename>stdout</filename>.
1285 <quote>Query results</quote> includes all tables, command
1286 responses, and notices obtained from the database server, as
1287 well as output of various backslash commands that query the
1288 database (such as <command>\d</command>), but not error
1294 To intersperse text output in between query results, use
1295 <command>\qecho</command>.
1303 <term><literal>\p</literal></term>
1306 Print the current query buffer to the standard output.
1313 <term><literal>\pset</literal> <replaceable class="parameter">parameter</replaceable> [ <replaceable class="parameter">value</replaceable> ]</term>
1317 This command sets options affecting the output of query result
1318 tables. <replaceable class="parameter">parameter</replaceable>
1319 describes which option is to be set. The semantics of
1320 <replaceable class="parameter">value</replaceable> depend
1325 Adjustable printing options are:
1328 <term><literal>format</literal></term>
1331 Sets the output format to one of <literal>unaligned</literal>,
1332 <literal>aligned</literal>, <literal>html</literal>, or
1333 <literal>latex</literal>. Unique abbreviations are allowed.
1334 (That would mean one letter is enough.)
1338 <quote>Unaligned</quote> writes all fields of a tuple on a
1339 line, separated by the currently active field separator. This
1340 is intended to create output that might be intended to be read
1341 in by other programs (tab-separated, comma-separated).
1342 <quote>Aligned</quote> mode is the standard, human-readable,
1343 nicely formatted text output that is default. The
1344 <quote><acronym>HTML</acronym></quote> and
1345 <quote>LaTeX</quote> modes put out tables that are intended to
1346 be included in documents using the respective mark-up
1347 language. They are not complete documents! (This might not be
1348 so dramatic in <acronym>HTML</acronym>, but in LaTeX you must
1349 have a complete document wrapper.)
1355 <term><literal>border</literal></term>
1358 The second argument must be a number. In general, the higher
1359 the number the more borders and lines the tables will have,
1360 but this depends on the particular format. In
1361 <acronym>HTML</acronym> mode, this will translate directly
1362 into the <literal>border=...</literal> attribute, in the
1363 others only values 0 (no border), 1 (internal dividing lines),
1364 and 2 (table frame) make sense.
1370 <term><literal>expanded</literal> (or <literal>x</literal>)</term>
1373 Toggles between regular and expanded format. When expanded
1374 format is enabled, all output has two columns with the field
1375 name on the left and the data on the right. This mode is
1376 useful if the data wouldn't fit on the screen in the normal
1377 <quote>horizontal</quote> mode.
1381 Expanded mode is supported by all four output modes.
1387 <term><literal>null</literal></term>
1390 The second argument is a string that should be printed
1391 whenever a field is null. The default is not to print
1392 anything, which can easily be mistaken for, say, an empty
1393 string. Thus, one might choose to write <literal>\pset null
1400 <term><literal>fieldsep</literal></term>
1403 Specifies the field separator to be used in unaligned output
1404 mode. That way one can create, for example, tab- or
1405 comma-separated output, which other programs might prefer. To
1406 set a tab as field separator, type <literal>\pset fieldsep
1407 '\t'</literal>. The default field separator is
1408 <literal>'|'</literal> (a <quote>pipe</quote> symbol).
1414 <term><literal>footer</literal></term>
1417 Toggles the display of the default footer <literal>(x
1424 <term><literal>recordsep</literal></term>
1427 Specifies the record (line) separator to use in unaligned
1428 output mode. The default is a newline character.
1434 <term><literal>tuples_only</literal> (or <literal>t</literal>)</term>
1437 Toggles between tuples only and full display. Full display may
1438 show extra information such as column headers, titles, and
1439 various footers. In tuples only mode, only actual table data
1446 <term><literal>title</literal> [ <replaceable class="parameter">text</replaceable> ]</term>
1449 Sets the table title for any subsequently printed tables. This
1450 can be used to give your output descriptive tags. If no
1451 argument is given, the title is unset.
1456 This formerly only affected <acronym>HTML</acronym> mode. You
1457 can now set titles in any output format.
1464 <term><literal>tableattr</literal> (or <literal>T</literal>) [ <replaceable class="parameter">text</replaceable> ]</term>
1467 Allows you to specify any attributes to be placed inside the
1468 <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. This
1469 could for example be <literal>cellpadding</literal> or
1470 <literal>bgcolor</literal>. Note that you probably don't want
1471 to specify <literal>border</literal> here, as that is already
1472 taken care of by <literal>\pset border</literal>.
1479 <term><literal>pager</literal></term>
1482 Controls use of a pager for query and <application>psql</>
1483 help output. If the environment variable <envar>PAGER</envar>
1484 is set, the output is piped to the specified program.
1485 Otherwise a platform-dependent default (such as
1486 <filename>more</filename>) is used.
1490 When the pager is off, the pager is not used. When the pager
1491 is on, the pager is used only when appropriate, i.e. the
1492 output is to a terminal and will not fit on the screen.
1493 (<application>psql</> does not do a perfect job of estimating
1494 when to use the pager.) <literal>\pset pager</> turns the
1495 pager on and off. Pager can also be set to <literal>always</>,
1496 which causes the pager to be always used.
1501 Illustrations on how these different formats look can be seen in
1502 the <xref linkend="APP-PSQL-examples"
1503 endterm="APP-PSQL-examples-title"> section.
1508 There are various shortcut commands for <command>\pset</command>. See
1509 <command>\a</command>, <command>\C</command>, <command>\H</command>,
1510 <command>\t</command>, <command>\T</command>, and <command>\x</command>.
1516 It is an error to call <command>\pset</command> without
1517 arguments. In the future this call might show the current status
1518 of all printing options.
1527 <term><literal>\q</literal></term>
1530 Quit the <application>psql</application> program.
1537 <term><literal>\qecho</literal> <replaceable class="parameter">text</replaceable> [ ... ] </term>
1540 This command is identical to <command>\echo</command> except
1541 that all output will be written to the query output channel, as
1542 set by <command>\o</command>.
1549 <term><literal>\r</literal></term>
1552 Resets (clears) the query buffer.
1559 <term><literal>\s</literal> [ <replaceable class="parameter">filename</replaceable> ]</term>
1562 Print or save the command line history to <replaceable
1563 class="parameter">filename</replaceable>. If <replaceable
1564 class="parameter">filename</replaceable> is omitted, the history
1565 is written to the standard output. This option is only available
1566 if <application>psql</application> is configured to use the
1567 <acronym>GNU</acronym> history library.
1572 In the current version, it is no longer necessary to save the
1573 command history, since that will be done automatically on
1574 program termination. The history is also loaded automatically
1575 every time <application>psql</application> starts up.
1583 <term><literal>\set</literal> [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ]]]</term>
1587 Sets the internal variable <replaceable
1588 class="parameter">name</replaceable> to <replaceable
1589 class="parameter">value</replaceable> or, if more than one value
1590 is given, to the concatenation of all of them. If no second
1591 argument is given, the variable is just set with no value. To
1592 unset a variable, use the <command>\unset</command> command.
1596 Valid variable names can contain characters, digits, and
1597 underscores. See the section about
1598 <application>psql</application> variables for details.
1602 Although you are welcome to set any variable to anything you
1603 want, <application>psql</application> treats several variables
1604 as special. They are documented in the section about variables.
1609 This command is totally separate from the <acronym>SQL</acronym>
1610 command <xref linkend="SQL-SET" endterm="SQL-SET-title">.
1618 <term><literal>\t</literal></term>
1621 Toggles the display of output column name headings and row count
1622 footer. This command is equivalent to <literal>\pset
1623 tuples_only</literal> and is provided for convenience.
1630 <term><literal>\T</literal> <replaceable class="parameter">table_options</replaceable></term>
1633 Allows you to specify options to be placed within the
1634 <sgmltag>table</sgmltag> tag in <acronym>HTML</acronym> tabular
1635 output mode. This command is equivalent to <literal>\pset
1636 tableattr <replaceable
1637 class="parameter">table_options</replaceable></literal>.
1644 <term><literal>\timing</literal></term>
1647 Toggles a display of how long each SQL statement takes, in milliseconds.
1654 <term><literal>\w</literal> {<replaceable class="parameter">filename</replaceable> | <replaceable class="parameter">|command</replaceable>}</term>
1657 Outputs the current query buffer to the file <replaceable
1658 class="parameter">filename</replaceable> or pipes it to the Unix
1659 command <replaceable class="parameter">command</replaceable>.
1666 <term><literal>\x</literal></term>
1669 Toggles extended row format mode. As such it is equivalent to
1670 <literal>\pset expanded</literal>.
1677 <term><literal>\z</literal> [ <replaceable class="parameter">pattern</replaceable> ]</term>
1680 Produces a list of all available tables with their
1681 associated access permissions.
1682 If a <replaceable class="parameter">pattern</replaceable> is
1683 specified, only tables whose name matches the pattern are listed.
1687 The commands <xref linkend="SQL-GRANT"> and
1688 <xref linkend="SQL-REVOKE">
1689 are used to set access permissions. See <xref linkend="SQL-GRANT">
1690 for more information.
1694 This is an alias for <command>\dp</command> (<quote>display
1695 permissions</quote>).
1702 <term><literal>\!</literal> [ <replaceable class="parameter">command</replaceable> ]</term>
1705 Escapes to a separate Unix shell or executes the Unix command
1706 <replaceable class="parameter">command</replaceable>. The
1707 arguments are not further interpreted, the shell will see them
1715 <term><literal>\?</literal></term>
1718 Get help information about the backslash (<quote>\</quote>)
1728 The various <literal>\d</> commands accept a <replaceable
1729 class="parameter">pattern</replaceable> parameter to specify the
1730 object name(s) to be displayed. Patterns are interpreted similarly
1731 to SQL identifiers, in that unquoted letters are forced to lowercase,
1732 while double quotes (<literal>"</>) protect letters from case conversion
1733 and allow incorporation of whitespace into the identifier. Within
1734 double quotes, paired double quotes reduce to a single double quote in
1735 the resulting name. For example, <literal>FOO"BAR"BAZ</> is interpreted
1736 as <literal>fooBARbaz</>, and <literal>"A weird"" name"</> becomes
1737 <literal>A weird" name</>.
1741 More interestingly, <literal>\d</> patterns allow the use of
1742 <literal>*</> to mean <quote>any sequence of characters</>, and
1743 <literal>?</> to mean <quote>any single character</>. (This notation
1744 is comparable to Unix shell filename patterns.) Advanced users can
1745 also use regular-expression notations such as character classes, for
1746 example <literal>[0-9]</> to match <quote>any digit</>. To make any of
1747 these pattern-matching characters be interpreted literally, surround it
1752 A pattern that contains an (unquoted) dot is interpreted as a schema
1753 name pattern followed by an object name pattern. For example,
1754 <literal> \dt foo*.bar*</> displays all tables in schemas whose name
1755 starts with <literal>foo</> and whose table name
1756 starts with <literal>bar</>. If no dot appears, then the pattern
1757 matches only objects that are visible in the current schema search path.
1761 Whenever the <replaceable class="parameter">pattern</replaceable> parameter
1762 is omitted completely, the <literal>\d</> commands display all objects
1763 that are visible in the current schema search path. To see all objects
1764 in the database, use the pattern <literal>*.*</>.
1769 <title>Advanced features</title>
1771 <refsect3 id="APP-PSQL-variables">
1772 <title id="APP-PSQL-variables-title">Variables</title>
1775 <application>psql</application> provides variable substitution
1776 features similar to common Unix command shells. This feature is new
1777 and not very sophisticated, yet, but there are plans to expand it in
1778 the future. Variables are simply name/value pairs, where the value
1779 can be any string of any length. To set variables, use the
1780 <application>psql</application> meta-command
1781 <command>\set</command>:
1783 testdb=> <userinput>\set foo bar</userinput>
1785 sets the variable <quote>foo</quote> to the value
1786 <quote>bar</quote>. To retrieve the content of the variable, precede
1787 the name with a colon and use it as the argument of any slash
1790 testdb=> <userinput>\echo :foo</userinput>
1797 The arguments of <command>\set</command> are subject to the same
1798 substitution rules as with other commands. Thus you can construct
1799 interesting references such as <literal>\set :foo
1800 'something'</literal> and get <quote>soft links</quote> or
1801 <quote>variable variables</quote> of <productname>Perl</productname>
1802 or <productname><acronym>PHP</acronym></productname> fame,
1803 respectively. Unfortunately (or fortunately?), there is no way to do
1804 anything useful with these constructs. On the other hand,
1805 <literal>\set bar :foo</literal> is a perfectly valid way to copy a
1811 If you call <command>\set</command> without a second argument, the
1812 variable is simply set, but has no value. To unset (or delete) a
1813 variable, use the command <command>\unset</command>.
1817 <application>psql</application>'s internal variable names can
1818 consist of letters, numbers, and underscores in any order and any
1819 number of them. A number of regular variables are treated specially
1820 by <application>psql</application>. They indicate certain option
1821 settings that can be changed at run time by altering the value of
1822 the variable or represent some state of the application. Although
1823 you can use these variables for any other purpose, this is not
1824 recommended, as the program behavior might grow really strange
1825 really quickly. By convention, all specially treated variables
1826 consist of all upper-case letters (and possibly numbers and
1827 underscores). To ensure maximum compatibility in the future, avoid
1828 such variables. A list of all specially treated variables follows.
1831 <term><varname>DBNAME</varname></term>
1834 The name of the database you are currently connected to. This is
1835 set every time you connect to a database (including program
1836 start-up), but can be unset.
1842 <term><varname>ECHO</varname></term>
1845 If set to <quote><literal>all</literal></quote>, all lines
1846 entered or from a script are written to the standard output
1847 before they are parsed or executed. To specify this on program
1848 start-up, use the switch <option>-a</option>. If set to
1849 <quote><literal>queries</literal></quote>,
1850 <application>psql</application> merely prints all queries as
1851 they are sent to the backend. The option for this is
1852 <option>-e</option>.
1858 <term><varname>ECHO_HIDDEN</varname></term>
1861 When this variable is set and a backslash command queries the
1862 database, the query is first shown. This way you can study the
1863 <productname>PostgreSQL</productname> internals and provide
1864 similar functionality in your own programs. If you set the
1865 variable to the value <literal>noexec</literal>, the queries are
1866 just shown but are not actually sent to the backend and
1873 <term><varname>ENCODING</varname></term>
1876 The current client multibyte encoding. If you are not set up to
1877 use multibyte characters, this variable will always contain
1878 <quote>SQL_ASCII</quote>.
1884 <term><varname>HISTCONTROL</varname></term>
1887 If this variable is set to <literal>ignorespace</literal>,
1888 lines which begin with a space are not entered into the history
1889 list. If set to a value of <literal>ignoredups</literal>, lines
1890 matching the previous history line are not entered. A value of
1891 <literal>ignoreboth</literal> combines the two options. If
1892 unset, or if set to any other value than those above, all lines
1893 read in interactive mode are saved on the history list.
1897 This feature was shamelessly plagiarized from
1898 <application>bash</application>.
1905 <term><varname>HISTSIZE</varname></term>
1908 The number of commands to store in the command history. The
1909 default value is 500.
1913 This feature was shamelessly plagiarized from
1914 <application>bash</application>.
1921 <term><varname>HOST</varname></term>
1924 The database server host you are currently connected to. This is
1925 set every time you connect to a database (including program
1926 start-up), but can be unset.
1932 <term><varname>IGNOREEOF</varname></term>
1935 If unset, sending an <acronym>EOF</> character (usually
1936 <keycombo action="simul"><keycap>Control</><keycap>D</></>)
1937 to an interactive session of <application>psql</application>
1938 will terminate the application. If set to a numeric value,
1939 that many <acronym>EOF</> characters are ignored before the
1940 application terminates. If the variable is set but has no
1941 numeric value, the default is 10.
1945 This feature was shamelessly plagiarized from
1946 <application>bash</application>.
1953 <term><varname>LASTOID</varname></term>
1956 The value of the last affected OID, as returned from an
1957 <command>INSERT</command> or <command>lo_insert</command>
1958 command. This variable is only guaranteed to be valid until
1959 after the result of the next <acronym>SQL</acronym> command has
1966 <term><varname>LO_TRANSACTION</varname></term>
1969 If you use the <productname>PostgreSQL</productname> large
1970 object interface to specially store data that does not fit into
1971 one tuple, all the operations must be contained in a transaction
1972 block. (See the documentation of the large object interface for
1973 more information.) Since <application>psql</application> has no
1974 way to tell if you already have a transaction in progress when
1975 you call one of its internal commands
1976 (<command>\lo_export</command>, <command>\lo_import</command>,
1977 <command>\lo_unlink</command>) it must take some arbitrary
1978 action. This action could either be to roll back any transaction
1979 that might already be in progress, or to commit any such
1980 transaction, or to do nothing at all. In the last case you must
1981 provide your own <command>BEGIN
1982 TRANSACTION</command>/<command>COMMIT</command> block or the
1983 results will be unpredictable (usually resulting in the desired
1984 action's not being performed in any case).
1988 To choose what you want to do you set this variable to one of
1989 <quote>rollback</quote>, <quote>commit</quote>, or
1990 <quote>nothing</quote>. The default is to roll back the
1991 transaction. If you just want to load one or a few objects this
1992 is fine. However, if you intend to transfer many large objects,
1993 it might be advisable to provide one explicit transaction block
1994 around all commands.
2000 <term><varname>ON_ERROR_STOP</varname></term>
2003 By default, if non-interactive scripts encounter an error, such
2004 as a malformed <acronym>SQL</acronym> query or internal
2005 meta-command, processing continues. This has been the
2006 traditional behavior of <application>psql</application> but it
2007 is sometimes not desirable. If this variable is set, script
2008 processing will immediately terminate. If the script was called
2009 from another script it will terminate in the same fashion. If
2010 the outermost script was not called from an interactive
2011 <application>psql</application> session but rather using the
2012 <option>-f</option> option, <application>psql</application> will
2013 return error code 3, to distinguish this case from fatal error
2014 conditions (error code 1).
2020 <term><varname>PORT</varname></term>
2023 The database server port to which you are currently connected.
2024 This is set every time you connect to a database (including
2025 program start-up), but can be unset.
2031 <term><varname>PROMPT1</varname></term>
2032 <term><varname>PROMPT2</varname></term>
2033 <term><varname>PROMPT3</varname></term>
2036 These specify what the prompt <application>psql</application>
2037 issues is supposed to look like. See <quote><xref
2038 linkend="APP-PSQL-prompting"
2039 endterm="APP-PSQL-prompting-title"></quote> below.
2045 <term><varname>QUIET</varname></term>
2048 This variable is equivalent to the command line option
2049 <option>-q</option>. It is probably not too useful in
2056 <term><varname>SINGLELINE</varname></term>
2059 This variable is set by the command line option
2060 <option>-S</option>. You can unset or reset it at run time.
2066 <term><varname>SINGLESTEP</varname></term>
2069 This variable is equivalent to the command line option
2070 <option>-s</option>.
2076 <term><varname>USER</varname></term>
2079 The database user you are currently connected as. This is set
2080 every time you connect to a database (including program
2081 start-up), but can be unset.
2093 <title><acronym>SQL</acronym> Interpolation</title>
2096 An additional useful feature of <application>psql</application>
2097 variables is that you can substitute (<quote>interpolate</quote>)
2098 them into regular <acronym>SQL</acronym> statements. The syntax for
2099 this is again to prepend the variable name with a colon
2100 (<literal>:</literal>).
2102 testdb=> <userinput>\set foo 'my_table'</userinput>
2103 testdb=> <userinput>SELECT * FROM :foo;</userinput>
2105 would then query the table <literal>my_table</literal>. The value of
2106 the variable is copied literally, so it can even contain unbalanced
2107 quotes or backslash commands. You must make sure that it makes sense
2108 where you put it. Variable interpolation will not be performed into
2109 quoted <acronym>SQL</acronym> entities.
2113 A popular application of this facility is to refer to the last
2114 inserted <acronym>OID</acronym> in subsequent statements to build a
2115 foreign key scenario. Another possible use of this mechanism is to
2116 copy the contents of a file into a field. First load the file into a
2117 variable and then proceed as above.
2119 testdb=> <userinput>\set content '\'' `cat my_file.txt` '\''</userinput>
2120 testdb=> <userinput>INSERT INTO my_table VALUES (:content);</userinput>
2122 One possible problem with this approach is that <filename>my_file.txt</filename>
2123 might contain single quotes. These need to be escaped so that
2124 they don't cause a syntax error when the third line is processed. This
2125 could be done with the program <application>sed</application>:
2127 testdb=> <userinput>\set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\''</userinput>
2129 Observe the correct number of backslashes (6)! You can resolve it
2130 this way: After <application>psql</application> has parsed this
2131 line, it passes <literal>sed -e "s/'/\\\'/g" < my_file.txt</literal>
2132 to the shell. The shell will do its own thing inside the double
2133 quotes and execute <filename>sed</filename> with the arguments
2134 <literal>-e</literal> and <literal>s/'/\\'/g</literal>. When
2135 <application>sed</application> parses this it will replace the two
2136 backslashes with a single one and then do the substitution. Perhaps
2137 at one point you thought it was great that all Unix commands use the
2138 same escape character. And this is ignoring the fact that you might
2139 have to escape all backslashes as well because
2140 <acronym>SQL</acronym> text constants are also subject to certain
2141 interpretations. In that case you might be better off preparing the
2146 Since colons may legally appear in queries, the following rule
2147 applies: If the variable is not set, the character sequence
2148 <quote>colon+name</quote> is not changed. In any case you can escape
2149 a colon with a backslash to protect it from interpretation. (The
2150 colon syntax for variables is standard <acronym>SQL</acronym> for
2151 embedded query languages, such as <application>ecpg</application>.
2152 The colon syntax for array slices and type casts are
2153 <productname>PostgreSQL</productname> extensions, hence the
2159 <refsect3 id="APP-PSQL-prompting">
2160 <title id="APP-PSQL-prompting-title">Prompting</title>
2163 The prompts <application>psql</application> issues can be customized
2164 to your preference. The three variables <varname>PROMPT1</varname>,
2165 <varname>PROMPT2</varname>, and <varname>PROMPT3</varname> contain strings
2166 and special escape sequences that describe the appearance of the
2167 prompt. Prompt 1 is the normal prompt that is issued when
2168 <application>psql</application> requests a new query. Prompt 2 is
2169 issued when more input is expected during query input because the
2170 query was not terminated with a semicolon or a quote was not closed.
2171 Prompt 3 is issued when you run an <acronym>SQL</acronym>
2172 <command>COPY</command> command and you are expected to type in the
2173 tuples on the terminal.
2177 The value of the respective prompt variable is printed literally,
2178 except where a percent sign (<quote>%</quote>) is encountered.
2179 Depending on the next character, certain other text is substituted
2180 instead. Defined substitutions are:
2184 <term><literal>%M</literal></term>
2187 The full host name (with domain name) of the database server,
2188 or <literal>[local]</literal> if the connection is over a Unix
2190 <literal>[local:<replaceable>/dir/name</replaceable>]</literal
2191 >, if the Unix domain socket is not at the compiled in default
2198 <term><literal>%m</literal></term>
2201 The host name of the database server, truncated after the
2202 first dot, or <literal>[local]</literal> if the connection is
2203 over a Unix domain socket.
2209 <term><literal>%></literal></term>
2210 <listitem><para>The port number at which the database server is listening.</para></listitem>
2214 <term><literal>%n</literal></term>
2215 <listitem><para>The user name you are connected as (not your local system
2216 user name).</para></listitem>
2220 <term><literal>%/</literal></term>
2221 <listitem><para>The name of the current database.</para></listitem>
2225 <term><literal>%~</literal></term>
2226 <listitem><para>Like <literal>%/</literal>, but the output is <quote>~</quote>
2227 (tilde) if the database is your default database.</para></listitem>
2231 <term><literal>%#</literal></term>
2232 <listitem><para>If the current user is a database superuser, then a
2233 <quote>#</quote>, otherwise a <quote>></quote>.</para></listitem>
2237 <term><literal>%R</literal></term>
2240 In prompt 1 normally <quote>=</quote>, but <quote>^</quote> if
2241 in single-line mode, and <quote>!</quote> if the session is
2242 disconnected from the database (which can happen if
2243 <command>\connect</command> fails). In prompt 2 the sequence is
2244 replaced by <quote>-</quote>, <quote>*</quote>, a single quote,
2245 or a double quote, depending on whether
2246 <application>psql</application> expects more input because the
2247 query wasn't terminated yet, because you are inside a
2248 <literal>/* ... */</literal> comment, or because you are inside
2249 a quote. In prompt 3 the sequence doesn't resolve to anything.
2255 <term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
2258 If <replaceable class="parameter">digits</replaceable> starts
2259 with <literal>0x</literal> the rest of the characters are
2260 interpreted as a hexadecimal digit and the character with the
2261 corresponding code is substituted. If the first digit is
2262 <literal>0</literal> the characters are interpreted as on octal
2263 number and the corresponding character is substituted. Otherwise
2264 a decimal number is assumed.
2270 <term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term>
2273 The value of the <application>psql</application>, variable
2274 <replaceable class="parameter">name</replaceable>. See the
2275 section <quote><xref linkend="APP-PSQL-variables"
2276 endterm="APP-PSQL-variables-title"></quote> for details.
2282 <term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term>
2285 The output of <replaceable
2286 class="parameter">command</replaceable>, similar to ordinary
2287 <quote>back-tick</quote> substitution.
2294 To insert a percent sign into your prompt, write
2295 <literal>%%</literal>. The default prompts are equivalent to
2296 <literal>'%/%R%# '</literal> for prompts 1 and 2, and
2297 <literal>'>> '</literal> for prompt 3.
2302 This feature was shamelessly plagiarized from
2303 <application>tcsh</application>.
2310 <title>Command-Line Editing</title>
2313 <application>psql</application> supports the <application>Readline</application>
2314 library for convenient line editing and retrieval. The command
2315 history is stored in a file named <filename>.psql_history</filename>
2316 in your home directory and is reloaded when
2317 <application>psql</application> starts up. Tab-completion is also
2318 supported, although the completion logic makes no claim to be an
2319 <acronym>SQL</acronym> parser. When available,
2320 <application>psql</application> is automatically built to use these
2321 features. If for some reason you do not like the tab completion, you
2322 can turn if off by putting this in a file named
2323 <filename>.inputrc</filename> in your home directory:
2326 set disable-completion on
2329 (This is not a <application>psql</application> but a
2330 <application>readline</application> feature. Read its documentation
2331 for further details.)
2339 <title>Environment</title>
2343 <term><envar>HOME</envar></term>
2347 Directory for initialization file (<filename>.psqlrc</filename>)
2348 and command history file (<filename>.psql_history</filename>).
2354 <term><envar>PAGER</envar></term>
2358 If the query results do not fit on the screen, they are piped
2359 through this command. Typical values are
2360 <literal>more</literal> or <literal>less</literal>. The default
2361 is platform-dependent. The use of the pager can be disabled by
2362 using the <command>\pset</command> command.
2368 <term><envar>PGDATABASE</envar></term>
2372 Default database to connect to
2378 <term><envar>PGHOST</envar></term>
2379 <term><envar>PGPORT</envar></term>
2380 <term><envar>PGUSER</envar></term>
2384 Default connection parameters
2390 <term><envar>PSQL_EDITOR</envar></term>
2391 <term><envar>EDITOR</envar></term>
2392 <term><envar>VISUAL</envar></term>
2396 Editor used by the <command>\e</command> command. The variables
2397 are examined in the order listed; the first that is set is used.
2403 <term><envar>SHELL</envar></term>
2407 Command executed by the <command>\!</command> command.
2413 <term><envar>TMPDIR</envar></term>
2417 Directory for storing temporary files. The default is
2418 <filename>/tmp</filename>.
2427 <title>Files</title>
2432 Before starting up, <application>psql</application> attempts to
2433 read and execute commands from the file
2434 <filename>$HOME/.psqlrc</filename>. It could be used to set up
2435 the client or the server to taste (using the <command>\set
2436 </command> and <command>SET</command> commands).
2442 The command-line history is stored in the file
2443 <filename>$HOME/.psql_history</filename>.
2451 <title>Notes</title>
2456 In an earlier life <application>psql</application> allowed the
2457 first argument of a single-letter backslash command to start
2458 directly after the command, without intervening whitespace. For
2459 compatibility this is still supported to some extent,
2460 but I am not going to explain the details here as this use is
2461 discouraged. If you get strange messages, keep this in mind.
2464 testdb=> <userinput>\foo</userinput>
2465 Field separator is "oo",
2467 which is perhaps not what one would expect.
2473 <application>psql</application> only works smoothly with servers
2474 of the same version. That does not mean other combinations will
2475 fail outright, but subtle and not-so-subtle problems might come
2476 up. Backslash commands are particularly likely to fail if the
2477 server is of a different version.
2483 Pressing Control-C during a <quote>copy in</quote> (data sent to
2484 the server) doesn't show the most ideal of behaviors. If you get a
2485 message such as <quote>COPY state must be terminated
2486 first</quote>, simply reset the connection by entering <literal>\c
2495 <refsect1 id="APP-PSQL-examples">
2496 <title id="APP-PSQL-examples-title">Examples</title>
2500 This section only shows a few examples specific to
2501 <application>psql</application>. If you want to learn
2502 <acronym>SQL</acronym> or get familiar with
2503 <productname>PostgreSQL</productname>, you might wish to read the
2504 Tutorial that is included in the distribution.
2509 The first example shows how to spread a query over several lines of
2510 input. Notice the changing prompt:
2512 testdb=> <userinput>CREATE TABLE my_table (</userinput>
2513 testdb(> <userinput> first integer not null default 0,</userinput>
2514 testdb(> <userinput> second text</userinput>
2515 testdb-> <userinput>);</userinput>
2518 Now look at the table definition again:
2520 testdb=> <userinput>\d my_table</userinput>
2522 Attribute | Type | Modifier
2523 -----------+---------+--------------------
2524 first | integer | not null default 0
2528 At this point you decide to change the prompt to something more
2531 testdb=> <userinput>\set PROMPT1 '%n@%m %~%R%# '</userinput>
2532 peter@localhost testdb=>
2534 Let's assume you have filled the table with data and want to take a
2537 peter@localhost testdb=> SELECT * FROM my_table;
2547 You can make this table look differently by using the
2548 <command>\pset</command> command:
2550 peter@localhost testdb=> <userinput>\pset border 2</userinput>
2552 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2563 peter@localhost testdb=> <userinput>\pset border 0</userinput>
2565 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2574 peter@localhost testdb=> <userinput>\pset border 1</userinput>
2576 peter@localhost testdb=> <userinput>\pset format unaligned</userinput>
2577 Output format is unaligned.
2578 peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput>
2579 Field separator is ",".
2580 peter@localhost testdb=> <userinput>\pset tuples_only</userinput>
2581 Showing only tuples.
2582 peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
2588 Alternatively, use the short commands:
2590 peter@localhost testdb=> <userinput>\a \t \x</userinput>
2591 Output format is aligned.
2593 Expanded display is on.
2594 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2614 <!-- Keep this comment at the end of the file
2619 sgml-minimize-attributes:nil
2620 sgml-always-quote-attributes:t
2623 sgml-parent-document:nil
2624 sgml-default-dtd-file:"../reference.ced"
2625 sgml-exposed-tags:nil
2626 sgml-local-catalogs:"/usr/lib/sgml/catalog"
2627 sgml-local-ecat-files:nil