psql-ref.sgml

关系型数据库 Postgresql 6.5.2

<refentry id="APP-PSQL">
 <refmeta>
  <refentrytitle id="psql-ref">
   <application>psql</application>
  </refentrytitle>
  <refmiscinfo>Application</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   <application>psql</application>
  </refname>
  <refpurpose>
   <productname>Postgres</productname> interactive client
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1998-09-26</date>
  </refsynopsisdivinfo>
  <synopsis>
psql [ <replaceable class="parameter">dbname</replaceable> ]
psql -A [ -c <replaceable class="parameter">query</replaceable> ] [ -d <replaceable class="parameter">dbname</replaceable> ]
    -e -E [ -f <replaceable class="parameter">filename</replaceable> ] [ -F <replaceable class="parameter">separator</replaceable> ]
    [ -h <replaceable class="parameter">hostname</replaceable> ] -Hln [ -o <replaceable class="parameter">filename</replaceable> ]
    [ -p <replaceable class="parameter">port</replaceable> ] -qsSt [ -T <replaceable class="parameter">table_o</replaceable> ] -ux
    [ <replaceable class="parameter">dbname</replaceable> ]
  </synopsis>

  <refsect2 id="R2-APP-PSQL-1">
   <refsect2info>
    <date>1998-09-26</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>
    <application>psql</application> accepts many command-line arguments,
    a rich set of meta-commands, and the full <acronym>SQL</acronym> language
    supported by <productname>Postgres</productname>. The most common
    command-line arguments are:

    <variablelist>
     <varlistentry>
      <term><replaceable class="PARAMETER">dbname</replaceable></term>
      <listitem>
       <para>
	The name of an existing database to access.
	<replaceable class="parameter">dbname</replaceable>
	defaults to the value of the
	<envar>USER</envar>
	environment variable or, if that's not set, to the Unix account name of the
	current user.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>-c <replaceable class="parameter">query</replaceable></term>
      <listitem>
       <para>
	A single query to run. <application>psql</application> will exit on completion.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <para>
    The full set of command-line arguments and meta-commands are described in a subsequent
    section.
   </para>

   <para>
    There are some environment variables which can be used in liu of
    command line arguments. 
    Additionally, the <productname>Postgres</productname> frontend library used by 
    the <application>psql</application> application
    looks for other optional environment variables to configure, for example,
    the style of date/time representation and the local time zone. Refer
    to the chapter on <filename>libpq</filename> in the
    <citetitle>Programmer's Guide</citetitle> for more details.
   </para>
   <para>
    You may set any of the following environment variables to avoid
    specifying command-line options:
	
    <variablelist>
     <varlistentry>
      <term><envar>PGHOST</envar></term>
      <listitem>
       <para>
	The <acronym>DNS</acronym> host name of the database server.
	Setting <envar>PGHOST</envar> to a non-zero-length string causes
	<acronym>TCP/IP</acronym> communication
	to be used, rather than the default local Unix domain sockets.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><envar>PGPORT</envar></term>
      <listitem>
       <para>
	The port number on which a <productname>Postgres</productname> server is listening.
	Defaults to <literal>5432</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><envar>PGTTY</envar></term>
      <listitem>
       <para>
	The target for display of messages from the client support library.
	Not required.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><envar>PGOPTION</envar></term>
      <listitem>
       <para>
	If <envar>PGOPTION</envar>
	is specified, then the options it contains are parsed
	<emphasis>before</emphasis>
	any command-line options.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><envar>PGREALM</envar></term>
      <listitem>
       <para>
	<envar>PGREALM</envar>
	only applies if
	<productname>Kerberos</productname>
	authentication is in use.  
	If this environment variable is set, <productname>Postgres</productname>
	will attempt authentication with servers for this realm and will use
	separate ticket files to avoid conflicts with local ticket files.
	See the <citetitle>PostgreSQL Administrator's Guide</citetitle>
	for additional information on
	<productname>Kerberos</productname>.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-APP-PSQL-2">
   <refsect2info>
    <date>1998-09-26</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>
    <application>psql</application>
    returns 0 to the shell on successful completion of all queries,
    1 for errors, 2 for abrupt disconnection from the backend.
    <application>psql</application>
    will also return 1 if the connection to a database could not be made for
    any reason.
   </para>
   <para>
    The default TAB delimiter is used.
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-APP-PSQL-1">
  <refsect1info>
   <date>1998-09-26</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   <application>psql</application> is a character-based front-end to 
   <productname>Postgres</productname>.
   It enables you to
   type in queries interactively, issue them to <productname>Postgres</productname>, 
   and see the query
   results.
  </para>
  <para>
   <application>psql</application>
   is a <productname>Postgres</productname> client application.  Hence, a
   <application>postmaster</application> process
   must be running on the database server host before
   <application>psql</application>
   is executed.  In addition, the correct parameters to identify
   the database server, such as the
   <application>postmaster</application> host name,
   may need to be specified
   as described below.
  </para>
  <para>
   When
   <application>psql</application>
   starts, it reads SQL commands from
   <filename>/etc/psqlrc</filename>
   and then from
   <filename>$(<envar>HOME</envar>)/.psqlrc</filename>
   This allows SQL commands like
   <command>SET</command>
   which can be used to set the date style to be run at the start of
   every session.
  </para>

  <refsect2 id="R2-APP-PSQL-3">
   <refsect2info>
    <date>1998-09-26</date>
   </refsect2info>
   <title>
    Connecting To A Database
   </title>
   <para>
    <application>psql</application>
    attempts to make a connection to the database at the hostname and
    port number specified on the command line.   If the connection could not
    be made for any reason (e.g. insufficient privileges, postmaster is not
    running on the server, etc)
    .IR <application>psql</application>
    will return an error that says
    <programlisting>
     Connection to database failed.
    </programlisting>
    The reason for the connection failure is not provided.
   </para>
  </refsect2>

  <refsect2 id="R2-APP-PSQL-4">
   <refsect2info>
    <date>1998-09-26</date>
   </refsect2info>
   <title>
    Entering Queries
   </title>
   <para>
    In normal operation, 
    <application>psql</application> provides a prompt with the name of the
    database that <application>psql</application> is current connected to 
    followed by the string "=>".
    For example,
    <programlisting>
