psql.1

来自「PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开」· 1 代码 · 共 1,822 行 · 第 1/4 页

.\\" auto-generated by docbook2man-spec $Revision: 1.1.1.1 $
.TH "PSQL" "1" "2008-01-03" "Application" "PostgreSQL Client Applications"
.SH NAME
psql \- PostgreSQL interactive terminal

.SH SYNOPSIS
.sp
\fBpsql\fR [ \fB\fIoption\fB\fR\fI...\fR ]  [ \fB\fIdbname\fB
 [ \fIusername\fB ] \fR ] 
.SH "DESCRIPTION"
.PP
\fBpsql\fR is a terminal-based front-end to
PostgreSQL. It enables you to type in
queries interactively, issue them to
PostgreSQL, 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.
.SH "OPTIONS"
.TP
\fB-a\fR
.TP
\fB--echo-all\fR
Print all input lines to standard output as they are read. This is more
useful for script processing rather than interactive mode. This is
equivalent to setting the variable ECHO to
all.
.TP
\fB-A\fR
.TP
\fB--no-align\fR
Switches to unaligned output mode. (The default output mode is
otherwise aligned.)
.TP
\fB-c \fIcommand\fB\fR
.TP
\fB--command \fIcommand\fB\fR
Specifies that \fBpsql\fR is to execute one
command string, \fIcommand\fR,
and then exit. This is useful in shell scripts.

\fIcommand\fR must be either
a command string that is completely parsable by the server (i.e.,
it contains no \fBpsql\fR specific features),
or a single backslash command. Thus you cannot mix
SQL and \fBpsql\fR
meta-commands with this option. To achieve that, you could
pipe the string into \fBpsql\fR, like
this: echo '\\x \\\\ SELECT * FROM foo;' | psql.
(\\\\ is the separator meta-command.)

If the command string contains multiple SQL commands, they are
processed in a single transaction, unless there are explicit
\fBBEGIN\fR/\fBCOMMIT\fR commands included in the
string to divide it into multiple transactions. This is
different from the behavior when the same string is fed to
\fBpsql\fR's standard input.
.TP
\fB-d \fIdbname\fB\fR
.TP
\fB--dbname \fIdbname\fB\fR
Specifies the name of the database to connect to. This is
equivalent to specifying \fIdbname\fR as the first non-option
argument on the command line.
.TP
\fB-e\fR
.TP
\fB--echo-queries\fR
Copy all SQL commands sent to the server to standard output as well.
This is equivalent
to setting the variable ECHO to
queries.
.TP
\fB-E\fR
.TP
\fB--echo-hidden\fR
Echo the actual queries generated by \fB\\d\fR and other backslash
commands. You can use this to study \fBpsql\fR's
internal operations. This is equivalent to
setting the variable ECHO_HIDDEN from within
\fBpsql\fR.
.TP
\fB-f \fIfilename\fB\fR
.TP
\fB--file \fIfilename\fB\fR
Use the file \fIfilename\fR
as the source of commands instead of reading commands interactively.
After the file is processed, \fBpsql\fR
terminates. This is in many ways equivalent to the internal
command \fB\\i\fR.

If \fIfilename\fR is -
(hyphen), then standard input is read.

