ALTER TABLE

语法铁道图 PostgreSQL 18

锁模式 PostgreSQL 18

具体锁模式取决于操作变体、目标对象和执行阶段；同一命令可同时取得表锁与行锁。查看完整冲突矩阵

ACCESS SHARE · 表级锁

ALTER TABLE … NO INHERIT（父表）：检查并解除继承关系时，在父表上取得 ACCESS SHARE；被修改的子表另取 ACCESS EXCLUSIVE。

ROW SHARE · 表级锁

ALTER TABLE … VALIDATE CONSTRAINT（外键被引用表）：验证外键时，被引用表另取 ROW SHARE；正在验证约束的表取 SHARE UPDATE EXCLUSIVE。

SHARE UPDATE EXCLUSIVE · 表级锁

ALTER TABLE … ALTER COLUMN … SET STATISTICS：修改列统计目标。

ALTER TABLE … ALTER COLUMN … SET (n_distinct = …)：修改列统计选项；RESET 同样适用。

ALTER TABLE … CLUSTER ON：设置后续聚簇使用的索引；此命令本身不重写表。

ALTER TABLE … SET WITHOUT CLUSTER：清除聚簇索引标记。

ALTER TABLE … VALIDATE CONSTRAINT：在当前表上验证约束；外键验证还需要访问被引用表。

ALTER TABLE … ATTACH PARTITION（父表）：父表锁级别逐版本解析；待挂载分区另取 ACCESS EXCLUSIVE。PostgreSQL 11 起如有默认分区，也需锁定默认分区。

ALTER TABLE … DETACH PARTITION … FINALIZE（父表）：完成中断的并发分离；被分离分区另取 ACCESS EXCLUSIVE。

ALTER TABLE … SET / RESET (fillfactor = …)：表存储参数按参数定义选择锁；fillfactor 可用 SHARE UPDATE EXCLUSIVE。

ALTER TABLE … SET / RESET (autovacuum_* = …)：表的自动清理存储参数使用 SHARE UPDATE EXCLUSIVE。

ALTER TABLE … SET / RESET (toast.autovacuum_* = …)：这里给出 TOAST 自动清理参数；不存在笼统的 SET TOAST 子命令，其他参数按具体定义选择锁。

ALTER TABLE … DETACH PARTITION … CONCURRENTLY（父表）：PostgreSQL 14 起，分两个事务进行；父表使用 SHARE UPDATE EXCLUSIVE，等待使用分区的事务结束后进入完成阶段。

ALTER TABLE … DETACH PARTITION … CONCURRENTLY（分区，第一阶段）：第一阶段父表与分区使用 SHARE UPDATE EXCLUSIVE；提交并等待后再升级分区锁。

ALTER TABLE … INHERIT（父表）：在新指定的继承父表上取得 SHARE UPDATE EXCLUSIVE；被修改的子表另取 ACCESS EXCLUSIVE。

SHARE ROW EXCLUSIVE · 表级锁

ALTER TABLE … ENABLE / DISABLE TRIGGER：启停触发器，包括 USER / ALL / REPLICA / ALWAYS 相应形式。

ALTER TABLE … ADD FOREIGN KEY [NOT VALID]（引用表）：引用方与被引用方都取得 SHARE ROW EXCLUSIVE；NOT VALID 只省略已有数据的初始验证，不免除这个锁。

ALTER TABLE … ADD FOREIGN KEY [NOT VALID]（被引用表）：与引用方同样取得 SHARE ROW EXCLUSIVE，不能把被引用表记为只有 ACCESS SHARE。

ACCESS EXCLUSIVE · 表级锁

ALTER TABLE … ADD COLUMN：是否需要重写取决于版本与默认表达式；避免重写并不降低这里的锁级别。

ALTER TABLE … DROP COLUMN：删除列并改变表结构。

ALTER TABLE … ALTER COLUMN … SET / DROP DEFAULT：修改列默认值。

ALTER TABLE … ALTER COLUMN … TYPE：改变列类型；是否重写不改变这里列出的锁要求。

ALTER TABLE … ALTER COLUMN … SET STORAGE：修改列存储策略。

ALTER TABLE … ALTER COLUMN … SET COMPRESSION：修改列压缩方法。

ALTER TABLE … SET TABLESPACE：移动表的表空间。

ALTER TABLE … SET LOGGED / UNLOGGED：切换日志持久性。

ALTER TABLE … SET ACCESS METHOD：修改表访问方法。

