psql-ref.sgml

PostgreSQL7.4.6 for Linux

    <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>
      Show the <application>psql</application> version.
      </para>
      </listitem>
    </varlistentry>

    <varlistentry>
      <term><option>-W</></term>
      <term><option>--password</></term>
      <listitem>
      <para>
      Requests that <application>psql</application> should prompt for a
      password before connecting to a database. This will remain set for
      the entire session, even if you change the database connection
      with the meta-command <command>\connect</command>.
      </para>

      <para>
      In the current version, <application>psql</application>
      automatically issues a password prompt whenever the server
      requests password authentication. Because this is currently based
      on a hack, the automatic recognition might mysteriously fail,
      hence this option to force a prompt. If no password prompt is
      issued and the server requires password authentication the
      connection attempt will fail.
      </para>
      </listitem>
    </varlistentry>

    <varlistentry>
      <term><option>-x</></term>
      <term><option>--expanded</></term>
      <listitem>
      <para>
      Turn on the extended table formatting mode. This is equivalent to the
      command <command>\x</command>.
      </para>
      </listitem>
    </varlistentry>

    <varlistentry>
      <term><option>-X,</></term>
      <term><option>--no-psqlrc</></term>
      <listitem>
      <para>
      Do not read the start-up file <filename>~/.psqlrc</filename>.
      </para>
      </listitem>
    </varlistentry>

    <varlistentry>
      <term><option>-?</></term>
      <term><option>--help</></term>
      <listitem>
      <para>
      Show help about <application>psql</application> command line
      arguments.
      </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 (out of memory,
   file not found) occurs, 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 also given). Not all
    these options are required, defaults do apply. If you omit the host
    name, <application>psql</> will connect via a Unix domain socket to a server on the
    local host. The default port number is compile-time determined.
    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 can't
    just connect to any database under any user name. Your database
    administrator should have informed you about your access rights. To
    save you some typing you can also set the environment variables
    <envar>PGDATABASE</envar>, <envar>PGHOST</envar>,
    <envar>PGPORT</envar> and <envar>PGUSER</envar> to appropriate
    values.
    </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>=&gt;</literal>. For example,
<programlisting>
$ <userinput>psql testdb</userinput>
Welcome to psql &version;, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

testdb=>
</programlisting>
    </para>

    <para>
    At the prompt, the user may 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 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>
    <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 are what makes
    <application>psql</application> interesting for administration or
    scripting. Meta-commands are more commonly 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 may quote it with a
    single quote. To include a single quote into such an argument,
    precede it by a backslash. 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>,
    <literal>\0</literal><replaceable>digits</replaceable>, and
    <literal>\0x</literal><replaceable>digits</replaceable> (the
    character with the given decimal, octal, or hexadecimal code).
    </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.
    </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 when another unquoted backslash occurs.
    This 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
	general solution.
        </para>
        </listitem>
      </varlistentry>

      <varlistentry>
       <term><literal>\cd</literal> <optional><replaceable>directory</replaceable></optional></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</literal> [ <replaceable class="parameter">title</replaceable> ]</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>) [ <replaceable class="parameter">dbname</replaceable> [ <replaceable class="parameter">username</replaceable> ] ]</term>
        <listitem>
        <para>
	Establishes a connection to a new database and/or under a user
	name. The previous connection is closed. If <replaceable
	class="parameter">dbname</replaceable> is <literal>-</literal>
	the current database name is assumed.
	</para>

	<para>
	If <replaceable class="parameter">username</replaceable> is
	omitted the current user name is assumed. </para>

	<para>
	As a special rule, <command>\connect</command> without any
	arguments will connect to the default database as the default
	user (as you would have gotten by starting
	<application>psql</application> without any arguments).
	</para>

	<para>
	If the connection attempt failed (wrong user name, access
	denied, etc.), the previous connection will be kept if and only
	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>\copy <replaceable class="parameter">table</replaceable>
	[ ( <replaceable class="parameter">column_list</replaceable> ) ]
        { <literal>from</literal> | <literal>to</literal> }
	<replaceable class="parameter">filename</replaceable> | stdin | stdout
        [ <literal>with</literal> ] 
            [ <literal>oids</literal> ] 
            [ <literal>delimiter [as] </literal> '<replaceable class="parameter">character</replaceable>' ]
            [ <literal>null [as] </literal> '<replaceable class="parameter">string</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> <command>COPY</command> command.  (See its
	description for the details.)  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>

        <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 other technique may be preferable.
        </para>
        </tip>

        <note>
        <para>
        Note the difference in interpretation of
        <literal>stdin</literal> and <literal>stdout</literal> between
        client and server copies: in a client copy these always
        refer to <application>psql</application>'s input and output
        stream. On a server copy <literal>stdin</literal> comes from