PostgreSQL 管理数据表（二）

修改表

当我们创建好一个表之后，可能会由于业务变更或者其他原因需要修改它的结构。PostgreSQL使ALTER TABLE语句修改表的定义：

ALTER TABLE name action;

其中的action表示要执行的操作。常见的修改操作包括：

• 添加字段
• 删除字段
• 添加约束
• 删除约束
• 修改字段默认值
• 修改字段数据类型
• 重命名字段
• 重命名表

添加字段

为表添加一个字段的命令如下：

ALTER TABLE table_nameADD COLUMN column_name data_type column_constraint;

添加字段与创建表时的字段选项相同，包含字段名称、字段类型以及可选的约束。假设我们已经创建了一个产品表products：

CREATE TABLE products
(
    product_no integer PRIMARY KEY,
    name       text,
    price      numeric
);

通过以下语句为产品表增加一个新的字段product_desc

alter table products add column product_desc text;

对于表中已有的数据，新增加的列将会使用默认值进行填充；如果没有指定DEFAULT值，使用空值填充。添加字段时还可以定义约束。不过需要注意的是，如果表中已经存在数据，新增字段的默认值有可能会违反指定的约束。例如：

test=# INSERT INTO products (product_no, name, price) VALUES (1001, '4G校园套餐（折扣版）', 18.2);
INSERT 0 1                    ^
test=# select * from products;
 product_no |         name         | price 
------------+----------------------+-------
       1001 | 4G校园套餐（折扣版） |  18.2
test=# alter table products add column product_desc text not null;
ERROR:  column "product_desc" contains null values

以上语句出错的原因在于新增的字段notes存在非空约束，但是对于已有的数据该字段的值为空。解决的方法有两个：添加约束的同时指定一个默认值；添加字段时不指定约束，将所有数据的字段值手动填充（UPDATE）之后，再添加约束。以下语句为新增的字段指定了一个默认值：

test=# aLTER TABLE products ADD COLUMN notes text DEFAULT'new product' not null;
ALTER TABLE
test=# select * from products;
 product_no |         name         | price |    notes    
------------+----------------------+-------+-------------
       1001 | 4G校园套餐（折扣版） |  18.2 | new product

删除字段

删除一个字段的语句如下：

ALTER TABLE table_name DROP COLUMN column_name;

将产品表中的notes字段删除：

test=# \d products;
                        Table "hr.products"
   Column   |  Type   | Collation | Nullable |       Default       
------------+---------+-----------+----------+---------------------
 product_no | integer |           | not null | 
 name       | text    |           |          | 
 price      | numeric |           |          | 
 notes      | text    |           | not null | 'new product'::text
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_no)

test=# alter table products  drop  column notes;
ALTER TABLE
test=# \d products;
                  Table "hr.products"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 product_no | integer |           | not null | 
 name       | text    |           |          | 
 price      | numeric |           |          | 
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_no)

test=# 

删除字段后，相应的数据也会自动删除。同时，该字段上的索引或约束（products_description_check）也会同时被删除。但是，如果该字段被其他对象（例如外键引用、视图、存储过程等）引用，无法直接删除。

test=# ALTER TABLE departments DROP COLUMN department_id;
ERROR:  cannot drop column department_id of table departments because other objects depend on it
DETAIL:  constraint emp_dept_fk on table employees depends on column department_id of table departments
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

由于departments表的department_id是employees表的外键引用列，无法直接删除该字段。通过提示可以看出，在DROP的最后加上CASCADE选项即可级联删除依赖的对象。

ALTER TABLE departments DROP COLUMN department_id CASCADE;

字段department_id被删除，同时employees表中的外键也被级联删除。

添加约束

添加约束时通常使用表级约束语法：

ALTER TABLE table_name ADD table_constraint;

其中，table_constraint可以参考前文。以下是为产品表products增加约束的一些示例： 

test=# ALTER TABLE products ADD CONSTRAINT products_price_min CHECK (price > 0);
ALTER TABLE
test=# ALTER TABLE products ADD CONSTRAINT products_name_uk UNIQUE (name);
ALTER TABLE

对于非空约束（NOT NULL），可以使用以下语法：

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

将产品表的name字段设置为非空：

test=# \d products;
                  Table "hr.products"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 product_no | integer |           | not null | 
 name       | text    |           |          | 
 price      | numeric |           |          | 
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_no)
    "products_name_uk" UNIQUE CONSTRAINT, btree (name)
Check constraints:
    "products_price_min" CHECK (price > 0::numeric)

test=# ALTER TABLE products ALTER COLUMN name SET NOT NULL;
ALTER TABLE
test=# \d products;
                  Table "hr.products"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 product_no | integer |           | not null | 
 name       | text    |           | not null | 
 price      | numeric |           |          | 
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_no)
    "products_name_uk" UNIQUE CONSTRAINT, btree (name)
Check constraints:
    "products_price_min" CHECK (price > 0::numeric)

添加约束时，系统会检验已有数据是否满足条件，如果不满足将会添加失败。