psql.1

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

server. If the new connection is successfully made, the
previous connection is closed. If any of \fIdbname\fR, \fIusername\fR, \fIhost\fR or \fIport\fR are omitted or specified
as -, the value of that parameter from the
previous connection is used. If there is no previous
connection, the \fBlibpq\fR default for
the parameter's value is used.

If the connection attempt failed (wrong user name, access
denied, etc.), the previous connection will only be kept if
\fBpsql\fR 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.
.TP
\fB\\copy { \fItable\fB [ ( \fIcolumn_list\fB ) ] | ( \fIquery\fB ) }\fR
Performs a frontend (client) copy. This is an operation that
runs an SQL COPY [\fBcopy\fR(7)] command, but instead of the server
reading or writing the specified file,
\fBpsql\fR 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.

The syntax of the command is similar to that of the
SQL COPY [\fBcopy\fR(7)] command. Note that, because of this,
special parsing rules apply to the \fB\\copy\fR
command. In particular, the variable substitution rules and
backslash escapes do not apply.

\\copy ... from stdin | to stdout
reads/writes based on the command input and output respectively.
All rows are read from the same source that issued the command,
continuing until \\. is read or the stream
reaches EOF. Output is sent to the same place as
command output. To read/write from
\fBpsql\fR's standard input or output, use
pstdin or pstdout. This option is useful
for populating tables in-line within a SQL script file.
.sp
.RS
.B "Tip:"
This operation is not as efficient as the SQL
\fBCOPY\fR command because all data must pass
through the client/server connection. For large
amounts of data the SQL command may be preferable.
.RE
.sp
.TP
\fB\\copyright\fR
Shows the copyright and distribution terms of
PostgreSQL.
.TP
\fB\\d [ \fIpattern\fB ]\fR
.TP
\fB\\d+ [ \fIpattern\fB ]\fR
For each relation (table, view, index, or sequence) matching the
\fIpattern\fR, show all
columns, their types, the tablespace (if not the default) and any special
attributes such as NOT NULL or defaults, if
any. Associated indexes, constraints, rules, and triggers are
also shown, as is the view definition if the relation is a view.
(``Matching the pattern'' is defined below.)

The command form \\d+ 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.
.sp
.RS
.B "Note:"
If \fB\\d\fR is used without a
\fIpattern\fR argument, it is
equivalent to \fB\\dtvs\fR which will show a list of
all tables, views, and sequences. This is purely a convenience
measure.
.RE
.sp
.TP
\fB\\da [ \fIpattern\fB ]\fR
Lists all available aggregate functions, together with the data
types they operate on. If \fIpattern\fR
is specified, only aggregates whose names match the pattern are shown.
.TP
\fB\\db [ \fIpattern\fB ]\fR
.TP
\fB\\db+ [ \fIpattern\fB ]\fR
Lists all available tablespaces. If \fIpattern\fR
is specified, only tablespaces whose names match the pattern are shown.
If + is appended to the command name, each object 
is listed with its associated permissions.
.TP
\fB\\dc [ \fIpattern\fB ]\fR
Lists all available conversions between character-set encodings.
If \fIpattern\fR
is specified, only conversions whose names match the pattern are
listed.
.TP
\fB\\dC\fR
Lists all available type casts.
.TP
\fB\\dd [ \fIpattern\fB ]\fR
Shows the descriptions of objects matching the \fIpattern\fR, or of all visible objects if
no argument is given. But in either case, only objects that have
a description are listed.
(``Object'' covers aggregates, functions, operators,
types, relations (tables, views, indexes, sequences, large
objects), rules, and triggers.) For example:
.sp
.nf
=> \fB\\dd version\fR
                     Object descriptions
   Schema   |  Name   |  Object  |        Description
------------+---------+----------+---------------------------
 pg_catalog | version | function | PostgreSQL version string
(1 row)
.sp
.fi

Descriptions for objects can be created with the COMMENT [\fBcomment\fR(7)]
SQL command.
.TP
\fB\\dD [ \fIpattern\fB ]\fR
Lists all available domains. If \fIpattern\fR
is specified, only matching domains are shown.
.TP
\fB\\df [ \fIpattern\fB ]\fR
.TP
\fB\\df+ [ \fIpattern\fB ]\fR
Lists available functions, together with their argument and
return types. If \fIpattern\fR
is specified, only functions whose names match the pattern are shown.
If the form \\df+ is used, additional information about
each function, including language and description, is shown.
.sp
.RS
.B "Note:"
.PP
To look up functions taking argument or returning values of a specific
type, use your pager's search capability to scroll through the \\df
output.
.PP
To reduce clutter, \\df does not show data type I/O
functions. This is implemented by ignoring functions that accept
or return type \fBcstring\fR.
.RE
.sp
.TP
\fB\\dg [ \fIpattern\fB ]\fR
Lists all database roles. If \fIpattern\fR is specified, only
those roles whose names match the pattern are listed.
(This command is now effectively the same as \\du.)
.TP
\fB\\distvS [ \fIpattern\fB ]\fR
This is not the actual command name: the letters
i, s,
t, v,
S stand for index, sequence, table, view,
and system table, respectively. You can specify any or all of
these letters, in any order, to obtain a listing of all the
matching objects. The letter S restricts
the listing to system objects; without S,
only non-system objects are shown. If + is
appended to the command name, each object is listed with its
associated description, if any.

