Postgresql学习手册(一) 数据表

对于任何一种关系型数据库而言，表都是数据存储的最核心、最基础的对象单元。现在就让我们从这里起步吧。

1. 创建表：
CREATE TABLEproducts (
product_no integer,
name text,255)"> price numeric
);

2. 删除表：
DROP TABLEproducts;
3. 创建带有缺省值的表：
price numericDEFAULT9.99--DEFAULT是关键字，其后的数值9.99是字段price的默认值。
product_noSERIAL,--SERIAL类型的字段表示该字段为自增字段，完全等同于Oracle中的Sequence。
price numericDEFAULT9.99
输出为：
NOTICE: CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no"

4. 约束：
检查约束是表中最为常见的约束类型，它允许你声明在某个字段里的数值必须满足一个布尔表达式。不仅如此，我们也可以声明表级别的检查约束。
--price字段的值必须大于0，否则在插入或修改该字段值是，将引发违规错误。还需要说明的是，该检查约束
--是匿名约束，即在表定义时没有显示命名该约束，这样Postgresql将会根据当前的表名、字段名和约束类型，
--为该约束自动命名，如：products_price_check。
price numericCHECK(price > 0)
--该字段的检查约束被显式命名为positive_price。这样做的好处在于今后维护该约束时，可以根据该名进行直接操作。
price numericCONSTRAINTpositive_priceCHECK(price > 0)
下面的约束是非空约束，即约束的字段不能插入空值，或者是将已有数据更新为空值。
product_no integerNOT NULL,255)"> name textNOT NULL,sans-serif; font-size:14px; line-height:25.2000007629395px"> 如果一个字段中存在多个约束，在定义时可以不用考虑约束的声明顺序。
price numericNOT NULL CHECK(price > 0)
唯一性约束，即指定的字段不能插入重复值，或者是将某一记录的值更新为当前表中的已有值。
product_no integerUNIQUE,sans-serif; font-size:14px; line-height:25.2000007629395px">
price numeric,255)"> UNIQUE(product_no)
为表中的多个字段定义联合唯一性。
CREATE TABLEexample (
a integer,255)"> b integer,255)"> c integer,255)"> UNIQUE(a,c)
为唯一性约束命名。
product_no integerCONSTRAINTmust_be_differentUNIQUE,sans-serif; font-size:14px; line-height:25.2000007629395px"> 在插入数据时，空值(NULL)之间被视为不相等的数据，因此对于某一唯一性字段，可以多次插入空值。然而需要注意的是，这一规则并不是被所有数据库都遵守，因此在进行数据库移植时可能会造成一定的麻烦。
5. 主键和外键：
从技术上来讲，主键约束只是唯一约束和非空约束的组合。
product_no integerPRIMARY KEY,--字段product_no被定义为该表的唯一主键。
和唯一性约束一样，主键可以同时作用于多个字段，形成联合主键：
PRIMARY KEY(b,sans-serif; font-size:14px; line-height:25.2000007629395px"> 外键约束声明一个字段（或者一组字段）的数值必须匹配另外一个表中某些行出现的数值。 我们把这个行为称做两个相关表之间的参考完整性。
CREATE TABLEorders (
order_id integerPRIMARY KEY,0)">--该表也可以有自己的主键。
--该表的product_no字段为上面products表主键(product_no)的外键。
product_no integerREFERENCESproducts(product_no),255)"> quantity integer
CREATE TABLEt1 (
a integerPRIMARY KEY,0)"> --该外键的字段数量和被引用表中主键的数量必须保持一致。
FOREIGN KEY(b,c)REFERENCESexample (b,255)"> ); 当多个表之间存在了主外键的参考性约束关系时，如果想删除被引用表(主键表)中的某行记录，由于该行记录的主键字段值可能正在被其引用表(外键表)中某条记录所关联，所以删除操作将会失败。如果想完成此操作，一个显而易见的方法是先删除引用表中和该记录关联的行，之后再删除被引用表中的该行记录。然而需要说明的是，Postgresql为我们提供了更为方便的方式完成此类操作。
CREATE TABLEorders (
shipping_address text
CREATE TABLEorder_items (
product_no integerREFERENCESproductsON DELETE RESTRICT,0)">--限制选项
order_id integerREFERENCESordersON DELETE CASCADE,0)">--级联删除选项
quantity integer,255)"> PRIMARY KEY(product_no,order_id)
限制和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。 NO ACTION 的意思是如果在检查约束的时候，如果还存在任何引用行，则抛出错误； 如果你不声明任何东西，那么它就是缺省的行为。(这两个选择的实际区别是，NO ACTION 允许约束检查推迟到事务的晚些时候，而 RESTRICT 不行。) CASCADE声明在删除一个被引用的行的时候，引用它的行也会被自动删除掉。 在外键字段上的动作还有两个选项： SET NULL 和 SET DEFAULT。 这样会导致在被引用行删除的时候，引用它们的字段分别设置为空或者缺省值。 请注意这些选项并不能让你逃脱被观察和约束的境地。比如，如果一个动作声明 SET DEFAULT，但是缺省值并不能满足外键，那么动作就会失败。类似ON DELETE，还有ON UPDATE 选项，它是在被引用字段修改(更新)的时候调用的。可用的动作是一样的。
二、系统字段：
Postgresql的每个数据表中都包含几个隐含定义的系统字段。因此，这些名字不能用于用户定义的字段名。这些系统字段的功能有些类似于Oracle中的rownum和rowid等。
oid: 行的对象标识符(对象ID)。这个字段只有在创建表的时候使用了WITH OIDS，或者是设置了配置参数default_with_oids时出现。这个字段的类型是oid(和字段同名)。
tableoid: 包含本行的表的OID。这个字段对那些从继承层次中选取的查询特别有用，因为如果没有它的话，我们就很难说明一行来自哪个独立的表。tableoid可以和pg_class的oid字段连接起来获取表名字。
xmin: 插入该行版本的事务的标识(事务ID)。
cmin: 在插入事务内部的命令标识(从零开始)。
xmax: 删除事务的标识(事务ID)，如果不是被删除的行版本，那么是零。
cmax: 在删除事务内部的命令标识符，或者是零。
ctid: 一个行版本在它所处的表内的物理位置。请注意，尽管ctid可以用于非常快速地定位行版本，但每次VACUUM FULL之后，一个行的ctid都会被更新或者移动。因此ctid是不能作为长期的行标识符的。
OID是32位的量，是在同一个集群内通用的计数器上赋值的。对于一个大型或者长时间使用的数据库，这个计数器是有可能重叠的。因此，假设OID是唯一的是非常错误的，除非你自己采取了措施来保证它们是唯一的。如果你需要标识表中的行，我们强烈建议使用序列号生成器。
三、表的修改：
1. 增加字段：
ALTER TABLEproductsADD COLUMNdescription text;
新增的字段对于表中已经存在的行而言最初将先填充所给出的缺省值(如果你没有声明DEFAULT子句，那么缺省是空值)。
在新增字段时，可以同时给该字段指定约束。
ALTER TABLEproductsADD COLUMNdescription textCHECK(description <> '');
2. 删除字段：
ALTER TABLEproductsDROP COLUMNdescription;
如果该表为被引用表，该字段为被引用字段，那么上面的删除操作将会失败。如果要想在删除被引用字段的同时级联的删除其所有引用字段，可以采用下面的语法形式。
ALTER TABLEproductsDROP COLUMNdescriptionCASCADE;
3. 增加约束：
ALTER TABLEproductsADD CHECK(name <> '');--增加一个表级约束
ALTER TABLEproductsADD CONSTRAINTsame_nameUNIQUE(product_no);--增加命名的唯一性约束。
ALTER TABLEproductsADD FOREIGN KEY(pdt_grp_id)REFERENCESpdt_grps;--增加外键约束。
ALTER TABLEproductsALTER COLUMNproduct_noSET NOT NULL;--增加一个非空约束。
4. 删除约束：
ALTER TABLEproductsDROP CONSTRAINTsome_name;
对于显示命名的约束，可以根据其名称直接删除，对于隐式自动命名的约束，可以通过psql的\d tablename来获取该约束的名字。和删除字段一样，如果你想删除有着被依赖关系的约束，你需要用CASCADE。一个例子是某个外键约束依赖被引用字段上的唯一约束或者主键约束。如：
MyTest=# \d products
Table "public.products"
Column | Type | Modifiers
------------+---------+-----------
product_no | integer |
name | text |
price | numeric |
Check constraints:
"positive_price" CHECK (price > 0::numeric)
和其他约束不同的是，非空约束没有名字，因此只能通过下面的方式删除：
ALTER TABLEproductsALTER COLUMNproduct_noDROP NOT NULL;
5. 改变字段的缺省值:
在为已有字段添加缺省值时，不会影响任何表中现有的数据行， 它只是为将来INSERT命令改变缺省值。
ALTER TABLEproductsALTER COLUMNpriceSET DEFAULT7.77;
下面为删除缺省值：
ALTER TABLEproducts ALTER COLUMN priceDROP DEFAULT
6. 修改字段的数据类型：
只有在字段里现有的每个项都可以用一个隐含的类型转换转换成新的类型时才可能成功。比如当前的数据都是整型，而转换的目标类型为numeric或 varchar，这样的转换一般都可以成功。与此同时，Postgresql还将试图把字段的缺省值（如果存在）转换成新的类型， 还有涉及该字段的任何约束。但是这些转换可能失败，或者可能生成奇怪的结果。 在修改某字段类型之前，你最好删除那些约束，然后再把自己手工修改过的添加上去。
ALTER TABLEproductsALTER COLUMNpriceTYPEnumeric(10,2);