ALTER TABLE … DROP CONSTRAINT：删除约束及可能依赖的索引。

ALTER TABLE … ALTER CONSTRAINT：修改约束属性。

ALTER TABLE … ALTER COLUMN … SET NOT NULL：设置非空约束。

ALTER TABLE … ALTER COLUMN … DROP NOT NULL：移除非空约束。

ALTER TABLE … ENABLE / DISABLE RULE：启停规则。

ALTER TABLE … ENABLE / DISABLE ROW LEVEL SECURITY：启停行级安全策略。

ALTER TABLE … FORCE / NO FORCE ROW LEVEL SECURITY：改变表所有者是否受行级安全策略约束。

ALTER TABLE … ALTER COLUMN … DROP EXPRESSION：移除生成列表达式。

ALTER TABLE … ALTER COLUMN … SET EXPRESSION：设置生成列表达式。

ALTER TABLE … ALTER COLUMN … SET GENERATED / RESTART / SET sequence_option：修改标识列属性或其隐式序列选项；不是名为 SET SEQUENCE 的独立子命令。

ALTER TABLE … ALTER COLUMN … ADD GENERATED … AS IDENTITY：为列增加标识属性。

ALTER TABLE … ALTER COLUMN … DROP IDENTITY：移除标识属性。

ALTER TABLE … INHERIT / NO INHERIT（子表）：此处列出被修改的子表；父表另按继承处理锁定。

ALTER TABLE … OWNER TO：修改表所有者。

ALTER TABLE … REPLICA IDENTITY：修改复制标识。

ALTER TABLE … RENAME：重命名表、列或约束。

ALTER TABLE … ADD CONSTRAINT（CHECK / UNIQUE / PRIMARY KEY / EXCLUDE）：普通添加约束；ADD FOREIGN KEY 使用较弱的 SHARE ROW EXCLUSIVE。

ALTER TABLE … ATTACH PARTITION（待挂载分区）：待挂载表需 ACCESS EXCLUSIVE；合适的 CHECK 约束可以免除验证扫描，但不免除对这张表的锁。

ALTER TABLE … ATTACH PARTITION（默认分区）：PostgreSQL 11 起如有默认分区，对其取得 ACCESS EXCLUSIVE；合适 CHECK 约束可免扫描，不能免除默认分区本身的锁。递归子分区另按验证需要处理。

ALTER TABLE … DETACH PARTITION（父表）：未使用 CONCURRENTLY 的常规分离。

ALTER TABLE … DETACH PARTITION（被分离分区）：常规分离在被分离分区上取得 ACCESS EXCLUSIVE。

ALTER TABLE … DETACH PARTITION … CONCURRENTLY（分区，完成阶段）：第二阶段在被分离分区取得 ACCESS EXCLUSIVE；不允许存在默认分区时使用 CONCURRENTLY。

语法概要

相对 PostgreSQL 17 新增 12 行，移除 0 行；新增行以绿色标记。

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

其中action为以下之一：

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
ALTER [ COLUMN ] column_name SET EXPRESSION AS ( expression )
ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
ALTER [ COLUMN ] column_name SET STATISTICS { integer | DEFAULT }
ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
ADD table_constraint [ NOT VALID ]
ADD table_constraint_using_index
ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]
ALTER CONSTRAINT constraint_name [ INHERIT | NO INHERIT ]
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
DISABLE ROW LEVEL SECURITY
ENABLE ROW LEVEL SECURITY
FORCE ROW LEVEL SECURITY
NO FORCE ROW LEVEL SECURITY
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
SET ACCESS METHOD { new_access_method | DEFAULT }
SET TABLESPACE new_tablespace
SET { LOGGED | UNLOGGED }
SET ( storage_parameter [= value] [, ... ] )
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

而partition_bound_spec为：

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

而column_constraint为：

[ CONSTRAINT constraint_name ]
{ NOT NULL [ NO INHERIT ] |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ] |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]

而table_constraint为：

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
NOT NULL column_name [ NO INHERIT ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD refcolumn ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]

而table_constraint_using_index为：

[ CONSTRAINT constraint_name ]
{ UNIQUE | PRIMARY KEY } USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

UNIQUE、PRIMARY KEY和EXCLUDE约束中的index_parameters为：

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

EXCLUDE约束中的exclude_element为：