Using this option is subtly different from writing psql
< \fIfilename\fR. In general,
both will do what you expect, but using -f
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 that you would have gotten had you entered
everything by hand.
.TP
\fB-F \fIseparator\fB\fR
.TP
\fB--field-separator \fIseparator\fB\fR
Use \fIseparator\fR as the
field separator for unaligned output. This is equivalent to
\fB\\pset fieldsep\fR or \fB\\f\fR.
.TP
\fB-h \fIhostname\fB\fR
.TP
\fB--host \fIhostname\fB\fR
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.
.TP
\fB-H\fR
.TP
\fB--html\fR
Turn on HTML tabular output. This is
equivalent to \\pset format html or the
\fB\\H\fR command.
.TP
\fB-l\fR
.TP
\fB--list\fR
List all available databases, then exit. Other non-connection
options are ignored. This is similar to the internal command
\fB\\list\fR.
.TP
\fB-L \fIfilename\fB\fR
.TP
\fB--log-file \fIfilename\fB\fR
Write all query output into file \fIfilename\fR, in addition to the
normal output destination.
.TP
\fB-o \fIfilename\fB\fR
.TP
\fB--output \fIfilename\fB\fR
Put all query output into file \fIfilename\fR. This is equivalent to
the command \fB\\o\fR.
.TP
\fB-p \fIport\fB\fR
.TP
\fB--port \fIport\fB\fR
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 \fBPGPORT\fR
environment variable or, if not set, to the port specified at
compile time, usually 5432.
.TP
\fB-P \fIassignment\fB\fR
.TP
\fB--pset \fIassignment\fB\fR
Allows you to specify printing options in the style of
\fB\\pset\fR on the command line. Note that here you
have to separate name and value with an equal sign instead of a
space. Thus to set the output format to LaTeX, you could write
-P format=latex.
.TP
\fB-q\fR
.TP
\fB--quiet\fR
Specifies that \fBpsql\fR 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 \fB-c\fR option.
Within \fBpsql\fR you can also set the
QUIET variable to achieve the same effect.
.TP
\fB-R \fIseparator\fB\fR
.TP
\fB--record-separator \fIseparator\fB\fR
Use \fIseparator\fR as the
record separator for unaligned output. This is equivalent to the
\fB\\pset recordsep\fR command.
.TP
\fB-s\fR
.TP
\fB--single-step\fR
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.
.TP
\fB-S\fR
.TP
\fB--single-line\fR
Runs in single-line mode where a newline terminates an SQL command, as a
semicolon does.
.sp
.RS
.B "Note:"
This mode is provided for those who insist on it, but you are not
necessarily encouraged to use it. In particular, if you mix
SQL and meta-commands on a line the order of
execution might not always be clear to the inexperienced user.
.RE
.sp
.TP
\fB-t\fR
.TP
\fB--tuples-only\fR
Turn off printing of column names and result row count footers,
etc. This is equivalent to the \fB\\t\fR command.
.TP
\fB-T \fItable_options\fB\fR
.TP
\fB--table-attr \fItable_options\fB\fR
Allows you to specify options to be placed within the
HTML table tag. See
\fB\\pset\fR for details.
.TP
\fB-u\fR
Forces \fBpsql\fR to prompt for the user name and
password before connecting to the database.

This option is deprecated, as it is conceptually flawed.
(Prompting for a non-default user name and prompting for a
password because the server requires it are really two different
things.) You are encouraged to look at the \fB-U\fR and
\fB-W\fR options instead.
.TP
\fB-U \fIusername\fB\fR
.TP
\fB--username \fIusername\fB\fR
Connect to the database as the user \fIusername\fR instead of the default.
(You must have permission to do so, of course.)
.TP
\fB-v \fIassignment\fB\fR
.TP
\fB--set \fIassignment\fB\fR
.TP
\fB--variable \fIassignment\fB\fR
Perform a variable assignment, like the \fB\\set\fR
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.
.TP
\fB-V\fR
.TP
\fB--version\fR
Print the \fBpsql\fR version and exit.
.TP
\fB-W\fR
.TP
\fB--password\fR
Forces \fBpsql\fR to prompt for a
password before connecting to a database. 

\fBpsql\fR should automatically prompt for a
password whenever the server requests password authentication.
However, currently password request detection is not totally
reliable, hence this option to force a prompt. If no password
prompt is issued and the server requires password authentication,
the connection attempt will fail.

This option will remain set for the entire session, even if you
change the database connection with the meta-command
\fB\\connect\fR.
.TP
\fB-x\fR
.TP
\fB--expanded\fR
Turn on the expanded table formatting mode. This is equivalent to the
\fB\\x\fR command.
.TP
\fB-X,\fR
.TP
\fB--no-psqlrc\fR
Do not read the start-up file (neither the system-wide
\fIpsqlrc\fR file nor the user's
\fI~/.psqlrc\fR file).
.TP
\fB-1\fR
.TP
\fB--single-transaction\fR
When \fBpsql\fR executes a script with the
\fB-f\fR option, adding this option wraps
\fBBEGIN\fR/\fBCOMMIT\fR around the script to execute it
as a single transaction. This ensures that either all the commands
complete successfully, or no changes are applied. 

