Postgresql 简明教程

PostgreSQL - ALTER TABLE Command

PostgreSQL ALTER TABLE 命令用于添加、删除或修改现有表中的列。

The PostgreSQL ALTER TABLE command is used to add, delete or modify columns in an existing table.

您还将使用 ALTER TABLE 命令在现有表中添加和删除各种约束。

You would also use ALTER TABLE command to add and drop various constraints on an existing table.

Syntax

ALTER TABLE 用于在现有表中添加新列的基本语法如下 −

The basic syntax of ALTER TABLE to add a new column in an existing table is as follows −

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE 用于 DROP COLUMN 现有表中的语法如下所示：

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows −

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE 用于更改表中列的 DATA TYPE 的基本语法如下 −

The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is as follows −

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

ALTER TABLE 用于向表中列添加 NOT NULL 约束的基本语法如下 −

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as follows −

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

ALTER TABLE 用于 ADD UNIQUE CONSTRAINT 表的基本语法如下 −

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows −

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

ALTER TABLE 用于 ADD CHECK CONSTRAINT 表的基本语法如下 −

The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows −

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

ALTER TABLE 用于 ADD PRIMARY KEY 表约束的基本语法如下 −

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows −

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

ALTER TABLE 用于 DROP CONSTRAINT 表的基本语法如下 −

The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows −

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

如果您使用 MySQL，则代码如下 −

If you are using MySQL, the code is as follows −

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

ALTER TABLE 用于 DROP PRIMARY KEY 表约束的基本语法如下 −

The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is as follows −

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

如果您使用 MySQL，则代码如下 −

If you are using MySQL, the code is as follows −

ALTER TABLE table_name
DROP PRIMARY KEY;

Example

考虑我们的 COMPANY 表有以下记录 −

Consider our COMPANY table has the following records −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

以下是向现有表中 ADD 新列的示例 −

The following is the example to ADD a new column in an existing table −

testdb=# ALTER TABLE COMPANY ADD GENDER char(1);

现在，COMPANY 表已更改，SELECT 语句的输出将如下所示 −

Now, COMPANY table is changed and the following would be the output from SELECT statement −

 id | name  | age | address     | salary | gender
----+-------+-----+-------------+--------+--------
  1 | Paul  |  32 | California  |  20000 |
  2 | Allen |  25 | Texas       |  15000 |
  3 | Teddy |  23 | Norway      |  20000 |
  4 | Mark  |  25 | Rich-Mond   |  65000 |
  5 | David |  27 | Texas       |  85000 |
  6 | Kim   |  22 | South-Hall  |  45000 |
  7 | James |  24 | Houston     |  10000 |
(7 rows)

以下是删除现有表格中性别列的示例 -

The following is the example to DROP gender column from existing table −

testdb=# ALTER TABLE COMPANY DROP GENDER;

现在，COMPANY 表已更改，SELECT 语句的输出将如下所示 −

Now, COMPANY table is changed and the following would be the output from SELECT statement −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000