$ <userinput>psql testdb</userinput>
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3]

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: testdb
	  
testdb=>
    </programlisting>
   </para>
   <para>
    At the prompt, the user may type in <acronym>SQL</acronym> queries.  
    Unless the -S option
    is set, input lines are sent to the backend when a query-terminating
    semicolon is reached.
   </para>
   <para>
    Whenever a query is executed, 
    <application>psql</application> also polls for asynchronous notification
    events generated by <command>LISTEN</command> and <command>NOTIFY</command>.
   </para>
   <para>
    <application>psql</application>
    can be used in a pipe sequence, and automatically detects when it
    is not listening or talking to a real tty.
   </para>
  </refsect2>

  <refsect2 id="R2-APP-PSQL-5">
   <title>Paging To Screen</title>

   <note>
    <title>Author</title>
    <para>
     From Brett McCormick on the mailing list 1998-04-04.
    </para>
   </note>

   <para>
    To affect the paging behavior of your <command>psql</command> output,
    set or unset your PAGER environment variable.  I always have to set mine
    before it will pause.  And of course you have to do this before
    starting the program.
   </para>

   <para>
    In csh/tcsh or other C shells:

    <programlisting>
% unsetenv PAGER
    </programlisting>

    while in sh/bash or other Bourne shells:

    <programlisting>
% unset PAGER
    </programlisting>
   </para>
  </refsect2>
 </refsect1>

 <refsect1 id="R1-APP-PSQL-2">
  <refsect1info>
   <date>1998-09-26</date>
  </refsect1info>
  <title>
   Command-line Options
  </title>
  <para>
   <application>psql</application>
   understands the following command-line options:

   <variablelist>
    <varlistentry>
     <term>-A</term>
     <listitem>
      <para>
       Turn off fill justification when printing out table elements.
      </para>
     </listitem>
    </varlistentry>
	
    <varlistentry>
     <term>-c <replaceable class="parameter">query</replaceable></term>
     <listitem>
      <para>
       Specifies that
       <application>psql</application>
       is to execute one query string,
       <replaceable class="parameter">query</replaceable>,
       and then exit.  This is useful for shell scripts, typically in
       conjunction with the <option>-q</option> option in shell scripts.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-d <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 last field in the
       command line.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-e</term>
     <listitem>
      <para>
       Echo the query sent to the backend
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-E</term>
     <listitem>
      <para>
       Echo the actual query generated by \d and other backslash commands
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-f <replaceable class="parameter">filename</replaceable></term>
     <listitem>
      <para>
       Use the file <replaceable class="parameter">filename</replaceable>
       as the source of queries instead of reading queries interactively.
       This file must be specified for and visible to the client frontend.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-F <replaceable class="parameter">separator</replaceable></term>
     <listitem>
      <para>
       Use <replaceable class="parameter">separator</replaceable>
       as the field separator.
       The default is an ASCII vertical bar ("|").
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-h <replaceable class="parameter">hostname</replaceable></term>
     <listitem>
      <para>
       Specifies the host name of the machine on which the
       <application>postmaster</application>
       is running.
       Without this option, communication is performed using
       local Unix domain sockets.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-H</term>
     <listitem>
      <para>
       Turns on
       <acronym>HTML 3.0</acronym>
       tabular output.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-l</term>
     <listitem>
      <para>
       Lists all available databases, then exit. Other non-connection options are ignored.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-n</term>
     <listitem>
      <para>
       Do not use the readline library for input line editing and command history.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-o <replaceable class="parameter">filename</replaceable></term>
     <listitem>
      <para>
       Put all output into file <replaceable class="parameter">filename</replaceable>.
       The path must be writable by the client.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-p <replaceable class="parameter">port</replaceable></term>
     <listitem>
      <para>
       Specifies the TCP/IP port or, by omission, the local Unix domain socket file
       extension on which the
       <application>postmaster</application>
       is listening for connections.  Defaults to the value of the
       <envar>PGPORT</envar>
       environment variable, if set, or to 5432.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-q</term>
     <listitem>
      <para>
       Specifies that
       <application>psql</application>
       should do its work quietly.  By default, it
       prints welcome and exit messages and prompts for each query, and prints
       out the number of rows returned from a query.
       If this option is used, none of this happens. This is useful with the
       <option>-c</option> option.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-s</term>
     <listitem>
      <para>
       Run in single-step mode where the user is prompted for each query before
       it is sent to the backend.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>-S</term>
     <listitem>
      <para>
       Runs in single-line mode where each query is terminated by a newline,
       instead of a semicolon.
      </para>
     </listitem>
    </varlistentry>