PostgreSQL ALTER TABLE(EN)

长平狐 发布于 2012/09/06 15:10
阅读 125
收藏 0

Name

ALTER TABLE -- change the definition of a table

Synopsis

ALTER TABLE [ ONLY ] name [ * ] action [ , ... ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name ALTER TABLE name SET SCHEMA new_schema where action is one of : ADD [ COLUMN ] column data_type [ COLLATE collation ] [ column_constraint [ ... ] ] DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ] ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING exssion ] ALTER [ COLUMN ] column SET DEFAULT exssion ALTER [ COLUMN ] column DROP DEFAULT ALTER [ COLUMN ] column { SET | DROP } NOT NULL ALTER [ COLUMN ] column SET STATISTICS integer ALTER [ COLUMN ] column SET ( attribute_option = value [ , ... ] ) ALTER [ COLUMN ] column RESET ( attribute_option [ , ... ] ) ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name ENABLE ALWAYS TRIGGER trigger_name DISABLE RULE rewrite_rule_name ENABLE RULE rewrite_rule_name ENABLE REPLICA RULE rewrite_rule_name ENABLE ALWAYS RULE rewrite_rule_name CLUSTER ON index_name SET WITHOUT CLUSTER SET WITH OIDS SET WITHOUT OIDS SET ( storage_parameter = value [ , ... ] ) RESET ( storage_parameter [ , ... ] ) INHERIT parent_table NO INHERIT parent_table OF type_name NOT OF OWNER TO new_owner SET TABLESPACE new_tablespace and table_constraint_using_index is : [ CONSTRAINT constraint_name ] { UNIQUE | PRIMARY KEY } USING INDEX index_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Examples

To add a column of type varchar to a table:

ALTER TABLE distributors ADD COLUMN address varchar( 30);

To drop a column from a table:

ALTER TABLE distributors DROP COLUMN address RESTRICT;

To change the types of two existing columns in one operation:

ALTER TABLE distributors ALTER COLUMN address TYPE varchar( 80 ), ALTER COLUMN name TYPE varchar( 100);

To change an integer column containing UNIX timestamps to timestamp with time zone via a USING clause:

ALTER TABLE foo ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone USING timestamp with time zone ' epoch ' + foo_timestamp * interval ' 1 second ';

The same, when the column has a default exssion that won't automatically cast to the new data type:

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();

To rename an existing column:

ALTER TABLE distributors RENAME COLUMN address TO city;

To rename an existing table:

ALTER TABLE distributors RENAME TO suppliers;

To add a not-null constraint to a column:

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

To remove a not-null constraint from a column:

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

To add a check constraint to a table and all its children:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

To remove a check constraint from a table and all its children:

ALTER TABLE distributors DROP CONSTRAINT zipchk;

To remove a check constraint from a table only:

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

(The check constraint remains in place for any child tables.)

To add a foreign key constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;

To add a (multicolumn) unique constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

To add an automatically named primary key constraint to a table, noting that a table can only ever have one primary key:

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

To move a table to a different tablespace:

ALTER TABLE distributors SET TABLESPACE fasttablespace;

To move a table to a different schema:

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

To recreate a primary key constraint, without blocking updates while the index is rebuilt:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

 

http://www.postgresql.org/docs/current/static/sql-altertable.html


原文链接:http://www.cnblogs.com/mchina/archive/2012/08/08/2609758.html
加载中
返回顶部
顶部