PostgreSQL支持ALTER TABLE语句来修改现有表的结构。

ALTER TABLE 语法可用于

  1. 添加列
  2. 重命名列
  3. 修改列数据类型
  4. 设置或删除列的默认值
  5. 添加或删除列约束
  6. 删除表中的列

ALTER TABLE 语句的一般语法:

ALTER TABLE action

Where action can be:
ADD [ COLUMN ] 
DROP COLUMN
ALTER COLUMN TYPE
ALTER COLUMN SET DEFAULT
ALTER COLUMN DROP DEFAULT 
ALTER COLUMN SET attribute_option
ALTER COLUMN RESET 
ADD table_constraint
VALIDATE CONSTRAINT
DISABLE TRIGGER
ENABLE TRIGGER
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

考虑以下employee表:

让我们看看 ALTER TABLE 语句的概述:

要将 varchar 类型的列添加到表中:

ALTER TABLE employee ADD COLUMN address varchar(50);

要从表中删除列:

ALTER TABLE employee DROP COLUMN employee CASCADE;

要在一项操作中更改两个现有列的类型:

ALTER TABLE employee
    ALTER COLUMN first_name TYPE varchar(80),
    ALTER COLUMN last_name TYPE varchar(80);

要重命名现有列:

ALTER TABLE employee RENAME COLUMN birthdate TO BoD;

要重命名现有表:

ALTER TABLE employee RENAME TO employee_info;

向列添加 NOT NULL 约束:

ALTER TABLE employee ALTER COLUMN gender SET NOT NULL;

要从列中删除 NOT NULL 约束:

ALTER TABLE employee ALTER COLUMN salary DROP NOT NULL;

向表及其所有子表添加检查约束:

ALTER TABLE employee ADD CONSTRAINT first_name CHECK (char_length(first_name) > 1);

要从表及其所有子表中删除检查约束:

ALTER TABLE employee DROP CONSTRAINT first_name;
ALTER TABLE ONLY employee DROP CONSTRAINT first_name; -- 仅从一个表中删除检查约束

要在表中添加主键:

ALTER TABLE employee ADD PRIMARY KEY (id);

要将表移动到不同的表空间:

ALTER TABLE employee SET TABLESPACE emptablespace;

要将表移动到不同的架构:

ALTER TABLE myschema.employee SET SCHEMA yourschema;

在下一章中了解所有 ALTER TABLE 语句。