{ column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

FOREIGN KEY/REFERENCES约束中的referential_action为：

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

描述

参数

注解

示例

ALTER TABLE distributors ADD COLUMN address varchar(30);

ALTER TABLE measurements
  ADD COLUMN mtime timestamp with time zone DEFAULT now();

ALTER TABLE transactions
  ADD COLUMN status varchar(30) DEFAULT 'old',
  ALTER COLUMN status SET default 'current';

ALTER TABLE distributors DROP COLUMN address RESTRICT;

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

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';

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

ALTER TABLE distributors RENAME COLUMN address TO city;

ALTER TABLE distributors RENAME TO suppliers;

ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

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

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

ALTER TABLE distributors DROP CONSTRAINT zipchk;

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

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

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;

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

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

ALTER TABLE distributors SET TABLESPACE fasttablespace;

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

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;

ALTER TABLE measurement
    ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

ALTER TABLE cities
    ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

ALTER TABLE orders
    ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

ALTER TABLE cities
    ATTACH PARTITION cities_partdef DEFAULT;

ALTER TABLE measurement
    DETACH PARTITION measurement_y2015m12;

兼容性

另见

语法演化

相邻大版本之间的差异，新的在前。版本号链接到对应快照。

1. PostgreSQL 19← 18正文更新

   正文更新

2. PostgreSQL 18← 17语法变化

   + ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]+ ALTER CONSTRAINT constraint_name [ INHERIT | NO INHERIT ]+ { NOT NULL [ NO INHERIT ] |+ GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ] |+ [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]+ [ CONSTRAINT constraint_name ]+ { CHECK ( expression ) [ NO INHERIT ] |+ NOT NULL column_name [ NO INHERIT ] |+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |+ PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters |+ FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD refcolumn ] ) ]+ [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ]正文更新

3. PostgreSQL 17← 16语法变化

   + ALTER [ COLUMN ] column_name SET EXPRESSION AS ( expression )+ ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]+ ALTER [ COLUMN ] column_name SET STATISTICS { integer | DEFAULT }+ SET ACCESS METHOD { new_access_method | DEFAULT }正文更新

4. PostgreSQL 16← 15语法变化

   + ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }正文更新

5. PostgreSQL 15← 14语法变化

   + SET ACCESS METHOD new_access_method+ SET TABLESPACE new_tablespace+ UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |+ { NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }正文更新

6. PostgreSQL 14← 13语法变化

   + DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]+ ALTER [ COLUMN ] column_name SET COMPRESSION compression_method+ OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }正文更新

7. PostgreSQL 13← 12语法变化

   + ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]+ { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]正文更新

8. PostgreSQL 12← 11语法变化

   + IN ( partition_bound_expr [, ...] ) |+ FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )+ TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |+ GENERATED ALWAYS AS ( generation_expr ) STORED |+ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |+ [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }+ [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }− SET WITH OIDS− SET WITHOUT OIDS− IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |− FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )− TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) |− [ ON DELETE action ] [ ON UPDATE action ] }− [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }正文更新

9. PostgreSQL 11← 10语法变化

   + ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }+ IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |+ FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )+ TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) |+ WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )+ [ CONSTRAINT constraint_name ]+ { NOT NULL |+ NULL |+ CHECK ( expression ) [ NO INHERIT ] |+ DEFAULT default_expr |+ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |+ UNIQUE index_parameters |+ PRIMARY KEY index_parameters |+ REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]+ [ ON DELETE action ] [ ON UPDATE action ] }+ [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]+ [ CONSTRAINT constraint_name ]+ { CHECK ( expression ) [ NO INHERIT ] |+ UNIQUE ( column_name [, ... ] ) index_parameters |+ PRIMARY KEY ( column_name [, ... ] ) index_parameters |+ EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |+ FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]+ [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }+ [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]+ [ INCLUDE ( column_name [, ... ] ) ]+ [ WITH ( storage_parameter [= value] [, ... ] ) ]+ [ USING INDEX TABLESPACE tablespace_name ]+ { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]正文更新

10. PostgreSQL 10← 9.6语法变化

    + ALTER TABLE [ IF EXISTS ] name+ ATTACH PARTITION partition_name FOR VALUES partition_bound_spec+ ALTER TABLE [ IF EXISTS ] name+ DETACH PARTITION partition_name+ ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]+ ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]+ ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]正文更新

11. PostgreSQL 9.6← 9.5语法变化

    + ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]正文更新

