8.|8. PostgreSQL逻辑结构（2）——表 8.PostgreSQL逻辑结构（2）—

在PostgreSQL中，数据就是存放在数据表中，在一张数据表中，每一列就是一个字段，即表示哪种类型的数据。每一行就是不同的类型的数据。对数据表的操作也是：创建、修改和删除。
创建表
创建表的基本命令格式如下：

CREATE TABLE table_name ( field1datatype1, field2datatype2, .... );

格式就是在括号中一个字段名和一个字段类型，字段和字段类型之间用空格隔开。不同字段之间用逗号隔开。示例如下：

postgres=# CREATE TABLE testdb1 (id int, comments varchar(20)); CREATE TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers ----------+-----------------------+----------- id| integer| comments | character varying(20) |

上面的示例中就创建了一个普通的表，表中没有任何约束。约束指的是对表中的字段进行一定的限制，或对两个不同的表之间的字段进行限制。约束的类型主要包括：

主键约束，分为单字段主键和多字段主键，后者也被称为复合主键。
外键约束，表中的字段和另外一张表里的字段有对应关系，更新一张表时必须检查另外一张表
唯一约束，有这个约束的字段中的值只能出现一次。一般用于具有唯一性的数值。
非空约束，即这个字段的值不能是null
检查约束，即Check约束，用于定义某些字段的值必须满足某些要求。

下面几个示例中分别显示了定义表的时候对表的某些字段进行约束。
主键约束、唯一约束和非空约束

#上面的表中，我们创建了一个两个字段的表testdb1，且表中没有任何约束，如下所示： postgres=# \d testdb1 Table "public.testdb1" Column|Type| Modifiers ----------+-----------------------+----------- id| integer| comments | character varying(20) |

将这个表的id字段设置为表的主键，可以看到id字段也自动变成了非空字段（not null）

postgres=# alter table testdb1 add primary key(id); ALTER TABLE postgres=# \d testdb1 Table "public.testdb1" Column|Type| Modifiers ----------+-----------------------+----------- id| integer| not null comments | character varying(20) | Indexes: "testdb1_pkey" PRIMARY KEY, btree (id)

尝试往表中插入数据

postgres=# insert into testdb1 values(1, 'test'); INSERT 0 1 postgres=# insert into testdb1 values(2, 'not all'); INSERT 0 1 postgres=# insert into testdb1 values(2, 'not you'); ERROR:duplicate key value violates unique constraint "testdb1_pkey" DETAIL:Key (id)=(2) already exists. postgres=# select * from testdb1; id | comments ----+---------- 1 | test 2 | not all (2 rows)

上面进行了3次插入操作，前两次插入成功，第三次插入失败。第三次插入的数据中id=2，和第二次插入的数据中的id相同，说明主键具有唯一性。
通过字段名进行插入，如下所示：

postgres=# insert into testdb1(comments) values('not you'); ERROR:null value in column "id" violates not-null constraint DETAIL:Failing row contains (null, not you).

发现插入出错，说明主键字段id不会自增到下一个值，说明主键没有自增特性
同时也看到了非空约束的作用，就是限制这个字段的值不能是一个空值。

#删除主键约束 postgres=# alter table testdb1 drop constraint testdb1_pkey; ALTER TABLEpostgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers ----------+-----------------------+----------- id| integer| not null comments | character varying(20) |

从上面的示例中讲解了主键约束的添加、数值插入、以及删除操作。从四个简短的小示例中我们看到主键约束有以下特性：

非空
唯一性
不会自动增加值

也看到了唯一性约束和非空约束的作用：

唯一约束的字段值不能重复
非空约束的字段对应的值不能是一个空值

如果没有定义主键，而是想直接定义另外一个键的类型为唯一约束，可以使用关键字UNIQUE，示例如下：

postgres=# alter table testdb1 add unique(comments); ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers ----------+-----------------------+----------- id| integer| not null comments | character varying(20) | Indexes: "testdb1_comments_key" UNIQUE CONSTRAINT, btree (comments)postgres=# select * from testdb1; id | comments ----+---------- 1 | test 2 | not all (2 rows)postgres=# insert into testdb1 values(3, 'test'); ERROR:duplicate key value violates unique constraint "testdb1_comments_key" DETAIL:Key (comments)=(test) already exists.

删除唯一性约束

postgres=# alter table testdb1 drop constraint testdb1_comments_key; ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers ----------+-----------------------+----------- id| integer| not null comments | character varying(20) |

在上面示例中，我们将comments字段设置为UNIQE约束，然后做了一次插入操作，插入的comments值和id=1时的值相同，此时发生了报错。说明UNIQE约束生效。
而单独设置某个字段为not null类型字段，则需要使用另外一种命令格式：

#在comments字段上添加非空约束 postgres=# alter table testdb1 alter comments set not null; ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers ----------+-----------------------+----------- id| integer| not null comments | character varying(20) | not null Indexes: "testdb1_comments_key" UNIQUE CONSTRAINT, btree (comments)# 取消comments字段的非空约束 postgres=# alter table testdb1 alter comments drop not null; ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers ----------+-----------------------+----------- id| integer| not null comments | character varying(20) | Indexes: "testdb1_comments_key" UNIQUE CONSTRAINT, btree (comments)