7. 修改字段名：
ALTER TABLEproductsRENAME COLUMNproduct_noTOproduct_number;
8. 修改表名：
ALTER TABLEproductsRENAME TOitems;
四、权限：
只有表的所有者才能修改或者删除表的权限。要赋予一个权限，我们使用GRANT命令，要撤销一个权限，使用REVOKE命令。
需要指出的是，PUBLIC是特殊"用户"可以用于将权限赋予系统中的每一个用户。在声明权限的位置写ALL则将所有的与该对象类型相关的权限都赋予出去。
GRANT UPDATE ONtable_nameTOuser;--将表的更新权限赋予指定的user。
GRANT SELECT ONtable_nameTO GROUPgroup;--将表的select权限赋予指定的组。
REVOKE ALL ONtable_nameFROMPUBLIC;--将表的所有权限从Public撤销。

最初，只有对象所有者(或者超级用户)可以赋予或者撤销对象的权限。但是，我们可以赋予一个"with grant option"权限，这样就给接受权限的人以授予该权限给其它人的权限。如果授予选项后来被撤销，那么所有那些从这个接受者接受了权限的用户(直接或者通过级连的授权)都将失去该权限。

Postgresql学习手册(二) 模式(Schema) 一个数据库包含一个或多个命名的模式，模式又包含表。模式还包含其它命名的对象，包括数据类型、函数，以及操作符。同一个对象名可以在不同的模式里使用而不会导致冲突； 比如，schema1和myschema都可以包含叫做mytable的表。和数据库不同，模式不是严格分离的：一个用户可以访问他所连接的数据库中的任意模式中的对象，只要他有权限。
我们需要模式有以下几个主要原因：
1). 允许多个用户使用一个数据库而不会干扰其它用户。
2). 把数据库对象组织成逻辑组，让它们更便于管理。
3). 第三方的应用可以放在不同的模式中，这样它们就不会和其它对象的名字冲突。
1. 创建模式：
CREATE SCHEMAmyschema;
通过以上命令可以创建名字为myschema的模式，在该模式被创建后，其便可拥有自己的一组逻辑对象，如表、视图和函数等。
2. public模式：
在介绍后面的内容之前，这里我们需要先解释一下public模式。每当我们创建一个新的数据库时，Postgresql都会为我们自动创建该模式。当登录到该数据库时，如果没有特殊的指定，我们将以该模式(public)的形式操作各种数据对象，如：
CREATE TABLEproducts ( ... ) 等同于 CREATE TABLEpublic.products ( ... )
3. 权限：
缺省时，用户看不到模式中不属于他们所有的对象。为了让他们看得见，模式的所有者需要在模式上赋予USAGE权限。为了让用户使用模式中的对象，我们可能需要赋予额外的权限，只要是适合该对象的。Postgresql根据不同的对象提供了不同的权限类型，如：
GRANT ALL ON SCHEMAmyschemaTOpublic; 上面的ALL关键字将包含 CREATE 和 USAGE 两种权限。如果public模式拥有了myschema模式的CREATE权限，那么登录到该模式的用户将可以在myschema模式中创建任意对象，如：
CREATE TABLEmyschema.products (
price numericCHECK(price > 0),sans-serif; font-size:14px; line-height:25.2000007629395px"> 在为模式下的所有表赋予权限时，需要将权限拆分为各种不同的表操作，如：
ALTER DEFAULT PRIVILEGES IN SCHEMAmyschema
GRANT INSERT,SELECT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER ON TABLES TOpublic;
在为模式下的所有Sequence序列对象赋予权限时，需要将权限拆分为各种不同的Sequence操作，如：
GRANT SELECT,USAGE ON SEQUENCES TOpublic;
在为模式下的所有函数赋予权限时，仅考虑执行权限，如：
GRANT EXECUTE ON FUNCTIONS TOpublic;
可以看出，通过以上方式在public模式下为myschema模式创建各种对象是极为不方便的。下面我们将要介绍另外一种方式，即通过role对象，直接登录并关联到myschema对象，之后便可以在myschema模式下直接创建各种所需的对象了。
CREATE ROLEmyschemaLOGIN PASSWORD'123456'; --创建了和该模式关联的角色对象。
CREATE SCHEMAmyschemaAUTHORIZATIONmyschema; --将该模式关联到指定的角色，模式名和角色名可以不相等。
在Linux Shell下，以myschema的角色登录到数据库MyTest，在密码输入正确后将成功登录到该数据库。
/> psql -d MyTest -U myschema
Password:
MyTest=> CREATE TABLE test(i integer);
CREATE TABLE
\d --查看该模式下，以及该模式有权限看到的tables信息列表。
List of relations
Schema | Name | Type | Owner
------------+---------+------+----------
myschema | test | table | myschema
(1 rows)
4. 删除模式：
DROP SCHEMAmyschema;
如果要删除模式及其所有对象，请使用级联删除：
DROP SCHEMAmyschemaCASCADE;
5. 模式搜索路径：
我们在使用一个数据库对象时可以使用它的全称来定位对象，然而这样做往往也是非常繁琐的，每次都不得不键入 owner_name.object_name。Postgresql中提供了模式搜索路径，这有些类似于Linux中的$PATH环境变量，当我们执行一个Shell命令时，只有该命令位于$PATH的目录列表中，我们才可以通过命令名直接执行，否则就需要输入它的全路径名。Postgresql同样也通过查找一个搜索路径来判断一个表究竟是哪个表，这个路径是一个需要查找的模式列表。在搜索路径里找到的第一个表将被当作选定的表。如果在搜索路径中没有匹配表，那么就报告一个错误，即使匹配表的名字在数据库其它的模式中存在也如此。
在搜索路径中的第一个模式叫做当前模式。除了是搜索的第一个模式之外，它还是在CREATE TABLE没有声明模式名的时候，新建表所属于的模式。要显示当前搜索路径，使用下面的命令：
SHOW search_path;
search_path
----------------
"$user",public
(1 row)
可以将新模式加入到搜索路径中，如：
SET search_path TOmyschema,public;
为搜索路径设置指定的模式，如：
SET search_path TOmyschema; --当前搜索路径中将只是包含myschema一种模式。

Postgresql学习手册(三) 表的继承和分区

一、表的继承：
这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生，然而它的实现方式和设计原理却是简单易懂，现在就让我们从一个简单的例子开始吧。
1. 第一个继承表：
CREATE TABLEcities (--父表
population float,255)"> altitude int
);
CREATE TABLEcapitals (--子表
state char(2)
)INHERITS(cities);
capitals表继承自cities表的所有属性。在Postgresql里，一个表可以从零个或多个其它表中继承属性，而且一个查询既可以引用父表中的所有行，也可以引用父表的所有行加上其所有子表的行，其中后者是缺省行为。
MyTest=# INSERT INTO cities values('Las Vegas',1.53,2174);--插入父表
INSERT 0 1
MyTest=# INSERT INTO cities values('Mariposa',3.30,1953);MyTest=# INSERT INTO capitals values('Madison',4.34,845,'WI');--插入子表
INSERT 0 1
MyTest=# SELECT name,altitude FROM cities WHERE altitude > 500;--父表和子表的数据均被取出。
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)

