| <!-- |
| doc/src/sgml/ref/psql-ref.sgml |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="APP-PSQL"> |
| <refmeta> |
| <refentrytitle id="app-psql-title"><application>psql</application></refentrytitle> |
| <manvolnum>1</manvolnum> |
| <refmiscinfo>Application</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname><application>psql</application></refname> |
| <refpurpose> |
| <productname>PostgreSQL</productname> interactive terminal |
| </refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="app-psql"> |
| <primary>psql</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <cmdsynopsis> |
| <command>psql</command> |
| <arg rep="repeat"><replaceable class="parameter">option</replaceable></arg> |
| <arg><replaceable class="parameter">dbname</replaceable> |
| <arg><replaceable class="parameter">username</replaceable></arg></arg> |
| </cmdsynopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <application>psql</application> is a terminal-based front-end to |
| <productname>PostgreSQL</productname>. It enables you to type in |
| queries interactively, issue them to |
| <productname>PostgreSQL</productname>, and see the query results. |
| Alternatively, input can be from a file. In addition, it provides a |
| number of meta-commands and various shell-like features to |
| facilitate writing scripts and automating a wide variety of tasks. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="R1-APP-PSQL-3"> |
| <title>Options</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><option>-a</></term> |
| <term><option>--echo-all</></term> |
| <listitem> |
| <para> |
| Print all input lines to standard output as they are read. This is more |
| useful for script processing than interactive mode. This is |
| equivalent to setting the variable <varname>ECHO</varname> to |
| <literal>all</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-A</></term> |
| <term><option>--no-align</></term> |
| <listitem> |
| <para> |
| Switches to unaligned output mode. (The default output mode is |
| otherwise aligned.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-c <replaceable class="parameter">command</replaceable></></term> |
| <term><option>--command <replaceable class="parameter">command</replaceable></></term> |
| <listitem> |
| <para> |
| Specifies that <application>psql</application> is to execute one |
| command string, <replaceable class="parameter">command</replaceable>, |
| and then exit. This is useful in shell scripts. Start-up files |
| (<filename>psqlrc</filename> and <filename>~/.psqlrc</filename>) are |
| ignored with this option. |
| </para> |
| <para> |
| <replaceable class="parameter">command</replaceable> must be either |
| a command string that is completely parsable by the server (i.e., |
| it contains no <application>psql</application>-specific features), |
| or a single backslash command. Thus you cannot mix |
| <acronym>SQL</acronym> and <application>psql</application> |
| meta-commands with this option. To achieve that, you could |
| pipe the string into <application>psql</application>, like |
| this: <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>. |
| (<literal>\\</> is the separator meta-command.) |
| </para> |
| <para> |
| If the command string contains multiple SQL commands, they are |
| processed in a single transaction, unless there are explicit |
| <command>BEGIN</>/<command>COMMIT</> commands included in the |
| string to divide it into multiple transactions. This is |
| different from the behavior when the same string is fed to |
| <application>psql</application>'s standard input. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-d <replaceable class="parameter">dbname</replaceable></></term> |
| <term><option>--dbname <replaceable class="parameter">dbname</replaceable></></term> |
| <listitem> |
| <para> |
| Specifies the name of the database to connect to. This is |
| equivalent to specifying <replaceable |
| class="parameter">dbname</replaceable> as the first non-option |
| argument on the command line. |
| </para> |
| <para> |
| If this parameter contains an <symbol>=</symbol> sign, it is treated as a |
| <parameter>conninfo</parameter> string. See <xref linkend="libpq-connect"> for more information. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-e</></term> |
| <term><option>--echo-queries</></term> |
| <listitem> |
| <para> |
| Copy all SQL commands sent to the server to standard output as well. |
| This is equivalent |
| to setting the variable <varname>ECHO</varname> to |
| <literal>queries</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-E</></term> |
| <term><option>--echo-hidden</></term> |
| <listitem> |
| <para> |
| Echo the actual queries generated by <command>\d</command> and other backslash |
| commands. You can use this to study <application>psql</application>'s |
| internal operations. This is equivalent to |
| setting the variable <varname>ECHO_HIDDEN</varname> from within |
| <application>psql</application>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-f <replaceable class="parameter">filename</replaceable></></term> |
| <term><option>--file <replaceable class="parameter">filename</replaceable></></term> |
| <listitem> |
| <para> |
| Use the file <replaceable class="parameter">filename</replaceable> |
| as the source of commands instead of reading commands interactively. |
| After the file is processed, <application>psql</application> |
| terminates. This is in many ways equivalent to the internal |
| command <command>\i</command>. |
| </para> |
| |
| <para> |
| If <replaceable>filename</replaceable> is <literal>-</literal> |
| (hyphen), then standard input is read. |
| </para> |
| |
| <para> |
| Using this option is subtly different from writing <literal>psql |
| < <replaceable |
| class="parameter">filename</replaceable></literal>. In general, |
| both will do what you expect, but using <literal>-f</literal> |
| enables some nice features such as error messages with line |
| numbers. There is also a slight chance that using this option will |
| reduce the start-up overhead. On the other hand, the variant using |
| the shell's input redirection is (in theory) guaranteed to yield |
| exactly the same output you would have received had you entered |
| everything by hand. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-F <replaceable class="parameter">separator</replaceable></></term> |
| <term><option>--field-separator <replaceable class="parameter">separator</replaceable></></term> |
| <listitem> |
| <para> |
| Use <replaceable class="parameter">separator</replaceable> as the |
| field separator for unaligned output. This is equivalent to |
| <command>\pset fieldsep</command> or <command>\f</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-h <replaceable class="parameter">hostname</replaceable></></term> |
| <term><option>--host <replaceable class="parameter">hostname</replaceable></></term> |
| <listitem> |
| <para> |
| Specifies the host name of the machine on which the |
| server is running. If the value begins |
| with a slash, it is used as the directory for the Unix-domain |
| socket. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-H</></term> |
| <term><option>--html</></term> |
| <listitem> |
| <para> |
| Turn on <acronym>HTML</acronym> tabular output. This is |
| equivalent to <literal>\pset format html</literal> or the |
| <command>\H</command> command. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-l</></term> |
| <term><option>--list</></term> |
| <listitem> |
| <para> |
| List all available databases, then exit. Other non-connection |
| options are ignored. This is similar to the internal command |
| <command>\list</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-L <replaceable class="parameter">filename</replaceable></></term> |
| <term><option>--log-file <replaceable class="parameter">filename</replaceable></></term> |
| <listitem> |
| <para> |
| Write all query output into file <replaceable |
| class="parameter">filename</replaceable>, in addition to the |
| normal output destination. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-n</></term> |
| <term><option>--no-readline</></term> |
| <listitem> |
| <para> |
| Do not use readline for line editing and do not use the history. |
| This can be useful to turn off tab expansion when cutting and pasting. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-o <replaceable class="parameter">filename</replaceable></></term> |
| <term><option>--output <replaceable class="parameter">filename</replaceable></></term> |
| <listitem> |
| <para> |
| Put all query output into file <replaceable |
| class="parameter">filename</replaceable>. This is equivalent to |
| the command <command>\o</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-p <replaceable class="parameter">port</replaceable></></term> |
| <term><option>--port <replaceable class="parameter">port</replaceable></></term> |
| <listitem> |
| <para> |
| Specifies the TCP port or the local Unix-domain |
| socket file extension on which the server is listening for |
| connections. Defaults to the value of the <envar>PGPORT</envar> |
| environment variable or, if not set, to the port specified at |
| compile time, usually 5432. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-P <replaceable class="parameter">assignment</replaceable></></term> |
| <term><option>--pset <replaceable class="parameter">assignment</replaceable></></term> |
| <listitem> |
| <para> |
| Specifies printing options, in the style of |
| <command>\pset</command>. Note that here you |
| have to separate name and value with an equal sign instead of a |
| space. For example, to set the output format to LaTeX, you could write |
| <literal>-P format=latex</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-q</></term> |
| <term><option>--quiet</></term> |
| <listitem> |
| <para> |
| Specifies that <application>psql</application> should do its work |
| quietly. By default, it prints welcome messages and various |
| informational output. If this option is used, none of this |
| happens. This is useful with the <option>-c</option> option. |
| Within <application>psql</application> you can also set the |
| <varname>QUIET</varname> variable to achieve the same effect. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-R <replaceable class="parameter">separator</replaceable></></term> |
| <term><option>--record-separator <replaceable class="parameter">separator</replaceable></></term> |
| <listitem> |
| <para> |
| Use <replaceable class="parameter">separator</replaceable> as the |
| record separator for unaligned output. This is equivalent to the |
| <command>\pset recordsep</command> command. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-s</></term> |
| <term><option>--single-step</></term> |
| <listitem> |
| <para> |
| Run in single-step mode. That means the user is prompted before |
| each command is sent to the server, with the option to cancel |
| execution as well. Use this to debug scripts. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-S</></term> |
| <term><option>--single-line</></term> |
| <listitem> |
| <para> |
| Runs in single-line mode where a newline terminates an SQL command, as a |
| semicolon does. |
| </para> |
| |
| <note> |
| <para> |
| This mode is provided for those who insist on it, but you are not |
| necessarily encouraged to use it. In particular, if you mix |
| <acronym>SQL</acronym> and meta-commands on a line the order of |
| execution might not always be clear to the inexperienced user. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-t</></term> |
| <term><option>--tuples-only</></term> |
| <listitem> |
| <para> |
| Turn off printing of column names and result row count footers, |
| etc. This is equivalent to the <command>\t</command> command. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-T <replaceable class="parameter">table_options</replaceable></></term> |
| <term><option>--table-attr <replaceable class="parameter">table_options</replaceable></></term> |
| <listitem> |
| <para> |
| Specifies options to be placed within the |
| <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag. See |
| <command>\pset</command> for details. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-U <replaceable class="parameter">username</replaceable></></term> |
| <term><option>--username <replaceable class="parameter">username</replaceable></></term> |
| <listitem> |
| <para> |
| Connect to the database as the user <replaceable |
| class="parameter">username</replaceable> instead of the default. |
| (You must have permission to do so, of course.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-v <replaceable class="parameter">assignment</replaceable></></term> |
| <term><option>--set <replaceable class="parameter">assignment</replaceable></></term> |
| <term><option>--variable <replaceable class="parameter">assignment</replaceable></></term> |
| <listitem> |
| <para> |
| Perform a variable assignment, like the <command>\set</command> |
| internal command. Note that you must separate name and value, if |
| any, by an equal sign on the command line. To unset a variable, |
| leave off the equal sign. To just set a variable without a value, |
| use the equal sign but leave off the value. These assignments are |
| done during a very early stage of start-up, so variables reserved |
| for internal purposes might get overwritten later. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-V</></term> |
| <term><option>--version</></term> |
| <listitem> |
| <para> |
| Print the <application>psql</application> version and exit. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-w</></term> |
| <term><option>--no-password</></term> |
| <listitem> |
| <para> |
| Never issue a password prompt. If the server requires password |
| authentication and a password is not available by other means |
| such as a <filename>.pgpass</filename> file, the connection |
| attempt will fail. This option can be useful in batch jobs and |
| scripts where no user is present to enter a password. |
| </para> |
| |
| <para> |
| Note that this option will remain set for the entire session, |
| and so it affects uses of the meta-command |
| <command>\connect</command> as well as the initial connection attempt. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-W</></term> |
| <term><option>--password</></term> |
| <listitem> |
| <para> |
| Force <application>psql</application> to prompt for a |
| password before connecting to a database. |
| </para> |
| |
| <para> |
| This option is never essential, since <application>psql</application> |
| will automatically prompt for a password if the server demands |
| password authentication. However, <application>psql</application> |
| will waste a connection attempt finding out that the server wants a |
| password. In some cases it is worth typing <option>-W</> to avoid |
| the extra connection attempt. |
| </para> |
| |
| <para> |
| Note that this option will remain set for the entire session, |
| and so it affects uses of the meta-command |
| <command>\connect</command> as well as the initial connection attempt. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-x</></term> |
| <term><option>--expanded</></term> |
| <listitem> |
| <para> |
| Turn on the expanded table formatting mode. This is equivalent to the |
| <command>\x</command> command. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-X,</></term> |
| <term><option>--no-psqlrc</></term> |
| <listitem> |
| <para> |
| Do not read the start-up file (neither the system-wide |
| <filename>psqlrc</filename> file nor the user's |
| <filename>~/.psqlrc</filename> file). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-1</option></term> |
| <term><option>--single-transaction</option></term> |
| <listitem> |
| <para> |
| When <application>psql</application> executes a script with the |
| <option>-f</> option, adding this option wraps |
| <command>BEGIN</>/<command>COMMIT</> around the script to execute it |
| as a single transaction. This ensures that either all the commands |
| complete successfully, or no changes are applied. |
| </para> |
| |
| <para> |
| If the script itself uses <command>BEGIN</>, <command>COMMIT</>, |
| or <command>ROLLBACK</>, this option will not have the desired |
| effects. |
| Also, if the script contains any command that cannot be executed |
| inside a transaction block, specifying this option will cause that |
| command (and hence the whole transaction) to fail. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-?</></term> |
| <term><option>--help</></term> |
| <listitem> |
| <para> |
| Show help about <application>psql</application> command line |
| arguments, and exit. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>Exit Status</title> |
| |
| <para> |
| <application>psql</application> returns 0 to the shell if it |
| finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, |
| file not found), 2 if the connection to the server went bad |
| and the session was not interactive, and 3 if an error occurred in a |
| script and the variable <varname>ON_ERROR_STOP</varname> was set. |
| </para> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>Usage</title> |
| |
| <refsect2 id="R2-APP-PSQL-connecting"> |
| <title>Connecting To A Database</title> |
| |
| <para> |
| <application>psql</application> is a regular |
| <productname>PostgreSQL</productname> client application. In order |
| to connect to a database you need to know the name of your target |
| database, the host name and port number of the server, and what user |
| name you want to connect as. <application>psql</application> can be |
| told about those parameters via command line options, namely |
| <option>-d</option>, <option>-h</option>, <option>-p</option>, and |
| <option>-U</option> respectively. If an argument is found that does |
| not belong to any option it will be interpreted as the database name |
| (or the user name, if the database name is already given). Not all |
| of these options are required; there are useful defaults. If you omit the host |
| name, <application>psql</> will connect via a Unix-domain socket |
| to a server on the local host, or via TCP/IP to <literal>localhost</> on |
| machines that don't have Unix-domain sockets. The default port number is |
| determined at compile time. |
| Since the database server uses the same default, you will not have |
| to specify the port in most cases. The default user name is your |
| Unix user name, as is the default database name. Note that you cannot |
| just connect to any database under any user name. Your database |
| administrator should have informed you about your access rights. |
| </para> |
| |
| <para> |
| When the defaults aren't quite right, you can save yourself |
| some typing by setting the environment variables |
| <envar>PGDATABASE</envar>, <envar>PGHOST</envar>, |
| <envar>PGPORT</envar> and/or <envar>PGUSER</envar> to appropriate |
| values. (For additional environment variables, see <xref |
| linkend="libpq-envars">.) It is also convenient to have a |
| <filename>~/.pgpass</> file to avoid regularly having to type in |
| passwords. See <xref linkend="libpq-pgpass"> for more information. |
| </para> |
| |
| <para> |
| An alternative way to specify connection parameters is in a |
| <parameter>conninfo</parameter> string, which is used instead of a |
| database name. This mechanism give you very wide control over the |
| connection. For example: |
| <programlisting> |
| $ <userinput>psql "service=myservice sslmode=require"</userinput> |
| </programlisting> |
| This way you can also use LDAP for connection parameter lookup as |
| described in <xref linkend="libpq-ldap">. |
| See <xref linkend="libpq-connect"> for more information on all the |
| available connection options. |
| </para> |
| |
| <para> |
| If the connection could not be made for any reason (e.g., insufficient |
| privileges, server is not running on the targeted host, etc.), |
| <application>psql</application> will return an error and terminate. |
| </para> |
| </refsect2> |
| |
| <refsect2 id="R2-APP-PSQL-4"> |
| <title>Entering SQL Commands</title> |
| |
| <para> |
| In normal operation, <application>psql</application> provides a |
| prompt with the name of the database to which |
| <application>psql</application> is currently connected, followed by |
| the string <literal>=></literal>. For example: |
| <programlisting> |
| $ <userinput>psql testdb</userinput> |
| psql (&version;) |
| Type "help" for help. |
| |
| testdb=> |
| </programlisting> |
| </para> |
| |
| <para> |
| At the prompt, the user can type in <acronym>SQL</acronym> commands. |
| Ordinarily, input lines are sent to the server when a |
| command-terminating semicolon is reached. An end of line does not |
| terminate a command. Thus commands can be spread over several lines for |
| clarity. If the command was sent and executed without error, the results |
| of the command are displayed on the screen. |
| </para> |
| |
| <para> |
| Whenever a command is executed, <application>psql</application> also polls |
| for asynchronous notification events generated by |
| <xref linkend="SQL-LISTEN" endterm="SQL-LISTEN-title"> and |
| <xref linkend="SQL-NOTIFY" endterm="SQL-NOTIFY-title">. |
| </para> |
| </refsect2> |
| |
| <refsect2 id="APP-PSQL-meta-commands"> |
| <title>Meta-Commands</title> |
| |
| <para> |
| Anything you enter in <application>psql</application> that begins |
| with an unquoted backslash is a <application>psql</application> |
| meta-command that is processed by <application>psql</application> |
| itself. These commands make |
| <application>psql</application> more useful for administration or |
| scripting. Meta-commands are often called slash or backslash commands. |
| </para> |
| |
| <para> |
| The format of a <application>psql</application> command is the backslash, |
| followed immediately by a command verb, then any arguments. The arguments |
| are separated from the command verb and each other by any number of |
| whitespace characters. |
| </para> |
| |
| <para> |
| To include whitespace into an argument you can quote it with a |
| single quote. To include a single quote into such an argument, |
| use two single quotes. Anything contained in single quotes is |
| furthermore subject to C-like substitutions for |
| <literal>\n</literal> (new line), <literal>\t</literal> (tab), |
| <literal>\</literal><replaceable>digits</replaceable> (octal), and |
| <literal>\x</literal><replaceable>digits</replaceable> (hexadecimal). |
| </para> |
| |
| <para> |
| If an unquoted argument begins with a colon (<literal>:</literal>), |
| it is taken as a <application>psql</> variable and the value of the |
| variable is used as the argument instead. If the variable name is |
| surrounded by single quotes (e.g. <literal>:'var'</literal>), it |
| will be escaped as an SQL literal and the result will be used as |
| the argument. If the variable name is surrounded by double quotes, |
| it will be escaped as an SQL identifier and the result will be used |
| as the argument. |
| </para> |
| |
| <para> |
| Arguments that are enclosed in backquotes (<literal>`</literal>) |
| are taken as a command line that is passed to the shell. The |
| output of the command (with any trailing newline removed) is taken |
| as the argument value. The above escape sequences also apply in |
| backquotes. |
| </para> |
| |
| <para> |
| Some commands take an <acronym>SQL</acronym> identifier (such as a |
| table name) as argument. These arguments follow the syntax rules |
| of <acronym>SQL</acronym>: Unquoted letters are forced to |
| lowercase, while double quotes (<literal>"</>) protect letters |
| from case conversion and allow incorporation of whitespace into |
| the identifier. Within double quotes, paired double quotes reduce |
| to a single double quote in the resulting name. For example, |
| <literal>FOO"BAR"BAZ</> is interpreted as <literal>fooBARbaz</>, |
| and <literal>"A weird"" name"</> becomes <literal>A weird" |
| name</>. |
| </para> |
| |
| <para> |
| Parsing for arguments stops at the end of the line, or when another |
| unquoted backslash is found. An unquoted backslash |
| is taken as the beginning of a new meta-command. The special |
| sequence <literal>\\</literal> (two backslashes) marks the end of |
| arguments and continues parsing <acronym>SQL</acronym> commands, if |
| any. That way <acronym>SQL</acronym> and |
| <application>psql</application> commands can be freely mixed on a |
| line. But in any case, the arguments of a meta-command cannot |
| continue beyond the end of the line. |
| </para> |
| |
| <para> |
| The following meta-commands are defined: |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>\a</literal></term> |
| <listitem> |
| <para> |
| If the current table output format is unaligned, it is switched to aligned. |
| If it is not unaligned, it is set to unaligned. This command is |
| kept for backwards compatibility. See <command>\pset</command> for a |
| more general solution. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\cd [ <replaceable>directory</replaceable> ]</literal></term> |
| <listitem> |
| <para> |
| Changes the current working directory to |
| <replaceable>directory</replaceable>. Without argument, changes |
| to the current user's home directory. |
| </para> |
| |
| <tip> |
| <para> |
| To print your current working directory, use <literal>\! pwd</literal>. |
| </para> |
| </tip> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\C [ <replaceable class="parameter">title</replaceable> ]</literal></term> |
| <listitem> |
| <para> |
| Sets the title of any tables being printed as the result of a |
| query or unset any such title. This command is equivalent to |
| <literal>\pset title <replaceable |
| class="parameter">title</replaceable></literal>. (The name of |
| this command derives from <quote>caption</quote>, as it was |
| previously only used to set the caption in an |
| <acronym>HTML</acronym> table.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\connect</literal> (or <literal>\c</literal>) <literal>[ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] [ <replaceable class="parameter">host</replaceable> ] [ <replaceable class="parameter">port</replaceable> ] ]</literal></term> |
| <listitem> |
| <para> |
| Establishes a new connection to a <productname>PostgreSQL</> |
| server. If the new connection is successfully made, the |
| previous connection is closed. If any of <replaceable |
| class="parameter">dbname</replaceable>, <replaceable |
| class="parameter">username</replaceable>, <replaceable |
| class="parameter">host</replaceable> or <replaceable |
| class="parameter">port</replaceable> are omitted or specified |
| as <literal>-</literal>, the value of that parameter from the |
| previous connection is used. If there is no previous |
| connection, the <application>libpq</application> default for |
| the parameter's value is used. |
| </para> |
| |
| <para> |
| If the connection attempt failed (wrong user name, access |
| denied, etc.), the previous connection will only be kept if |
| <application>psql</application> is in interactive mode. When |
| executing a non-interactive script, processing will |
| immediately stop with an error. This distinction was chosen as |
| a user convenience against typos on the one hand, and a safety |
| mechanism that scripts are not accidentally acting on the |
| wrong database on the other hand. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\conninfo</literal></term> |
| <listitem> |
| <para> |
| Outputs information about the current database connection. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) } |
| { <literal>from</literal> | <literal>to</literal> } |
| { <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout } |
| [ with ] |
| [ binary ] |
| [ oids ] |
| [ delimiter [ as ] '<replaceable class="parameter">character</replaceable>' ] |
| [ null [ as ] '<replaceable class="parameter">string</replaceable>' ] |
| [ csv |
| [ header ] |
| [ quote [ as ] '<replaceable class="parameter">character</replaceable>' ] |
| [ escape [ as ] '<replaceable class="parameter">character</replaceable>' ] |
| [ force quote <replaceable class="parameter">column_list</replaceable> | * ] |
| [ force not null <replaceable class="parameter">column_list</replaceable> ] ]</literal> |
| </term> |
| |
| <listitem> |
| <para> |
| Performs a frontend (client) copy. This is an operation that |
| runs an <acronym>SQL</acronym> <xref linkend="SQL-COPY" |
| endterm="SQL-COPY-title"> command, but instead of the server |
| reading or writing the specified file, |
| <application>psql</application> reads or writes the file and |
| routes the data between the server and the local file system. |
| This means that file accessibility and privileges are those of |
| the local user, not the server, and no SQL superuser |
| privileges are required. |
| </para> |
| |
| <para> |
| The syntax of the command is similar to that of the |
| <acronym>SQL</acronym> <xref linkend="sql-copy" |
| endterm="sql-copy-title"> command. Note that, because of this, |
| special parsing rules apply to the <command>\copy</command> |
| command. In particular, the variable substitution rules and |
| backslash escapes do not apply. |
| </para> |
| |
| <para> |
| <literal>\copy ... from stdin | to stdout</literal> |
| reads/writes based on the command input and output respectively. |
| All rows are read from the same source that issued the command, |
| continuing until <literal>\.</literal> is read or the stream |
| reaches <acronym>EOF</>. Output is sent to the same place as |
| command output. To read/write from |
| <application>psql</application>'s standard input or output, use |
| <literal>pstdin</> or <literal>pstdout</>. This option is useful |
| for populating tables in-line within a SQL script file. |
| </para> |
| |
| <tip> |
| <para> |
| This operation is not as efficient as the <acronym>SQL</acronym> |
| <command>COPY</command> command because all data must pass |
| through the client/server connection. For large |
| amounts of data the <acronym>SQL</acronym> command might be preferable. |
| </para> |
| </tip> |
| |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\copyright</literal></term> |
| <listitem> |
| <para> |
| Shows the copyright and distribution terms of |
| <productname>PostgreSQL</productname>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\d[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| |
| <listitem> |
| <para> |
| For each relation (table, view, index, or sequence) matching the |
| <replaceable class="parameter">pattern</replaceable>, show all |
| columns, their types, the tablespace (if not the default) and any |
| special attributes such as <literal>NOT NULL</literal> or defaults. |
| Associated indexes, constraints, rules, and triggers are |
| also shown. |
| (<quote>Matching the pattern</> is defined in |
| <xref linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title"> |
| below.) |
| </para> |
| |
| <para> |
| The command form <literal>\d+</literal> is identical, except that |
| more information is displayed: any comments associated with the |
| columns of the table are shown, as is the presence of OIDs in the |
| table, and the view definition if the relation is a view. |
| </para> |
| |
| <para> |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system |
| objects. |
| </para> |
| |
| <note> |
| <para> |
| If <command>\d</command> is used without a |
| <replaceable class="parameter">pattern</replaceable> argument, it is |
| equivalent to <command>\dtvsxr</command> which will show a list of |
| all visible tables, views, sequences, and external tables. This is purely a convenience |
| measure. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\da[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| |
| <listitem> |
| <para> |
| Lists aggregate functions, together with their |
| return type and the data types they operate on. If <replaceable |
| class="parameter">pattern</replaceable> |
| is specified, only aggregates whose names match the pattern are shown. |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system |
| objects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\db[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| |
| <listitem> |
| <para> |
| Lists tablespaces. If <replaceable |
| class="parameter">pattern</replaceable> |
| is specified, only tablespaces whose names match the pattern are shown. |
| If <literal>+</literal> is appended to the command name, each object |
| is listed with its associated permissions. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dc[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists conversions between character-set encodings. |
| If <replaceable class="parameter">pattern</replaceable> |
| is specified, only conversions whose names match the pattern are |
| listed. |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system |
| objects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dC [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists type casts. |
| If <replaceable class="parameter">pattern</replaceable> |
| is specified, only casts whose source or target types match the |
| pattern are listed. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dd[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Shows the descriptions of objects matching the <replaceable |
| class="parameter">pattern</replaceable>, or of all visible objects if |
| no argument is given. But in either case, only objects that have |
| a description are listed. |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system |
| objects. |
| <quote>Object</quote> covers aggregates, functions, operators, |
| types, relations (tables, views, indexes, sequences), large |
| objects, rules, and triggers. For example: |
| <programlisting> |
| => <userinput>\dd version</userinput> |
| Object descriptions |
| Schema | Name | Object | Description |
| ------------+---------+----------+--------------------------- |
| pg_catalog | version | function | PostgreSQL version string |
| (1 row) |
| </programlisting> |
| </para> |
| |
| <para> |
| Descriptions for objects can be created with the <xref |
| linkend="sql-comment" endterm="sql-comment-title"> |
| <acronym>SQL</acronym> command. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\ddp [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists default access privilege settings. An entry is shown for |
| each role (and schema, if applicable) for which the default |
| privilege settings have been changed from the built-in defaults. |
| If <replaceable class="parameter">pattern</replaceable> is |
| specified, only entries whose role name or schema name matches |
| the pattern are listed. |
| </para> |
| |
| <para> |
| The <xref linkend="sql-alterdefaultprivileges"> command is used to set |
| default access privileges. The meaning of the |
| privilege display is explained under |
| <xref linkend="sql-grant">. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dD[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists domains. If <replaceable |
| class="parameter">pattern</replaceable> |
| is specified, only domains whose names match the pattern are shown. |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system |
| objects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\des[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists foreign servers (mnemonic: <quote>external |
| servers</quote>). |
| If <replaceable class="parameter">pattern</replaceable> is |
| specified, only those servers whose name matches the pattern |
| are listed. If the form <literal>\des+</literal> is used, a |
| full description of each server is shown, including the |
| server's ACL, type, version, and options. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\deu[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists user mappings (mnemonic: <quote>external |
| users</quote>). |
| If <replaceable class="parameter">pattern</replaceable> is |
| specified, only those mappings whose user names match the |
| pattern are listed. If the form <literal>\deu+</literal> is |
| used, additional information about each mapping is shown. |
| </para> |
| |
| <caution> |
| <para> |
| <literal>\deu+</literal> might also display the user name and |
| password of the remote user, so care should be taken not to |
| disclose them. |
| </para> |
| </caution> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dew[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists foreign-data wrappers (mnemonic: <quote>external |
| wrappers</quote>). |
| If <replaceable class="parameter">pattern</replaceable> is |
| specified, only those foreign-data wrappers whose name matches |
| the pattern are listed. If the form <literal>\dew+</literal> |
| is used, the ACL and options of the foreign-data wrapper are |
| also shown. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\df[antwS+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| |
| <listitem> |
| <para> |
| Lists functions, together with their arguments, return types, and |
| function types, which are classified as <quote>agg</> (aggregate), |
| <quote>normal</>, <quote>trigger</>, or <quote>window</>. |
| To display only functions |
| of specific type(s), add the corresponding letters <literal>a</>, |
| <literal>n</>, <literal>t</>, or <literal>w</> to the command. |
| If <replaceable |
| class="parameter">pattern</replaceable> is specified, only |
| functions whose names match the pattern are shown. If the |
| form <literal>\df+</literal> is used, additional information |
| about each function, including volatility, language, source |
| code and description, is shown. By default, only user-created |
| objects are shown; supply a pattern or the <literal>S</literal> |
| modifier to include system objects. |
| </para> |
| |
| <tip> |
| <para> |
| To look up functions taking arguments or returning values of a specific |
| type, use your pager's search capability to scroll through the |
| <literal>\df</> output. |
| </para> |
| </tip> |
| |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\dF[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists text search configurations. |
| If <replaceable class="parameter">pattern</replaceable> is specified, |
| only configurations whose names match the pattern are shown. |
| If the form <literal>\dF+</literal> is used, a full description of |
| each configuration is shown, including the underlying text search |
| parser and the dictionary list for each parser token type. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\dFd[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists text search dictionaries. |
| If <replaceable class="parameter">pattern</replaceable> is specified, |
| only dictionaries whose names match the pattern are shown. |
| If the form <literal>\dFd+</literal> is used, additional information |
| is shown about each selected dictionary, including the underlying |
| text search template and the option values. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\dFp[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists text search parsers. |
| If <replaceable class="parameter">pattern</replaceable> is specified, |
| only parsers whose names match the pattern are shown. |
| If the form <literal>\dFp+</literal> is used, a full description of |
| each parser is shown, including the underlying functions and the |
| list of recognized token types. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\dFt[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists text search templates. |
| If <replaceable class="parameter">pattern</replaceable> is specified, |
| only templates whose names match the pattern are shown. |
| If the form <literal>\dFt+</literal> is used, additional information |
| is shown about each template, including the underlying function names. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dg[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists database roles. If <replaceable |
| class="parameter">pattern</replaceable> is specified, only |
| those roles whose names match the pattern are listed. |
| (This command is now effectively the same as <literal>\du</literal>). |
| If the form <literal>\dg+</literal> is used, additional information |
| is shown about each role, including the comment for each role. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\di[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <term><literal>\ds[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <term><literal>\dt[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <term><literal>\dv[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <term><literal>\dP[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <term><literal>\dx[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <term><literal>\dr[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| |
| <listitem> |
| <para> |
| In this group of commands, the letters |
| <literal>i</literal>, <literal>s</literal>, |
| <literal>t</literal>, <literal>v</literal>, |
| <literal>P</literal>, <literal>x</literal>, and <literal>r</literal> |
| stand for index, sequence, table, view, parent table, external table, and foreign table respectively. |
| You can specify any or all of |
| these letters, in any order, to obtain a listing of objects |
| of these types. For example, <literal>\dit</> lists indexes |
| and tables. If <literal>+</literal> is |
| appended to the command name, each object is listed with its |
| physical size on disk and its associated description, if any. |
| If <replaceable class="parameter">pattern</replaceable> is |
| specified, only objects whose names match the pattern are listed. |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system |
| objects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dl</literal></term> |
| <listitem> |
| <para> |
| This is an alias for <command>\lo_list</command>, which shows a |
| list of large objects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dn[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| |
| <listitem> |
| <para> |
| Lists schemas (namespaces). If <replaceable |
| class="parameter">pattern</replaceable> |
| is specified, only schemas whose names match the pattern are listed. |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system objects. |
| If <literal>+</literal> is appended to the command name, each object |
| is listed with its associated permissions and description, if any. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\do[S] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists operators with their operand and return types. |
| If <replaceable class="parameter">pattern</replaceable> is |
| specified, only operators whose names match the pattern are listed. |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system |
| objects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\dp [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists tables, views and sequences with their |
| associated access privileges. |
| If <replaceable class="parameter">pattern</replaceable> is |
| specified, only tables, views and sequences whose names match the |
| pattern are listed. |
| </para> |
| |
| <para> |
| The <xref linkend="sql-grant" endterm="sql-grant-title"> and |
| <xref linkend="sql-revoke" endterm="sql-revoke-title"> |
| commands are used to set access privileges. The meaning of the |
| privilege display is explained under |
| <xref linkend="sql-grant">. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\drds [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">role-pattern</replaceable></link> [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">database-pattern</replaceable></link> ] ]</literal></term> |
| <listitem> |
| <para> |
| Lists defined configuration settings. These settings can be |
| role-specific, database-specific, or both. |
| <replaceable>role-pattern</replaceable> and |
| <replaceable>database-pattern</replaceable> are used to select |
| specific roles and databases to list, respectively. If omitted, or if |
| <literal>*</> is specified, all settings are listed, including those |
| not role-specific or database-specific, respectively. |
| </para> |
| |
| <para> |
| The <xref linkend="sql-alterrole"> and |
| <xref linkend="sql-alterdatabase"> |
| commands are used to define per-role and per-database configuration |
| settings. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\dT[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists data types. |
| If <replaceable class="parameter">pattern</replaceable> is |
| specified, only types whose names match the pattern are listed. |
| If <literal>+</literal> is appended to the command name, each type is |
| listed with its internal name and size, as well as its allowed values |
| if it is an <type>enum</> type. |
| By default, only user-created objects are shown; supply a |
| pattern or the <literal>S</literal> modifier to include system |
| objects. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\du[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists database roles. If <replaceable |
| class="parameter">pattern</replaceable> is specified, only |
| those roles whose names match the pattern are listed. |
| If the form <literal>\du+</literal> is used, additional information |
| is shown about each role, including the comment for each role. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\edit</> (or <literal>\e</>) <literal> <optional> <replaceable class="parameter">filename</> </optional> <optional> <replaceable class="parameter">line_number</> </optional> </literal></term> |
| |
| <listitem> |
| <para> |
| If <replaceable class="parameter">filename</replaceable> is |
| specified, the file is edited; after the editor exits, its |
| content is copied back to the query buffer. If no <replaceable |
| class="parameter">filename</replaceable> is given, the current query |
| buffer is copied to a temporary file which is then edited in the same |
| fashion. |
| </para> |
| |
| <para> |
| The new query buffer is then re-parsed according to the normal |
| rules of <application>psql</application>, where the whole buffer |
| is treated as a single line. (Thus you cannot make scripts this |
| way. Use <command>\i</command> for that.) This means that |
| if the query ends with (or contains) a semicolon, it is |
| immediately executed. Otherwise it will merely wait in the |
| query buffer; type semicolon or <literal>\g</> to send it, or |
| <literal>\r</> to cancel. |
| </para> |
| |
| <tip> |
| <para> |
| <application>psql</application> checks the environment |
| variables <envar>PSQL_EDITOR</envar>, <envar>EDITOR</envar>, and |
| <envar>VISUAL</envar> (in that order) for an editor to use. If |
| all of them are unset, <filename>vi</filename> is used on Unix |
| systems, <filename>notepad.exe</filename> on Windows systems. |
| </para> |
| </tip> |
| |
| <para> |
| If a line number is specified, <application>psql</application> will |
| position the cursor on the specified line of the file or query buffer. |
| This feature requires the <varname>EDITOR_LINENUMBER_SWITCH</varname> |
| variable to be set, so that <application>psql</application> knows how |
| to specify the line number to the editor. Note that if a single |
| all-digits argument is given, <application>psql</application> assumes |
| it is a line number not a file name. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\ef <optional> <replaceable class="parameter">function_description</> <optional> <replaceable class="parameter">line_number</> </optional> </optional> </literal></term> |
| |
| <listitem> |
| <para> |
| This command fetches and edits the definition of the named function, |
| in the form of a <command>CREATE OR REPLACE FUNCTION</> command. |
| Editing is done in the same way as for <literal>\edit</>. |
| After the editor exits, the updated command waits in the query buffer; |
| type semicolon or <literal>\g</> to send it, or <literal>\r</> |
| to cancel. |
| </para> |
| |
| <para> |
| The target function can be specified by name alone, or by name |
| and arguments, for example <literal>foo(integer, text)</>. |
| The argument types must be given if there is more |
| than one function of the same name. |
| </para> |
| |
| <para> |
| If no function is specified, a blank <command>CREATE FUNCTION</> |
| template is presented for editing. |
| </para> |
| |
| <para> |
| If a line number is specified, <application>psql</application> will |
| position the cursor on the specified line of the function body |
| (note that the function body typically does not begin on the |
| first line of the file). |
| This feature requires the <varname>EDITOR_LINENUMBER_SWITCH</varname> |
| variable to be set, so that <application>psql</application> knows how |
| to specify the line number to the editor. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\echo <replaceable class="parameter">text</replaceable> [ ... ]</literal></term> |
| <listitem> |
| <para> |
| Prints the arguments to the standard output, separated by one |
| space and followed by a newline. This can be useful to |
| intersperse information in the output of scripts. For example: |
| <programlisting> |
| => <userinput>\echo `date`</userinput> |
| Tue Oct 26 21:40:57 CEST 1999 |
| </programlisting> |
| If the first argument is an unquoted <literal>-n</literal> the trailing |
| newline is not written. |
| </para> |
| |
| <tip> |
| <para> |
| If you use the <command>\o</command> command to redirect your |
| query output you might wish to use <command>\qecho</command> |
| instead of this command. |
| </para> |
| </tip> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\encoding [ <replaceable class="parameter">encoding</replaceable> ]</literal></term> |
| |
| <listitem> |
| <para> |
| Sets the client character set encoding. Without an argument, this command |
| shows the current encoding. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\f [ <replaceable class="parameter">string</replaceable> ]</literal></term> |
| |
| <listitem> |
| <para> |
| Sets the field separator for unaligned query output. The default |
| is the vertical bar (<literal>|</literal>). See also |
| <command>\pset</command> for a generic way of setting output |
| options. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\g</literal> [ { <replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable> } ]</term> |
| |
| <listitem> |
| <para> |
| Sends the current query input buffer to the server and |
| optionally stores the query's output in <replaceable |
| class="parameter">filename</replaceable> or pipes the output |
| into a separate Unix shell executing <replaceable |
| class="parameter">command</replaceable>. A bare |
| <literal>\g</literal> is virtually equivalent to a semicolon. A |
| <literal>\g</literal> with argument is a <quote>one-shot</quote> |
| alternative to the <command>\o</command> command. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\help</literal> (or <literal>\h</literal>) <literal>[ <replaceable class="parameter">command</replaceable> ]</literal></term> |
| <listitem> |
| <para> |
| Gives syntax help on the specified <acronym>SQL</acronym> |
| command. If <replaceable class="parameter">command</replaceable> |
| is not specified, then <application>psql</application> will list |
| all the commands for which syntax help is available. If |
| <replaceable class="parameter">command</replaceable> is an |
| asterisk (<literal>*</literal>), then syntax help on all |
| <acronym>SQL</acronym> commands is shown. |
| </para> |
| |
| <note> |
| <para> |
| To simplify typing, commands that consists of several words do |
| not have to be quoted. Thus it is fine to type <userinput>\help |
| alter table</userinput>. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\H</literal></term> |
| <listitem> |
| <para> |
| Turns on <acronym>HTML</acronym> query output format. If the |
| <acronym>HTML</acronym> format is already on, it is switched |
| back to the default aligned text format. This command is for |
| compatibility and convenience, but see <command>\pset</command> |
| about setting other output options. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\i <replaceable class="parameter">filename</replaceable></literal></term> |
| <listitem> |
| <para> |
| Reads input from the file <replaceable |
| class="parameter">filename</replaceable> and executes it as |
| though it had been typed on the keyboard. |
| </para> |
| <note> |
| <para> |
| If you want to see the lines on the screen as they are read you |
| must set the variable <varname>ECHO</varname> to |
| <literal>all</literal>. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\l</literal> (or <literal>\list</literal>)</term> |
| <term><literal>\l+</literal> (or <literal>\list+</literal>)</term> |
| <listitem> |
| <para> |
| List the names, owners, character set encodings, and access privileges |
| of all the databases in the server. |
| If <literal>+</literal> is appended to the command name, database |
| sizes, default tablespaces, and descriptions are also displayed. |
| (Size information is only available for databases that the current |
| user can connect to.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\lo_export <replaceable class="parameter">loid</replaceable> <replaceable class="parameter">filename</replaceable></literal></term> |
| |
| <listitem> |
| <para> |
| Reads the large object with <acronym>OID</acronym> <replaceable |
| class="parameter">loid</replaceable> from the database and |
| writes it to <replaceable |
| class="parameter">filename</replaceable>. Note that this is |
| subtly different from the server function |
| <function>lo_export</function>, which acts with the permissions |
| of the user that the database server runs as and on the server's |
| file system. |
| </para> |
| <tip> |
| <para> |
| Use <command>\lo_list</command> to find out the large object's |
| <acronym>OID</acronym>. |
| </para> |
| </tip> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\lo_import <replaceable class="parameter">filename</replaceable> [ <replaceable class="parameter">comment</replaceable> ]</literal></term> |
| |
| <listitem> |
| <para> |
| Stores the file into a <productname>PostgreSQL</productname> |
| large object. Optionally, it associates the given |
| comment with the object. Example: |
| <programlisting> |
| foo=> <userinput>\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'</userinput> |
| lo_import 152801 |
| </programlisting> |
| The response indicates that the large object received object |
| ID 152801, which can be used to access the newly-created large |
| object in the future. For the sake of readability, it is |
| recommended to always associate a human-readable comment with |
| every object. Both OIDs and comments can be viewed with the |
| <command>\lo_list</command> command. |
| </para> |
| |
| <para> |
| Note that this command is subtly different from the server-side |
| <function>lo_import</function> because it acts as the local user |
| on the local file system, rather than the server's user and file |
| system. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\lo_list</literal></term> |
| <listitem> |
| <para> |
| Shows a list of all <productname>PostgreSQL</productname> |
| large objects currently stored in the database, |
| along with any comments provided for them. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\lo_unlink <replaceable class="parameter">loid</replaceable></literal></term> |
| |
| <listitem> |
| <para> |
| Deletes the large object with <acronym>OID</acronym> |
| <replaceable class="parameter">loid</replaceable> from the |
| database. |
| </para> |
| |
| <tip> |
| <para> |
| Use <command>\lo_list</command> to find out the large object's |
| <acronym>OID</acronym>. |
| </para> |
| </tip> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\o</literal> [ {<replaceable class="parameter">filename</replaceable> | <literal>|</literal><replaceable class="parameter">command</replaceable>} ]</term> |
| |
| <listitem> |
| <para> |
| Saves future query results to the file <replaceable |
| class="parameter">filename</replaceable> or pipes future results |
| into a separate Unix shell to execute <replaceable |
| class="parameter">command</replaceable>. If no arguments are |
| specified, the query output will be reset to the standard output. |
| </para> |
| |
| <para> |
| <quote>Query results</quote> includes all tables, command |
| responses, and notices obtained from the database server, as |
| well as output of various backslash commands that query the |
| database (such as <command>\d</command>), but not error |
| messages. |
| </para> |
| |
| <tip> |
| <para> |
| To intersperse text output in between query results, use |
| <command>\qecho</command>. |
| </para> |
| </tip> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\p</literal></term> |
| <listitem> |
| <para> |
| Print the current query buffer to the standard output. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\password [ <replaceable class="parameter">username</replaceable> ]</literal></term> |
| <listitem> |
| <para> |
| Changes the password of the specified user (by default, the current |
| user). This command prompts for the new password, encrypts it, and |
| sends it to the server as an <command>ALTER ROLE</> command. This |
| makes sure that the new password does not appear in cleartext in the |
| command history, the server log, or elsewhere. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\prompt [ <replaceable class="parameter">text</replaceable> ] <replaceable class="parameter">name</replaceable></literal></term> |
| <listitem> |
| <para> |
| Prompts the user to set variable <replaceable |
| class="parameter">name</>. An optional prompt, <replaceable |
| class="parameter">text</>, can be specified. (For multiword |
| prompts, use single quotes.) |
| </para> |
| |
| <para> |
| By default, <literal>\prompt</> uses the terminal for input and |
| output. However, if the <option>-f</> command line switch is |
| used, <literal>\prompt</> uses standard input and standard output. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>\pset <replaceable class="parameter">option</replaceable> [ <replaceable class="parameter">value</replaceable> ]</literal></term> |
| |
| <listitem> |
| <para> |
| This command sets options affecting the output of query result tables. |
| <replaceable class="parameter">option</replaceable> |
| indicates which option is to be set. The semantics of |
| <replaceable class="parameter">value</replaceable> vary depending |
| on the selected option. For some options, omitting <replaceable |
| class="parameter">value</replaceable> causes the option to be toggled |
| or unset, as described under the particular option. If no such |
| behavior is mentioned, then omitting |
| <replaceable class="parameter">value</replaceable> just results in |
| the current setting being displayed. |
| </para> |
| |
| <para> |
| Adjustable printing options are: |
| <variablelist> |
| <varlistentry> |
| <term><literal>format</literal></term> |
| <listitem> |
| <para> |
| Sets the output format to one of <literal>unaligned</literal>, |
| <literal>aligned</literal>, <literal>wrapped</literal>, |
| <literal>html</literal>, |
| <literal>latex</literal>, or <literal>troff-ms</literal>. |
| Unique abbreviations are allowed. (That would mean one letter |
| is enough.) |
| </para> |
| |
| <para> |
| <literal>unaligned</> format writes all columns of a row on one |
| line, separated by the currently active field separator. This |
| is useful for creating output that might be intended to be read |
| in by other programs (for example, tab-separated or comma-separated |
| format). |
| </para> |
| |
| <para> |
| <literal>aligned</literal> format is the standard, human-readable, |
| nicely formatted text output; this is the default. |
| </para> |
| |
| <para> |
| <literal>wrapped</> format is like <literal>aligned</> but wraps |
| wide data values across lines to make the output fit in the target |
| column width. The target width is determined as described under |
| the <literal>columns</> option. Note that <application>psql</> will |
| not attempt to wrap column header titles; therefore, |
| <literal>wrapped</> format behaves the same as <literal>aligned</> |
| if the total width needed for column headers exceeds the target. |
| </para> |
| |
| <para> |
| The <literal>html</>, <literal>latex</>, and <literal>troff-ms</> |
| formats put out tables that are intended to |
| be included in documents using the respective mark-up |
| language. They are not complete documents! (This might not be |
| so dramatic in <acronym>HTML</acronym>, but in LaTeX you must |
| have a complete document wrapper.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>columns</literal></term> |
| <listitem> |
| <para> |
| Sets the target width for the <literal>wrapped</> format, and also |
| the width limit for determining whether output is wide enough to |
| require the pager. |
| Zero (the default) causes the target width to be controlled by the |
| environment variable <envar>COLUMNS</>, or the detected screen width |
| if <envar>COLUMNS</> is not set. |
| In addition, if <literal>columns</> is zero then the |
| <literal>wrapped</> format only affects screen output. |
| If <literal>columns</> is nonzero then file and pipe output is |
| wrapped to that width as well. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>border</literal></term> |
| <listitem> |
| <para> |
| The <replaceable class="parameter">value</replaceable> must be a |
| number. In general, the higher |
| the number the more borders and lines the tables will have, |
| but this depends on the particular format. In |
| <acronym>HTML</acronym> format, this will translate directly |
| into the <literal>border=...</literal> attribute; in the |
| other formats only values 0 (no border), 1 (internal dividing lines), |
| and 2 (table frame) make sense. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>linestyle</literal></term> |
| <listitem> |
| <para> |
| Sets the border line drawing style to one |
| of <literal>ascii</literal>, <literal>old-ascii</literal> |
| or <literal>unicode</literal>. |
| Unique abbreviations are allowed. (That would mean one |
| letter is enough.) |
| The default setting is <literal>ascii</>. |
| This option only affects the <literal>aligned</> and |
| <literal>wrapped</> output formats. |
| </para> |
| |
| <para> |
| <literal>ascii</literal> style uses plain <acronym>ASCII</acronym> |
| characters. Newlines in data are shown using |
| a <literal>+</literal> symbol in the right-hand margin. |
| When the <literal>wrapped</literal> format wraps data from |
| one line to the next without a newline character, a dot |
| (<literal>.</>) is shown in the right-hand margin of the first line, |
| and again in the left-hand margin of the following line. |
| </para> |
| |
| <para> |
| <literal>old-ascii</literal> style uses plain <acronym>ASCII</> |
| characters, using the formatting style used |
| in <productname>PostgreSQL</productname> 8.4 and earlier. |
| Newlines in data are shown using a <literal>:</literal> |
| symbol in place of the left-hand column separator. |
| When the data is wrapped from one line |
| to the next without a newline character, a <literal>;</> |
| symbol is used in place of the left-hand column separator. |
| </para> |
| |
| <para> |
| <literal>unicode</literal> style uses Unicode box-drawing characters. |
| Newlines in data are shown using a carriage return symbol |
| in the right-hand margin. When the data is wrapped from one line |
| to the next without a newline character, an ellipsis symbol |
| is shown in the right-hand margin of the first line, and |
| again in the left-hand margin of the following line. |
| </para> |
| |
| <para> |
| When the <literal>border</> setting is greater than zero, |
| this option also determines the characters |
| with which the border lines are drawn. |
| Plain <acronym>ASCII</acronym> characters work everywhere, but |
| Unicode characters look nicer on displays that recognize them. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>expanded</literal> (or <literal>x</literal>)</term> |
| <listitem> |
| <para> |
| If <replaceable class="parameter">value</replaceable> is specified |
| it must be either <literal>on</literal> or <literal>off</literal> |
| which will enable or disable expanded mode. If <replaceable |
| class="parameter">value</replaceable> is omitted the command toggles |
| between regular and expanded mode. |
| When expanded mode is enabled, query results |
| are displayed in two columns, with the column name on the left and |
| the data on the right. This mode is useful if the data wouldn't fit |
| on the screen in the normal <quote>horizontal</quote> mode. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>null</literal></term> |
| <listitem> |
| <para> |
| Sets the string to be printed in place of a null value. |
| The default is to print nothing, which can easily be mistaken for |
| an empty string. For example, one might prefer <literal>\pset null |
| '(null)'</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>fieldsep</literal></term> |
| <listitem> |
| <para> |
| Specifies the field separator to be used in unaligned output |
| format. That way one can create, for example, tab- or |
| comma-separated output, which other programs might prefer. To |
| set a tab as field separator, type <literal>\pset fieldsep |
| '\t'</literal>. The default field separator is |
| <literal>'|'</literal> (a vertical bar). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>footer</literal></term> |
| <listitem> |
| <para> |
| If <replaceable class="parameter">value</replaceable> is specified |
| it must be either <literal>on</literal> or <literal>off</literal> |
| which will enable or disable display of the table footer |
| (the <literal>(<replaceable>n</> rows)</literal> count). |
| If <replaceable class="parameter">value</replaceable> is omitted the |
| command toggles footer display on or off. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>numericlocale</literal></term> |
| <listitem> |
| <para> |
| If <replaceable class="parameter">value</replaceable> is specified |
| it must be either <literal>on</literal> or <literal>off</literal> |
| which will enable or disable display of a locale-specific character |
| to separate groups of digits to the left of the decimal marker. |
| If <replaceable class="parameter">value</replaceable> is omitted the |
| command toggles between regular and locale-specific numeric output. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>recordsep</literal></term> |
| <listitem> |
| <para> |
| Specifies the record (line) separator to use in unaligned |
| output format. The default is a newline character. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>tuples_only</literal> (or <literal>t</literal>)</term> |
| <listitem> |
| <para> |
| If <replaceable class="parameter">value</replaceable> is specified |
| it must be either <literal>on</literal> or <literal>off</literal> |
| which will enable or disable tuples-only mode. |
| If <replaceable class="parameter">value</replaceable> is omitted the |
| command toggles between regular and tuples-only output. |
| Regular output includes extra information such |
| as column headers, titles, and various footers. In tuples-only |
| mode, only actual table data is shown. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>title</literal></term> |
| <listitem> |
| <para> |
| Sets the table title for any subsequently printed tables. This |
| can be used to give your output descriptive tags. If no |
| <replaceable class="parameter">value</replaceable> is given, |
| the title is unset. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>tableattr</literal> (or <literal>T</literal>)</term> |
| <listitem> |
| <para> |
| Specifies attributes to be placed inside the |
| <acronym>HTML</acronym> <sgmltag>table</sgmltag> tag in |
| <literal>html</> output format. This |
| could for example be <literal>cellpadding</literal> or |
| <literal>bgcolor</literal>. Note that you probably don't want |
| to specify <literal>border</literal> here, as that is already |
| taken care of by <literal>\pset border</literal>. |
| If no |
| <replaceable class="parameter">value</replaceable> is given, |
| the table attributes are unset. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>pager</literal></term> |
| <listitem> |
| <para> |
| Controls use of a pager program for query and <application>psql</> |
| help output. If the environment variable <envar>PAGER</envar> |
| is set, the output is piped to the specified program. |
| Otherwise a platform-dependent default (such as |
| <filename>more</filename>) is used. |
| </para> |
| |
| <para> |
| When the <literal>pager</> option is <literal>off</>, the pager |
| program is not used. When the <literal>pager</> option is |
| <literal>on</>, the pager is used when appropriate, i.e., when the |
| output is to a terminal and will not fit on the screen. |
| The <literal>pager</> option can also be set to <literal>always</>, |
| which causes the pager to be used for all terminal output regardless |
| of whether it fits on the screen. <literal>\pset pager</> |
| without a <replaceable class="parameter">value</replaceable> |
| toggles pager use on and off. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </para> |
| |
| <para> |
| Illustrations of how these different formats look can be seen in |
| the <xref linkend="APP-PSQL-examples" |
| endterm="APP-PSQL-examples-title"> section. |
| </para> |
| |
| <tip> |
| <para> |
| There are various shortcut commands for <command>\pset</command>. See |
| <command>\a</command>, <command>\C</command>, <command>\H</command>, |
| <command>\t</command>, <command>\T</command>, and <command>\x</command>. |
| </para> |
| </tip> |
| |
| <note> |
| <para> |
| It is an error to call <command>\pset</command> without any |
| arguments. In the future this case might show the current status |
| of all printing options. |
| </para> |
| </note> |
| |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\q</literal></term> |
| <listitem> |
| <para> |
| Quits the <application>psql</application> program. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\qecho <replaceable class="parameter">text</replaceable> [ ... ] </literal></term> |
| <listitem> |
| <para> |
| This command is identical to <command>\echo</command> except |
| that the output will be written to the query output channel, as |
| set by <command>\o</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\r</literal></term> |
| <listitem> |
| <para> |
| Resets (clears) the query buffer. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\s [ <replaceable class="parameter">filename</replaceable> ]</literal></term> |
| <listitem> |
| <para> |
| Print or save the command line history to <replaceable |
| class="parameter">filename</replaceable>. If <replaceable |
| class="parameter">filename</replaceable> is omitted, the history |
| is written to the standard output. This option is only available |
| if <application>psql</application> is configured to use the |
| <acronym>GNU</acronym> <application>Readline</application> library. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\set [ <replaceable class="parameter">name</replaceable> [ <replaceable class="parameter">value</replaceable> [ ... ] ] ]</literal></term> |
| |
| <listitem> |
| <para> |
| Sets the internal variable <replaceable |
| class="parameter">name</replaceable> to <replaceable |
| class="parameter">value</replaceable> or, if more than one value |
| is given, to the concatenation of all of them. If no second |
| argument is given, the variable is just set with no value. To |
| unset a variable, use the <command>\unset</command> command. |
| </para> |
| |
| <para> |
| Valid variable names can contain characters, digits, and |
| underscores. See the section <xref |
| linkend="APP-PSQL-variables" |
| endterm="APP-PSQL-variables-title"> below for details. |
| Variable names are case-sensitive. |
| </para> |
| |
| <para> |
| Although you are welcome to set any variable to anything you |
| want, <application>psql</application> treats several variables |
| as special. They are documented in the section about variables. |
| </para> |
| |
| <note> |
| <para> |
| This command is totally separate from the <acronym>SQL</acronym> |
| command <xref linkend="SQL-SET" endterm="SQL-SET-title">. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\sf[+] <replaceable class="parameter">function_description</> </literal></term> |
| |
| <listitem> |
| <para> |
| This command fetches and shows the definition of the named function, |
| in the form of a <command>CREATE OR REPLACE FUNCTION</> command. |
| The definition is printed to the current query output channel, |
| as set by <command>\o</command>. |
| </para> |
| |
| <para> |
| The target function can be specified by name alone, or by name |
| and arguments, for example <literal>foo(integer, text)</>. |
| The argument types must be given if there is more |
| than one function of the same name. |
| </para> |
| |
| <para> |
| If <literal>+</literal> is appended to the command name, then the |
| output lines are numbered, with the first line of the function body |
| being line 1. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\t</literal></term> |
| <listitem> |
| <para> |
| Toggles the display of output column name headings and row count |
| footer. This command is equivalent to <literal>\pset |
| tuples_only</literal> and is provided for convenience. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\T <replaceable class="parameter">table_options</replaceable></literal></term> |
| <listitem> |
| <para> |
| Specifies attributes to be placed within the |
| <sgmltag>table</sgmltag> tag in <acronym>HTML</acronym> |
| output format. This command is equivalent to <literal>\pset |
| tableattr <replaceable |
| class="parameter">table_options</replaceable></literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\timing [ <replaceable class="parameter">on</replaceable> | <replaceable class="parameter">off</replaceable> ]</literal></term> |
| <listitem> |
| <para> |
| Without parameter, toggles a display of how long each SQL statement |
| takes, in milliseconds. With parameter, sets same. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\w</literal> <replaceable class="parameter">filename</replaceable></term> |
| <term><literal>\w</literal> <literal>|</><replaceable class="parameter">command</replaceable></term> |
| <listitem> |
| <para> |
| Outputs the current query buffer to the file <replaceable |
| class="parameter">filename</replaceable> or pipes it to the Unix |
| command <replaceable class="parameter">command</replaceable>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\x</literal></term> |
| <listitem> |
| <para> |
| Toggles expanded table formatting mode. As such it is equivalent to |
| <literal>\pset expanded</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\z [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term> |
| <listitem> |
| <para> |
| Lists tables, views and sequences with their |
| associated access privileges. |
| If a <replaceable class="parameter">pattern</replaceable> is |
| specified, only tables, views and sequences whose names match the |
| pattern are listed. |
| </para> |
| |
| <para> |
| This is an alias for <command>\dp</command> (<quote>display |
| privileges</quote>). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\! [ <replaceable class="parameter">command</replaceable> ]</literal></term> |
| <listitem> |
| <para> |
| Escapes to a separate Unix shell or executes the Unix command |
| <replaceable class="parameter">command</replaceable>. The |
| arguments are not further interpreted; the shell will see them |
| as-is. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| |
| <varlistentry> |
| <term><literal>\?</literal></term> |
| <listitem> |
| <para> |
| Shows help information about the backslash commands. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </para> |
| |
| <refsect3 id="APP-PSQL-patterns"> |
| <title id="APP-PSQL-patterns-title">Patterns</title> |
| |
| <indexterm> |
| <primary>patterns</primary> |
| <secondary>in psql and pg_dump</secondary> |
| </indexterm> |
| |
| <para> |
| The various <literal>\d</> commands accept a <replaceable |
| class="parameter">pattern</replaceable> parameter to specify the |
| object name(s) to be displayed. In the simplest case, a pattern |
| is just the exact name of the object. The characters within a |
| pattern are normally folded to lower case, just as in SQL names; |
| for example, <literal>\dt FOO</> will display the table named |
| <literal>foo</>. As in SQL names, placing double quotes around |
| a pattern stops folding to lower case. Should you need to include |
| an actual double quote character in a pattern, write it as a pair |
| of double quotes within a double-quote sequence; again this is in |
| accord with the rules for SQL quoted identifiers. For example, |
| <literal>\dt "FOO""BAR"</> will display the table named |
| <literal>FOO"BAR</> (not <literal>foo"bar</>). Unlike the normal |
| rules for SQL names, you can put double quotes around just part |
| of a pattern, for instance <literal>\dt FOO"FOO"BAR</> will display |
| the table named <literal>fooFOObar</>. |
| </para> |
| |
| <para> |
| Whenever the <replaceable class="parameter">pattern</replaceable> parameter |
| is omitted completely, the <literal>\d</> commands display all objects |
| that are visible in the current schema search path — this is |
| equivalent to using <literal>*</> as the pattern. |
| (An object is said to be <firstterm>visible</> if its |
| containing schema is in the search path and no object of the same |
| kind and name appears earlier in the search path. This is equivalent to the |
| statement that the object can be referenced by name without explicit |
| schema qualification.) |
| To see all objects in the database regardless of visibility, |
| use <literal>*.*</> as the pattern. |
| </para> |
| |
| <para> |
| Within a pattern, <literal>*</> matches any sequence of characters |
| (including no characters) and <literal>?</> matches any single character. |
| (This notation is comparable to Unix shell file name patterns.) |
| For example, <literal>\dt int*</> displays tables whose names |
| begin with <literal>int</>. But within double quotes, <literal>*</> |
| and <literal>?</> lose these special meanings and are just matched |
| literally. |
| </para> |
| |
| <para> |
| A pattern that contains a dot (<literal>.</>) is interpreted as a schema |
| name pattern followed by an object name pattern. For example, |
| <literal>\dt foo*.*bar*</> displays all tables whose table name |
| includes <literal>bar</> that are in schemas whose schema name |
| starts with <literal>foo</>. When no dot appears, then the pattern |
| matches only objects that are visible in the current schema search path. |
| Again, a dot within double quotes loses its special meaning and is matched |
| literally. |
| </para> |
| |
| <para> |
| Advanced users can use regular-expression notations such as character |
| classes, for example <literal>[0-9]</> to match any digit. All regular |
| expression special characters work as specified in |
| <xref linkend="functions-posix-regexp">, except for <literal>.</> which |
| is taken as a separator as mentioned above, <literal>*</> which is |
| translated to the regular-expression notation <literal>.*</>, |
| <literal>?</> which is translated to <literal>.</>, and |
| <literal>$</> which is matched literally. You can emulate |
| these pattern characters at need by writing |
| <literal>?</> for <literal>.</>, |
| <literal>(<replaceable class="parameter">R</replaceable>+|)</literal> for |
| <literal><replaceable class="parameter">R</replaceable>*</literal>, or |
| <literal>(<replaceable class="parameter">R</replaceable>|)</literal> for |
| <literal><replaceable class="parameter">R</replaceable>?</literal>. |
| <literal>$</> is not needed as a regular-expression character since |
| the pattern must match the whole name, unlike the usual |
| interpretation of regular expressions (in other words, <literal>$</> |
| is automatically appended to your pattern). Write <literal>*</> at the |
| beginning and/or end if you don't wish the pattern to be anchored. |
| Note that within double quotes, all regular expression special characters |
| lose their special meanings and are matched literally. Also, the regular |
| expression special characters are matched literally in operator name |
| patterns (i.e., the argument of <literal>\do</>). |
| </para> |
| </refsect3> |
| </refsect2> |
| |
| <refsect2> |
| <title>Advanced features</title> |
| |
| <refsect3 id="APP-PSQL-variables"> |
| <title id="APP-PSQL-variables-title">Variables</title> |
| |
| <para> |
| <application>psql</application> provides variable substitution |
| features similar to common Unix command shells. |
| Variables are simply name/value pairs, where the value |
| can be any string of any length. To set variables, use the |
| <application>psql</application> meta-command |
| <command>\set</command>: |
| <programlisting> |
| testdb=> <userinput>\set foo bar</userinput> |
| </programlisting> |
| sets the variable <literal>foo</literal> to the value |
| <literal>bar</literal>. To retrieve the content of the variable, precede |
| the name with a colon and use it as the argument of any slash |
| command: |
| <programlisting> |
| testdb=> <userinput>\echo :foo</userinput> |
| bar |
| </programlisting> |
| </para> |
| |
| <note> |
| <para> |
| The arguments of <command>\set</command> are subject to the same |
| substitution rules as with other commands. Thus you can construct |
| interesting references such as <literal>\set :foo |
| 'something'</literal> and get <quote>soft links</quote> or |
| <quote>variable variables</quote> of <productname>Perl</productname> |
| or <productname><acronym>PHP</acronym></productname> fame, |
| respectively. Unfortunately (or fortunately?), there is no way to do |
| anything useful with these constructs. On the other hand, |
| <literal>\set bar :foo</literal> is a perfectly valid way to copy a |
| variable. |
| </para> |
| </note> |
| |
| <para> |
| If you call <command>\set</command> without a second argument, the |
| variable is set, with an empty string as value. To unset (or delete) a |
| variable, use the command <command>\unset</command>. |
| </para> |
| |
| <para> |
| <application>psql</application>'s internal variable names can |
| consist of letters, numbers, and underscores in any order and any |
| number of them. A number of these variables are treated specially |
| by <application>psql</application>. They indicate certain option |
| settings that can be changed at run time by altering the value of |
| the variable or that represent some state of the application. Although |
| you can use these variables for any other purpose, this is not |
| recommended, as the program behavior might grow really strange |
| really quickly. By convention, all specially treated variables |
| consist of all upper-case letters (and possibly numbers and |
| underscores). To ensure maximum compatibility in the future, avoid |
| using such variable names for your own purposes. A list of all specially |
| treated variables follows. |
| </para> |
| |
| <variablelist> |
| <varlistentry> |
| <indexterm> |
| <primary>autocommit</primary> |
| <secondary>psql</secondary> |
| </indexterm> |
| <term><varname>AUTOCOMMIT</varname></term> |
| <listitem> |
| <para> |
| When <literal>on</> (the default), each SQL command is automatically |
| committed upon successful completion. To postpone commit in this |
| mode, you must enter a <command>BEGIN</> or <command>START |
| TRANSACTION</> SQL command. When <literal>off</> or unset, SQL |
| commands are not committed until you explicitly issue |
| <command>COMMIT</> or <command>END</>. The autocommit-off |
| mode works by issuing an implicit <command>BEGIN</> for you, just |
| before any command that is not already in a transaction block and |
| is not itself a <command>BEGIN</> or other transaction-control |
| command, nor a command that cannot be executed inside a transaction |
| block (such as <command>VACUUM</>). |
| </para> |
| |
| <note> |
| <para> |
| In autocommit-off mode, you must explicitly abandon any failed |
| transaction by entering <command>ABORT</> or <command>ROLLBACK</>. |
| Also keep in mind that if you exit the session |
| without committing, your work will be lost. |
| </para> |
| </note> |
| |
| <note> |
| <para> |
| The autocommit-on mode is <productname>PostgreSQL</>'s traditional |
| behavior, but autocommit-off is closer to the SQL spec. If you |
| prefer autocommit-off, you might wish to set it in the system-wide |
| <filename>psqlrc</filename> file or your |
| <filename>~/.psqlrc</filename> file. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>DBNAME</varname></term> |
| <listitem> |
| <para> |
| The name of the database you are currently connected to. This is |
| set every time you connect to a database (including program |
| start-up), but can be unset. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>ECHO</varname></term> |
| <listitem> |
| <para> |
| If set to <literal>all</literal>, all lines |
| entered from the keyboard or from a script are written to the standard output |
| before they are parsed or executed. To select this behavior on program |
| start-up, use the switch <option>-a</option>. If set to |
| <literal>queries</literal>, |
| <application>psql</application> merely prints all queries as |
| they are sent to the server. The switch for this is |
| <option>-e</option>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>ECHO_HIDDEN</varname></term> |
| <listitem> |
| <para> |
| When this variable is set and a backslash command queries the |
| database, the query is first shown. This way you can study the |
| <productname>PostgreSQL</productname> internals and provide |
| similar functionality in your own programs. (To select this behavior |
| on program start-up, use the switch <option>-E</option>.) If you set |
| the variable to the value <literal>noexec</literal>, the queries are |
| just shown but are not actually sent to the server and executed. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>ENCODING</varname></term> |
| <listitem> |
| <para> |
| The current client character set encoding. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>FETCH_COUNT</varname></term> |
| <listitem> |
| <para> |
| If this variable is set to an integer value > 0, |
| the results of <command>SELECT</command> queries are fetched |
| and displayed in groups of that many rows, rather than the |
| default behavior of collecting the entire result set before |
| display. Therefore only a |
| limited amount of memory is used, regardless of the size of |
| the result set. Settings of 100 to 1000 are commonly used |
| when enabling this feature. |
| Keep in mind that when using this feature, a query might |
| fail after having already displayed some rows. |
| </para> |
| <tip> |
| <para> |
| Although you can use any output format with this feature, |
| the default <literal>aligned</> format tends to look bad |
| because each group of <varname>FETCH_COUNT</varname> rows |
| will be formatted separately, leading to varying column |
| widths across the row groups. The other output formats work better. |
| </para> |
| </tip> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>HISTCONTROL</varname></term> |
| <listitem> |
| <para> |
| If this variable is set to <literal>ignorespace</literal>, |
| lines which begin with a space are not entered into the history |
| list. If set to a value of <literal>ignoredups</literal>, lines |
| matching the previous history line are not entered. A value of |
| <literal>ignoreboth</literal> combines the two options. If |
| unset, or if set to any other value than those above, all lines |
| read in interactive mode are saved on the history list. |
| </para> |
| <note> |
| <para> |
| This feature was shamelessly plagiarized from |
| <application>Bash</application>. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>HISTFILE</varname></term> |
| <listitem> |
| <para> |
| The file name that will be used to store the history list. The default |
| value is <filename>~/.psql_history</filename>. For example, putting: |
| <programlisting> |
| \set HISTFILE ~/.psql_history- :DBNAME |
| </programlisting> |
| in <filename>~/.psqlrc</filename> will cause |
| <application>psql</application> to maintain a separate history for |
| each database. |
| </para> |
| <note> |
| <para> |
| This feature was shamelessly plagiarized from |
| <application>Bash</application>. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>HISTSIZE</varname></term> |
| <listitem> |
| <para> |
| The number of commands to store in the command history. The |
| default value is 500. |
| </para> |
| <note> |
| <para> |
| This feature was shamelessly plagiarized from |
| <application>Bash</application>. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>HOST</varname></term> |
| <listitem> |
| <para> |
| The database server host you are currently connected to. This is |
| set every time you connect to a database (including program |
| start-up), but can be unset. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>IGNOREEOF</varname></term> |
| <listitem> |
| <para> |
| If unset, sending an <acronym>EOF</> character (usually |
| <keycombo action="simul"><keycap>Control</><keycap>D</></>) |
| to an interactive session of <application>psql</application> |
| will terminate the application. If set to a numeric value, |
| that many <acronym>EOF</> characters are ignored before the |
| application terminates. If the variable is set but has no |
| numeric value, the default is 10. |
| </para> |
| <note> |
| <para> |
| This feature was shamelessly plagiarized from |
| <application>Bash</application>. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>LASTOID</varname></term> |
| <listitem> |
| <para> |
| The value of the last affected OID, as returned from an |
| <command>INSERT</command> or <command>lo_insert</command> |
| command. This variable is only guaranteed to be valid until |
| after the result of the next <acronym>SQL</acronym> command has |
| been displayed. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <indexterm> |
| <primary>rollback</primary> |
| <secondary>psql</secondary> |
| </indexterm> |
| <term><varname>ON_ERROR_ROLLBACK</varname></term> |
| <listitem> |
| <para> |
| When <literal>on</>, if a statement in a transaction block |
| generates an error, the error is ignored and the transaction |
| continues. When <literal>interactive</>, such errors are only |
| ignored in interactive sessions, and not when reading script |
| files. When <literal>off</> (the default), a statement in a |
| transaction block that generates an error aborts the entire |
| transaction. The on_error_rollback-on mode works by issuing an |
| implicit <command>SAVEPOINT</> for you, just before each command |
| that is in a transaction block, and rolls back to the savepoint |
| on error. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>ON_ERROR_STOP</varname></term> |
| <listitem> |
| <para> |
| By default, if non-interactive scripts encounter an error, such |
| as a malformed <acronym>SQL</acronym> command or internal |
| meta-command, processing continues. This has been the |
| traditional behavior of <application>psql</application> but it |
| is sometimes not desirable. If this variable is set, script |
| processing will immediately terminate. If the script was called |
| from another script it will terminate in the same fashion. If |
| the outermost script was not called from an interactive |
| <application>psql</application> session but rather using the |
| <option>-f</option> option, <application>psql</application> will |
| return error code 3, to distinguish this case from fatal error |
| conditions (error code 1). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>PORT</varname></term> |
| <listitem> |
| <para> |
| The database server port to which you are currently connected. |
| This is set every time you connect to a database (including |
| program start-up), but can be unset. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>PROMPT1</varname></term> |
| <term><varname>PROMPT2</varname></term> |
| <term><varname>PROMPT3</varname></term> |
| <listitem> |
| <para> |
| These specify what the prompts <application>psql</application> |
| issues should look like. See <xref |
| linkend="APP-PSQL-prompting" |
| endterm="APP-PSQL-prompting-title"> below. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>QUIET</varname></term> |
| <listitem> |
| <para> |
| This variable is equivalent to the command line option |
| <option>-q</option>. It is probably not too useful in |
| interactive mode. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>SINGLELINE</varname></term> |
| <listitem> |
| <para> |
| This variable is equivalent to the command line option |
| <option>-S</option>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>SINGLESTEP</varname></term> |
| <listitem> |
| <para> |
| This variable is equivalent to the command line option |
| <option>-s</option>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>USER</varname></term> |
| <listitem> |
| <para> |
| The database user you are currently connected as. This is set |
| every time you connect to a database (including program |
| start-up), but can be unset. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><varname>VERBOSITY</varname></term> |
| <listitem> |
| <para> |
| This variable can be set to the values <literal>default</>, |
| <literal>verbose</>, or <literal>terse</> to control the verbosity |
| of error reports. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| |
| </refsect3> |
| |
| <refsect3> |
| <title><acronym>SQL</acronym> Interpolation</title> |
| |
| <para> |
| An additional useful feature of <application>psql</application> |
| variables is that you can substitute (<quote>interpolate</quote>) |
| them into regular <acronym>SQL</acronym> statements. |
| <application>psql</application> provides special facilities for |
| ensuring that values used as SQL literals and identifiers are |
| properly escaped. The syntax for interpolating a value without |
| any special escaping is again to prepend the variable name with a colon |
| (<literal>:</literal>): |
| <programlisting> |
| testdb=> <userinput>\set foo 'my_table'</userinput> |
| testdb=> <userinput>SELECT * FROM :foo;</userinput> |
| </programlisting> |
| would then query the table <literal>my_table</literal>. Note that this |
| may be unsafe: the value of the variable is copied literally, so it can |
| even contain unbalanced quotes or backslash commands. You must make sure |
| that it makes sense where you put it. |
| </para> |
| |
| <para> |
| When a value is to be used as an SQL literal or identifier, it is |
| safest to arrange for it to be escaped. To escape the value of |
| a variable as an SQL literal, write a colon followed by the variable |
| name in single quotes. To escape the value an SQL identifier, write |
| a colon followed by the variable name in double quotes. The previous |
| example would be more safely written this way: |
| <programlisting> |
| testdb=> <userinput>\set foo 'my_table'</userinput> |
| testdb=> <userinput>SELECT * FROM :"foo";</userinput> |
| </programlisting> |
| Variable interpolation will not be performed into quoted |
| <acronym>SQL</acronym> entities. |
| </para> |
| |
| <para> |
| One possible use of this mechanism is to |
| copy the contents of a file into a table column. First load the file into a |
| variable and then proceed as above: |
| <programlisting> |
| testdb=> <userinput>\set content `cat my_file.txt`</userinput> |
| testdb=> <userinput>INSERT INTO my_table VALUES (:'content');</userinput> |
| </programlisting> |
| (Note that this still won't work if <filename>my_file.txt</filename> contains NUL bytes. |
| psql does not support embedded NUL bytes in variable values.) |
| </para> |
| |
| <para> |
| Since colons can legally appear in SQL commands, an apparent attempt |
| at interpolation (such as <literal>:name</literal>, |
| <literal>:'name'</literal>, or <literal>:"name"</literal>) is not |
| changed unless the named variable is currently set. In any case, you |
| can escape a colon with a backslash to protect it from substitution. |
| (The colon syntax for variables is standard <acronym>SQL</acronym> for |
| embedded query languages, such as <application>ECPG</application>. |
| The colon syntax for array slices and type casts are |
| <productname>PostgreSQL</productname> extensions, hence the |
| conflict. The colon syntax for escaping a variable's value as an |
| SQL literal or identifier is a <application>psql</application> |
| extension.) |
| </para> |
| |
| </refsect3> |
| |
| <refsect3 id="APP-PSQL-prompting"> |
| <title id="APP-PSQL-prompting-title">Prompting</title> |
| |
| <para> |
| The prompts <application>psql</application> issues can be customized |
| to your preference. The three variables <varname>PROMPT1</varname>, |
| <varname>PROMPT2</varname>, and <varname>PROMPT3</varname> contain strings |
| and special escape sequences that describe the appearance of the |
| prompt. Prompt 1 is the normal prompt that is issued when |
| <application>psql</application> requests a new command. Prompt 2 is |
| issued when more input is expected during command input because the |
| command was not terminated with a semicolon or a quote was not closed. |
| Prompt 3 is issued when you run an <acronym>SQL</acronym> |
| <command>COPY</command> command and you are expected to type in the |
| row values on the terminal. |
| </para> |
| |
| <para> |
| The value of the selected prompt variable is printed literally, |
| except where a percent sign (<literal>%</literal>) is encountered. |
| Depending on the next character, certain other text is substituted |
| instead. Defined substitutions are: |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>%M</literal></term> |
| <listitem> |
| <para> |
| The full host name (with domain name) of the database server, |
| or <literal>[local]</literal> if the connection is over a Unix |
| domain socket, or |
| <literal>[local:<replaceable>/dir/name</replaceable>]</literal>, |
| if the Unix domain socket is not at the compiled in default |
| location. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%m</literal></term> |
| <listitem> |
| <para> |
| The host name of the database server, truncated at the |
| first dot, or <literal>[local]</literal> if the connection is |
| over a Unix domain socket. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%></literal></term> |
| <listitem><para>The port number at which the database server is listening.</para></listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%n</literal></term> |
| <listitem> |
| <para> |
| The database session user name. (The expansion of this |
| value might change during a database session as the result |
| of the command <command>SET SESSION |
| AUTHORIZATION</command>.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%/</literal></term> |
| <listitem><para>The name of the current database.</para></listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%~</literal></term> |
| <listitem><para>Like <literal>%/</literal>, but the output is <literal>~</literal> |
| (tilde) if the database is your default database.</para></listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%#</literal></term> |
| <listitem> |
| <para> |
| If the session user is a database superuser, then a |
| <literal>#</literal>, otherwise a <literal>></literal>. |
| (The expansion of this value might change during a database |
| session as the result of the command <command>SET SESSION |
| AUTHORIZATION</command>.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%R</literal></term> |
| <listitem> |
| <para> |
| In prompt 1 normally <literal>=</literal>, but <literal>^</literal> if |
| in single-line mode, and <literal>!</literal> if the session is |
| disconnected from the database (which can happen if |
| <command>\connect</command> fails). In prompt 2 the sequence is |
| replaced by <literal>-</literal>, <literal>*</literal>, a single quote, |
| a double quote, or a dollar sign, depending on whether |
| <application>psql</application> expects more input because the |
| command wasn't terminated yet, because you are inside a |
| <literal>/* ... */</literal> comment, or because you are inside |
| a quoted or dollar-escaped string. In prompt 3 the sequence doesn't |
| produce anything. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%x</literal></term> |
| <listitem> |
| <para> |
| Transaction status: an empty string when not in a transaction |
| block, or <literal>*</> when in a transaction block, or |
| <literal>!</> when in a failed transaction block, or <literal>?</> |
| when the transaction state is indeterminate (for example, because |
| there is no connection). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%</literal><replaceable class="parameter">digits</replaceable></term> |
| <listitem> |
| <para> |
| The character with the indicated octal code is substituted. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%:</literal><replaceable class="parameter">name</replaceable><literal>:</literal></term> |
| <listitem> |
| <para> |
| The value of the <application>psql</application> variable |
| <replaceable class="parameter">name</replaceable>. See the |
| section <xref linkend="APP-PSQL-variables" |
| endterm="APP-PSQL-variables-title"> for details. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%`</literal><replaceable class="parameter">command</replaceable><literal>`</literal></term> |
| <listitem> |
| <para> |
| The output of <replaceable |
| class="parameter">command</replaceable>, similar to ordinary |
| <quote>back-tick</quote> substitution. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>%[</literal> ... <literal>%]</literal></term> |
| <listitem> |
| <para> |
| Prompts can contain terminal control characters which, for |
| example, change the color, background, or style of the prompt |
| text, or change the title of the terminal window. In order for |
| the line editing features of <application>Readline</application> to work properly, these |
| non-printing control characters must be designated as invisible |
| by surrounding them with <literal>%[</literal> and |
| <literal>%]</literal>. Multiple pairs of these can occur within |
| the prompt. For example: |
| <programlisting> |
| testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# ' |
| </programlisting> |
| results in a boldfaced (<literal>1;</literal>) yellow-on-black |
| (<literal>33;40</literal>) prompt on VT100-compatible, color-capable |
| terminals. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| |
| To insert a percent sign into your prompt, write |
| <literal>%%</literal>. The default prompts are |
| <literal>'%/%R%# '</literal> for prompts 1 and 2, and |
| <literal>'>> '</literal> for prompt 3. |
| </para> |
| |
| <note> |
| <para> |
| This feature was shamelessly plagiarized from |
| <application>tcsh</application>. |
| </para> |
| </note> |
| |
| </refsect3> |
| |
| <refsect3> |
| <title>Command-Line Editing</title> |
| |
| <para> |
| <application>psql</application> supports the <application>Readline</application> |
| library for convenient line editing and retrieval. The command |
| history is automatically saved when <application>psql</application> |
| exits and is reloaded when |
| <application>psql</application> starts up. Tab-completion is also |
| supported, although the completion logic makes no claim to be an |
| <acronym>SQL</acronym> parser. If for some reason you do not like the tab completion, you |
| can turn it off by putting this in a file named |
| <filename>.inputrc</filename> in your home directory: |
| <programlisting> |
| $if psql |
| set disable-completion on |
| $endif |
| </programlisting> |
| (This is not a <application>psql</application> but a |
| <application>Readline</application> feature. Read its documentation |
| for further details.) |
| </para> |
| </refsect3> |
| </refsect2> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>Environment</title> |
| |
| <variablelist> |
| |
| <varlistentry> |
| <term><envar>COLUMNS</envar></term> |
| |
| <listitem> |
| <para> |
| If <literal>\pset columns</> is zero, controls the |
| width for the <literal>wrapped</> format and width for determining |
| if wide output requires the pager. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><envar>PAGER</envar></term> |
| |
| <listitem> |
| <para> |
| If the query results do not fit on the screen, they are piped |
| through this command. Typical values are |
| <literal>more</literal> or <literal>less</literal>. The default |
| is platform-dependent. The use of the pager can be disabled by |
| using the <command>\pset</command> command. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><envar>PGDATABASE</envar></term> |
| <term><envar>PGHOST</envar></term> |
| <term><envar>PGPORT</envar></term> |
| <term><envar>PGUSER</envar></term> |
| |
| <listitem> |
| <para> |
| Default connection parameters (see <xref linkend="libpq-envars">). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><envar>PSQL_EDITOR</envar></term> |
| <term><envar>EDITOR</envar></term> |
| <term><envar>VISUAL</envar></term> |
| |
| <listitem> |
| <para> |
| Editor used by the <command>\e</command> command. The variables |
| are examined in the order listed; the first that is set is used. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><envar>SHELL</envar></term> |
| |
| <listitem> |
| <para> |
| Command executed by the <command>\!</command> command. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><envar>TMPDIR</envar></term> |
| |
| <listitem> |
| <para> |
| Directory for storing temporary files. The default is |
| <filename>/tmp</filename>. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| <para> |
| This utility, like most other <productname>PostgreSQL</> utilities, |
| also uses the environment variables supported by <application>libpq</> |
| (see <xref linkend="libpq-envars">). |
| </para> |
| |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>Files</title> |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| Unless it is passed an <option>-X</option> |
| or <option>-c</option> option, |
| <application>psql</application> attempts to |
| read and execute commands from the system-wide |
| <filename>psqlrc</filename> file and the user's |
| <filename>~/.psqlrc</filename> file before starting up. |
| (On Windows, the user's startup file is named |
| <filename>%APPDATA%\postgresql\psqlrc.conf</filename>.) |
| See <filename><replaceable>PREFIX</>/share/psqlrc.sample</> |
| for information on setting up the system-wide file. It could be used |
| to set up the client or the server to taste (using the <command>\set |
| </command> and <command>SET</command> commands). |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| Both the system-wide <filename>psqlrc</filename> file and the user's |
| <filename>~/.psqlrc</filename> file can be made version-specific |
| by appending a dash and the <productname>PostgreSQL</productname> |
| release number, for example <filename>~/.psqlrc-&version;</filename>. |
| A matching version-specific file will be read in preference to a |
| non-version-specific file. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| The command-line history is stored in the file |
| <filename>~/.psql_history</filename>, or |
| <filename>%APPDATA%\postgresql\psql_history</filename> on Windows. |
| </para> |
| </listitem> |
| </itemizedlist> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| In an earlier life <application>psql</application> allowed the |
| first argument of a single-letter backslash command to start |
| directly after the command, without intervening whitespace. |
| As of <productname>PostgreSQL</productname> 8.4 this is no |
| longer allowed. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| <application>psql</application> is only guaranteed to work smoothly |
| with servers of the same version. That does not mean other combinations |
| will fail outright, but subtle and not-so-subtle problems might come |
| up. Backslash commands are particularly likely to fail if the |
| server is of a newer version than <application>psql</> itself. However, |
| backslash commands of the <literal>\d</> family should work with |
| servers of versions back to 7.4, though not necessarily with servers |
| newer than <application>psql</> itself. |
| </para> |
| </listitem> |
| |
| </itemizedlist> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>Notes for Windows users</title> |
| |
| <para> |
| <application>psql</application> is built as a <quote>console |
| application</>. Since the Windows console windows use a different |
| encoding than the rest of the system, you must take special care |
| when using 8-bit characters within <application>psql</application>. |
| If <application>psql</application> detects a problematic |
| console code page, it will warn you at startup. To change the |
| console code page, two things are necessary: |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| Set the code page by entering <userinput>cmd.exe /c chcp |
| 1252</userinput>. (1252 is a code page that is appropriate for |
| German; replace it with your value.) If you are using Cygwin, |
| you can put this command in <filename>/etc/profile</filename>. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| Set the console font to <literal>Lucida Console</>, because the |
| raster font does not work with the ANSI code page. |
| </para> |
| </listitem> |
| </itemizedlist> |
| </para> |
| |
| </refsect1> |
| |
| |
| <refsect1 id="APP-PSQL-examples"> |
| <title id="APP-PSQL-examples-title">Examples</title> |
| |
| <para> |
| The first example shows how to spread a command over several lines of |
| input. Notice the changing prompt: |
| <programlisting> |
| testdb=> <userinput>CREATE TABLE my_table (</userinput> |
| testdb(> <userinput> first integer not null default 0,</userinput> |
| testdb(> <userinput> second text)</userinput> |
| testdb-> <userinput>;</userinput> |
| CREATE TABLE |
| </programlisting> |
| Now look at the table definition again: |
| <programlisting> |
| testdb=> <userinput>\d my_table</userinput> |
| Table "my_table" |
| Attribute | Type | Modifier |
| -----------+---------+-------------------- |
| first | integer | not null default 0 |
| second | text | |
| |
| </programlisting> |
| Now we change the prompt to something more interesting: |
| <programlisting> |
| testdb=> <userinput>\set PROMPT1 '%n@%m %~%R%# '</userinput> |
| peter@localhost testdb=> |
| </programlisting> |
| Let's assume you have filled the table with data and want to take a |
| look at it: |
| <programlisting> |
| peter@localhost testdb=> SELECT * FROM my_table; |
| first | second |
| -------+-------- |
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| (4 rows) |
| |
| </programlisting> |
| You can display tables in different ways by using the |
| <command>\pset</command> command: |
| <programlisting> |
| peter@localhost testdb=> <userinput>\pset border 2</userinput> |
| Border style is 2. |
| peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput> |
| +-------+--------+ |
| | first | second | |
| +-------+--------+ |
| | 1 | one | |
| | 2 | two | |
| | 3 | three | |
| | 4 | four | |
| +-------+--------+ |
| (4 rows) |
| |
| peter@localhost testdb=> <userinput>\pset border 0</userinput> |
| Border style is 0. |
| peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput> |
| first second |
| ----- ------ |
| 1 one |
| 2 two |
| 3 three |
| 4 four |
| (4 rows) |
| |
| peter@localhost testdb=> <userinput>\pset border 1</userinput> |
| Border style is 1. |
| peter@localhost testdb=> <userinput>\pset format unaligned</userinput> |
| Output format is unaligned. |
| peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput> |
| Field separator is ",". |
| peter@localhost testdb=> <userinput>\pset tuples_only</userinput> |
| Showing only tuples. |
| peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput> |
| one,1 |
| two,2 |
| three,3 |
| four,4 |
| </programlisting> |
| Alternatively, use the short commands: |
| <programlisting> |
| peter@localhost testdb=> <userinput>\a \t \x</userinput> |
| Output format is aligned. |
| Tuples only is off. |
| Expanded display is on. |
| peter@localhost testdb=> <userinput>SELECT * FROM my_table;</userinput> |
| -[ RECORD 1 ]- |
| first | 1 |
| second | one |
| -[ RECORD 2 ]- |
| first | 2 |
| second | two |
| -[ RECORD 3 ]- |
| first | 3 |
| second | three |
| -[ RECORD 4 ]- |
| first | 4 |
| second | four |
| </programlisting> |
| </para> |
| |
| </refsect1> |
| |
| </refentry> |