12. PostgreSQL 9.5← 9.4语法变化

    + DISABLE ROW LEVEL SECURITY+ ENABLE ROW LEVEL SECURITY+ FORCE ROW LEVEL SECURITY+ NO FORCE ROW LEVEL SECURITY+ SET TABLESPACE new_tablespace+ SET { LOGGED | UNLOGGED }+ SET ( storage_parameter [= value] [, ... ] )+ OWNER TO { new_owner | CURRENT_USER | SESSION_USER }− SET TABLESPACE new_tablespace正文更新

13. PostgreSQL 9.4← 9.3语法变化

    + ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]+ SET TABLESPACE new_tablespace [ NOWAIT ]+ ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]+ REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING}正文更新

14. PostgreSQL 9.3← 9.2正文更新

    正文更新

15. PostgreSQL 9.2← 9.1语法变化

    + ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]+ ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]+ RENAME [ COLUMN ] column_name TO new_column_name+ ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]+ RENAME CONSTRAINT constraint_name TO new_constraint_name+ ALTER TABLE [ IF EXISTS ] name+ ALTER TABLE [ IF EXISTS ] name+ ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]+ DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]+ ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]+ ALTER [ COLUMN ] column_name SET DEFAULT expression+ ALTER [ COLUMN ] column_name DROP DEFAULT+ ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL+ ALTER [ COLUMN ] column_name SET STATISTICS integer+ ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )+ ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )+ ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }− RENAME [ COLUMN ] column TO new_column− 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 expression ]− ALTER [ COLUMN ] column SET DEFAULT expression− 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 }正文更新

16. PostgreSQL 9.1← 9.0语法变化

    + ADD [ COLUMN ] column data_type [ COLLATE collation ] [ column_constraint [ ... ] ]+ ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]+ ADD table_constraint [ NOT VALID ]+ ADD table_constraint_using_index+ VALIDATE CONSTRAINT constraint_name+ OF type_name+ NOT OF+ [ CONSTRAINT constraint_name ]+ { UNIQUE | PRIMARY KEY } USING INDEX index_name+ [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]− ADD [ COLUMN ] column type [ column_constraint [ ... ] ]− ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]正文更新

17. PostgreSQL 9.0← 8.4语法变化

    + DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]+ ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )+ ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] )+ ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }+ DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]− where action is one of:正文更新

18. PostgreSQL 8.4← 8.3语法变化

    + ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]+ SET WITH OIDS+ SET WITHOUT OIDS正文更新

19. PostgreSQL 8.3← 8.2语法变化

    + 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正文更新

20. PostgreSQL 8.2← 8.1语法变化

    + SET ( storage_parameter = value [, ... ] )+ RESET ( storage_parameter [, ... ] )+ INHERIT parent_table+ NO INHERIT parent_table正文更新

21. PostgreSQL 8.1← 8.0语法变化

    + ALTER TABLE name+ SET SCHEMA new_schema+ DISABLE TRIGGER [ trigger_name | ALL | USER ]+ ENABLE TRIGGER [ trigger_name | ALL | USER ]+ SET TABLESPACE new_tablespace− SET TABLESPACE tablespace_name正文更新

22. PostgreSQL 8.0← 7.4语法变化

    + action [, ... ]+ where action is one of:+ ADD [ COLUMN ] column type [ column_constraint [ ... ] ]+ DROP [ COLUMN ] column [ RESTRICT | CASCADE ]+ ALTER [ COLUMN ] column TYPE type [ USING expression ]+ ALTER [ COLUMN ] column SET DEFAULT expression+ ALTER [ COLUMN ] column DROP DEFAULT+ ALTER [ COLUMN ] column { SET | DROP } NOT NULL+ ALTER [ COLUMN ] column SET STATISTICS integer+ ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }+ CLUSTER ON index_name+ SET WITHOUT CLUSTER+ SET WITHOUT OIDS+ SET TABLESPACE tablespace_name− ADD [ COLUMN ] column type [ column_constraint [ ... ] ]− ALTER TABLE [ ONLY ] name [ * ]− DROP [ COLUMN ] column [ RESTRICT | CASCADE ]− ALTER TABLE [ ONLY ] name [ * ]− ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }− ALTER TABLE [ ONLY ] name [ * ]− ALTER [ COLUMN ] column { SET | DROP } NOT NULL− ALTER TABLE [ ONLY ] name [ * ]− ALTER [ COLUMN ] column SET STATISTICS integer− ALTER TABLE [ ONLY ] name [ * ]− ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }− ALTER TABLE [ ONLY ] name [ * ]− SET WITHOUT OIDS− ALTER TABLE [ ONLY ] name [ * ]− ALTER TABLE [ ONLY ] name [ * ]− ALTER TABLE name− ALTER TABLE name− CLUSTER ON index_name正文更新