--只有子表的数据被取出。
name | altitude
---------+----------
Madison | 845
(1 row)

如果希望只从父表中提取数据，则需要在sql中加入ONLY关键字，如：
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
(2 rows)
上例中cities前面的"ONLY"关键字表示该查询应该只对cities进行查找而不包括继承级别低于cities的表。许多我们已经讨论过的命令--SELECT，UPDATE和DELETE--支持这个"ONLY"符号。
在执行整表数据删除时，如果直接truncate父表，此时父表和其所有子表的数据均被删除，如果只是truncate子表，那么其父表的数据将不会变化，只是子表中的数据被清空。
MyTest=# TRUNCATE TABLE cities;--父表和子表的数据均被删除。
TRUNCATE TABLE
MyTest=# SELECT * FROM capitals;
name | population | altitude | state
------+------------+----------+-------
(0 rows)

2. 确定数据来源：
有时候你可能想知道某条记录来自哪个表。在每个表里我们都有一个系统隐含字段tableoid，它可以告诉你表的来源：
MyTest=# SELECT tableoid,name,altitude FROM cities WHERE altitude > 500;
tableoid | name | altitude
----------+-----------+----------
16532 | Las Vegas | 2174
16532 | Mariposa | 1953
16538 | Madison | 845
(3 rows)
以上的结果只是给出了tableoid，仅仅通过该值，我们还是无法看出实际的表名。要完成此操作，我们就需要和系统表pg_class进行关联，以通过tableoid字段从该表中提取实际的表名，见以下查询：
MyTest=# SELECT p.relname,c.name,c.altitude FROM cities c,pg_class p WHERE c.altitude > 500 and c.tableoid = p.oid;
relname | name | altitude
----------+-----------+----------
cities | Las Vegas | 2174
cities | Mariposa | 1953
capitals | Madison | 845
(3 rows)

