alter_table.7

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

.\\" auto-generated by docbook2man-spec $Revision: 1.1.1.1 $
.TH "ALTER TABLE" "" "2008-01-03" "SQL - Language Statements" "SQL Commands"
.SH NAME
ALTER TABLE \- change the definition of a table

.SH SYNOPSIS
.sp
.nf
ALTER TABLE [ ONLY ] \fIname\fR [ * ]
    \fIaction\fR [, ... ]
ALTER TABLE [ ONLY ] \fIname\fR [ * ]
    RENAME [ COLUMN ] \fIcolumn\fR TO \fInew_column\fR
ALTER TABLE \fIname\fR
    RENAME TO \fInew_name\fR
ALTER TABLE \fIname\fR
    SET SCHEMA \fInew_schema\fR

where \fIaction\fR is one of:

    ADD [ COLUMN ] \fIcolumn\fR \fItype\fR [ \fIcolumn_constraint\fR [ ... ] ]
    DROP [ COLUMN ] \fIcolumn\fR [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] \fIcolumn\fR TYPE \fItype\fR [ USING \fIexpression\fR ]
    ALTER [ COLUMN ] \fIcolumn\fR SET DEFAULT \fIexpression\fR
    ALTER [ COLUMN ] \fIcolumn\fR DROP DEFAULT
    ALTER [ COLUMN ] \fIcolumn\fR { SET | DROP } NOT NULL
    ALTER [ COLUMN ] \fIcolumn\fR SET STATISTICS \fIinteger\fR
    ALTER [ COLUMN ] \fIcolumn\fR SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD \fItable_constraint\fR
    DROP CONSTRAINT \fIconstraint_name\fR [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ \fItrigger_name\fR | ALL | USER ]
    ENABLE TRIGGER [ \fItrigger_name\fR | ALL | USER ]
    CLUSTER ON \fIindex_name\fR
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET ( \fIstorage_parameter\fR = \fIvalue\fR [, ... ] )
    RESET ( \fIstorage_parameter\fR [, ... ] )
    INHERIT \fIparent_table\fR
    NO INHERIT \fIparent_table\fR
    OWNER TO \fInew_owner\fR
    SET TABLESPACE \fInew_tablespace\fR
.sp
.fi
.SH "DESCRIPTION"
.PP
\fBALTER TABLE\fR changes the definition of an existing table.
There are several subforms:
.TP
\fBADD COLUMN\fR
This form adds a new column to the table, using the same syntax as
CREATE TABLE [\fBcreate_table\fR(7)].
.TP
\fBDROP COLUMN\fR
This form drops a column from a table. Indexes and
table constraints involving the column will be automatically
dropped as well. You will need to say CASCADE if
anything outside the table depends on the column, for example,
foreign key references or views.
.TP
\fBALTER COLUMN TYPE\fR
This form changes the type of a column of a table. Indexes and
simple table constraints involving the column will be automatically
converted to use the new column type by reparsing the originally
supplied expression. The optional USING
clause specifies how to compute the new column value from the old;
if omitted, the default conversion is the same as an assignment
cast from old data type to new. A USING
clause must be provided if there is no implicit or assignment
cast from old to new type.
.TP
\fBSET/DROP DEFAULT\fR
These forms set or remove the default value for a column.
The default values only apply to subsequent \fBINSERT\fR
commands; they do not cause rows already in the table to change.
Defaults may also be created for views, in which case they are
inserted into \fBINSERT\fR statements on the view before
the view's ON INSERT rule is applied.
.TP
\fBSET/DROP NOT NULL\fR
These forms change whether a column is marked to allow null
values or to reject null values. You can only use SET
NOT NULL when the column contains no null values.
.TP
\fBSET STATISTICS\fR
This form
sets the per-column statistics-gathering target for subsequent
ANALYZE [\fBanalyze\fR(7)] operations.
The target can be set in the range 0 to 1000; alternatively, set it
to -1 to revert to using the system default statistics
target (default_statistics_target).
For more information on the use of statistics by the
PostgreSQL query planner, refer to
in the documentation.

.TP
\fBSET STORAGE\fR
This form sets the storage mode for a column. This controls whether this
column is held inline or in a supplementary table, and whether the data
should be compressed or not. PLAIN must be used
for fixed-length values such as \fBinteger\fR and is
inline, uncompressed. MAIN is for inline,
compressible data. EXTERNAL is for external,
uncompressed data, and EXTENDED is for external,
compressed data. EXTENDED is the default for most
data types that support non-PLAIN storage.
Use of EXTERNAL will
make substring operations on \fBtext\fR and \fBbytea\fR
columns faster, at the penalty of increased storage space. Note that
SET STORAGE doesn't itself change anything in the table,
it just sets the strategy to be pursued during future table updates.
See in the documentation for more information.
.TP
\fBADD \fItable_constraint\fB\fR
This form adds a new constraint to a table using the same syntax as
CREATE TABLE [\fBcreate_table\fR(7)]. 
.TP
\fBDROP CONSTRAINT\fR
This form drops the specified constraint on a table.
.TP
\fBDISABLE/ENABLE TRIGGER\fR
These forms disable or enable trigger(s) belonging to the table.
A disabled trigger is still known to the system, but is not executed
when its triggering event occurs. For a deferred trigger, the enable
status is checked when the event occurs, not when the trigger function
is actually executed. One may disable or enable a single
trigger specified by name, or all triggers on the table, or only
user triggers (this option excludes triggers that are used to implement
foreign key constraints). Disabling or enabling constraint triggers
requires superuser privileges; it should be done with caution since
of course the integrity of the constraint cannot be guaranteed if the
triggers are not executed.
.TP
\fBCLUSTER\fR
This form selects the default index for future 
CLUSTER [\fBcluster\fR(7)]
operations. It does not actually re-cluster the table.
.TP
\fBSET WITHOUT CLUSTER\fR
This form removes the most recently used
CLUSTER [\fBcluster\fR(7)]
index specification from the table. This affects
future cluster operations that don't specify an index.
.TP
\fBSET WITHOUT OIDS\fR
This form removes the oid system column from the
table. This is exactly equivalent to
DROP COLUMN oid RESTRICT,
except that it will not complain if there is already no
oid column.