从上面两个示例中我们还可以总结出一点，主键约束和唯一约束，是另外附加给字段的约束类型，因此会显示在表信息下面。而非空约束则是字段内在的属性，会和字段一起显示。因此会使用不同的方式来添加和删除，主键约束和唯一约束通过下面的命令来添加和删除

alter table table_name add constraint_name (field_name); #添加主键约束或唯一约束 alter table table_name drop constraint constraint_name; #删除主键约束或唯一约束

而非空约束则通过修改字段属性来添加和删除非空约束，如下所示：

alter table tablename alter field_name set notnull; #添加非空约束 alter table tablename alter field_name drop not null; #删除非空约束

外键约束外键约束也是一种外部约束，可以使用主键约束和唯一约束相同的命令来添加，唯一不同的就是语法，同时要求关联的字段必须是另外一张表的主键。示例如下：
还是前面的testdb1表格，我们新增一个字段parent_id，要求将这个字段和一个新的testdb2表格的id字段进行关联：

postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | # 增加一个字段parent_id，命令如下： postgres=# alter table testdb1 add column parent_id int; ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | parent_id | integer| #字段添加完成后，查看新表testdb2， postgres=# \d testdb2; Table "public.testdb2" Column |Type| Modifiers --------+-----------------------+----------- id| integer| not null info| character varying(50) | Indexes: "testdb2_pkey" PRIMARY KEY, btree (id)#字段id是表testdb2的主键，在testdb1的parent_id字段上增加外键约束，命令如下： postgres=# alter table testdb1 add foreign key(parent_id) references testdb2(id); ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | parent_id | integer| Foreign-key constraints: "testdb1_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES testdb2(id)## 删除外键约束 postgres=# alter table testdb1 drop constraint testdb1_parent_id_fkey; ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | parent_id | integer| #可以看到外键约束已经删除。

检查约束检查约束一般是用来限制字段的类型或数值范围的，也是一种外部约束，添加和删除的命令和其他几种外部约束相同，示例如下：

#添加检查约束，限制parent_id字段数值范围是3~10之间 postgres=# alter table testdb1 add check (parent_id > 3 and parent_id < 10); ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | parent_id | integer| Check constraints: "testdb1_parent_id_check" CHECK (parent_id > 3 AND parent_id < 10)postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | parent_id | integer| Check constraints: "testdb1_parent_id_check" CHECK (parent_id > 3 AND parent_id < 10)#删除检查约束，直接根据约束名称删除。 postgres=# alter table testdb1 drop constraint testdb1_parent_id_check; ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | parent_id | integer|

修改表
修改表的操作主要有两种，第一种是修改字段的属性，第二种是修改字段对应的值。字段的属性包括：字段类型、字段默认值、字段约束等。字段约束的添加和修改我们在上面创建表的部分已经讲过，这一部分我们就讲解一下字段类型和字段默认值的修改。
修改字段类型基本命令格式如下：

alter table tablename alter field_name type tyepname;

修改字段类型这个操作要求修改前后的字段类型必须是能够转换的类型，同时，进行类型转换的时候，会存在数据精度丢失等问题，因此在前期进行数据库设计的时候，对于字段的类型一定要考虑清楚，因为一旦表中存入了数据，再进行类型修改的操作是非常危险的。示例如下：

postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | parent_id | integer| postgres=# select * from testdb1; id | comments | parent_id ----+----------+----------- 1 | test| 2 | not all| (2 rows)postgres=# alter table testdb1 alter parent_id type varchar(5); ALTER TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type| Modifiers -----------+-----------------------+----------- id| integer| not null comments| character varying(20) | parent_id | character varying(5)|

上面的示例中直接将testdb1表中的parent_id字段类型从int类型修改为varchar(5)类型，字符类型和整数类型存在转换关系，且parent_id字段在表中没有任何值，因此可以直接转换。
我们在表中插入一个值，然后再尝试将parent_id转换为int类型，如下所示:

postgres=# insert into testdb1 values(3,'no poll', 't'); INSERT 0 1 postgres=# select * from testdb1; id | comments | parent_id ----+----------+----------- 1 | test| 2 | not all| 3 | no poll| t (3 rows)postgres=# alter table testdb1 alter parent_id type int; ERROR:column "parent_id" cannot be cast automatically to type integer HINT:You might need to specify "USING parent_id::integer".

此时我们就看到了系统给出的提示，需要使用USING parent_id::integer这样的关键字来强制转换。我们来尝试一下，然后看表中的数值变化：

postgres=# alter table testdb1 alter COLUMN parent_id type int USING parent_id::integer; ERROR:invalid input syntax for integer: "t"

但是这个时候发现，它给出提示，t是对于integer类型来说是一个错误的语法，说明我们刚刚插入的值是不能进行varchar到integer的转换的，我们将这个值删去，然后插入另外一个字符类型的数字，再尝试转换类型看看。