3. 数据插入的注意事项：
继承并不自动从INSERT或者COPY中向继承级别中的其它表填充数据。在我们的例子里，下面的INSERT语句不会成功：
INSERT INTO cities (name,population,altitude,state) VALUES ('New York',NULL,'NY');
我们可能希望数据被传递到capitals表里面去，但是这是不会发生的：INSERT总是插入明确声明的那个表。

4. 多表继承：
一个表可以从多个父表继承，这种情况下它拥有父表们的字段的总和。子表中任意定义的字段也会加入其中。如果同一个字段名出现在多个父表中，或者同时出现 在父表和子表的定义里，那么这些字段就会被"融合"，这样在子表里面就只有一个这样的字段。要想融合，字段必须是相同的数据类型，否则就会抛出一个错误。 融合的字段将会拥有它所继承的字段的所有约束。
CREATE TABLE parent1 (FirstCol integer);
CREATE TABLE parent2 (FirstCol integer,SecondCol varchar(20));
CREATE TABLE parent3 (FirstCol varchar(200));
--子表child1将同时继承自parent1和parent2表，而这两个父表中均包含integer类型的FirstCol字段，因此child1可以创建成功。
CREATE TABLE child1 (MyCol timestamp) INHERITS (parent1,parent2);
--子表child2将不会创建成功，因为其两个父表中均包含FirstCol字段，但是它们的类型不相同。
CREATE TABLE child2 (MyCol timestamp) INHERITS (parent1,parent3);
--子表child3同样不会创建成功，因为它和其父表均包含FirstCol字段，但是它们的类型不相同。
CREATE TABLE child3 (FirstCol varchar(20)) INHERITS(parent1);