If \fIpattern\fR is
specified, only objects whose names match the pattern are listed.
.TP
\fB\\dl\fR
This is an alias for \fB\\lo_list\fR, which shows a
list of large objects.
.TP
\fB\\dn [ \fIpattern\fB ]\fR
.TP
\fB\\dn+ [ \fIpattern\fB ]\fR
Lists all available schemas (namespaces). If \fIpattern\fR (a regular expression)
is specified, only schemas whose names match the pattern are listed.
Non-local temporary schemas are suppressed. If +
is appended to the command name, each object is listed with its associated
permissions and description, if any.
.TP
\fB\\do [ \fIpattern\fB ]\fR
Lists available operators with their operand and return types.
If \fIpattern\fR is
specified, only operators whose names match the pattern are listed.
.TP
\fB\\dp [ \fIpattern\fB ]\fR
Produces a list of all available tables, views and sequences with their
associated access privileges.
If \fIpattern\fR is
specified, only tables, views and sequences whose names match the pattern are listed.

The GRANT [\fBgrant\fR(7)] and
REVOKE [\fBrevoke\fR(7)]
commands are used to set access privileges.
.TP
\fB\\dT [ \fIpattern\fB ]\fR
.TP
\fB\\dT+ [ \fIpattern\fB ]\fR
Lists all data types or only those that match \fIpattern\fR. The command form
\\dT+ shows extra information.
.TP
\fB\\du [ \fIpattern\fB ]\fR
Lists all database roles, or only those that match \fIpattern\fR.
.TP
\fB\\edit (or \\e) [ \fIfilename\fB ]\fR
If \fIfilename\fR is
specified, the file is edited; after the editor exits, its
content is copied back to the query buffer. If no argument is
given, the current query buffer is copied to a temporary file
which is then edited in the same fashion.

The new query buffer is then re-parsed according to the normal
rules of \fBpsql\fR, where the whole buffer
is treated as a single line. (Thus you cannot make scripts this
way. Use \fB\\i\fR for that.) This means also that
if the query ends with (or rather contains) a semicolon, it is
immediately executed. In other cases it will merely wait in the
query buffer.
.sp
.RS
.B "Tip:"
\fBpsql\fR searches the environment
variables \fBPSQL_EDITOR\fR, \fBEDITOR\fR, and
\fBVISUAL\fR (in that order) for an editor to use. If
all of them are unset, \fIvi\fR is used on Unix
systems, \fInotepad.exe\fR on Windows systems.
.RE
.sp
.TP
\fB\\echo \fItext\fB [ ... ]\fR
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:
.sp
.nf
=> \fB\\echo `date`\fR
Tue Oct 26 21:40:57 CEST 1999
.sp
.fi
If the first argument is an unquoted -n the trailing
newline is not written.
.sp
.RS
.B "Tip:"
If you use the \fB\\o\fR command to redirect your
query output you may wish to use \fB\\qecho\fR
instead of this command.
.RE
.sp
.TP
\fB\\encoding [ \fIencoding\fB ]\fR
Sets the client character set encoding. Without an argument, this command
shows the current encoding.
.TP
\fB\\f [ \fIstring\fB ]\fR
Sets the field separator for unaligned query output. The default
is the vertical bar (|). See also
\fB\\pset\fR for a generic way of setting output
options.
.TP
\fB\\g [ { \fIfilename\fB | |\fIcommand\fB } ]\fR
Sends the current query input buffer to the server and
optionally stores the query's output in \fIfilename\fR or pipes the output
into a separate Unix shell executing \fIcommand\fR. A bare
\\g is virtually equivalent to a semicolon. A
\\g with argument is a ``one-shot''
alternative to the \fB\\o\fR command.
.TP
\fB\\help (or \\h) [ \fIcommand\fB ]\fR
Gives syntax help on the specified SQL
command. If \fIcommand\fR
is not specified, then \fBpsql\fR will list
all the commands for which syntax help is available. If
\fIcommand\fR is an
asterisk (*), then syntax help on all
SQL commands is shown.
.sp
.RS
.B "Note:"
To simplify typing, commands that consists of several words do
not have to be quoted. Thus it is fine to type \fB\\help
alter table\fR.
.RE
.sp
.TP
\fB\\H\fR
Turns on HTML query output format. If the
HTML format is already on, it is switched
back to the default aligned text format. This command is for
compatibility and convenience, but see \fB\\pset\fR
about setting other output options.
.TP
\fB\\i \fIfilename\fB\fR
Reads input from the file \fIfilename\fR and executes it as
though it had been typed on the keyboard.
.sp
.RS
.B "Note:"
If you want to see the lines on the screen as they are read you
must set the variable ECHO to
all.
.RE
.sp
.TP
\fB\\l (or \\list)\fR
.TP
\fB\\l+ (or \\list+)\fR
List the names, owners, and character set encodings of all the databases in
the server. If + is appended to the command
name, database descriptions are also displayed.
.TP
\fB\\lo_export \fIloid\fB \fIfilename\fB\fR
Reads the large object with OID \fIloid\fR from the database and
writes it to \fIfilename\fR. Note that this is
subtly different from the server function
\fBlo_export\fR, which acts with the permissions
of the user that the database server runs as and on the server's
file system.
.sp
.RS
.B "Tip:"
Use \fB\\lo_list\fR to find out the large object's
OID.
.RE
.sp
.TP
\fB\\lo_import \fIfilename\fB [ \fIcomment\fB ]\fR
Stores the file into a PostgreSQL
large object. Optionally, it associates the given
comment with the object. Example:
.sp
.nf
foo=> \fB\\lo_import '/home/peter/pictures/photo.xcf' 'a picture of me'\fR
lo_import 152801
.sp
.fi
The response indicates that the large object received object ID
152801 which one ought to remember if one wants to access the
object ever again. For that reason it is recommended to always
associate a human-readable comment with every object. Those can
then be seen with the \fB\\lo_list\fR command.

