2 $PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.119 2004/07/15 03:56:04 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
20 <indexterm zone="app-psql">
21 <primary>psql</primary>
26 <command>psql</command>
27 <arg rep="repeat"><replaceable class="parameter">option</replaceable></arg>
28 <arg><replaceable class="parameter">dbname</replaceable>
29 <arg><replaceable class="parameter">username</replaceable></arg></arg>
34 <title>Description</title>
37 <application>psql</application> is a terminal-based front-end to
38 <productname>PostgreSQL</productname>. It enables you to type in
39 queries interactively, issue them to
40 <productname>PostgreSQL</productname>, and see the query results.
41 Alternatively, input can be from a file. In addition, it provides a
42 number of meta-commands and various shell-like features to
43 facilitate writing scripts and automating a wide variety of tasks.
47 <refsect1 id="R1-APP-PSQL-3">
48 <title>Options</title>
52 <term><option>-a</></term>
53 <term><option>--echo-all</></term>
56 Print all the lines to the screen as they are read. This is more
57 useful for script processing rather than interactive mode. This is
58 equivalent to setting the variable <varname>ECHO</varname> to
59 <literal>all</literal>.
65 <term><option>-A</></term>
66 <term><option>--no-align</></term>
69 Switches to unaligned output mode. (The default output mode is
76 <term><option>-c <replaceable class="parameter">command</replaceable></></term>
77 <term><option>--command <replaceable class="parameter">command</replaceable></></term>
80 Specifies that <application>psql</application> is to execute one
81 command string, <replaceable class="parameter">command</replaceable>,
82 and then exit. This is useful in shell scripts.
85 <replaceable class="parameter">command</replaceable> must be either
86 a command string that is completely parsable by the server (i.e.,
87 it contains no <application>psql</application> specific features),
88 or it is a single backslash command. Thus you cannot mix
89 <acronym>SQL</acronym> and <application>psql</application>
90 meta-commands. To achieve that, you could pipe the string into
91 <application>psql</application>, like this: <literal>echo "\x \\
92 select * from foo;" | psql</literal>.
95 If the command string contains multiple SQL commands, they are
96 processed in a single transaction, unless there are explicit
97 BEGIN/COMMIT commands included in the string to divide it into
98 multiple transactions. This is different from the behavior when
99 the same string is fed to <application>psql</application>'s standard input.
105 <term><option>-d <replaceable class="parameter">dbname</replaceable></></term>
106 <term><option>--dbname <replaceable class="parameter">dbname</replaceable></></term>
109 Specifies the name of the database to connect to. This is
110 equivalent to specifying <replaceable
111 class="parameter">dbname</replaceable> as the first non-option
112 argument on the command line.
118 <term><option>-e</></term>
119 <term><option>--echo-queries</></term>
122 Show all commands that are sent to the server. This is equivalent
123 to setting the variable <varname>ECHO</varname> to
124 <literal>queries</literal>.
130 <term><option>-E</></term>
131 <term><option>--echo-hidden</></term>
134 Echo the actual queries generated by <command>\d</command> and other backslash
135 commands. You can use this if you wish to include similar
136 functionality into your own programs. This is equivalent to
137 setting the variable <varname>ECHO_HIDDEN</varname> from within
138 <application>psql</application>.
144 <term><option>-f <replaceable class="parameter">filename</replaceable></></term>
145 <term><option>--file <replaceable class="parameter">filename</replaceable></></term>
148 Use the file <replaceable class="parameter">filename</replaceable>
149 as the source of commands instead of reading commands interactively.
150 After the file is processed, <application>psql</application>
151 terminates. This is in many ways equivalent to the internal
152 command <command>\i</command>.
156 If <replaceable>filename</replaceable> is <literal>-</literal>
157 (hyphen), then standard input is read.
161 Using this option is subtly different from writing <literal>psql
163 class="parameter">filename</replaceable></literal>. In general,
164 both will do what you expect, but using <literal>-f</literal>
165 enables some nice features such as error messages with line
166 numbers. There is also a slight chance that using this option will
167 reduce the start-up overhead. On the other hand, the variant using
168 the shell's input redirection is (in theory) guaranteed to yield
169 exactly the same output that you would have gotten had you entered
176 <term><option>-F <replaceable class="parameter">separator</replaceable></></term>
177 <term><option>--field-separator <replaceable class="parameter">separator</replaceable></></term>
180 Use <replaceable class="parameter">separator</replaceable> as the
181 field separator. This is equivalent to <command>\pset
182 fieldsep</command> or <command>\f</command>.
188 <term><option>-h <replaceable class="parameter">hostname</replaceable></></term>
189 <term><option>--host <replaceable class="parameter">hostname</replaceable></></term>
192 Specifies the host name of the machine on which the
193 server is running. If the value begins
194 with a slash, it is used as the directory for the Unix-domain
201 <term><option>-H</></term>
202 <term><option>--html</></term>
205 Turn on <acronym>HTML</acronym> tabular output. This is
206 equivalent to <literal>\pset format html</literal> or the
207 <command>\H</command> command.
213 <term><option>-l</></term>
214 <term><option>--list</></term>
217 List all available databases, then exits. Other non-connection
218 options are ignored. This is similar to the internal command
219 <command>\list</command>.
225 <term><option>-o <replaceable class="parameter">filename</replaceable></></term>
226 <term><option>--output <replaceable class="parameter">filename</replaceable></></term>
229 Put all query output into file <replaceable
230 class="parameter">filename</replaceable>. This is equivalent to
231 the command <command>\o</command>.
237 <term><option>-p <replaceable class="parameter">port</replaceable></></term>
238 <term><option>--port <replaceable class="parameter">port</replaceable></></term>
241 Specifies the TCP port or the local Unix domain
242 socket file extension on which the server is listening for
243 connections. Defaults to the value of the <envar>PGPORT</envar>
244 environment variable or, if not set, to the port specified at
245 compile time, usually 5432.
251 <term><option>-P <replaceable class="parameter">assignment</replaceable></></term>
252 <term><option>--pset <replaceable class="parameter">assignment</replaceable></></term>
255 Allows you to specify printing options in the style of
256 <command>\pset</command> on the command line. Note that here you
257 have to separate name and value with an equal sign instead of a
258 space. Thus to set the output format to LaTeX, you could write
259 <literal>-P format=latex</literal>.
265 <term><option>-q</></term>
266 <term><option>--quiet</></term>
269 Specifies that <application>psql</application> should do its work
270 quietly. By default, it prints welcome messages and various
271 informational output. If this option is used, none of this
272 happens. This is useful with the <option>-c</option> option.
273 Within <application>psql</application> you can also set the
274 <varname>QUIET</varname> variable to achieve the same effect.
280 <term><option>-R <replaceable class="parameter">separator</replaceable></></term>
281 <term><option>--record-separator <replaceable class="parameter">separator</replaceable></></term>
284 Use <replaceable class="parameter">separator</replaceable> as the
285 record separator. This is equivalent to the <command>\pset
286 recordsep</command> command.
292 <term><option>-s</></term>
293 <term><option>--single-step</></term>
296 Run in single-step mode. That means the user is prompted before
297 each command is sent to the server, with the option to cancel
298 execution as well. Use this to debug scripts.
304 <term><option>-S</></term>
305 <term><option>--single-line</></term>
308 Runs in single-line mode where a newline terminates an SQL command, as a
314 This mode is provided for those who insist on it, but you are not
315 necessarily encouraged to use it. In particular, if you mix
316 <acronym>SQL</acronym> and meta-commands on a line the order of
317 execution might not always be clear to the inexperienced user.
324 <term><option>-t</></term>
325 <term><option>--tuples-only</></term>
328 Turn off printing of column names and result row count footers,
329 etc. It is completely equivalent to the <command>\t</command>
336 <term><option>-T <replaceable class="parameter">table_options</replaceable></></term>
337 <term><option>--table-attr <replaceable class="parameter">table_options</replaceable></></term>
340 Allows you to specify options to be placed within the
341 <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. See
342 <command>\pset</command> for details.
348 <term><option>-u</></term>
351 Makes <application>psql</application> prompt for the user name and
352 password before connecting to the database.
356 This option is deprecated, as it is conceptually flawed.
357 (Prompting for a non-default user name and prompting for a
358 password because the server requires it are really two different
359 things.) You are encouraged to look at the <option>-U</option> and
360 <option>-W</option> options instead.
366 <term><option>-U <replaceable class="parameter">username</replaceable></></term>
367 <term><option>--username <replaceable class="parameter">username</replaceable></></term>
370 Connect to the database as the user <replaceable
371 class="parameter">username</replaceable> instead of the default.
372 (You must have permission to do so, of course.)
378 <term><option>-v <replaceable class="parameter">assignment</replaceable></></term>
379 <term><option>--set <replaceable class="parameter">assignment</replaceable></></term>
380 <term><option>--variable <replaceable class="parameter">assignment</replaceable></></term>
383 Perform a variable assignment, like the <command>\set</command>
384 internal command. Note that you must separate name and value, if
385 any, by an equal sign on the command line. To unset a variable,
386 leave off the equal sign. To just set a variable without a value,
387 use the equal sign but leave off the value. These assignments are
388 done during a very early stage of start-up, so variables reserved
389 for internal purposes might get overwritten later.
395 <term><option>-V</></term>
396 <term><option>--version</></term>
399 Show the <application>psql</application> version.
405 <term><option>-W</></term>
406 <term><option>--password</></term>
409 Requests that <application>psql</application> should prompt for a
410 password before connecting to a database. This will remain set for
411 the entire session, even if you change the database connection
412 with the meta-command <command>\connect</command>.
416 In the current version, <application>psql</application>
417 automatically issues a password prompt whenever the server
418 requests password authentication. Because this is currently based
419 on a hack, the automatic recognition might mysteriously fail,
420 hence this option to force a prompt. If no password prompt is
421 issued and the server requires password authentication the
422 connection attempt will fail.
428 <term><option>-x</></term>
429 <term><option>--expanded</></term>
432 Turn on the extended table formatting mode. This is equivalent to the
433 command <command>\x</command>.
439 <term><option>-X,</></term>
440 <term><option>--no-psqlrc</></term>
443 Do not read the start-up file <filename>/psqlrc</filename> or
444 <filename>~/.psqlrc</filename>.
450 <term><option>-?</></term>
451 <term><option>--help</></term>
454 Show help about <application>psql</application> command line
464 <title>Exit Status</title>
467 <application>psql</application> returns 0 to the shell if it
468 finished normally, 1 if a fatal error of its own (out of memory,
469 file not found) occurs, 2 if the connection to the server went bad
470 and the session was not interactive, and 3 if an error occurred in a
471 script and the variable <varname>ON_ERROR_STOP</varname> was set.
479 <refsect2 id="R2-APP-PSQL-connecting">
480 <title>Connecting To A Database</title>
483 <application>psql</application> is a regular
484 <productname>PostgreSQL</productname> client application. In order
485 to connect to a database you need to know the name of your target
486 database, the host name and port number of the server and what user
487 name you want to connect as. <application>psql</application> can be
488 told about those parameters via command line options, namely
489 <option>-d</option>, <option>-h</option>, <option>-p</option>, and
490 <option>-U</option> respectively. If an argument is found that does
491 not belong to any option it will be interpreted as the database name
492 (or the user name, if the database name is also given). Not all
493 these options are required, defaults do apply. If you omit the host
494 name, <application>psql</> will connect via a Unix domain socket
495 to a server on the local host, or via TCP/IP to <literal>localhost</> on machines
496 that don't have unix domain sockets. The default port number is compile-time determined.
497 Since the database server uses the same default, you will not have
498 to specify the port in most cases. The default user name is your
499 Unix user name, as is the default database name. Note that you can't
500 just connect to any database under any user name. Your database
501 administrator should have informed you about your access rights. To
502 save you some typing you can also set the environment variables
503 <envar>PGDATABASE</envar>, <envar>PGHOST</envar>,
504 <envar>PGPORT</envar> and <envar>PGUSER</envar> to appropriate
509 If the connection could not be made for any reason (e.g., insufficient
510 privileges, server is not running on the targeted host, etc.),
511 <application>psql</application> will return an error and terminate.
515 <refsect2 id="R2-APP-PSQL-4">
516 <title>Entering SQL Commands</title>
519 In normal operation, <application>psql</application> provides a
520 prompt with the name of the database to which
521 <application>psql</application> is currently connected, followed by
522 the string <literal>=></literal>. For example,
524 $ <userinput>psql testdb</userinput>
525 Welcome to psql &version;, the PostgreSQL interactive terminal.
527 Type: \copyright for distribution terms
528 \h for help with SQL commands
529 \? for help with psql commands
530 \g or terminate with semicolon to execute query
538 At the prompt, the user may type in <acronym>SQL</acronym> commands.
539 Ordinarily, input lines are sent to the server when a
540 command-terminating semicolon is reached. An end of line does not
541 terminate a command. Thus commands can be spread over several lines for
542 clarity. If the command was sent and without error, the results of the command
543 are displayed on the screen.
547 Whenever a command is executed, <application>psql</application> also polls
548 for asynchronous notification events generated by
549 <xref linkend="SQL-LISTEN" endterm="SQL-LISTEN-title"> and
550 <xref linkend="SQL-NOTIFY" endterm="SQL-NOTIFY-title">.
555 <title>Meta-Commands</title>
558 Anything you enter in <application>psql</application> that begins
559 with an unquoted backslash is a <application>psql</application>
560 meta-command that is processed by <application>psql</application>
561 itself. These commands are what makes
562 <application>psql</application> interesting for administration or
563 scripting. Meta-commands are more commonly called slash or backslash
568 The format of a <application>psql</application> command is the backslash,
569 followed immediately by a command verb, then any arguments. The arguments
570 are separated from the command verb and each other by any number of
571 whitespace characters.
575 To include whitespace into an argument you may quote it with a
576 single quote. To include a single quote into such an argument,
577 precede it by a backslash. Anything contained in single quotes is
578 furthermore subject to C-like substitutions for
579 <literal>\n</literal> (new line), <literal>\t</literal> (tab),
580 <literal>\</literal><replaceable>digits</replaceable>,
581 <literal>\0</literal><replaceable>digits</replaceable>, and
582 <literal>\0x</literal><replaceable>digits</replaceable> (the
583 character with the given decimal, octal, or hexadecimal code).
587 If an unquoted argument begins with a colon (<literal>:</literal>),
588 it is taken as a <application>psql</> variable and the value of the
589 variable is used as the argument instead.
593 Arguments that are enclosed in backquotes (<literal>`</literal>)
594 are taken as a command line that is passed to the shell. The
595 output of the command (with any trailing newline removed) is taken
596 as the argument value. The above escape sequences also apply in
601 Some commands take an <acronym>SQL</acronym> identifier (such as a
602 table name) as argument. These arguments follow the syntax rules
603 of <acronym>SQL</acronym>: Unquoted letters are forced to
604 lowercase, while double quotes (<literal>"</>) protect letters
605 from case conversion and allow incorporation of whitespace into
606 the identifier. Within double quotes, paired double quotes reduce
607 to a single double quote in the resulting name. For example,
608 <literal>FOO"BAR"BAZ</> is interpreted as <literal>fooBARbaz</>,
609 and <literal>"A weird"" name"</> becomes <literal>A weird"
614 Parsing for arguments stops when another unquoted backslash occurs.
615 This is taken as the beginning of a new meta-command. The special
616 sequence <literal>\\</literal> (two backslashes) marks the end of
617 arguments and continues parsing <acronym>SQL</acronym> commands, if
618 any. That way <acronym>SQL</acronym> and
619 <application>psql</application> commands can be freely mixed on a
620 line. But in any case, the arguments of a meta-command cannot
621 continue beyond the end of the line.
625 The following meta-commands are defined:
629 <term><literal>\a</literal></term>
632 If the current table output format is unaligned, it is switched to aligned.
633 If it is not unaligned, it is set to unaligned. This command is
634 kept for backwards compatibility. See <command>\pset</command> for a
641 <term><literal>\cd [ <replaceable>directory</replaceable> ]</literal></term>
644 Changes the current working directory to
645 <replaceable>directory</replaceable>. Without argument, changes
646 to the current user's home directory.
651 To print your current working directory, use <literal>\!pwd</literal>.
658 <term><literal>\C [ <replaceable class="parameter">title</replaceable> ]</literal></term>
661 Sets the title of any tables being printed as the result of a
662 query or unset any such title. This command is equivalent to
663 <literal>\pset title <replaceable
664 class="parameter">title</replaceable></literal>. (The name of
665 this command derives from <quote>caption</quote>, as it was
666 previously only used to set the caption in an
667 <acronym>HTML</acronym> table.)
673 <term><literal>\connect</literal> (or <literal>\c</literal>) <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] ]</literal></term>
676 Establishes a connection to a new database and/or under a user
677 name. The previous connection is closed. If <replaceable
678 class="parameter">dbname</replaceable> is <literal>-</literal>
679 the current database name is assumed.
683 If <replaceable class="parameter">username</replaceable> is
684 omitted the current user name is assumed. </para>
687 As a special rule, <command>\connect</command> without any
688 arguments will connect to the default database as the default
689 user (as you would have gotten by starting
690 <application>psql</application> without any arguments).
694 If the connection attempt failed (wrong user name, access
695 denied, etc.), the previous connection will be kept if and only
696 if <application>psql</application> is in interactive mode. When
697 executing a non-interactive script, processing will immediately
698 stop with an error. This distinction was chosen as a user
699 convenience against typos on the one hand, and a safety
700 mechanism that scripts are not accidentally acting on the wrong
701 database on the other hand.
707 <term><literal>\copy <replaceable class="parameter">table</replaceable>
708 [ ( <replaceable class="parameter">column_list</replaceable> ) ]
709 { <literal>from</literal> | <literal>to</literal> }
710 { <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
713 [ delimiter [ as ] '<replaceable class="parameter">character</replaceable>' ]
714 [ null [ as ] '<replaceable class="parameter">string</replaceable>' ]
715 [ csv [ quote [ as ] '<replaceable class="parameter">character</replaceable>' ]
716 [ escape [ as ] '<replaceable class="parameter">character</replaceable>' ]
717 [ force quote <replaceable class="parameter">column_list</replaceable> ]
718 [ force not null <replaceable class="parameter">column_list</replaceable> ] ]</literal>
723 Performs a frontend (client) copy. This is an operation that
724 runs an <acronym>SQL</acronym> <xref linkend="SQL-COPY"
725 endterm="SQL-COPY-title"> command, but instead of the server
726 reading or writing the specified file,
727 <application>psql</application> reads or writes the file and
728 routes the data between the server and the local file system.
729 This means that file accessibility and privileges are those of
730 the local user, not the server, and no SQL superuser
731 privileges are required.
735 The syntax of the command is similar to that of the
736 <acronym>SQL</acronym> <xref linkend="sql-copy"
737 endterm="sql-copy-title"> command. Note that, because of this,
738 special parsing rules apply to the <command>\copy</command>
739 command. In particular, the variable substitution rules and
740 backslash escapes do not apply.
744 <literal>\copy <replaceable
745 class="parameter">table</replaceable> from <replaceable
746 class="parameter">stdin | stdout</replaceable></literal>
747 reads/writes based on the command input and output respectively.
748 All rows are read from the same source that issued the command,
749 continuing until <literal>\.</literal> is read or the stream
750 reaches <acronym>EOF</>. Output is sent to the same place as
751 command output. To read/write from
752 <application>psql</application>'s standard input or output, use
753 <literal>pstdin</> or <literal>pstdout</>. This option is useful
754 for populating tables in-line within a SQL script file.
759 This operation is not as efficient as the <acronym>SQL</acronym>
760 <command>COPY</command> command because all data must pass
761 through the client/server connection. For large
762 amounts of data the <acronym>SQL</acronym> command may be preferable.
770 <term><literal>\copyright</literal></term>
773 Shows the copyright and distribution terms of
774 <application>PostgreSQL</application>.
780 <term><literal>\d [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
781 <term><literal>\d+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
785 For each relation (table, view, index, or sequence) matching the
786 <replaceable class="parameter">pattern</replaceable>, show all
787 columns, their types, the tablespace (if not the default) and any special
788 attributes such as <literal>NOT NULL</literal> or defaults, if
789 any. Associated indexes, constraints, rules, and triggers are
790 also shown, as is the view definition if the relation is a view.
791 (<quote>Matching the pattern</> is defined below.)
795 The command form <literal>\d+</literal> is identical, except that
796 more information is displayed: any comments associated with the
797 columns of the table are shown, as is the presence of OIDs in the
803 If <command>\d</command> is used without a
804 <replaceable class="parameter">pattern</replaceable> argument, it is
805 equivalent to <command>\dtvs</command> which will show a list of
806 all tables, views, and sequences. This is purely a convenience
814 <term><literal>\da [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
818 Lists all available aggregate functions, together with the data
819 type they operate on. If <replaceable
820 class="parameter">pattern</replaceable>
821 is specified, only aggregates whose names match the pattern are shown.
828 <term><literal>\db [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
832 Lists all available tablespaces. If <replaceable
833 class="parameter">pattern</replaceable>
834 is specified, only tablespaces whose names match the pattern are shown.
835 If <literal>+</literal> is appended to the command name, each object
836 is listed with its associated permissions.
843 <term><literal>\dc [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
846 Lists all available conversions between character-set encodings.
847 If <replaceable class="parameter">pattern</replaceable>
848 is specified, only conversions whose names match the pattern are
856 <term><literal>\dC</literal></term>
859 Lists all available type casts.
866 <term><literal>\dd [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
869 Shows the descriptions of objects matching the <replaceable
870 class="parameter">pattern</replaceable>, or of all visible objects if
871 no argument is given. But in either case, only objects that have
872 a description are listed.
873 (<quote>Object</quote> covers aggregates, functions, operators,
874 types, relations (tables, views, indexes, sequences, large
875 objects), rules, and triggers.) For example:
877 => <userinput>\dd version</userinput>
879 Schema | Name | Object | Description
880 ------------+---------+----------+---------------------------
881 pg_catalog | version | function | PostgreSQL version string
887 Descriptions for objects can be created with the <xref
888 linkend="sql-comment" endterm="sql-comment-title">
889 <acronym>SQL</acronym> command.
896 <term><literal>\dD [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
899 Lists all available domains. If <replaceable
900 class="parameter">pattern</replaceable>
901 is specified, only matching domains are shown.
908 <term><literal>\df [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
909 <term><literal>\df+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
913 Lists available functions, together with their argument and
914 return types. If <replaceable
915 class="parameter">pattern</replaceable>
916 is specified, only functions whose names match the pattern are shown.
917 If the form <literal>\df+</literal> is used, additional information about
918 each function, including language and description, is shown.
923 To look up functions taking argument or returning values of a specific
924 type, use your pager's search capability to scroll through the <literal>\df</>
929 To reduce clutter, <literal>\df</> does not show data type I/O
930 functions. This is implemented by ignoring functions that accept
931 or return type <type>cstring</>.
940 <term><literal>\dg [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
943 Lists all database groups. If <replaceable
944 class="parameter">pattern</replaceable> is specified, only
945 those groups whose names match the pattern are listed.
952 <term><literal>\distvS [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
956 This is not the actual command name: the letters
957 <literal>i</literal>, <literal>s</literal>, <literal>t</literal>,
958 <literal>v</literal>, <literal>S</literal> stand for index,
959 sequence, table, view, and system table, respectively. You can
960 specify any or all of these letters, in any order, to obtain a
961 listing of all the matching objects. The letter S restricts the
962 listing to system objects; without <literal>S</literal>, only
963 non-system objects are shown. If <literal>+</literal> is appended
964 to the command name, each object is listed with its associated
969 If <replaceable class="parameter">pattern</replaceable> is
970 specified, only objects whose names match the pattern are listed.
977 <term><literal>\dl</literal></term>
980 This is an alias for <command>\lo_list</command>, which shows a
981 list of large objects.
988 <term><literal>\dn [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
992 Lists all available schemas (namespaces). If <replaceable
993 class="parameter">pattern</replaceable> (a regular expression)
994 is specified, only schemas whose names match the pattern are listed.
995 Non-local temporary schemas are suppressed. If <literal>+</literal>
996 is appended to the command name, each object is listed with its associated
997 permissions and description, if any.
1004 <term><literal>\do [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1007 Lists available operators with their operand and return types.
1008 If <replaceable class="parameter">pattern</replaceable> is
1009 specified, only operators whose names match the pattern are listed.
1016 <term><literal>\dp [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1019 Produces a list of all available tables, views and sequences with their
1020 associated access privileges.
1021 If <replaceable class="parameter">pattern</replaceable> is
1022 specified, only tables, views and sequences whose names match the pattern are listed.
1026 The commands <xref linkend="SQL-GRANT"> and
1027 <xref linkend="SQL-REVOKE">
1028 are used to set access privileges. See <xref linkend="SQL-GRANT">
1029 for more information.
1036 <term><literal>\dT [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1037 <term><literal>\dT+ [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1040 Lists all data types or only those that match <replaceable
1041 class="parameter">pattern</replaceable>. The command form
1042 <literal>\dT+</literal> shows extra information.
1049 <term><literal>\du [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1052 Lists all database users or only those that match <replaceable
1053 class="parameter">pattern</replaceable>.
1060 <term><literal>\edit</literal> (or <literal>\e</literal>) <literal>[ <replaceable class="parameter">filename</replaceable> ]</literal></term>
1064 If <replaceable class="parameter">filename</replaceable> is
1065 specified, the file is edited; after the editor exits, its
1066 content is copied back to the query buffer. If no argument is
1067 given, the current query buffer is copied to a temporary file
1068 which is then edited in the same fashion.
1072 The new query buffer is then re-parsed according to the normal
1073 rules of <application>psql</application>, where the whole buffer
1074 is treated as a single line. (Thus you cannot make scripts this
1075 way. Use <command>\i</command> for that.) This means also that
1076 if the query ends with (or rather contains) a semicolon, it is
1077 immediately executed. In other cases it will merely wait in the
1083 <application>psql</application> searches the environment
1084 variables <envar>PSQL_EDITOR</envar>, <envar>EDITOR</envar>, and
1085 <envar>VISUAL</envar> (in that order) for an editor to use. If
1086 all of them are unset, <filename>/bin/vi</filename> is run.
1094 <term><literal>\echo <replaceable class="parameter">text</replaceable> [ ... ]</literal></term>
1097 Prints the arguments to the standard output, separated by one
1098 space and followed by a newline. This can be useful to
1099 intersperse information in the output of scripts. For example:
1101 => <userinput>\echo `date`</userinput>
1102 Tue Oct 26 21:40:57 CEST 1999
1104 If the first argument is an unquoted <literal>-n</literal> the the trailing
1105 newline is not written.
1110 If you use the <command>\o</command> command to redirect your
1111 query output you may wish to use <command>\qecho</command>
1112 instead of this command.
1120 <term><literal>\encoding [ <replaceable class="parameter">encoding</replaceable> ]</literal></term>
1124 Sets the client character set encoding. Without an argument, this command
1125 shows the current encoding.
1132 <term><literal>\f [ <replaceable class="parameter">string</replaceable> ]</literal></term>
1136 Sets the field separator for unaligned query output. The default
1137 is the vertical bar (<literal>|</literal>). See also
1138 <command>\pset</command> for a generic way of setting output
1146 <term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term>
1150 Sends the current query input buffer to the server and
1151 optionally saves the output in <replaceable
1152 class="parameter">filename</replaceable> or pipes the output
1153 into a separate Unix shell to execute <replaceable
1154 class="parameter">command</replaceable>. A bare
1155 <literal>\g</literal> is virtually equivalent to a semicolon. A
1156 <literal>\g</literal> with argument is a <quote>one-shot</quote>
1157 alternative to the <command>\o</command> command.
1163 <term><literal>\help</literal> (or <literal>\h</literal>) <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term>
1166 Gives syntax help on the specified <acronym>SQL</acronym>
1167 command. If <replaceable class="parameter">command</replaceable>
1168 is not specified, then <application>psql</application> will list
1169 all the commands for which syntax help is available. If
1170 <replaceable class="parameter">command</replaceable> is an
1171 asterisk (<literal>*</literal>), then syntax help on all
1172 <acronym>SQL</acronym> commands is shown.
1177 To simplify typing, commands that consists of several words do
1178 not have to be quoted. Thus it is fine to type <userinput>\help
1179 alter table</userinput>.
1187 <term><literal>\H</literal></term>
1190 Turns on <acronym>HTML</acronym> query output format. If the
1191 <acronym>HTML</acronym> format is already on, it is switched
1192 back to the default aligned text format. This command is for
1193 compatibility and convenience, but see <command>\pset</command>
1194 about setting other output options.
1201 <term><literal>\i <replaceable class="parameter">filename</replaceable></literal></term>
1204 Reads input from the file <replaceable
1205 class="parameter">filename</replaceable> and executes it as
1206 though it had been typed on the keyboard.
1210 If you want to see the lines on the screen as they are read you
1211 must set the variable <varname>ECHO</varname> to
1212 <literal>all</literal>.
1220 <term><literal>\l</literal> (or <literal>\list</literal>)</term>
1221 <term><literal>\l+</literal> (or <literal>\list+</literal>)</term>
1224 List the names, owners, and character set encodings of all the databases in
1225 the server. If <literal>+</literal> is appended to the command
1226 name, database descriptions are also displayed.
1233 <term><literal>\lo_export <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></literal></term>
1237 Reads the large object with <acronym>OID</acronym> <replaceable
1238 class="parameter">loid</replaceable> from the database and
1239 writes it to <replaceable
1240 class="parameter">filename</replaceable>. Note that this is
1241 subtly different from the server function
1242 <function>lo_export</function>, which acts with the permissions
1243 of the user that the database server runs as and on the server's
1248 Use <command>\lo_list</command> to find out the large object's
1249 <acronym>OID</acronym>.
1257 <term><literal>\lo_import <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</literal></term>
1261 Stores the file into a <productname>PostgreSQL</productname>
1262 large object. Optionally, it associates the given
1263 comment with the object. Example:
1265 foo=> <userinput>\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'</userinput>
1268 The response indicates that the large object received object ID
1269 152801 which one ought to remember if one wants to access the
1270 object ever again. For that reason it is recommended to always
1271 associate a human-readable comment with every object. Those can
1272 then be seen with the <command>\lo_list</command> command.
1276 Note that this command is subtly different from the server-side
1277 <function>lo_import</function> because it acts as the local user
1278 on the local file system, rather than the server's user and file
1285 <term><literal>\lo_list</literal></term>
1288 Shows a list of all <productname>PostgreSQL</productname>
1289 large objects currently stored in the database,
1290 along with any comments provided for them.
1296 <term><literal>\lo_unlink <replaceable class="parameter">loid</replaceable></literal></term>
1300 Deletes the large object with <acronym>OID</acronym>
1301 <replaceable class="parameter">loid</replaceable> from the
1307 Use <command>\lo_list</command> to find out the large object's
1308 <acronym>OID</acronym>.
1316 <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term>
1320 Saves future query results to the file <replaceable
1321 class="parameter">filename</replaceable> or pipes future results
1322 into a separate Unix shell to execute <replaceable
1323 class="parameter">command</replaceable>. If no arguments are
1324 specified, the query output will be reset to the standard output.
1328 <quote>Query results</quote> includes all tables, command
1329 responses, and notices obtained from the database server, as
1330 well as output of various backslash commands that query the
1331 database (such as <command>\d</command>), but not error
1337 To intersperse text output in between query results, use
1338 <command>\qecho</command>.
1346 <term><literal>\p</literal></term>
1349 Print the current query buffer to the standard output.
1356 <term><literal>\pset <replaceable class="parameter">parameter</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term>
1360 This command sets options affecting the output of query result
1361 tables. <replaceable class="parameter">parameter</replaceable>
1362 describes which option is to be set. The semantics of
1363 <replaceable class="parameter">value</replaceable> depend
1368 Adjustable printing options are:
1371 <term><literal>format</literal></term>
1374 Sets the output format to one of <literal>unaligned</literal>,
1375 <literal>aligned</literal>, <literal>html</literal>, or
1376 <literal>latex</literal>. Unique abbreviations are allowed.
1377 (That would mean one letter is enough.)
1381 <quote>Unaligned</quote> writes all columns of a row on a
1382 line, separated by the currently active field separator. This
1383 is intended to create output that might be intended to be read
1384 in by other programs (tab-separated, comma-separated).
1385 <quote>Aligned</quote> mode is the standard, human-readable,
1386 nicely formatted text output that is default. The
1387 <quote><acronym>HTML</acronym></quote> and
1388 <quote>LaTeX</quote> modes put out tables that are intended to
1389 be included in documents using the respective mark-up
1390 language. They are not complete documents! (This might not be
1391 so dramatic in <acronym>HTML</acronym>, but in LaTeX you must
1392 have a complete document wrapper.)
1398 <term><literal>border</literal></term>
1401 The second argument must be a number. In general, the higher
1402 the number the more borders and lines the tables will have,
1403 but this depends on the particular format. In
1404 <acronym>HTML</acronym> mode, this will translate directly
1405 into the <literal>border=...</literal> attribute, in the
1406 others only values 0 (no border), 1 (internal dividing lines),
1407 and 2 (table frame) make sense.
1413 <term><literal>expanded</literal> (or <literal>x</literal>)</term>
1416 Toggles between regular and expanded format. When expanded
1417 format is enabled, all output has two columns with the column
1418 name on the left and the data on the right. This mode is
1419 useful if the data wouldn't fit on the screen in the normal
1420 <quote>horizontal</quote> mode.
1424 Expanded mode is supported by all four output formats.
1430 <term><literal>null</literal></term>
1433 The second argument is a string that should be printed
1434 whenever a column is null. The default is not to print
1435 anything, which can easily be mistaken for, say, an empty
1436 string. Thus, one might choose to write <literal>\pset null
1443 <term><literal>fieldsep</literal></term>
1446 Specifies the field separator to be used in unaligned output
1447 mode. That way one can create, for example, tab- or
1448 comma-separated output, which other programs might prefer. To
1449 set a tab as field separator, type <literal>\pset fieldsep
1450 '\t'</literal>. The default field separator is
1451 <literal>'|'</literal> (a vertical bar).
1457 <term><literal>footer</literal></term>
1460 Toggles the display of the default footer <literal>(x
1467 <term><literal>recordsep</literal></term>
1470 Specifies the record (line) separator to use in unaligned
1471 output mode. The default is a newline character.
1477 <term><literal>tuples_only</literal> (or <literal>t</literal>)</term>
1480 Toggles between tuples only and full display. Full display may
1481 show extra information such as column headers, titles, and
1482 various footers. In tuples only mode, only actual table data
1489 <term><literal>title [ <replaceable class="parameter">text</replaceable> ]</literal></term>
1492 Sets the table title for any subsequently printed tables. This
1493 can be used to give your output descriptive tags. If no
1494 argument is given, the title is unset.
1500 <term><literal>tableattr</literal> (or <literal>T</literal>) <literal>[ <replaceable class="parameter">text</replaceable> ]</literal></term>
1503 Allows you to specify any attributes to be placed inside the
1504 <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. This
1505 could for example be <literal>cellpadding</literal> or
1506 <literal>bgcolor</literal>. Note that you probably don't want
1507 to specify <literal>border</literal> here, as that is already
1508 taken care of by <literal>\pset border</literal>.
1515 <term><literal>pager</literal></term>
1518 Controls use of a pager for query and <application>psql</>
1519 help output. If the environment variable <envar>PAGER</envar>
1520 is set, the output is piped to the specified program.
1521 Otherwise a platform-dependent default (such as
1522 <filename>more</filename>) is used.
1526 When the pager is off, the pager is not used. When the pager
1527 is on, the pager is used only when appropriate, i.e. the
1528 output is to a terminal and will not fit on the screen.
1529 (<application>psql</> does not do a perfect job of estimating
1530 when to use the pager.) <literal>\pset pager</> turns the
1531 pager on and off. Pager can also be set to <literal>always</>,
1532 which causes the pager to be always used.
1540 Illustrations on how these different formats look can be seen in
1541 the <xref linkend="APP-PSQL-examples"
1542 endterm="APP-PSQL-examples-title"> section.
1547 There are various shortcut commands for <command>\pset</command>. See
1548 <command>\a</command>, <command>\C</command>, <command>\H</command>,
1549 <command>\t</command>, <command>\T</command>, and <command>\x</command>.
1555 It is an error to call <command>\pset</command> without
1556 arguments. In the future this call might show the current status
1557 of all printing options.
1566 <term><literal>\q</literal></term>
1569 Quits the <application>psql</application> program.
1576 <term><literal>\qecho <replaceable class="parameter">text</replaceable> [ ... ] </literal></term>
1579 This command is identical to <command>\echo</command> except
1580 that all output will be written to the query output channel, as
1581 set by <command>\o</command>.
1588 <term><literal>\r</literal></term>
1591 Resets (clears) the query buffer.
1598 <term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term>
1601 Print or save the command line history to <replaceable
1602 class="parameter">filename</replaceable>. If <replaceable
1603 class="parameter">filename</replaceable> is omitted, the history
1604 is written to the standard output. This option is only available
1605 if <application>psql</application> is configured to use the
1606 <acronym>GNU</acronym> history library.
1611 In the current version, it is no longer necessary to save the
1612 command history, since that will be done automatically on
1613 program termination. The history is also loaded automatically
1614 every time <application>psql</application> starts up.
1622 <term><literal>\set [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ] ] ]</literal></term>
1626 Sets the internal variable <replaceable
1627 class="parameter">name</replaceable> to <replaceable
1628 class="parameter">value</replaceable> or, if more than one value
1629 is given, to the concatenation of all of them. If no second
1630 argument is given, the variable is just set with no value. To
1631 unset a variable, use the <command>\unset</command> command.
1635 Valid variable names can contain characters, digits, and
1636 underscores. See the section <xref
1637 linkend="APP-PSQL-variables"
1638 endterm="APP-PSQL-variables-title"> below for details.
1639 Variable names are case-sensitive.
1643 Although you are welcome to set any variable to anything you
1644 want, <application>psql</application> treats several variables
1645 as special. They are documented in the section about variables.
1650 This command is totally separate from the <acronym>SQL</acronym>
1651 command <xref linkend="SQL-SET" endterm="SQL-SET-title">.
1659 <term><literal>\t</literal></term>
1662 Toggles the display of output column name headings and row count
1663 footer. This command is equivalent to <literal>\pset
1664 tuples_only</literal> and is provided for convenience.
1671 <term><literal>\T <replaceable class="parameter">table_options</replaceable></literal></term>
1674 Allows you to specify attributes to be placed within the
1675 <sgmltag>table</sgmltag> tag in <acronym>HTML</acronym> tabular
1676 output mode. This command is equivalent to <literal>\pset
1677 tableattr <replaceable
1678 class="parameter">table_options</replaceable></literal>.
1685 <term><literal>\timing</literal></term>
1688 Toggles a display of how long each SQL statement takes, in milliseconds.
1695 <term><literal>\w</literal> {<replaceable class="parameter">filename</replaceable> | <replaceable class="parameter">|command</replaceable>}</term>
1698 Outputs the current query buffer to the file <replaceable
1699 class="parameter">filename</replaceable> or pipes it to the Unix
1700 command <replaceable class="parameter">command</replaceable>.
1707 <term><literal>\x</literal></term>
1710 Toggles extended table formatting mode. As such it is equivalent to
1711 <literal>\pset expanded</literal>.
1718 <term><literal>\z [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
1721 Produces a list of all available tables, views and sequences with their
1722 associated access privileges.
1723 If a <replaceable class="parameter">pattern</replaceable> is
1724 specified, only tables,views and sequences whose names match the pattern are listed.
1728 The commands <xref linkend="SQL-GRANT"> and
1729 <xref linkend="SQL-REVOKE">
1730 are used to set access privileges. See <xref linkend="SQL-GRANT">
1731 for more information.
1735 This is an alias for <command>\dp</command> (<quote>display
1736 privileges</quote>).
1743 <term><literal>\! [ <replaceable class="parameter">command</replaceable> ]</literal></term>
1746 Escapes to a separate Unix shell or executes the Unix command
1747 <replaceable class="parameter">command</replaceable>. The
1748 arguments are not further interpreted, the shell will see them
1756 <term><literal>\?</literal></term>
1759 Shows help information about the backslash commands.
1768 The various <literal>\d</> commands accept a <replaceable
1769 class="parameter">pattern</replaceable> parameter to specify the
1770 object name(s) to be displayed. <literal>*</> means <quote>any
1771 sequence of characters</> and <literal>?</> means <quote>any single
1772 character</>. (This notation is comparable to Unix shell file name
1773 patterns.) Advanced users can also use regular-expression
1774 notations such as character classes, for example <literal>[0-9]</>
1775 to match <quote>any digit</>. To make any of these
1776 pattern-matching characters be interpreted literally, surround it
1781 A pattern that contains an (unquoted) dot is interpreted as a schema
1782 name pattern followed by an object name pattern. For example,
1783 <literal>\dt foo*.bar*</> displays all tables in schemas whose name
1784 starts with <literal>foo</> and whose table name
1785 starts with <literal>bar</>. If no dot appears, then the pattern
1786 matches only objects that are visible in the current schema search path.
1790 Whenever the <replaceable class="parameter">pattern</replaceable> parameter
1791 is omitted completely, the <literal>\d</> commands display all objects
1792 that are visible in the current schema search path. To see all objects
1793 in the database, use the pattern <literal>*.*</>.
1798 <title>Advanced features</title>
1800 <refsect3 id="APP-PSQL-variables">
1801 <title id="APP-PSQL-variables-title">Variables</title>
1804 <application>psql</application> provides variable substitution
1805 features similar to common Unix command shells.
1806 Variables are simply name/value pairs, where the value
1807 can be any string of any length. To set variables, use the
1808 <application>psql</application> meta-command
1809 <command>\set</command>:
1811 testdb=> <userinput>\set foo bar</userinput>
1813 sets the variable <literal>foo</literal> to the value
1814 <literal>bar</literal>. To retrieve the content of the variable, precede
1815 the name with a colon and use it as the argument of any slash
1818 testdb=> <userinput>\echo :foo</userinput>
1825 The arguments of <command>\set</command> are subject to the same
1826 substitution rules as with other commands. Thus you can construct
1827 interesting references such as <literal>\set :foo
1828 'something'</literal> and get <quote>soft links</quote> or
1829 <quote>variable variables</quote> of <productname>Perl</productname>
1830 or <productname><acronym>PHP</acronym></productname> fame,
1831 respectively. Unfortunately (or fortunately?), there is no way to do
1832 anything useful with these constructs. On the other hand,
1833 <literal>\set bar :foo</literal> is a perfectly valid way to copy a
1839 If you call <command>\set</command> without a second argument, the
1840 variable is set, with an empty string as value. To unset (or delete) a
1841 variable, use the command <command>\unset</command>.
1845 <application>psql</application>'s internal variable names can
1846 consist of letters, numbers, and underscores in any order and any
1847 number of them. A number of these variables are treated specially
1848 by <application>psql</application>. They indicate certain option
1849 settings that can be changed at run time by altering the value of
1850 the variable or represent some state of the application. Although
1851 you can use these variables for any other purpose, this is not
1852 recommended, as the program behavior might grow really strange
1853 really quickly. By convention, all specially treated variables
1854 consist of all upper-case letters (and possibly numbers and
1855 underscores). To ensure maximum compatibility in the future, avoid
1856 using such variable names for your own purposes. A list of all specially
1857 treated variables follows.
1863 <primary>autocommit</primary>
1864 <secondary>psql</secondary>
1866 <term><varname>AUTOCOMMIT</varname></term>
1869 When <literal>on</> (the default), each SQL command is automatically
1870 committed upon successful completion. To postpone commit in this
1871 mode, you must enter a <command>BEGIN</> or <command>START
1872 TRANSACTION</> SQL command. When <literal>off</> or unset, SQL
1873 commands are not committed until you explicitly issue
1874 <command>COMMIT</> or <command>END</>. The autocommit-off
1875 mode works by issuing an implicit <command>BEGIN</> for you, just
1876 before any command that is not already in a transaction block and
1877 is not itself a <command>BEGIN</> or other transaction-control
1883 In autocommit-off mode, you must explicitly abandon any failed
1884 transaction by entering <command>ABORT</> or <command>ROLLBACK</>.
1885 Also keep in mind that if you exit the session
1886 without committing, your work will be lost.
1892 The autocommit-on mode is <productname>PostgreSQL</>'s traditional
1893 behavior, but autocommit-off is closer to the SQL spec. If you
1894 prefer autocommit-off, you may wish to set it in the system-wide
1895 <filename>psqlrc</filename> or your
1896 <filename>.psqlrc</filename> file.
1903 <term><varname>DBNAME</varname></term>
1906 The name of the database you are currently connected to. This is
1907 set every time you connect to a database (including program
1908 start-up), but can be unset.
1914 <term><varname>ECHO</varname></term>
1917 If set to <literal>all</literal>, all lines
1918 entered or from a script are written to the standard output
1919 before they are parsed or executed. To select this behavior on program
1920 start-up, use the switch <option>-a</option>. If set to
1921 <literal>queries</literal>,
1922 <application>psql</application> merely prints all queries as
1923 they are sent to the server. The switch for this is
1924 <option>-e</option>.
1930 <term><varname>ECHO_HIDDEN</varname></term>
1933 When this variable is set and a backslash command queries the
1934 database, the query is first shown. This way you can study the
1935 <productname>PostgreSQL</productname> internals and provide
1936 similar functionality in your own programs. (To select this behavior
1937 on program start-up, use the switch <option>-E</option>.) If you set
1938 the variable to the value <literal>noexec</literal>, the queries are
1939 just shown but are not actually sent to the server and executed.
1945 <term><varname>ENCODING</varname></term>
1948 The current client character set encoding.
1954 <term><varname>HISTCONTROL</varname></term>
1957 If this variable is set to <literal>ignorespace</literal>,
1958 lines which begin with a space are not entered into the history
1959 list. If set to a value of <literal>ignoredups</literal>, lines
1960 matching the previous history line are not entered. A value of
1961 <literal>ignoreboth</literal> combines the two options. If
1962 unset, or if set to any other value than those above, all lines
1963 read in interactive mode are saved on the history list.
1967 This feature was shamelessly plagiarized from
1968 <application>Bash</application>.
1975 <term><varname>HISTSIZE</varname></term>
1978 The number of commands to store in the command history. The
1979 default value is 500.
1983 This feature was shamelessly plagiarized from
1984 <application>Bash</application>.
1991 <term><varname>HOST</varname></term>
1994 The database server host you are currently connected to. This is
1995 set every time you connect to a database (including program
1996 start-up), but can be unset.
2002 <term><varname>IGNOREEOF</varname></term>
2005 If unset, sending an <acronym>EOF</> character (usually
2006 <keycombo action="simul"><keycap>Control</><keycap>D</></>)
2007 to an interactive session of <application>psql</application>
2008 will terminate the application. If set to a numeric value,
2009 that many <acronym>EOF</> characters are ignored before the
2010 application terminates. If the variable is set but has no
2011 numeric value, the default is 10.
2015 This feature was shamelessly plagiarized from
2016 <application>Bash</application>.
2023 <term><varname>LASTOID</varname></term>
2026 The value of the last affected OID, as returned from an
2027 <command>INSERT</command> or <command>lo_insert</command>
2028 command. This variable is only guaranteed to be valid until
2029 after the result of the next <acronym>SQL</acronym> command has
2036 <term><varname>ON_ERROR_STOP</varname></term>
2039 By default, if non-interactive scripts encounter an error, such
2040 as a malformed <acronym>SQL</acronym> command or internal
2041 meta-command, processing continues. This has been the
2042 traditional behavior of <application>psql</application> but it
2043 is sometimes not desirable. If this variable is set, script
2044 processing will immediately terminate. If the script was called
2045 from another script it will terminate in the same fashion. If
2046 the outermost script was not called from an interactive
2047 <application>psql</application> session but rather using the
2048 <option>-f</option> option, <application>psql</application> will
2049 return error code 3, to distinguish this case from fatal error
2050 conditions (error code 1).
2056 <term><varname>PORT</varname></term>
2059 The database server port to which you are currently connected.
2060 This is set every time you connect to a database (including
2061 program start-up), but can be unset.
2067 <term><varname>PROMPT1</varname></term>
2068 <term><varname>PROMPT2</varname></term>
2069 <term><varname>PROMPT3</varname></term>
2072 These specify what the prompts <application>psql</application>
2073 issues should look like. See <xref
2074 linkend="APP-PSQL-prompting"
2075 endterm="APP-PSQL-prompting-title"> below.
2081 <term><varname>QUIET</varname></term>
2084 This variable is equivalent to the command line option
2085 <option>-q</option>. It is probably not too useful in
2092 <term><varname>SINGLELINE</varname></term>
2095 This variable is equivalent to the command line option
2096 <option>-S</option>.
2102 <term><varname>SINGLESTEP</varname></term>
2105 This variable is equivalent to the command line option
2106 <option>-s</option>.
2112 <term><varname>USER</varname></term>
2115 The database user you are currently connected as. This is set
2116 every time you connect to a database (including program
2117 start-up), but can be unset.
2123 <term><varname>VERBOSITY</varname></term>
2126 This variable can be set to the values <literal>default</>,
2127 <literal>verbose</>, or <literal>terse</> to control the verbosity
2138 <title><acronym>SQL</acronym> Interpolation</title>
2141 An additional useful feature of <application>psql</application>
2142 variables is that you can substitute (<quote>interpolate</quote>)
2143 them into regular <acronym>SQL</acronym> statements. The syntax for
2144 this is again to prepend the variable name with a colon
2145 (<literal>:</literal>).
2147 testdb=> <userinput>\set foo 'my_table'</userinput>
2148 testdb=> <userinput>SELECT * FROM :foo;</userinput>
2150 would then query the table <literal>my_table</literal>. The value of
2151 the variable is copied literally, so it can even contain unbalanced
2152 quotes or backslash commands. You must make sure that it makes sense
2153 where you put it. Variable interpolation will not be performed into
2154 quoted <acronym>SQL</acronym> entities.
2158 A popular application of this facility is to refer to the last
2159 inserted <acronym>OID</acronym> in subsequent statements to build a
2160 foreign key scenario. Another possible use of this mechanism is to
2161 copy the contents of a file into a table column. First load the file into a
2162 variable and then proceed as above.
2164 testdb=> <userinput>\set content '\'' `cat my_file.txt` '\''</userinput>
2165 testdb=> <userinput>INSERT INTO my_table VALUES (:content);</userinput>
2167 One possible problem with this approach is that <filename>my_file.txt</filename>
2168 might contain single quotes. These need to be escaped so that
2169 they don't cause a syntax error when the second line is processed. This
2170 could be done with the program <command>sed</command>:
2172 testdb=> <userinput>\set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\''</userinput>
2174 Observe the correct number of backslashes (6)! It works
2175 this way: After <application>psql</application> has parsed this
2176 line, it passes <literal>sed -e "s/'/\\\'/g" < my_file.txt</literal>
2177 to the shell. The shell will do its own thing inside the double
2178 quotes and execute <command>sed</command> with the arguments
2179 <literal>-e</literal> and <literal>s/'/\\'/g</literal>. When
2180 <command>sed</command> parses this it will replace the two
2181 backslashes with a single one and then do the substitution. Perhaps
2182 at one point you thought it was great that all Unix commands use the
2183 same escape character. And this is ignoring the fact that you might
2184 have to escape all backslashes as well because
2185 <acronym>SQL</acronym> text constants are also subject to certain
2186 interpretations. In that case you might be better off preparing the
2191 Since colons may legally appear in SQL commands, the following rule
2192 applies: the character sequence
2193 <quote>:name</quote> is not changed unless <quote>name</> is the name
2194 of a variable that is currently set. In any case you can escape
2195 a colon with a backslash to protect it from substitution. (The
2196 colon syntax for variables is standard <acronym>SQL</acronym> for
2197 embedded query languages, such as <application>ECPG</application>.
2198 The colon syntax for array slices and type casts are
2199 <productname>PostgreSQL</productname> extensions, hence the
2205 <refsect3 id="APP-PSQL-prompting">
2206 <title id="APP-PSQL-prompting-title">Prompting</title>
2209 The prompts <application>psql</application> issues can be customized
2210 to your preference. The three variables <varname>PROMPT1</varname>,
2211 <varname>PROMPT2</varname>, and <varname>PROMPT3</varname> contain strings
2212 and special escape sequences that describe the appearance of the
2213 prompt. Prompt 1 is the normal prompt that is issued when
2214 <application>psql</application> requests a new command. Prompt 2 is
2215 issued when more input is expected during command input because the
2216 command was not terminated with a semicolon or a quote was not closed.
2217 Prompt 3 is issued when you run an <acronym>SQL</acronym>
2218 <command>COPY</command> command and you are expected to type in the
2219 row values on the terminal.
2223 The value of the selected prompt variable is printed literally,
2224 except where a percent sign (<literal>%</literal>) is encountered.
2225 Depending on the next character, certain other text is substituted
2226 instead. Defined substitutions are:
2230 <term><literal>%M</literal></term>
2233 The full host name (with domain name) of the database server,
2234 or <literal>[local]</literal> if the connection is over a Unix
2236 <literal>[local:<replaceable>/dir/name</replaceable>]</literal
2237 >, if the Unix domain socket is not at the compiled in default
2244 <term><literal>%m</literal></term>
2247 The host name of the database server, truncated at the
2248 first dot, or <literal>[local]</literal> if the connection is
2249 over a Unix domain socket.
2255 <term><literal>%></literal></term>
2256 <listitem><para>The port number at which the database server is listening.</para></listitem>
2260 <term><literal>%n</literal></term>
2263 The database session user name. (The expansion of this
2264 value might change during a database session as the result
2265 of the command <command>SET SESSION
2266 AUTHORIZATION</command>.)
2272 <term><literal>%/</literal></term>
2273 <listitem><para>The name of the current database.</para></listitem>
2277 <term><literal>%~</literal></term>
2278 <listitem><para>Like <literal>%/</literal>, but the output is <literal>~</literal>
2279 (tilde) if the database is your default database.</para></listitem>
2283 <term><literal>%#</literal></term>
2286 If the session user is a database superuser, then a
2287 <literal>#</literal>, otherwise a <literal>></literal>.
2288 (The expansion of this value might change during a database
2289 session as the result of the command <command>SET SESSION
2290 AUTHORIZATION</command>.)
2296 <term><literal>%R</literal></term>
2299 In prompt 1 normally <literal>=</literal>, but <literal>^</literal> if
2300 in single-line mode, and <literal>!</literal> if the session is
2301 disconnected from the database (which can happen if
2302 <command>\connect</command> fails). In prompt 2 the sequence is
2303 replaced by <literal>-</literal>, <literal>*</literal>, a single quote,
2304 or a double quote, depending on whether
2305 <application>psql</application> expects more input because the
2306 command wasn't terminated yet, because you are inside a
2307 <literal>/* ... */</literal> comment, or because you are inside
2308 a quote. In prompt 3 the sequence doesn't produce anything.
2314 <term><literal>%x</literal></term>
2317 Transaction status: an empty string when not in a transaction
2318 block, or <literal>*</> when in a transaction block, or
2319 <literal>!</> when in a failed transaction block, or <literal>?</>
2320 when the transaction state is indeterminate (for example, because
2321 there is no connection).
2327 <term><literal>%</literal><replaceable class="parameter">digits</replaceable></term>
2330 The character with the indicated numeric code is substituted.
2331 If <replaceable class="parameter">digits</replaceable> starts
2332 with <literal>0x</literal> the rest of the characters are
2333 interpreted as hexadecimal; otherwise if the first digit is
2334 <literal>0</literal> the digits are interpreted as octal;
2335 otherwise the digits are read as a decimal number.
2341 <term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term>
2344 The value of the <application>psql</application> variable
2345 <replaceable class="parameter">name</replaceable>. See the
2346 section <xref linkend="APP-PSQL-variables"
2347 endterm="APP-PSQL-variables-title"> for details.
2353 <term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term>
2356 The output of <replaceable
2357 class="parameter">command</replaceable>, similar to ordinary
2358 <quote>back-tick</quote> substitution.
2364 <term><literal>%[</literal> ... <literal>%]</literal></term>
2367 Prompts may contain terminal control characters which, for
2368 example, change the color, background, or style of the prompt
2369 text, or change the title of the terminal window. In order for
2370 the line editing features of readline to work properly, these
2371 non-printing control characters must be designated as invisible
2372 by surrounding them with <literal>%[</literal> and
2373 <literal>%]</literal>. Multiple pairs of these may occur within
2374 the prompt. For example,
2376 testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%#%] '
2378 results in a boldfaced (<literal>1;</literal>) yellow-on-black
2379 (<literal>33;40</literal>) prompt on VT100-compatible, color-capable
2387 To insert a percent sign into your prompt, write
2388 <literal>%%</literal>. The default prompts are
2389 <literal>'%/%R%# '</literal> for prompts 1 and 2, and
2390 <literal>'>> '</literal> for prompt 3.
2395 This feature was shamelessly plagiarized from
2396 <application>tcsh</application>.
2403 <title>Command-Line Editing</title>
2406 <application>psql</application> supports the <application>Readline</application>
2407 library for convenient line editing and retrieval. The command
2408 history is stored in a file named <filename>.psql_history</filename>
2409 in your home directory and is reloaded when
2410 <application>psql</application> starts up. Tab-completion is also
2411 supported, although the completion logic makes no claim to be an
2412 <acronym>SQL</acronym> parser. If for some reason you do not like the tab completion, you
2413 can turn if off by putting this in a file named
2414 <filename>.inputrc</filename> in your home directory:
2417 set disable-completion on
2420 (This is not a <application>psql</application> but a
2421 <application>Readline</application> feature. Read its documentation
2422 for further details.)
2430 <title>Environment</title>
2434 <term><envar>HOME</envar></term>
2438 Directory for initialization file (<filename>.psqlrc</filename>)
2439 and command history file (<filename>.psql_history</filename>).
2445 <term><envar>PAGER</envar></term>
2449 If the query results do not fit on the screen, they are piped
2450 through this command. Typical values are
2451 <literal>more</literal> or <literal>less</literal>. The default
2452 is platform-dependent. The use of the pager can be disabled by
2453 using the <command>\pset</command> command.
2459 <term><envar>PGDATABASE</envar></term>
2463 Default database to connect to
2469 <term><envar>PGHOST</envar></term>
2470 <term><envar>PGPORT</envar></term>
2471 <term><envar>PGUSER</envar></term>
2475 Default connection parameters
2481 <term><envar>PSQL_EDITOR</envar></term>
2482 <term><envar>EDITOR</envar></term>
2483 <term><envar>VISUAL</envar></term>
2487 Editor used by the <command>\e</command> command. The variables
2488 are examined in the order listed; the first that is set is used.
2494 <term><envar>SHELL</envar></term>
2498 Command executed by the <command>\!</command> command.
2504 <term><envar>TMPDIR</envar></term>
2508 Directory for storing temporary files. The default is
2509 <filename>/tmp</filename>.
2518 <title>Files</title>
2523 Before starting up, <application>psql</application> attempts to
2524 read and execute commands from the the system-wide
2525 <filename>psqlrc</filename> file and the
2526 <filename>$HOME/.psqlrc</filename> file in the user's home
2527 directory. See <filename><replaceable>PREFIX</>/share/psqlrc.sample</>
2528 for information on setting up the system-wide file. It could be used
2529 to set up the client or the server to taste (using the <command>\set
2530 </command> and <command>SET</command> commands).
2536 The command-line history is stored in the file
2537 <filename>$HOME/.psql_history</filename>.
2545 <title>Notes</title>
2550 In an earlier life <application>psql</application> allowed the
2551 first argument of a single-letter backslash command to start
2552 directly after the command, without intervening whitespace. For
2553 compatibility this is still supported to some extent,
2554 but were are not going to explain the details here as this use is
2555 discouraged. If you get strange messages, keep this in mind.
2558 testdb=> <userinput>\foo</userinput>
2559 Field separator is "oo".
2561 which is perhaps not what one would expect.
2567 <application>psql</application> only works smoothly with servers
2568 of the same version. That does not mean other combinations will
2569 fail outright, but subtle and not-so-subtle problems might come
2570 up. Backslash commands are particularly likely to fail if the
2571 server is of a different version.
2579 <refsect1 id="APP-PSQL-examples">
2580 <title id="APP-PSQL-examples-title">Examples</title>
2583 The first example shows how to spread a command over several lines of
2584 input. Notice the changing prompt:
2586 testdb=> <userinput>CREATE TABLE my_table (</userinput>
2587 testdb(> <userinput> first integer not null default 0,</userinput>
2588 testdb(> <userinput> second text</userinput>
2589 testdb-> <userinput>);</userinput>
2592 Now look at the table definition again:
2594 testdb=> <userinput>\d my_table</userinput>
2596 Attribute | Type | Modifier
2597 -----------+---------+--------------------
2598 first | integer | not null default 0
2602 Now we change the prompt to something more interesting:
2604 testdb=> <userinput>\set PROMPT1 '%n@%m %~%R%# '</userinput>
2605 peter@localhost testdb=>
2607 Let's assume you have filled the table with data and want to take a
2610 peter@localhost testdb=> SELECT * FROM my_table;
2620 You can make this table look differently by using the
2621 <command>\pset</command> command:
2623 peter@localhost testdb=> <userinput>\pset border 2</userinput>
2625 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2636 peter@localhost testdb=> <userinput>\pset border 0</userinput>
2638 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2647 peter@localhost testdb=> <userinput>\pset border 1</userinput>
2649 peter@localhost testdb=> <userinput>\pset format unaligned</userinput>
2650 Output format is unaligned.
2651 peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput>
2652 Field separator is ",".
2653 peter@localhost testdb=> <userinput>\pset tuples_only</userinput>
2654 Showing only tuples.
2655 peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
2661 Alternatively, use the short commands:
2663 peter@localhost testdb=> <userinput>\a \t \x</userinput>
2664 Output format is aligned.
2666 Expanded display is on.
2667 peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput>
2687 <!-- Keep this comment at the end of the file
2692 sgml-minimize-attributes:nil
2693 sgml-always-quote-attributes:t
2696 sgml-parent-document:nil
2697 sgml-default-dtd-file:"../reference.ced"
2698 sgml-exposed-tags:nil
2699 sgml-local-catalogs:"/usr/lib/sgml/catalog"
2700 sgml-local-ecat-files:nil