alter_table.7

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

.TP
\fB\fIconstraint_name\fB\fR
Name of an existing constraint to drop.
.TP
\fBCASCADE\fR
Automatically drop objects that depend on the dropped column
or constraint (for example, views referencing the column).
.TP
\fBRESTRICT\fR
Refuse to drop the column or constraint if there are any dependent
objects. This is the default behavior.
.TP
\fB\fItrigger_name\fB\fR
Name of a single trigger to disable or enable.
.TP
\fBALL\fR
Disable or enable all triggers belonging to the table.
(This requires superuser privilege if any of the triggers are for
foreign key constraints.)
.TP
\fBUSER\fR
Disable or enable all triggers belonging to the table except for
foreign key constraint triggers.
.TP
\fB\fIindex_name\fB\fR
The index name on which the table should be marked for clustering.
.TP
\fB\fIstorage_parameter\fB\fR
The name of a table storage parameter.
.TP
\fB\fIvalue\fB\fR
The new value for a table storage parameter.
This might be a number or a word depending on the parameter.
.TP
\fB\fIparent_table\fB\fR
A parent table to associate or de-associate with this table.
.TP
\fB\fInew_owner\fB\fR
The user name of the new owner of the table.
.TP
\fB\fInew_tablespace\fB\fR
The name of the tablespace to which the table will be moved.
.TP
\fB\fInew_schema\fB\fR
The name of the schema to which the table will be moved.
.SH "NOTES"
.PP
The key word COLUMN is noise and can be omitted.
.PP
When a column is added with ADD COLUMN, all existing
rows in the table are initialized with the column's default value
(NULL if no DEFAULT clause is specified).
.PP
Adding a column with a non-null default or changing the type of an
existing column will require the entire table to be rewritten. This
may take a significant amount of time for a large table; and it will
temporarily require double the disk space.
.PP
Adding a CHECK or NOT NULL constraint requires
scanning the table to verify that existing rows meet the constraint.
.PP
The main reason for providing the option to specify multiple changes
in a single \fBALTER TABLE\fR is that multiple table scans or
rewrites can thereby be combined into a single pass over the table.
.PP
The DROP COLUMN form does not physically remove
the column, but simply makes it invisible to SQL operations. Subsequent
insert and update operations in the table will store a null value for the
column. Thus, dropping a column is quick but it will not immediately
reduce the on-disk size of your table, as the space occupied 
by the dropped column is not reclaimed. The space will be
reclaimed over time as existing rows are updated.
.PP
The fact that ALTER TYPE requires rewriting the whole table
is sometimes an advantage, because the rewriting process eliminates
any dead space in the table. For example, to reclaim the space occupied
by a dropped column immediately, the fastest way is
.sp
.nf
ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
.sp
.fi
where anycol is any remaining table column and
anytype is the same type that column already has.
This results in no semantically-visible change in the table,
but the command forces rewriting, which gets rid of no-longer-useful
data.
.PP
The USING option of ALTER TYPE can actually
specify any expression involving the old values of the row; that is, it
can refer to other columns as well as the one being converted. This allows
very general conversions to be done with the ALTER TYPE
syntax. Because of this flexibility, the USING
expression is not applied to the column's default value (if any); the
result might not be a constant expression as required for a default.
This means that when there is no implicit or assignment cast from old to
new type, ALTER TYPE may fail to convert the default even
though a USING clause is supplied. In such cases,
drop the default with DROP DEFAULT, perform the ALTER
TYPE, and then use SET DEFAULT to add a suitable new
default. Similar considerations apply to indexes and constraints involving
the column.
.PP
If a table has any descendant tables, it is not permitted to add,
rename, or change the type of a column in the parent table without doing
the same to the descendants. That is, \fBALTER TABLE ONLY\fR
will be rejected. This ensures that the descendants always have
columns matching the parent.
.PP
A recursive DROP COLUMN operation will remove a
descendant table's column only if the descendant does not inherit
that column from any other parents and never had an independent
definition of the column. A nonrecursive DROP
COLUMN (i.e., \fBALTER TABLE ONLY ... DROP
COLUMN\fR) never removes any descendant columns, but
instead marks them as independently defined rather than inherited.
.PP
The TRIGGER, CLUSTER, OWNER,
and TABLESPACE actions never recurse to descendant tables;
that is, they always act as though ONLY were specified.
Adding a constraint can recurse only for CHECK constraints.
.PP
Changing any part of a system catalog table is not permitted.
.PP
Refer to CREATE TABLE [\fBcreate_table\fR(7)] for a further description of valid
parameters. in the documentation has further information on
inheritance.
.SH "EXAMPLES"
.PP
To add a column of type \fBvarchar\fR to a table:
.sp
.nf
ALTER TABLE distributors ADD COLUMN address varchar(30);
.sp
.fi
.PP
To drop a column from a table:
.sp
.nf
ALTER TABLE distributors DROP COLUMN address RESTRICT;
.sp
.fi
.PP
To change the types of two existing columns in one operation:
.sp
.nf
ALTER TABLE distributors
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);
.sp
.fi
.PP
To change an integer column containing UNIX timestamps to \fBtimestamp
with time zone\fR via a USING clause:
.sp
.nf
ALTER TABLE foo
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
.sp
.fi
.PP
The same, when the column has a default expression that won't automatically
cast to the new data type:
.sp
.nf
ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();
.sp
.fi
.PP
To rename an existing column:
.sp
.nf
ALTER TABLE distributors RENAME COLUMN address TO city;
.sp
.fi
.PP
To rename an existing table:
.sp
.nf
ALTER TABLE distributors RENAME TO suppliers;
.sp
.fi
.PP
To add a not-null constraint to a column:
.sp
.nf
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
.sp
.fi
To remove a not-null constraint from a column:
.sp
.nf
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
.sp
.fi
.PP
To add a check constraint to a table:
.sp
.nf
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
.sp
.fi
.PP
To remove a check constraint from a table and all its children:
.sp
.nf
ALTER TABLE distributors DROP CONSTRAINT zipchk;
.sp
.fi
.PP
To add a foreign key constraint to a table:
.sp
.nf
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
.sp
.fi
.PP
To add a (multicolumn) unique constraint to a table:
.sp
.nf
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
.sp
.fi
.PP
To add an automatically named primary key constraint to a table, noting
that a table can only ever have one primary key:
.sp
.nf
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
.sp
.fi
.PP
To move a table to a different tablespace:
.sp
.nf
ALTER TABLE distributors SET TABLESPACE fasttablespace;
.sp
.fi
.PP
To move a table to a different schema:
.sp
.nf
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
.sp
.fi
.SH "COMPATIBILITY"
.PP
The ADD, DROP, and SET DEFAULT
forms conform with the SQL standard. The other forms are
PostgreSQL extensions of the SQL standard.
Also, the ability to specify more than one manipulation in a single
\fBALTER TABLE\fR command is an extension.
.PP
\fBALTER TABLE DROP COLUMN\fR can be used to drop the only
column of a table, leaving a zero-column table. This is an
extension of SQL, which disallows zero-column tables.