postgres=# delete from testdb1 where id=3; DELETE 1postgres=# insert into testdb1 values(3,'test again', ' 42'); INSERT 0 1 postgres=# select * from testdb1; id |comments| parent_id ----+------------+----------- 1 | test| 2 | not all| 3 | test again |42 (3 rows)postgres=# alter table testdb1 alter column parent_id type int using parent_id::integer; ALTER TABLE postgres=# select * from testdb1; id |comments| parent_id ----+------------+----------- 1 | test| 2 | not all| 3 | test again |42 (3 rows)

我们发现这个时候转换成功了，这两个小例子说明，要转换类型的字段，转换前后的类型必须是可以转换的，否则会出错。而至于精度丢失的问题，例如从小数类型转换成整数类型，必然会丢失小数部分。这个就留给读者自己进行实验了。
修改字段默认值 【8.|8. PostgreSQL逻辑结构（2）——表】给表中插入数据的时候，如果这个字段没有给出值，则会使用默认值，对于某些设置来说还是非常方便的。默认值也属于字段的内在属性，可以使用和添加非空约束同样的命令来修改，示例如下：

postgres=# alter table testdb1alter COLUMN comments set default 'test'; ALTER TABLE

在上面的命令中，我将comments字段的默认值设置为'test'，下面我们尝试往testdb1表中再插入数值，插入的时候要注意，此时如果给拥有默认值的字段一个新值，则使用新值，如果不给新值，则使用默认值，但是必须显示指定要赋值的字段，否则会出错。看下面的示例：

# 给指定字段赋值，拥有默认值的字段使用默认值test postgres=# insert into testdb1(id, parent_id) values(6,30); INSERT 0 1 postgres=# select * from testdb1; id |comments| parent_id ----+-------------+----------- 1 | test| 2 | not all| 6 | test|30 (3 rows)# 给默认值字段赋予一个新值，此时使用的是新值。 postgres=# insert into testdb1 values(7, 'my test', 32); INSERT 0 1 postgres=# select * from testdb1; id |comments| parent_id ----+-------------+----------- 1 | test| 2 | not all| 3 | test again|42 5 | not a teste |35 6 | test|30 7 | my test|32 (6 rows)# 不指定要赋值的字段 postgres=# insert into testdb1 values(8, 35); INSERT 0 1 postgres=# select * from testdb1; id |comments| parent_id ----+-------------+----------- 1 | test| 2 | not all| 6 | test|30 7 | my test|32 8 | 35| (5 rows)postgres=# insert into testdb1values(9,,36); ERROR:syntax error at or near "," LINE 1: insert into testdb1values(9,,36); ^

从上面最后两个小例子中我们看到，在没有指定要赋值的字段的时候，postgresql并不会自动跳过有默认值的字段，会将对应错误的值赋值给comments字段，也不支持两个逗号表示跳过该字段的功能。因此在给具有默认值的字段的表做插入操作时一定要注意。
删除表
删除表应该是最简单的操作了，命令格式如下：

drop table table_name;

但是要注意的是，如果表的主键是另外一张表的外键，那么删除这张表之前一定要先删除对应表上的外键约束，否则会无法删除这张表，看下面的示例：

#我们将testdb1的parent_id和testdb2的id设置为外键约束关系，然后我们来尝试删除表testdb2 postgres=# alter table testdb1 add foreign key(parent_id) references testdb2(id); ALTER TABLE postgres=# \d testdb1 Table "public.testdb1" Column|Type|Modifiers -----------+-----------------------+----------------------------------- id| integer| not null comments| character varying(20) | default 'test'::character varying parent_id | integer| Indexes: "testdb1_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "testdb1_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES testdb2(id)postgres=# drop table testdb2; ERROR:cannot drop table testdb2 because other objects depend on it DETAIL:constraint testdb1_parent_id_fkey on table testdb1 depends on table testdb2 HINT:Use DROP ... CASCADE to drop the dependent objects too.

从提示中我们可以开电脑，无法直接删除表testdb2，因为它的主键是testdb1的外键，还给出了一个提示，可以使用DROP .. CASCADE的语法来删除表testdb2的同事删除对应依赖的对象。我们来尝试一下：

postgres=# DROP table testdb2 CASCADE; NOTICE:drop cascades to constraint testdb1_parent_id_fkey on table testdb1 DROP TABLE postgres=# \d testdb1; Table "public.testdb1" Column|Type|Modifiers -----------+-----------------------+----------------------------------- id| integer| not null comments| character varying(20) | default 'test'::character varying parent_id | integer| Indexes: "testdb1_pkey" PRIMARY KEY, btree (id)

从上面可以看到，我们使用DROP ... CASCADE的语句来删除表testdb2的时候，会自动删除testdb1表上的外键约束，而不需要手动去删除对应的外键约束。
上面就是表的创建、修改和删除的所有内容。更高深的操作我们在后面慢慢学习。下一句继续学习表相关的内容。