5. 继承和权限：
表访问权限并不会自动继承。因此，一个试图访问父表的用户还必须具有访问它的所有子表的权限，或者使用ONLY关键字只从父表中提取数据。在向现有的继承层次添加新的子表的时候，请注意给它赋予所有权限。
继承特性的一个严重的局限性是索引(包括唯一约束)和外键约束只施用于单个表，而不包括它们的继承的子表。这一点不管对引用表还是被引用表都是事实，因此在上面的例子里，如果我们声明cities.name为UNIQUE或者是一个PRIMARY KEY，那么也不会阻止capitals表拥有重复了名字的cities数据行。 并且这些重复的行缺省时在查询cities表的时候会显示出来。实际上，缺省时capitals将完全没有唯一约束，因此可能包含带有同名的多个行。你应该给capitals增加唯一约束，但是这样做也不会避免与cities的重复。类似，如果我们声明cities.name REFERENCES某些其它的表，这个约束不会自动广播到capitals。在这种条件下，你可以通过手工给capitals 增加同样的REFERENCES约束来做到这点。

二、分区表：
1. 概述分区表：
分区的意思是把逻辑上的一个大表分割成物理上的几块儿，分区可以提供若干好处：
1). 某些类型的查询性能可以得到极大提升。
2). 更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
3). 批量删除可以用简单地删除某个分区来实现。
4). 将很少用的数据可以移动到便宜的、慢一些地存储介质上。
假设当前的数据库并不支持分区表，而我们的应用所需处理的数据量也非常大，对于这种应用场景，我们不得不人为的将该大表按照一定的规则，手工拆分成多个小表，让每个小表包含不同区间的数据。这样一来，我们就必须在数据插入、更新、删除和查询之前，先计算本次的指令需要操作的小表。对于有些查询而言，由于查询区间可能会跨越多个小表，这样我们又不得不将多个小表的查询结果进行union操作，以合并来自多个表的数据，并最终形成一个结果集返回给客户端。可见，如果我们正在使用的数据库不支持分区表，那么在适合其应用的场景下，我们就需要做很多额外的编程工作以弥补这一缺失。然而需要说明的是，尽管功能可以勉强应付，但是性能却和分区表无法相提并论。
目前Postgresql支持的分区形式主要为以下两种：
1). 范围分区: 表被一个或者多个键字字段分区成"范围"，在这些范围之间没有重叠的数值分布到不同的分区里。比如，我们可以为特定的商业对象根据数据范围分区，或者根据标识符范围分区。
2). 列表分区: 表是通过明确地列出每个分区里应该出现那些键字值实现的。