Note that there is no variant of \fBALTER TABLE\fR
that allows OIDs to be restored to a table once they have been
removed.
.TP
\fBSET ( \fIstorage_parameter\fB = \fIvalue\fB [, ... ] )\fR
This form changes one or more storage parameters for the table. See
CREATE TABLE [\fBcreate_table\fR(7)]
for details on the available parameters. Note that the table contents
will not be modified immediately by this command; depending on the
parameter you may need to rewrite the table to get the desired effects.
That can be done with CLUSTER [\fBcluster\fR(7)] or one of the forms of \fBALTER
TABLE\fR that forces a table rewrite.
.sp
.RS
.B "Note:"
While \fBCREATE TABLE\fR allows OIDS to be specified
in the WITH (\fIstorage_parameter\fR) syntax,
\fBALTER TABLE\fR does not treat OIDS as a
storage parameter.
.RE
.sp
.TP
\fBRESET ( \fIstorage_parameter\fB [, ... ] )\fR
This form resets one or more storage parameters to their
defaults. As with SET, a table rewrite may be
needed to update the table entirely.
.TP
\fBINHERIT \fIparent_table\fB\fR
This form adds the target table as a new child of the specified parent
table. Subsequently, queries against the parent will include records
of the target table. To be added as a child, the target table must
already contain all the same columns as the parent (it could have
additional columns, too). The columns must have matching data types,
and if they have NOT NULL constraints in the parent
then they must also have NOT NULL constraints in the
child.

There must also be matching child-table constraints for all
CHECK constraints of the parent. Currently
UNIQUE, PRIMARY KEY, and
FOREIGN KEY constraints are not considered, but
this may change in the future.
.TP
\fBNO INHERIT \fIparent_table\fB\fR
This form removes the target table from the list of children of the
specified parent table.
Queries against the parent table will no longer include records drawn
from the target table.
.TP
\fBOWNER\fR
This form changes the owner of the table, sequence, or view to the
specified user.
.TP
\fBSET TABLESPACE\fR
This form changes the table's tablespace to the specified tablespace and
moves the data file(s) associated with the table to the new tablespace.
Indexes on the table, if any, are not moved; but they can be moved
separately with additional SET TABLESPACE commands.
See also 
CREATE TABLESPACE [\fBcreate_tablespace\fR(7)].
.TP
\fBRENAME\fR
The RENAME forms change the name of a table
(or an index, sequence, or view) or the name of an individual column in
a table. There is no effect on the stored data.
.TP
\fBSET SCHEMA\fR
This form moves the table into another schema. Associated indexes,
constraints, and sequences owned by table columns are moved as well.
.PP
.PP
All the actions except RENAME and SET SCHEMA
can be combined into
a list of multiple alterations to apply in parallel. For example, it
is possible to add several columns and/or alter the type of several
columns in a single command. This is particularly useful with large
tables, since only one pass over the table need be made.
.PP
You must own the table to use \fBALTER TABLE\fR.
To change the schema of a table, you must also have
CREATE privilege on the new schema.
To add the table as a new child of a parent table, you must own the
parent table as well.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have CREATE privilege on
the table's schema. (These restrictions enforce that altering the owner
doesn't do anything you couldn't do by dropping and recreating the table.
However, a superuser can alter ownership of any table anyway.)
.SH "PARAMETERS"
.TP
\fB\fIname\fB\fR
The name (possibly schema-qualified) of an existing table to
alter. If ONLY is specified, only that table is
altered. If ONLY is not specified, the table and all
its descendant tables (if any) are updated. * can be
appended to the table name to indicate that descendant tables are
to be altered, but in the current version, this is the default
behavior. (In releases before 7.1, ONLY was the
default behavior. The default can be altered by changing the
configuration parameter sql_inheritance.)
.TP
\fB\fIcolumn\fB\fR
Name of a new or existing column.
.TP
\fB\fInew_column\fB\fR
New name for an existing column.
.TP
\fB\fInew_name\fB\fR
New name for the table.
.TP
\fB\fItype\fB\fR
Data type of the new column, or new data type for an existing
column.
.TP
\fB\fItable_constraint\fB\fR
New table constraint for the table.