If the script itself uses \fBBEGIN\fR, \fBCOMMIT\fR,
or \fBROLLBACK\fR, 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.
.TP
\fB-?\fR
.TP
\fB--help\fR
Show help about \fBpsql\fR command line
arguments, and exit.
.SH "EXIT STATUS"
.PP
\fBpsql\fR 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 ON_ERROR_STOP was set.
.SH "USAGE"
.SS "CONNECTING TO A DATABASE"
.PP
\fBpsql\fR is a regular
PostgreSQL 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. \fBpsql\fR can be
told about those parameters via command line options, namely
\fB-d\fR, \fB-h\fR, \fB-p\fR, and
\fB-U\fR 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
these options are required; there are useful defaults. If you omit the host
name, \fBpsql\fR will connect via a Unix-domain socket
to a server on the local host, or via TCP/IP to 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 can't
just connect to any database under any user name. Your database
administrator should have informed you about your access rights.
.PP
When the defaults aren't quite right, you can save yourself
some typing by setting the environment variables
\fBPGDATABASE\fR, \fBPGHOST\fR,
\fBPGPORT\fR and/or \fBPGUSER\fR to appropriate
values. (For additional environment variables, see in the documentation.) It is also convenient to have a
\fI~/.pgpass\fR file to avoid regularly having to type in
passwords. See in the documentation for more information.
.PP
If the connection could not be made for any reason (e.g., insufficient
privileges, server is not running on the targeted host, etc.),
\fBpsql\fR will return an error and terminate.
.SS "ENTERING SQL COMMANDS"
.PP
In normal operation, \fBpsql\fR provides a
prompt with the name of the database to which
\fBpsql\fR is currently connected, followed by
the string =>. For example,
.sp
.nf
$ \fBpsql testdb\fR
Welcome to psql 8.2.6, the PostgreSQL interactive terminal.

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

testdb=>
.sp
.fi
.PP
At the prompt, the user may type in SQL 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.
.PP
Whenever a command is executed, \fBpsql\fR also polls
for asynchronous notification events generated by
LISTEN [\fBlisten\fR(7)] and
NOTIFY [\fBnotify\fR(7)].
.SS "META-COMMANDS"
.PP
Anything you enter in \fBpsql\fR that begins
with an unquoted backslash is a \fBpsql\fR
meta-command that is processed by \fBpsql\fR
itself. These commands help make
\fBpsql\fR more useful for administration or
scripting. Meta-commands are more commonly called slash or backslash
commands.
.PP
The format of a \fBpsql\fR 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.
.PP
To include whitespace into an argument you may 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
\\n (new line), \\t (tab),
\\\fIdigits\fR (octal), and
\\x\fIdigits\fR (hexadecimal).
.PP
If an unquoted argument begins with a colon (:),
it is taken as a \fBpsql\fR variable and the value of the
variable is used as the argument instead.
.PP
Arguments that are enclosed in backquotes (`)
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.
.PP
Some commands take an SQL identifier (such as a
table name) as argument. These arguments follow the syntax rules
of SQL: Unquoted letters are forced to
lowercase, while double quotes (") 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,
FOO"BAR"BAZ is interpreted as fooBARbaz,
and "A weird"" name" becomes A weird"
name.
.PP
Parsing for arguments stops when another unquoted backslash occurs.
This is taken as the beginning of a new meta-command. The special
sequence \\\\ (two backslashes) marks the end of
arguments and continues parsing SQL commands, if
any. That way SQL and
\fBpsql\fR 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.
.PP
The following meta-commands are defined:
.TP
\fB\\a\fR
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 \fB\\pset\fR for a
more general solution.
.TP
\fB\\cd [ \fIdirectory\fB ]\fR
Changes the current working directory to
\fIdirectory\fR. Without argument, changes
to the current user's home directory.
.sp
.RS
.B "Tip:"
To print your current working directory, use \\!pwd.
.RE
.sp
.TP
\fB\\C [ \fItitle\fB ]\fR
Sets the title of any tables being printed as the result of a
query or unset any such title. This command is equivalent to
\\pset title \fItitle\fR. (The name of
this command derives from ``caption'', as it was
previously only used to set the caption in an
HTML table.)
.TP
\fB\\connect (or \\c) [ \fIdbname\fB [ \fIusername\fB ] [ \fIhost\fB ] [ \fIport\fB ] ]\fR
Establishes a new connection to a PostgreSQL