2. 实现分区：
1). 创建"主表"，所有分区都从它继承。
CREATE TABLE measurement ( --主表
city_id int NOT NULL,
logdate date NOT NULL,255)"> peaktemp int,255)"> );
2). 创建几个"子"表，每个都从主表上继承。通常，这些"子"表将不会再增加任何字段。我们将把子表称作分区，尽管它们就是普通的Postgresql表。
CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement);
CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement);
CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement);
CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);
上面创建的子表，均以年、月的形式进行范围划分，不同年月的数据将归属到不同的子表内。这样的实现方式对于清空分区数据而言将极为方便和高效，即直接执行DROP TABLE语句删除相应的子表，之后在根据实际的应用考虑是否重建该子表(分区)。相比于直接DROP子表，Postgresql还提供了另外一种更为方便的方式来管理子表：
ALTER TABLEmeasurement_yy06mm01NO INHERITmeasurement;
和直接DROP相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据(TRUNCATE TABLE)，之后再让该表重新继承主表，如：
ALTER TABLEmeasurement_yy06mm01INHERITmeasurement;
3). 给分区表增加约束，定义每个分区允许的健值。同时需要注意的是，定义的约束要确保在不同的分区里不会有相同的键值。因此，我们需要将上面"子"表的定义修改为以下形式：
CREATE TABLE measurement_yy04mm02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01')
) INHERITS (measurement);
CREATE TABLE measurement_yy04mm03 (
CHECK (logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01')
CREATE TABLE measurement_yy05mm11 (
CHECK (logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01')
CREATE TABLE measurement_yy05mm12 (
CHECK (logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01')
CREATE TABLE measurement_yy06mm01 (
CHECK (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01')
) INHERITS (measurement);
4). 尽可能基于键值创建索引。如果需要，我们也同样可以为子表中的其它字段创建索引。
CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate);
CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03 (logdate);
CREATE INDEX measurement_yy05mm11_logdate ON measurement_yy05mm11 (logdate);
CREATE INDEX measurement_yy05mm12_logdate ON measurement_yy05mm12 (logdate);
CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);
5). 定义一个规则或者触发器，把对主表的修改重定向到适当的分区表。
如果数据只进入最新的分区，我们可以设置一个非常简单的规则来插入数据。我们必须每个月都重新定义这个规则，即修改重定向插入的子表名，这样它总是指向当前分区。
CREATE OR REPLACE RULEmeasurement_current_partitionAS
ON INSERT TOmeasurement
DO INSTEAD
INSERT INTO measurement_yy06mm01 VALUES (NEW.city_id,NEW.logdate,NEW.peaktemp);
其中NEW是关键字，表示新数据字段的集合。这里可以通过点(.)操作符来获取集合中的每一个字段。
我们可能想插入数据并且想让服务器自动定位应该向哪个分区插入数据。我们可以用像下面这样的更复杂的规则集来实现这个目标。
CREATE RULEmeasurement_insert_yy04mm02AS
ON INSERT TOmeasurementWHERE(logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01')
INSERT INTO measurement_yy04mm02 VALUES (NEW.city_id,NEW.peaktemp);
CREATE RULEmeasurement_insert_yy05mm12AS
ON INSERT TOmeasurementWHERE(logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01')
INSERT INTO measurement_yy05mm12 VALUES (NEW.city_id,255)"> CREATE RULEmeasurement_insert_yy06mm01AS
ON INSERT TOmeasurementWHERE(logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01')
请注意每个规则里面的WHERE子句正好匹配其分区的CHECK约束。
可以看出，一个复杂的分区方案可能要求相当多的DDL。在上面的例子里我们需要每个月创建一次新分区，因此写一个脚本自动生成需要的DDL是明智的。除此之外，我们还不难推断出，分区表对于新数据的批量插入操作有一定的抑制，这一点在Oracle中也同样如此。
除了上面介绍的通过Rule的方式重定向主表的数据到各个子表，我们还可以通过触发器的方式来完成此操作，相比于基于Rule的重定向方法，基于触发器的方式可能会带来更好的插入效率，特别是针对非批量插入的情况。然而对于批量插入而言，由于Rule的额外开销是基于表的，而不是基于行的，因此效果会好于触发器方式。另一个需要注意的是，copy操作将会忽略Rules，如果我们想要通过COPY方法来插入数据，你只能将数据直接copy到正确的子表，而不是主表。这种限制对于触发器来说是不会造成任何问题的。基于Rule的重定向方式还存在另外一个问题，就是当插入的数据不在任何子表的约束中时，Postgresql也不会报错，而是将数据直接保留在主表中。
6). 添加新分区：
这里将介绍两种添加新分区的方式，第一种方法简单且直观，我们只是创建新的子表，同时为其定义新的检查约束，如：
CREATE TABLE measurement_y2008m02 (
CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);
第二种方法的创建步骤相对繁琐，但更为灵活和实用。见以下四步：
/* 创建一个独立的数据表(measurement_y2008m02)，该表在创建时以将来的主表(measurement)为模板，包含模板表的缺省值(DEFAULTS)和一致性约束(CONSTRAINTS)。*/
CREATE TABLEmeasurement_y2008m02
(LIKEmeasurementINCLUDING DEFAULTS INCLUDING CONSTRAINTS);
/* 为该表创建未来作为子表时需要使用的检查约束。*/
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
CHECK (logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01');
/* 导入数据到该表。下面只是给出一种导入数据的方式作为例子。在导入数据之后，如有可能，还可以做进一步的数据处理，如数据转换、过滤等。*/
\copy measurement_y2008m02 from 'measurement_y2008m02'
/* 在适当的时候，或者说在需要的时候，让该表继承主表。*/
ALTER TABLE measurement_y2008m02 INHERIT measurement;
7). 确保postgresql.conf里的配置参数constraint_exclusion是打开的。没有这个参数，查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。
/> pwd
/opt/Postgresql/9.1/data
/> cat postgresql.conf | grep "constraint_exclusion"
constraint_exclusion = partition # on,off,or partition

3. 分区和约束排除：
约束排除(Constraint exclusion)是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。比如：
SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
如果没有约束排除，上面的查询会扫描measurement表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后再试图证明该分区不需要被扫描，因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。
你可以使用EXPLAIN命令显示一个规划在constraint_exclusion打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是：
SET constraint_exclusion = off;
EXPLAINSELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=158.66..158.68 rows=1 width=0)
-> Append (cost=0.00..151.88 rows=2715 width=0)
-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
-> Seq Scan on measurement_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
-> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
...
-> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
-> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)

从上面的查询计划中可以看出，Postgresql扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划：
SET constraint_exclusion = on;
EXPLAINSELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=63.47..63.48 rows=1 width=0)
-> Append (cost=0.00..60.75 rows=1086 width=0)
-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
-> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
请注意，约束排除只由CHECK约束驱动，而不会由索引驱动。
目前版本的Postgresql中该配置的缺省值是partition，该值是介于on和off之间的一种行为方式，即规划器只会将约束排除应用于基于分区表的查询，而on设置则会为所有查询都进行约束排除，那么对于普通数据表而言，也将不得不承担由该机制而产生的额外开销。

约束排除在使用时有以下几点注意事项：
1). 约束排除只是在查询的WHERE子句包含约束的时候才生效。一个参数化的查询不会被优化，因为在运行时规划器不知道该参数会选择哪个分区。因此像CURRENT_DATE这样的函数必须避免。把分区键值和另外一个表的字段连接起来也不会得到优化。
2). 在CHECK约束里面要避免跨数据类型的比较，因为目前规划器会无法证明这样的条件为假。比如，下面的约束会在x是整数字段的时候可用，但是在x是一个bigint的时候不能用：
CHECK (x = 1)
对于bigint字段，我们必须使用类似下面这样的约束：
CHECK (x = 1::bigint)
这个问题并不仅仅局限于bigint数据类型，它可能会发生在任何约束的缺省数据类型与其比较的字段的数据类型不匹配的场合。在提交的查询里的跨数据类型的比较通常是OK的，只是不能在CHECK条件里。
3). 在主表上的UPDATE和DELETE命令并不执行约束排除。
4). 在规划器进行约束排除时，主表上的所有分区的所有约束都将会被检查，因此，大量的分区会显著增加查询规划的时间。
5). 在执行ANALYZE语句时，要为每一个分区都执行该命令，而不是仅仅对主表执行该命令。

Postgresql学习手册(四) 常用数据类型

Postgresql学习手册(五) 函数和操作符