23. PostgreSQL 7.4← 7.3语法变化

    + ALTER TABLE [ ONLY ] name [ * ]+ ALTER TABLE [ ONLY ] name [ * ]+ ALTER TABLE [ ONLY ] name [ * ]+ ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }+ ALTER TABLE [ ONLY ] name [ * ]+ ALTER TABLE [ ONLY ] name [ * ]+ ALTER TABLE [ ONLY ] name [ * ]+ ALTER TABLE [ ONLY ] name [ * ]+ SET WITHOUT OIDS+ ALTER TABLE [ ONLY ] name [ * ]+ ALTER TABLE name+ RENAME TO new_name+ ALTER TABLE [ ONLY ] name [ * ]+ ALTER TABLE [ ONLY ] name [ * ]+ ALTER TABLE name+ ALTER TABLE name+ CLUSTER ON index_name− ALTER TABLE [ ONLY ] table [ * ]− ALTER TABLE [ ONLY ] table [ * ]− ALTER TABLE [ ONLY ] table [ * ]− ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }− ALTER TABLE [ ONLY ] table [ * ]− ALTER TABLE [ ONLY ] table [ * ]− ALTER TABLE [ ONLY ] table [ * ]− ALTER TABLE [ ONLY ] table [ * ]− ALTER TABLE table− RENAME TO new_table− ALTER TABLE [ ONLY ] table [ * ]− ALTER TABLE [ ONLY ] table [ * ]− ALTER TABLE table正文更新

24. PostgreSQL 7.3← 7.2语法变化

    + ADD [ COLUMN ] column type [ column_constraint [ ... ] ]+ ALTER TABLE [ ONLY ] table [ * ]+ DROP [ COLUMN ] column [ RESTRICT | CASCADE ]+ ALTER [ COLUMN ] column { SET | DROP } NOT NULL+ ALTER TABLE [ ONLY ] table [ * ]+ ALTER [ COLUMN ] column SET STATISTICS integer+ ALTER TABLE [ ONLY ] table [ * ]+ ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }+ RENAME [ COLUMN ] column TO new_column+ ALTER TABLE [ ONLY ] table [ * ]+ ADD table_constraint+ ALTER TABLE [ ONLY ] table [ * ]+ DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]− RENAME [ COLUMN ] column TO newcolumn− ADD table_constraint_definition− DROP CONSTRAINT constraint { RESTRICT | CASCADE }正文更新

25. PostgreSQL 7.2← 7.1语法变化

    + ADD [ COLUMN ] column type [ column_constraint [ ... ] ]+ ALTER TABLE [ ONLY ] table [ * ]+ ALTER [ COLUMN ] column SET STATISTICS integer+ ALTER TABLE [ ONLY ] table [ * ]+ RENAME TO new_table+ ADD table_constraint_definition+ ALTER TABLE [ ONLY ] table+ DROP CONSTRAINT constraint { RESTRICT | CASCADE }+ OWNER TO new_owner− RENAME TO newtable− ADD table constraint definition− OWNER TO new owner正文更新

26. PostgreSQL 7.1← 7.0语法变化

    sql-altertable.htm → sql-altertable.html

    + ALTER TABLE [ ONLY ] table [ * ]+ ALTER TABLE [ ONLY ] table [ * ]+ ALTER TABLE table+ OWNER TO new owner正文更新

27. PostgreSQL 7.0← 6.5语法变化

    + ADD [ COLUMN ] column type+ ALTER TABLE table [ * ]+ ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }+ ALTER TABLE table [ * ]+ RENAME [ COLUMN ] column TO newcolumn+ ALTER TABLE table+ ADD table constraint definition− [ * ] ADD [ COLUMN ] ER">coBLE> type− [ * ] RENAME [ COLUMN ] ER">coBLE> TO newcolumn正文更新

28. PostgreSQL 6.5← 6.4语法变化

    + [ * ] ADD [ COLUMN ] ER">coBLE> type+ [ * ] RENAME [ COLUMN ] ER">coBLE> TO newcolumn− [ * ] ADD [ COLUMN ] column type− ALTER TABLE table− [ * ] RENAME [ COLUMN ] column TO newcolumn正文更新

相关命令

CREATE TABLE

同组命令