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

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

创建表

创建表的基本命令格式如下：

CREATE TABLE table_name (
field1  datatype1,
field2  datatype2,
....
);

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

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 TABLE

postgres=# \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 not  null;  #添加非空约束
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 1

postgres=# 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)

我们发现这个时候转换成功了，这两个小例子说明，要转换类型的字段，转换前后的类型必须是可以转换的，否则会出错。而至于精度丢失的问题，例如从小数类型转换成整数类型，必然会丢失小数部分。这个就留给读者自己进行实验了。

修改字段默认值

给表中插入数据的时候，如果这个字段没有给出值，则会使用默认值，对于某些设置来说还是非常方便的。默认值也属于字段的内在属性，可以使用和添加非空约束同样的命令来修改，示例如下：

postgres=# alter table testdb1  alter 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 testdb1  values(9,,36); 
ERROR:  syntax error at or near ","
LINE 1: insert into testdb1  values(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表上的外键约束，而不需要手动去删除对应的外键约束。

上面就是表的创建、修改和删除的所有内容。更高深的操作我们在后面慢慢学习。下一句继续学习表相关的内容。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 194,761评论 5赞 460
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 81,953评论 2赞 371
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 141,998评论 0赞 320
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,248评论 1赞 263
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,130评论 4赞 356
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,145评论 1赞 272
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,550评论 3赞 381
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,236评论 0赞 253
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,510评论 1赞 291
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,601评论 2赞 310
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,376评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,247评论 3赞 313
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,613评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,911评论 0赞 17
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,191评论 1赞 250
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,532评论 2赞 342
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,739评论 2赞 335