Note that this command is subtly different from the server-side
\fBlo_import\fR because it acts as the local user
on the local file system, rather than the server's user and file
system.
.TP
\fB\\lo_list\fR
Shows a list of all PostgreSQL
large objects currently stored in the database,
along with any comments provided for them.
.TP
\fB\\lo_unlink \fIloid\fB\fR
Deletes the large object with OID
\fIloid\fR from the
database.
.sp
.RS
.B "Tip:"
Use \fB\\lo_list\fR to find out the large object's
OID.
.RE
.sp
.TP
\fB\\o [ {\fIfilename\fB | |\fIcommand\fB} ]\fR
Saves future query results to the file \fIfilename\fR or pipes future results
into a separate Unix shell to execute \fIcommand\fR. If no arguments are
specified, the query output will be reset to the standard output.

``Query results'' 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 \fB\\d\fR), but not error
messages.
.sp
.RS
.B "Tip:"
To intersperse text output in between query results, use
\fB\\qecho\fR.
.RE
.sp
.TP
\fB\\p\fR
Print the current query buffer to the standard output.
.TP
\fB\\password [ \fIusername\fB ]\fR
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 \fBALTER ROLE\fR command. This
makes sure that the new password does not appear in cleartext in the
command history, the server log, or elsewhere.
.TP
\fB\\pset \fIparameter\fB [ \fIvalue\fB ]\fR
This command sets options affecting the output of query result
tables. \fIparameter\fR
describes which option is to be set. The semantics of
\fIvalue\fR depend
thereon.

Adjustable printing options are:
.RS
.TP
\fBformat\fR
Sets the output format to one of unaligned,
aligned, html,
latex, or troff-ms.
Unique abbreviations are allowed. (That would mean one letter
is enough.)

``Unaligned'' writes all columns of a row on a
line, separated by the currently active field separator. This
is intended to create output that might be intended to be read
in by other programs (tab-separated, comma-separated).
``Aligned'' mode is the standard, human-readable,
nicely formatted text output that is default. The
``HTML'' and
``LaTeX'' modes 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 HTML, but in LaTeX you must
have a complete document wrapper.)
.TP
\fBborder\fR
The second argument 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
HTML mode, this will translate directly
into the border=... attribute, in the
others only values 0 (no border), 1 (internal dividing lines),
and 2 (table frame) make sense.
.TP
\fBexpanded (or x)\fR
Toggles between regular and expanded format. When expanded
format 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 ``horizontal'' mode.

Expanded mode is supported by all four output formats.
.TP
\fBnull\fR
The second argument is a string that should be printed
whenever a column is null. The default is not to print
anything, which can easily be mistaken for, say, an empty
string. Thus, one might choose to write \\pset null
\&'(null)'.
.TP
\fBfieldsep\fR
Specifies the field separator to be used in unaligned output
mode. 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 \\pset fieldsep