(数据表)
对于任何一种关系型数据库而言，表都是数据存储的最核心、最基础的对象单元。现在就让我们从这里起步吧。
1. 创建表：
CREATE TABLEproducts (
product_no integer,
name text,255)"> price numeric
);
2. 删除表：
DROP TABLEproducts;
3. 创建带有缺省值的表：
price numericDEFAULT9.99--DEFAULT是关键字，其后的数值9.99是字段price的默认值。@H_404_43@
product_noSERIAL,--SERIAL类型的字段表示该字段为自增字段，完全等同于Oracle中的Sequence@H_404_43@。
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; line-height:18px"> 如果一个字段中存在多个约束，在定义时可以不用考虑约束的声明顺序。
price numericNOT NULL CHECK(price > 0)
唯一性约束，即指定的字段不能插入重复值，或者是将某一记录的值更新为当前表中的已有值。
product_no integerUNIQUE,255)"> 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; line-height:18px"> 在插入数据时，空值(NULL)之间被视为不相等的数据，因此对于某一唯一性字段，可以多次插入空值。然而需要注意的是，这一规则并不是被所有数据库都遵守，因此在进行数据库移植时可能会造成一定的麻烦。
5. 主键和外键：
从技术上来讲，主键约束只是唯一约束和非空约束的组合。
product_no integerPRIMARY KEY,--字段product_no被定义为该表的唯一主键。@H_404_43@
和唯一性约束一样，主键可以同时作用于多个字段，形成联合主键：
PRIMARY KEY(b,sans-serif; line-height:18px"> 外键约束声明一个字段（或者一组字段）的数值必须匹配另外一个表中某些行出现的数值。 我们把这个行为称做两个相关表之间的参考完整性。
CREATE TABLEorders (
order_id integerPRIMARY KEY,0)">--该表也可以有自己的主键。@H_404_43@
--该表的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)">--限制选项@H_404_43@
order_id integerREFERENCESordersON DELETE CASCADE,0)">--级联删除选项@H_404_43@
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 <> '');--增加一个表级约束@H_404_43@
ALTER TABLEproductsADD CONSTRAINTsome_nameUNIQUE(product_no);--增加命名的唯一性约束。@H_404_43@
ALTER TABLEproductsADD FOREIGN KEY(pdt_grp_id)REFERENCESpdt_grps;--增加外键约束。@H_404_43@
ALTER TABLEproductsALTER COLUMNproduct_noSET NOT NULL;--增加一个非空约束。@H_404_43@
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"@H_404_43@ 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。@H_404_43@
GRANT SELECT ONtable_nameTO GROUPgroup;--将表的select权限赋予指定的组。@H_404_43@
REVOKE ALL ONtable_nameFROMPUBLIC;--将表的所有权限从Public撤销。@H_404_43@
最初，只有对象所有者(或者超级用户)可以赋予或者撤销对象的权限。但是，我们可以赋予一个 "with grant option" 权限，这样就给接受权限的人以授予该权限给其它人的权限。如果授予选项后来被撤销，那么所有那些从这个接受者接受了权限的用户(直接或者通过级连的授权)都将失去该权限。
(模式Schema)
我们需要模式有以下几个主要原因：
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),255)"> );
在为模式下的所有表赋予权限时，需要将权限拆分为各种不同的表操作，如：
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一种模式。@H_404_43@
@H_404_43@
(表的继承和分区)
这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生，然而它的实现方式和设计原理却是简单易懂，现在就让我们从一个简单的例子开始吧。
1. 第一个继承表：
CREATE TABLEcities (--父表@H_404_43@
population float,255)"> altitude int
);
CREATE TABLEcapitals (--子表@H_404_43@
state char(2)
)INHERITS(cities);
capitals表继承自cities表的所有属性。在Postgresql里，一个表可以从零个或多个其它表中继承属性，而且一个查询既可以引用父表中的所有行，也可以引用父表的所有行加上其所有子表的行，其中后者是缺省行为。
MyTest=# INSERT INTO cities values('Las Vegas',1.53,2174);--插入父表@H_404_43@
INSERT 0 1
MyTest=# INSERT INTO cities values('Mariposa',3.30,1953);MyTest=# INSERT INTO capitals values('Madison',4.34,845,'WI');--插入子表@H_404_43@
INSERT 0 1
MyTest=# SELECT name,altitude FROM cities WHERE altitude > 500;--父表和子表的数据均被取出。@H_404_43@
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
--只有子表的数据被取出。@H_404_43@
name | altitude
---------+----------
Madison | 845
(1 row)
如果希望只从父表中提取数据，则需要在sql中加入ONLY关键字，如：
404_43@
name | altitude
-----------+----------
Las Vegas | 2174
Mariposa | 1953
(2 rows)
上例中cities前面的"ONLY"关键字表示该查询应该只对cities进行查找而不包括继承级别低于cities的表。许多我们已经讨论过的命令--SELECT，UPDATE和DELETE--支持这个"ONLY"符号。
在执行整表数据删除时，如果直接truncate父表，此时父表和其所有子表的数据均被删除，如果只是truncate子表，那么其父表的数据将不会变化，只是子表中的数据被清空。
MyTest=# TRUNCATE TABLE cities;--父表和子表的数据均被删除。@H_404_43@
TRUNCATE TABLE
MyTest=# SELECT * FROM capitals;@H_404_43@
name | population | altitude | state
------+------------+----------+-------
(0 rows)
2. 确定数据来源：
有时候你可能想知道某条记录来自哪个表。在每个表里我们都有一个系统隐含字段tableoid，它可以告诉你表的来源：
MyTest=# SELECT tableoid,name,altitude FROM cities WHERE altitude > 500;@H_404_43@
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;@H_404_43@
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');@H_404_43@
我们可能希望数据被传递到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);@H_404_43@
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,255)"> logdate date NOT NULL,255)"> peaktemp int,255)"> );@H_404_43@
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);@H_404_43@
上面创建的子表，均已年、月的形式进行范围划分，不同年月的数据将归属到不同的子表内。这样的实现方式对于清空分区数据而言将极为方便和高效，即直接执行DROP TABLE语句删除相应的子表，之后在根据实际的应用考虑是否重建该子表(分区)。相比于直接DROP子表，Postgresql还提供了另外一种更为方便的方式来管理子表：
ALTER TABLEmeasurement_yy06mm01NO INHERITmeasurement;@H_404_43@
和直接DROP相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据(TRUNCATE TABLE)，之后再让该表重新继承主表，如：
ALTER TABLEmeasurement_yy06mm01INHERITmeasurement;@H_404_43@
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);@H_404_43@
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);@H_404_43@
5). 定义一个规则或者触发器，把对主表的修改重定向到适当的分区表。
如果数据只进入最新的分区，我们可以设置一个非常简单的规则来插入数据。我们必须每个月都重新定义这个规则，即修改重定向插入的子表名，这样它总是指向当前分区。
CREATE OR REPLACE RULEmeasurement_current_partitionAS
ON INSERT TOmeasurement@H_404_43@
DO INSTEAD@H_404_43@
INSERT INTO measurement_yy06mm01 VALUES (NEW.city_id,NEW.logdate,NEW.peaktemp);@H_404_43@
其中NEW是关键字，表示新数据字段的集合。这里可以通过点(.)操作符来获取集合中的每一个字段。
我们可能想插入数据并且想让服务器自动定位应该向哪个分区插入数据。我们可以用像下面这样的更复杂的规则集来实现这个目标。
CREATE RULEmeasurement_insert_yy04mm02AS
ON INSERT TOmeasurementWHERE(logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01')@H_404_43@
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')@H_404_43@
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')@H_404_43@
请注意每个规则里面的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);@H_404_43@
第二种方法的创建步骤相对繁琐，但更为灵活和实用。见以下四步：
/* 创建一个独立的数据表(measurement_y2008m02)，该表在创建时以将来的主表(measurement)为模板，包含模板表的缺省值(DEFAULTS)和一致性约束(CONSTRAINTS)。*/@H_404_43@
CREATE TABLEmeasurement_y2008m02
(LIKEmeasurementINCLUDING DEFAULTS INCLUDING CONSTRAINTS);@H_404_43@
/* 为该表创建未来作为子表时需要使用的检查约束。*/@H_404_43@
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
CHECK (logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01');@H_404_43@
/* 导入数据到该表。下面只是给出一种导入数据的方式作为例子。在导入数据之后，如有可能，还可以做进一步的数据处理，如数据转换、过滤等。*/@H_404_43@
\copy measurement_y2008m02 from 'measurement_y2008m02'@H_404_43@
/* 在适当的时候，或者说在需要的时候，让该表继承主表。*/@H_404_43@
ALTER TABLE measurement_y2008m02 INHERIT measurement;@H_404_43@
7). 确保postgresql.conf里的配置参数constraint_exclusion是打开的。没有这个参数，查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。
/> pwd@H_404_43@
/opt/Postgresql/9.1/data
/> cat postgresql.conf | grep "constraint_exclusion"@H_404_43@
constraint_exclusion = partition # on,off,or partition
3. 分区和约束排除：
约束排除(Constraint exclusion)是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。比如：
SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';@H_404_43@
如果没有约束排除，上面的查询会扫描measurement表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后再视图证明该分区不需要被扫描，因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。
你可以使用EXPLAIN命令显示一个规划在constraint_exclusion打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是：
SET constraint_exclusion = off;
EXPLAINSELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';@H_404_43@
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;@H_404_43@
EXPLAINSELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';@H_404_43@
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所支持的数值类型的列表和简单说明：
| 名字 | 存储空间 | 描述 | 范围 |
| smallint | 2 字节 | 小范围整数 | -32768 到 +32767 |
| integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647 |
| bigint | 8 字节 | 大范围的整数 | -9223372036854775808 到 9223372036854775807 |
| decimal | 变长 | 用户声明精度，精确 | 无限制 |
| numeric | real | 变精度，不精确 | 6 位十进制数字精度 |
| double | 15 位十进制数字精度 | ||
| serial | 自增整数 | 1 到 +2147483647 | |
| bigserial | 大范围的自增整数 | 1 到 9223372036854775807 |
1. 整数类型：
类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint。而只有在integer的范围不够的时候才使用bigint，因为前者(integer)绝对快得多。
2. 任意精度数值：
类型numeric可以存储最多1000位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢很多。
numeric字段的最大精度和最大比例都是可以配置的。要声明一个类型为numeric的字段，你可以用下面的语法：
NUMERIC(precision,scale)
比如数字23.5141的精度为6，而刻度为4。
在目前的Postgresql版本中，decimal和numeric是等效的。
3. 浮点数类型：
数据类型real和double是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储后再把数据打印出来可能显示一些缺失。
4. Serial(序号)类型：
serial和bigserial类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。
CREATE TABLE tablename (
colnameSERIAL
);
等价于
CREATE SEQUENCEtablename_colname_seq;
CREATE TABLE tablename(
colname integer DEFAULTnextval('tablename_colname_seq') NOT NULL
这样，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。应用了一个NOT NULL约束以确保空值不会被插入。在大多数情况下你可能还希望附加一个UNIQUE或者PRIMARY KEY约束避免意外地插入重复的数值，但这个不是自动发生的。因此，如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。
还需要另外说明的是，一个serial类型创建的序列在其所属字段被删除时，该序列也将被自动删除，但是其它情况下是不会被删除的。因此，如果你想用同一个序列发生器同时给几个字段提供数据，那么就应该以独立对象的方式创建该序列发生器。
二、字符类型：
下面是Postgresql所支持的字符类型的列表和简单说明：
| 描述 | |
| varchar(n) | 变长，有长度限制 |
| char(n) | 定长,不足补空白 |
| text | 变长，无长度限制 |
sql 定义了两种基本的字符类型，varchar(n)和char(n)，这里的n是一个正整数。两种类型都可以存储最多n个字符长的字串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长度。如果没有长度声明，char等于char(1)，而varchar则可以接受任何长度的字串。
MyTest=> CREATE TABLE testtable(first_col varchar(2));@H_404_43@
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES('333');--插入字符串的长度，超过其字段定义的长度，因此报错。@H_404_43@
ERROR: value too long for type character varying(2)
--插入字符串中，超出字段定义长度的部分是空格，因此可以插入，但是空白符被截断。@H_404_43@
MyTest=> INSERT INTO testtable VALUES('33 ');@H_404_43@
INSERT 0 1
MyTest=> SELECT * FROM testtable;@H_404_43@
first_col
-----------
33
(1 row)
这里需要注意的是，如果是将数值转换成char(n)或者varchar(n)，那么超长的数值将被截断成n个字符，而不会抛出错误。
MyTest=> select 1234::varchar(2);@H_404_43@
varchar
---------
12
(1 row)
最后需要提示的是，这三种类型之间没有性能差别，只不过是在使用char类型时增加了存储尺寸。虽然在某些其它的数据库系统里，char(n)有一定的性能优势，但在Postgresql里没有。在大多数情况下，应该使用text或者varchar。
三、日期/时间类型：
下面是Postgresql所支持的日期/时间类型的列表和简单说明：
| 最高值 | 分辨率 | ||||||||||
| timestamp[无时区] | 8字节 | 包括日期和时间 | 4713 BC | 5874897AD | 1毫秒/14位 | ||||||
| timestamp[含时区] | 日期和时间，带时区 | interval | 12字节 | 时间间隔 | -178000000年 | 178000000年 | date | 4字节 | 只用于日期 | 32767AD | 1天 |
| time[无时区] | 只用于一日内时间 | 00:00:00 | 24:00:00 | 1毫秒/14位 |
1. 日期/时间输入:
任何日期或者时间的文本输入均需要由单引号包围，就象一个文本字符串一样。
1). 日期:
以下为合法的日期格式列表：
| 例子 | January 8,1999 | 在任何datestyle输入模式下都无歧义 | |
| 1999-01-08 | ISO-8601格式，任何方式下都是1999年1月8号，(建议格式) | ||
| 1/8/1999 | 歧义，在MDY下是1月8号；在 DMY模式下读做8月1日 | ||
| 1/18/1999 | 在MDY模式下读做1月18日，其它模式下被拒绝 | ||
| 01/02/03 | MDY模式下的2003年1月2日；DMY模式下的2003年2月1日；YMD 模式下的2001年2月3日 | ||
| 1999-Jan-08 | 任何模式下都是1月8日 | ||
| Jan-08-1999 | 08-Jan-1999 | 99-Jan-08 | 在YMD模式下是1月8日，否则错误 |
| 08-Jan-99 | 1月8日，除了在YMD模式下是错误的之外 | ||
| Jan-08-99 | 19990108 | ISO-8601; 任何模式下都是1999年1月8日 | |
| 990108 | ISO-8601; 任何模式下都是1999年1月8日 |
2). 时间：
以下为合法的时间格式列表：
| ISO 8601 | ||||
| 04:05:06 | 04:05 | 040506 | 04:05 AM | 与04:05一样；AM不影响数值 |
| 04:05 PM | 与16:05一样；输入小时数必须 <= 12 | |||
| 04:05:06.789-8 | 04:05:06-08:00 | 04:05-08:00 | 040506-08 | ISO 8601 |
3). 时间戳：
时间戳类型的有效输入由一个日期和时间的联接组成，后面跟着一个可选的时区。因此，1999-01-08 04:05:06和1999-01-08 04:05:06 -8:00都是有效的数值。
2. 示例：
1). 在插入数据之前先查看datestyle系统变量的值：
MyTest=> showdatestyle;@H_404_43@
DateStyle
-----------
ISO,YMD
2). 创建包含日期、时间和时间戳类型的示例表：
MyTest=> CREATE TABLE testtable (id integer,date_col date,time_col time,timestamp_col timestamp);@H_404_43@
CREATE TABLE
3). 插入数据：
MyTest=> INSERT INTO testtable(id,date_col) VALUES(1,DATE'01/02/03');--datestyle为YMD@H_404_43@
INSERT 0 1
MyTest=> SELECT id,date_col FROM testtable;@H_404_43@
id | date_col
----+------------
1 | 2001-02-03
(1 row)
MyTest=> set datestyle =MDY;@H_404_43@
SET
MyTest=> INSERT INTO testtable(id,date_col) VALUES(2,0)">--datestyle为MDY@H_404_43@
INSERT 0 1
MyTest=> SELECT id,0)"> 2 | 2003-01-02
MyTest=> INSERT INTO testtable(id,time_col) VALUES(3,TIME'10:20:00');--插入时间。@H_404_43@
404_43@
id | time_col
----+----------
3 | 10:20:00
(1 row)
MyTest=> INSERT INTO testtable(id,timestamp_col) VALUES(4,DATE'01/02/03');@H_404_43@
INSERT 0 1
MyTest=> INSERT INTO testtable(id,timestamp_col) VALUES(5,TIMESTAMP'01/02/03 10:20:00');@H_404_43@
404_43@
id | timestamp_col
----+---------------------
4 | 2003-01-02 00:00:00
5 | 2003-01-02 10:20:00
(2 rows)
四、布尔类型：
Postgresql支持标准的sql boolean数据类型。boolean只能有两个状态之一：真(True)或 假(False)。该类型占用1个字节。
"真"值的有效文本值是：
TRUE@H_404_43@
't'@H_404_43@
'true'@H_404_43@
'y'@H_404_43@
'yes'@H_404_43@
'1'@H_404_43@
而对于"假"而言，你可以使用下面这些：
FALSE@H_404_43@
'f'@H_404_43@
'false'@H_404_43@
'n'@H_404_43@
'no'@H_404_43@
'0'@H_404_43@ 见如下使用方式：
MyTest=> CREATE TABLE testtable (a boolean,b text);@H_404_43@
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES(TRUE,'sic est');@H_404_43@
INSERT 0 1
MyTest=> INSERT INTO testtable VALUES(FALSE,'non est');@H_404_43@
INSERT 0 1
MyTest=> SELECT * FROM testtable;@H_404_43@
a | b
---+---------
t | sic est
f | non est
(2 rows)
MyTest=> SELECT * FROM testtable WHERE a;@H_404_43@
(1 row)
MyTest=> SELECT * FROM testtable WHERE a = true;@H_404_43@
(1 row)
五、位串类型：
位串就是一串1和0的字串。它们可以用于存储和视觉化位掩码。我们有两种类型的sql位类型：bit(n)和bit varying(n); 这里的n是一个正整数。bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的数据都是错误的。类型bit varying数据是最长n的变长类型；更长的串会被拒绝。写一个没有长度的bit等效于bit(1)，没有长度的bit varying相当于没有长度限制。
针对该类型，最后需要提醒的是，如果我们明确地把一个位串值转换成bit(n)，那么它的右边将被截断或者在右边补齐零，直到刚好n位，而不会抛出任何错误。类似地，如果我们明确地把一个位串数值转换成bit varying(n)，如果它超过n位，那么它的右边将被截断。 见如下具体使用方式：
MyTest=> CREATE TABLE testtable (a bit(3),b bit varying(5));@H_404_43@
CREATE TABLE
MyTest=> INSERT INTO testtable VALUES (B'101',B'00');@H_404_43@
INSERT 0 1
MyTest=> INSERT INTO testtable VALUES (B'10',B'101');@H_404_43@
ERROR: bit string length 2 does not match type bit(3)
MyTest=> INSERT INTO testtable VALUES (B'10'::bit(3),0)"> -----+-----
101 | 00
100 | 101
(2 rows)
MyTest=> SELECTB'11'::bit(3);@H_404_43@
bit
-----
110
(1 row)
六、数组：
1. 数组类型声明：
1). 创建字段含有数组类型的表。
CREATE TABLE sal_emp (
pay_by_quarter integer[]--还可以定义为integer[4]或integer ARRAY[4]@H_404_43@
);
2). 插入数组数据：
MyTest=# INSERT INTO sal_emp VALUES ('Bill','{11000,12000,13000,14000}');@H_404_43@
INSERT 0 1
MyTest=# INSERT INTO sal_emp VALUES ('Carol',ARRAY[21000,22000,23000,24000]);@H_404_43@
INSERT 0 1
MyTest=# SELECT * FROM sal_emp;@H_404_43@
name | pay_by_quarter
--------+---------------------------
Bill | {11000,14000}
Carol | {21000,24000}
(2 rows)
2. 访问数组：
和其他语言一样，Postgresql中数组也是通过下标数字(写在方括弧内)的方式进行访问，只是Postgresql中数组元素的下标是从1开始n结束。
MyTest=# SELECT pay_by_quarter[3] FROM sal_emp;@H_404_43@
pay_by_quarter
----------------
13000
23000
(2 rows)
MyTest=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];@H_404_43@
name
------
Bill
Carol
(2 rows)
Postgresql中还提供了访问数组范围的功能，即ARRAY[脚标下界:脚标上界]。
MyTest=# SELECT name,pay_by_quarter[1:3]FROM sal_emp;@H_404_43@
name | pay_by_quarter
--------+---------------------
Bill | {11000,13000}
Carol | {21000,23000}
(2 rows)
3. 修改数组：
1). 代替全部数组值：
--UPDATE sal_emp SET pay_by_quarter =ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; 也可以。@H_404_43@
MyTest=# UPDATE sal_emp SET pay_by_quarter = '{31000,32000,33000,34000}' WHERE name = 'Carol';@H_404_43@
UPDATE 1
MyTest=# UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill';@H_404_43@
UPDATE 1
MyTest=# SELECT * FROM sal_emp;@H_404_43@
name | pay_by_quarter
--------+---------------------------
Carol | {31000,34000}
Bill | {11000,15000}
(2 rows)
3). 更新数组某一范围的元素：
MyTest=# UPDATE sal_emp SET pay_by_quarter[1:2] = '{37000,37000}' WHERE name = 'Carol';@H_404_43@
UPDATE 1
MyTest=# UPDATE sal_emp SET pay_by_quarter[5] = 45000 WHERE name = 'Bill';@H_404_43@
UPDATE 1
MyTest=# SELECT * FROM sal_emp;@H_404_43@
name | pay_by_quarter
--------+---------------------------------
Carol | {37000,34000}
Bill | {11000,15000,45000}
(2 rows)
4. 在数组中检索：
1). 最简单直接的方法：
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;
2). 更加有效的方法：
SELECT * FROM sal_emp WHERE 10000 =ANY(pay_by_quarter);--数组元素中有任何一个等于10000，where条件将成立。@H_404_43@
SELECT * FROM sal_emp WHERE 10000 =ALL(pay_by_quarter);--只有当数组中所有的元素都等于10000时，where条件才成立。@H_404_43@
七、复合类型：
Postgresql中复合类型有些类似于C语言中的结构体，也可以被视为Oracle中的记录类型，但是还是感觉复合类型这个命名比较贴切。它实际上只是一个字段名和它们的数据类型的列表。Postgresql允许像简单数据类型那样使用复合类型。比如，表字段可以声明为一个复合类型。
1. 声明复合类型:
下面是两个简单的声明示例：
CREATE TYPEcomplexAS(
r double,
i double
);
CREATE TYPEinventory_itemAS(
name text,
supplier_id integer,
price numeric
);
和声明一个数据表相比，声明类型时需要加AS关键字，同时在声明TYPE时不能定义任何约束。下面我们看一下如何在表中指定复合类型的字段，如：
CREATE TABLE on_hand (
iteminventory_item,
count integer
);
最后需要指出的是，在创建表的时候，Postgresql也会自动创建一个与该表对应的复合类型，名字与表字相同，即表示该表的复合类型。
2. 复合类型值输入：
我们可以使用文本常量的方式表示复合类型值，即在圆括号里包围字段值并且用逗号分隔它们。你也可以将任何字段值用双引号括起，如果值本身包含逗号或者圆括号，那么就用双引号括起，对于上面的inventory_item复合类型的输入如下：
'("fuzzy dice",42,1.99)'
如果希望类型中的某个字段为NULL，只需在其对应的位置不予输入即可，如下面的输入中price字段的值为NULL，
'("fuzzy dice",)'
如果只是需要一个空字串，而非NULL，写一对双引号，如：
'("",0)"> 在更多的场合中Postgresql推荐使用ROW表达式来构建复合类型值，使用该种方式相对简单，无需考虑更多标识字符问题，如：
ROW('fuzzy dice',1.99)
ROW('',NULL)
注：对于ROW表达式，如果里面的字段数量超过1个，那么关键字ROW就可以省略，因此以上形式可以简化为：
('fuzzy dice',1.99)
('',NULL)
3. 访问复合类型：
访问复合类型中的字段和访问数据表中的字段在形式上极为相似，只是为了对二者加以区分，Postgresql设定在访问复合类型中的字段时，类型部分需要用圆括号括起，以避免混淆，如：
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
如果在查询中也需要用到表名，那么表名和类型名都需要被圆括号括起，如：
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
4. 修改复合类型：
见如下几个示例：
--直接插入复合类型的数据，这里是通过ROW表达式来完成的。@H_404_43@
INSERT INTO on_hand(item) VALUES(ROW("fuzzy dice",1.99));
--在更新操作中，也是可以通过ROW表达式来完成。@H_404_43@
UPDATE on_hand SET item = ROW("fuzzy dice",1.99) WHERE count = 0;
--在更新复合类型中的一个字段时，我们不能在SET后面出现的字段名周围加圆括号，
--但是在等号右边的表达式里引用同一个字段时却需要圆括号。@H_404_43@
UPDATE on_hand SET item.price = (item).price + 1 WHERE count = 0;
--可以在插入中，直接插入复合类型中字段。@H_404_43@
INSERT INTO on_hand (item.supplier_id,item.price) VALUES(100,2.2);
(函数和操作符<一>)
一、逻辑操作符：
常用的逻辑操作符有：AND、OR和NOT。其语义与其它编程语言中的逻辑操作符完全相同。
二、比较操作符：
下面是Postgresql中提供的比较操作符列表：
| 操作符 | 描述 |
| < | 小于 |
| > | 大于 |
| <= | 小于或等于 |
| >= | 大于或等于 |
| = | 等于 |
| != | 不等于 |
比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符，且返回boolean类型。除了比较操作符以外，我们还可以使用BETWEEN语句，如：
aBETWEENxANDy等效于a >= xANDa <= y
aNOT BETWEENxANDy等效于a < xORa > y
三、 数学函数和操作符：
下面是Postgresql中提供的数学操作符列表：
| 描述 | 例子 | 结果 | ||||||
| + | 加 | 2 + 3 | 5 | |||||
| - | 减 | 2 - 3 | -1 | |||||
| * | 乘 | 2 * 3 | 6 | |||||
| / | 除 | 4 / 2 | 2 | |||||
| % | 模 | 5 % 4 | 1 | |||||
| ^ | 幂 | 2.0 ^ 3.0 | 8 | |||||
| |/ | 平方根 | |/ 25.0 | ||/ | 立方根 | ||/ 27.0 | 3 | ||
| ! | 阶乘 | 5 ! | 120 | |||||
| !! | !! 5 | @ | 绝对值 | @ -5.0 | & | 按位AND | 91 & 15 | 11 |
| | | 按位OR | 32 | 3 | 35 | |||||
| # | 按位XOR | 17 # 5 | 20 | |||||
| ~ | 按位NOT | ~1 | -2 | |||||
| << | 按位左移 | 1 << 4 | 16 | |||||
| >> | 按位右移 | 8 >> 2 | 2 |
按位操作符只能用于整数类型，而其它的操作符可以用于全部数值数据类型。按位操作符还可以用于位串类型bit和bit varying，
下面是Postgresql中提供的数学函数列表，需要说明的是，这些函数中有许多都存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。
| 函数 | 返回类型 |
例子 |
abs(x) | abs(-17.4) | 17.4 | |||
| cbrt(double) | cbrt(27.0) | ceil(double/numeric) | 不小于参数的最小的整数 | ceil(-42.8) | -42 | |||
| degrees(double) |
把弧度转为角度 | degrees(0.5) | 28.6478897565412 | |||||
| exp(double/numeric) | 自然指数 | exp(1.0) | 2.71828182845905 | |||||
| floor(double/numeric) | 不大于参数的最大整数 | floor(-42.8) | -43 | |||||
| ln(double/numeric) | 自然对数 | ln(2.0) | 0.693147180559945 | |||||
| log(double/numeric) | 10为底的对数 | log(100.0) | log(b numeric,x numeric) | numeric指定底数的对数 | log(2.0,64.0) | 6.0000000000 | ||
| mod(y,x) | 取余数 | mod(9,4) | pi() |
"π"常量 | 3.14159265358979 | |||
| power(a double,b double) | 求a的b次幂 | power(9.0,3.0) | 729 | |||||
| power(a numeric,b numeric) | radians(double) | 把角度转为弧度 | radians(45.0) | 0.785398163397448 | ||||
| random() | 0.0到1.0之间的随机数值 | random() | ||||||
| round(double/numeric) | 圆整为最接近的整数 | round(42.4) | 42 | |||||
| round(v numeric,s int) | 圆整为s位小数数字 | round(42.438,2) | 42.44 | |||||
| sign(double/numeric) | 参数的符号(-1,+1) |
sign(-8.4) | sqrt(double/numeric) | sqrt(2.0) | 1.4142135623731 | |||
| trunc(double/numeric) | 截断(向零靠近) | trunc(42.8) | trunc(v numeric,255); font-size:13px">截断为s小数位置的数字 | trunc(42.438,255); font-size:13px">42.43 |
三角函数列表：
| 函数 | acos(x) | 反余弦 |
| asin(x) | 反正弦 | |
| atan(x) | 反正切 | |
| atan2(x,y) | 正切 y/x 的反函数 | |
| cos(x) | 余弦 | |
| cot(x) | 余切 | |
| sin(x) | 正弦 | |
| tan(x) | 正切 |
四、字符串函数和操作符：
下面是Postgresql中提供的字符串操作符列表：
| 函数 | 返回类型 | 描述 | 例子 | 结果 | ||
| string || string | text | 字串连接 | 'Post' || 'gresql' | Postgresql | ||
| bit_length(string) | int | 字串里二进制位的个数 | bit_length('jose') | 32 | ||
| char_length(string) | 字串中的字符个数 | char_length('jose') | 4 | |||
| convert(string using conversion_name) | 使用指定的转换名字改变编码。 | convert('Postgresql' using iso_8859_1_to_utf8) | 'Postgresql' | |||
| lower(string) | 把字串转化为小写 | lower('TOM') | tom | |||
| octet_length(string) | 字串中的字节数 | octet_length('jose') | overlay(string placing string from int [for int]) | 替换子字串 | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
| position(substring in string) | 指定的子字串的位置 | position('om' in 'Thomas') | 3 | |||
| substring(string [from int] [for int]) | 抽取子字串 | substring('Thomas' from 2 for 3) | hom | |||
| substring(string from pattern) | 抽取匹配 POSIX 正则表达式的子字串 | substring('Thomas' from '...$') | mas | |||
| substring(string from pattern for escape) | 抽取匹配sql正则表达式的子字串 | substring('Thomas' from '%#"o_a#"_' for '#') | oma | |||
| trim([leading | trailing | both] [characters] from string) | 从字串string的开头/结尾/两边/ 删除只包含characters(缺省是一个空白)的最长的字串 | trim(both 'x' from 'xTomxx') | Tom | |||
| upper(string) | 把字串转化为大写。 | upper('tom') | TOM | |||
| ascii(text) | 参数第一个字符的ASCII码 | ascii('x') | 120 | |||
| btrim(string text [,characters text]) | 从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串 | btrim('xyxtrimyyx','xy') | trim | |||
| chr(int) | 给出ASCII码的字符 | chr(65) | A | |||
| convert(string text,[src_encoding name,] dest_encoding name) | 把字串转换为dest_encoding | convert( 'text_in_utf8','UTF8','LATIN1') | 以ISO 8859-1编码表示的text_in_utf8 | |||
| initcap(text) | 把每个单词的第一个子母转为大写，其它的保留小写。单词是一系列字母数字组成的字符，用非字母数字分隔。 | initcap('hi thomas') | Hi Thomas | |||
| length(string text) | string中字符的数目 | length('jose') | lpad(string text,length int [,fill text]) | 通过填充字符fill(缺省时为空白)，把string填充为长度length。 如果string已经比length长则将其截断(在右边)。 | lpad('hi',5,255)">xyxhi | |
| ltrim(string text [,255)">从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。 | ltrim('zzzytrim','xyz') | md5(string text) | 计算给出string的MD5散列，以十六进制返回结果。 | md5('abc') | ||
| repeat(string text,number int) | 重复string number次。 | repeat('Pg',255)">PgPgPgPg | ||||
| replace(string text,from text,to text) | 把字串string里出现地所有子字串from替换成子字串to。 | replace('abcdefabcdef','cd','XX') | abXXefabXXef | |||
| rpad(string text,255)">通过填充字符fill(缺省时为空白)，把string填充为长度length。如果string已经比length长则将其截断。 | rpad('hi',255)">hixyx | |||||
| rtrim(string text [,character text]) | 从字串string的结尾删除只包含character(缺省是个空白)的最长的字 | rtrim('trimxxxx','x') | split_part(string text,delimiter text,field int) | 根据delimiter分隔string返回生成的第field个子字串(1 Base)。 | split_part('abc~@~def~@~ghi','~@~',255)">def | |
| strpos(string,substring) | 声明的子字串的位置。 | strpos('high','ig') | 2 | |||
| substr(string,from [,count]) | 抽取子字串。 | substr('alphabet',3,255)">ph | ||||
| to_ascii(text [,encoding]) | 把text从其它编码转换为ASCII。 | to_ascii('Karel') | Karel | |||
| to_hex(number int/bigint) | 把number转换成其对应地十六进制表现形式。 | to_hex(9223372036854775807) | 7fffffffffffffff | |||
| translate(string text,255)">把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。 | translate('12345','14','ax') | a23x5 |
五、位串函数和操作符：
对于类型bit和bit varying，除了常用的比较操作符之外，还可以使用以下列表中由Postgresql提供的位串函数和操作符，其中&、|和#的位串操作数必须等长。在移位的时候，保留原始的位串的的长度。
| 操作符 | 描述 | 例子 | 结果 |
| || | 连接 | B'10001' || B'011' | 10001011 |
| B'10001' & B'01101' | 00001 | ||
| B'10001' | B'01101' | 11101 | ||
| B'10001' # B'01101' | 11100 | ||
| ~ B'10001' | 01110 | ||
| B'10001' << 3 | 01000 | ||
| B'10001' >> 2 | 00100 |
除了以上列表中提及的操作符之外，位串还可以使用字符串函数：length， bit_length， octet_length， position， substring。此外，我们还可以在整数和bit之间来回转换，如：
MyTest=# SELECT 44::bit(10);@H_404_43@
bit
------------
0000101100
(1 row)
MyTest=# SELECT 44::bit(3);@H_404_43@
bit
-----
100
(1 row)
MyTest=# SELECT cast(-44 as bit(12));@H_404_43@
bit
--------------
111111010100
(1 row)
MyTest=# SELECT '1110'::bit(4)::integer;@H_404_43@
int4
------
14
(1 row)
注意：如果只是转换为"bit"，意思是转换成bit(1)，因此只会转换成整数的最低位。
(函数和操作符<二>)
六、模式匹配：
Postgresql中提供了三种实现模式匹配的方法：sql LIKE操作符，更近一些的SIMILAR TO操作符，和POSIX-风格正则表达式。
1. LIKE：
stringLIKEpattern [ESCAPEescape-character ]
stringNOT LIKEpattern [ESCAPEescape-character ]
每个pattern定义一个字串的集合。如果该string包含在pattern代表的字串集合里，那么LIKE表达式返回真。和我们想象的一样，如果LIKE返回真，那么NOT LIKE表达式返回假，反之亦然。在pattern里的下划线(_)代表匹配任何单个字符，而一个百分号(%)匹配任何零或更多字符，如：
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' false@H_404_43@
要匹配文本的下划线或者百分号，而不是匹配其它字符，在pattern里相应的字符必须前导转义字符。缺省的转义字符是反斜杠，但是你可以用ESCAPE子句指定一个。要匹配转义字符本身，写两个转义字符。我们也可以通过写成ESCAPE ''的方式有效地关闭转义机制，此时，我们就不能关闭下划线和百分号的特殊含义了。
关键字ILIKE可以用于替换LIKE，令该匹配就当前的区域设置是大小写无关的。这个特性不是sql标准，是Postgresql的扩展。操作符~~等效于LIKE， 而~~*对应ILIKE。还有!~~和!~~*操作符分别代表NOT LIKE和NOT ILIKE。所有这些操作符都是Postgresql特有的。
2. SIMILAR TO正则表达式：
SIMILAR TO根据模式是否匹配给定的字符串而返回真或者假。
stringSIMILAR TOpattern [ESCAPEescape-character]
stringNOT SIMILAR TOpattern [ESCAPEescape-character]
它和LIKE非常类似，支持LIKE的通配符('_'和'%')且保持其原意。除此之外，SIMILAR TO还支持一些自己独有的元字符，如：
1).|标识选择(两个候选之一)。
2).*表示重复前面的项零次或更多次。
3).+表示重复前面的项一次或更多次。
4). 可以使用圆括弧()把项组合成一个逻辑项。
5). 一个方括弧表达式[...]声明一个字符表，就像POSIX正则表达式一样。
见如下示例：
'abc' SIMILAR TO 'abc' true
'abc' SIMILAR TO 'a' false
'abc' SIMILAR TO '%(b|d)%' true
'abc' SIMILAR TO '(b|c)%' false@H_404_43@
带三个参数的substring，substring(string from pattern for escape-character)，提供了一个从字串中抽取一个匹配sql正则表达式模式的子字串的函数。和SIMILAR TO一样，声明的模式必须匹配整个数据串，否则函数失效并返回NULL。为了标识在成功的时候应该返回的模式部分，模式必须出现后跟双引号(")的两个转义字符。匹配这两个标记之间的模式的字串将被返回，如：
MyTest=# SELECT substring('foobar' from '%#"o_b#"%' FOR '#');--这里#是转义符，双引号内的模式是返回部分。@H_404_43@
substring
-----------
oob
(1 row)
MyTest=# SELECT substring('foobar' from '#"o_b#"%' FOR '#');--foobar不能完全匹配后面的模式，因此返回NULL。@H_404_43@
substring
-----------
(1 row)
七、数据类型格式化函数：
Postgresql格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。
| 函数 | 返回类型 | 描述 | 例子 | |
| to_char(timestamp,text) | text | 把时间戳转换成字串 | to_char(current_timestamp,'HH12:MI:SS') | |
| to_char(interval,255); font-size:14px">把时间间隔转为字串 | to_char(interval '15h 2m 12s','HH24:MI:SS') | |||
| to_char(int,255); font-size:14px">把整数转换成字串 | to_char(125,'999') | |||
| to_char(double precision,255); font-size:14px">把实数/双精度数转换成字串 | to_char(125.8::real,'999D9') | |||
| to_char(numeric,255); font-size:14px">把numeric转换成字串 | to_char(-125.8,'999D99S') | |||
| to_date(text,255); font-size:14px">date | 把字串转换成日期 | to_date('05 Dec 2000','DD Mon YYYY') | ||
| to_timestamp(text,255); font-size:14px">timestamp | 把字串转换成时间戳 | to_timestamp('05 Dec 2000',255); font-size:14px">to_timestamp(double) | 把UNIX纪元转换成时间戳 | to_timestamp(200120400) |
| to_number(text,255); font-size:14px">numeric | 把字串转换成numeric | to_number('12,454.8-','99G999D9S') |
1. 用于日期/时间格式化的模式：
| 模式 | 描述 | |
| HH | 一天的小时数(01-12) | |
| HH12 | HH24 | 一天的小时数(00-23) |
| MI | 分钟(00-59) | |
| SS | 秒(00-59) | |
| MS | 毫秒(000-999) | |
| US | 微秒(000000-999999) | |
| AM | 正午标识(大写) | |
| Y,YYY | 带逗号的年(4和更多位) | |
| YYYY | 年(4和更多位) | |
| YYY | 年的后三位 | |
| YY | 年的后两位 | |
| Y | 年的最后一位 | |
| MONTH | 全长大写月份名(空白填充为9字符) | |
| Month | 全长混合大小写月份名(空白填充为9字符) | |
| month | 全长小写月份名(空白填充为9字符) | |
| MON | 大写缩写月份名(3字符) | |
| Mon | 缩写混合大小写月份名(3字符) | |
| mon | 小写缩写月份名(3字符) | |
| MM | 月份号(01-12) | |
| DAY | 全长大写日期名(空白填充为9字符) | |
| Day | 全长混合大小写日期名(空白填充为9字符) | |
| day | 全长小写日期名(空白填充为9字符) | |
| DY | 缩写大写日期名(3字符) | |
| Dy | 缩写混合大小写日期名(3字符) | |
| dy | 缩写小写日期名(3字符) | |
| DDD | 一年里的日子(001-366) | |
| DD | 一个月里的日子(01-31) | |
| D | 一周里的日子(1-7；周日是1) | |
| W | 一个月里的周数(1-5)(第一周从该月第一天开始) | |
| WW | 一年里的周数(1-53)(第一周从该年的第一天开始) |
2. 用于数值格式化的模板模式：
| 带有指定数值位数的值 | |
| 0 | 带前导零的值 |
| .(句点) | 小数点 |
| ,(逗号) | 分组(千)分隔符 |
| PR | 尖括号内负值 |
| S | 带符号的数值 |
| L | 货币符号 |
| G | 分组分隔符 |
| 在指明的位置的负号(如果数字 < 0) | |
| PL | 在指明的位置的正号(如果数字 > 0) |
| SG | 在指明的位置的正/负号 |
八、时间/日期函数和操作符：
1. 下面是Postgresql中支持的时间/日期操作符的列表：
| date '2001-10-05' | |
| date '2001-09-28' + interval '1 hour' | timestamp '2001-09-28 01:00' |
| date '2001-09-28' + time '03:00' | timestamp '2001-09-28 03:00' |
| interval '1 day' + interval '1 hour' | interval '1 day 01:00' |
| timestamp '2001-09-28 01:00' + interval '23 hours' | timestamp '2001-09-29 00:00' |
| time '01:00' + interval '3 hours' | time '04:00' |
| - interval '23 hours' | interval '-23:00' |
| date '2001-10-01' - date '2001-09-28' | integer '3' |
| date '2001-10-01' - integer '7' | date '2001-09-24' |
| date '2001-09-28' - interval '1 hour' | timestamp '2001-09-27 23:00' |
| time '05:00' - time '03:00' | interval '02:00' |
| time '05:00' - interval '2 hours' | time '03:00' |
| timestamp '2001-09-28 23:00' - interval '23 hours' | timestamp '2001-09-28 00:00' |
| interval '1 day' - interval '1 hour' | interval '23:00' |
| timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' | interval '1 day 15:00' |
| interval '1 hour' * double precision '3.5' | interval '03:30' |
| interval '1 hour' / double precision '1.5' | interval '00:40' |
2. 日期/时间函数：
| 返回类型 | age(timestamp,timestamp) | 减去参数，生成一个使用年、月的"符号化"的结果 | age('2001-04-10',timestamp '1957-06-13') | 43 years 9 mons 27 days | ||||
| age(timestamp) | 从current_date减去得到的数值 | age(timestamp '1957-06-13') | 43 years 8 mons 3 days | |||||
| current_date | 今天的日期 | |||||||
| current_time | time | 现在的时间 | ||||||
| current_timestamp | timestamp | 日期和时间 | ||||||
| date_part(text,255); font-size:13px">获取子域(等效于extract) | date_part('hour',timestamp '2001-02-16 20:38:40') | date_part('month',interval '2 years 3 months') | date_trunc(text,255); font-size:13px">截断成指定的精度 | date_trunc('hour',255); font-size:13px">2001-02-16 20:00:00+00 | ||||
| extract(field from timestamp) | 获取子域 | extract(hour from timestamp '2001-02-16 20:38:40') | extract(field from interval) | extract(month from interval '2 years 3 months') | localtime | 今日的时间 | ||
| localtimestamp | now() | 当前的日期和时间(等效于 current_timestamp) | ||||||
| timeofday() | 当前日期和时间 |
3. EXTRACT，date_part函数支持的field：
| CENTURY | 世纪 | EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'); | DAY | (月分)里的日期域(1-31) | EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40'); | DECADE | 年份域除以10 | EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40'); | 200 |
| DOW | 每周的星期号(0-6；星期天是0) (仅用于timestamp) | EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); | DOY | 一年的第几天(1 -365/366) (仅用于 timestamp) | EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40'); | 47 | |||
| HOUR | 小时域(0-23) | EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40'); | MICROSECONDS | 秒域，包括小数部分，乘以 1,000,000。 | EXTRACT(MICROSECONDS from TIME '17:12:28.5'); | 28500000 | |||
| MILLENNIUM | 千年 | EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40'); | MILLISECONDS | 秒域，包括小数部分，乘以 1000。 | EXTRACT(MILLISECONDS from TIME '17:12:28.5'); | 28500 | |||
| MINUTE | 分钟域(0-59) | EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40'); | 38 | ||||||
| MONTH | 对于timestamp数值，它是一年里的月份数(1-12)；对于interval数值，它是月的数目，然后对12取模(0-11) | EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40'); | QUARTER | 该天所在的该年的季度(1-4)(仅用于 timestamp) | EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40'); | SECOND | 秒域，包括小数部分(0-59[1]) | EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40'); | 40 |
| WEEK | 该天在所在的年份里是第几周。 | EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); | 7 | ||||||
| YEAR | 年份域 | EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); | 2001 |
4. 当前日期/时间：
我们可以使用下面的函数获取当前的日期和/或时间∶
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)
九、序列操作函数：
序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。下面序列函数，为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法。
| 函数 | 返回类型 | 描述 |
| nextval(regclass) | bigint | 递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行nextval，每个进程也会安全地收到一个唯一的序列值。 |
| currval(regclass) | 在当前会话中返回最近一次nextval抓到的该序列的数值。(如果在本会话中从未在该序列上调用过nextval，那么会报告一个错误。)请注意因为此函数返回一个会话范围的数值，而且也能给出一个可预计的结果，因此可以用于判断其它会话是否执行过nextval。 | |
| lastval() | 返回当前会话里最近一次nextval返回的数值。这个函数等效于currval，只是它不用序列名为参数，它抓取当前会话里面最近一次nextval使用的序列。如果当前会话还没有调用过nextval，那么调用lastval将会报错。 | |
| setval(regclass,bigint) | 重置序列对象的计数器数值。设置序列的last_value字段为指定数值并且将其is_called字段设置为true，表示下一次nextval将在返回数值之前递增该序列。 | |
| 重置序列对象的计数器数值。功能等同于上面的setval函数，只是is_called可以设置为true或false。如果将其设置为false，那么下一次nextval将返回该数值，随后的nextval才开始递增该序列。 |
对于regclass参数，仅需用单引号括住序列名即可，因此它看上去就像文本常量。为了达到和处理普通sql对象一样的兼容性，这个字串将被转换成小写，除非该序列名是用双引号括起，如：
nextval('foo')--操作序列号foo
nextval('FOO') nextval('"Foo"')--操作序列号Foo
SELECT setval('foo',42);--下次nextval将返回43
404_43@
--下次nextval将返回42@H_404_43@
十、条件表达式：
1. CASE：
sql CASE表达式是一种通用的条件表达式，类似于其它语言中的if/else语句。
CASE WHENconditionTHENresult
[WHEN ...]
[ELSEresult]
END
condition是一个返回boolean的表达式。如果为真，那么CASE表达式的结果就是符合条件的result。如果结果为假，那么以相同方式搜寻随后的WHEN子句。如果没有WHEN condition为真，那么case表达式的结果就是在ELSE子句里的值。如果省略了ELSE子句而且没有匹配的条件，结果为NULL，如：
MyTest=> SELECT * FROM testtable;@H_404_43@
i
---
1
2
3
(3 rows)
MyTest=> SELECT i,CASE WHEN i=1 THEN 'one'
MyTest-> WHEN i=2 THEN 'two'
MyTest-> ELSE 'other'
MyTest-> END
MyTest-> FROM testtable;@H_404_43@
i | case
---+-------
1 | one
2 | two
3 | other
(3 rows)
注：CASE表达式并不计算任何对于判断结果并不需要的子表达式。
2. COALESCE：
COALESCE返回它的第一个非NULL的参数的值。它常用于在为显示目的检索数据时用缺省值替换NULL值。
COALESCE(value[,...])
和CASE表达式一样，COALESCE将不会计算不需要用来判断结果的参数。也就是说，在第一个非空参数右边的参数不会被计算。
3. NULLIF：
当且仅当value1和value2相等时，NULLIF才返回NULL。否则它返回value1。
NULLIF(value1,value2)
MyTest=> SELECT NULLIF('abc','abc');@H_404_43@
nullif
--------
(1 row)
MyTest=> SELECT NULLIF('abcd','abc');@H_404_43@
nullif
--------
abcd
(1 row)
4. GREATEST和LEAST：
GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。列表中的NULL数值将被忽略。只有所有表达式的结果都是NULL的时候，结果才会是NULL。
GREATEST(value [,...])
LEAST(value [,...])
MyTest=> SELECT GREATEST(1,5);@H_404_43@
greatest
----------
5
(1 row)
MyTest=> SELECT LEAST(1,NULL);@H_404_43@
least
-------
1
(1 row)
十一、数组函数和操作符：
1. Postgresql中提供的用于数组的操作符列表：
| ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] | t | |||||||
| <> | 不等于 | ARRAY[1,3] <> ARRAY[1,4] | 小于 | 大于 | 小于或等于 | 大于或等于 | 数组与数组连接 | {1,6} |
| {{1,3},{4,6},{7,9}} | ||||||||
| 元素与数组连接 | 3 || ARRAY[4,255); font-size:13px">{3,255); font-size:13px">ARRAY[4,6] || 7 | {4,6,7} |
| anyarray | 连接两个数组 | array_cat(ARRAY[1,3],ARRAY[4,5]) | {1,5} | |||||
| array_append(anyarray,anyelement) | 向一个数组末尾附加一个元素 | array_append(ARRAY[1,2],3) | array_prepend(anyelement,255)">向一个数组开头附加一个元素 | array_prepend(1,ARRAY[2,3]) | array_dims(anyarray) | 返回一个数组维数的文本表示 | array_dims(ARRAY[[1,[4,6]]) | [1:2][1:3] |
| array_lower(anyarray,int) | 返回指定的数组维数的下界 | array_lower(array_prepend(0,ARRAY[1,3]),1) | 0 | |||||
| array_upper(anyarray,255)">返回指定数组维数的上界 | array_upper(ARRAY[1,4],255)">array_to_string(anyarray,255)">使用提供的分隔符连接数组元素 | array_to_string(ARRAY[1,'~^~') | 1~^~2~^~3 | |||||
| string_to_array(text,255)">text[] | 使用指定的分隔符把字串拆分成数组元素 | string_to_array('xx~^~yy~^~zz',255)">{xx,yy,zz} |
十二、系统信息函数：
1. Postgresql中提供的和数据库相关的函数列表：
| 描述 | ||
| current_database() | name | 当前数据库的名字 |
| current_schema() | 当前模式的名字 | |
| current_schemas(boolean) | name[] | 在搜索路径中的模式名字 |
| current_user | 目前执行环境下的用户名 | |
| inet_client_addr() | inet | 连接的远端地址 |
| inet_client_port() | int | 连接的远端端口 |
| inet_server_addr() | 连接的本地地址 | |
| inet_server_port() | 连接的本地端口 | |
| session_user | 会话用户名 | |
| pg_postmaster_start_time() | postmaster启动的时间 | |
| user | current_user | |
| version() | Postgresql版本信息 |
| 描述 | 可用权限 | |
| has_table_privilege(user,table,privilege) | 用户是否有访问表的权限 | SELECT/INSERT/UPDATE/DELETE/RULE/REFERENCES/TRIGGER |
| has_table_privilege(table,255)">当前用户是否有访问表的权限 | has_database_privilege(user,database,255)">用户是否有访问数据库的权限 | CREATE/TEMPORARY |
| has_database_privilege(database,255)">当前用户是否有访问数据库的权限 | has_function_privilege(user,function,255)">用户是否有访问函数的权限 | EXECUTE |
| has_function_privilege(function,255)">当前用户是否有访问函数的权限 | has_language_privilege(user,language,255)">用户是否有访问语言的权限 | USAGE |
| has_language_privilege(language,255)">当前用户是否有访问语言的权限 | has_schema_privilege(user,schema,255)">用户是否有访问模式的权限 | CREAT/USAGE |
| has_schema_privilege(schema,255)">当前用户是否有访问模式的权限 | has_tablespace_privilege(user,tablespace,255)">用户是否有访问表空间的权限 | CREATE |
| has_tablespace_privilege(tablespace,255)">当前用户是否有访问表空间的权限 | CREATE |
注：以上函数均返回boolean类型。要评估一个用户是否在权限上持有赋权选项，给权限键字附加 WITH GRANT OPTION；比如 'UPDATE WITH GRANT OPTION'。@H_404_43@
3. 模式可视性查询函数：
那些判断一个对象是否在当前模式搜索路径中可见的函数。 如果一个表所在的模式在搜索路径中，并且没有同名的表出现在搜索路径的更早的地方，那么就说这个表视可见的。 它等效于表可以不带明确模式修饰进行引用。
| pg_table_is_visible(table_oid) | 该表/视图是否在搜索路径中可见 | regclass | ||
| pg_type_is_visible(type_oid) | 该类/视图型是否在搜索路径中可见 | regtype | ||
| pg_function_is_visible(function_oid) | 该函数是否在搜索路径中可见 | regprocedure | ||
| pg_operator_is_visible(operator_oid) | 该操作符是否在搜索路径中可见 | regoperator | ||
| pg_opclass_is_visible(opclass_oid) | 该操作符表是否在搜索路径中可见 | pg_conversion_is_visible(conversion_oid) | 转换是否在搜索路径中可见 | regoperator |
注：以上函数均返回boolean类型，所有这些函数都需要对象 OID 标识作为检查的对象。
postgres=# SELECTpg_table_is_visible('testtable'::regclass);@H_404_43@
pg_table_is_visible
---------------------
t
(1 row)
4. 系统表信息函数：
| 获取一个数据类型的sql名称 | ||
| pg_get_viewdef(view_oid) | 为视图获取CREATE VIEW命令 | |
| pg_get_viewdef(view_oid,pretty_bool) | pg_get_ruledef(rule_oid) | 为规则获取CREATE RULE命令 |
| pg_get_ruledef(rule_oid,255); font-size:13px">pg_get_indexdef(index_oid) | 为索引获取CREATE INDEX命令 | |
| pg_get_indexdef(index_oid,column_no,255); font-size:13px">为索引获取CREATE INDEX命令， 如果column_no不为零，则是只获取一个索引字段的定义 | ||
| pg_get_triggerdef(trigger_oid) | 为触发器获取CREATE [CONSTRAINT] TRIGGER | |
| pg_get_constraintdef(constraint_oid) | 获取一个约束的定义 | |
| pg_get_constraintdef(constraint_oid,255); font-size:13px">pg_get_expr(expr_text,relation_oid) | 反编译一个表达式的内部形式，假设其中的任何Vars都引用第二个参数指出的关系 | |
| pg_get_userbyid(roleid) | 获取给出的ID的角色名 | |
| pg_get_serial_sequence(table_name,column_name) | 获取一个serial或者bigserial字段使用的序列名字 | |
| pg_tablespace_databases(tablespace_oid) | setof oid | 获取在指定表空间(OID表示)中拥有对象的一套数据库的OID的集合 |
这些函数大多数都有两个变种，其中一个可以选择对结果的"漂亮的打印"。 漂亮打印的格式更容易读，但是缺省的格式更有可能被将来的Postgresql版本用同样的方法解释；如果是用于转储，那么尽可能避免使用漂亮打印。 给漂亮打印参数传递false生成的结果和那个没有这个参数的变种生成的结果完全一样。
十三、系统管理函数：
1. 查询以及修改运行时配置参数的函数：
| current_setting(setting_name) | 当前设置的值 |
| set_config(setting_name,new_value,is_local) | 设置参数并返回新值 |
current_setting用于以查询形式获取setting_name设置的当前数值。它和sql命令SHOW是等效的。 比如：
MyTest=# SELECT current_setting('datestyle');@H_404_43@
current_setting
-----------------
ISO,YMD
(1 row)
set_config将参数setting_name设置为new_value。如果is_local设置为true，那么新数值将只应用于当前事务。如果你希望新的数值应用于当前会话，那么应该使用false。它等效于sql命令SET。比如：
MyTest=# SELECT set_config('log_statement_stats','off',false);@H_404_43@
set_config
------------
off
(1 row)
2. 数据库对象尺寸函数：
| 指定OID代表的表空间使用的磁盘空间 | |
| pg_tablespace_size(name) | 指定名字的表空间使用的磁盘空间 |
| pg_database_size(oid) | 指定OID代表的数据库使用的磁盘空间 |
| pg_database_size(name) | 指定名称的数据库使用的磁盘空间 |
| pg_relation_size(oid) | 指定OID代表的表或者索引所使用的磁盘空间 |
| pg_relation_size(text) | 指定名称的表或者索引使用的磁盘空间。这个名字可以用模式名修饰 |
| pg_total_relation_size(oid) | 指定OID代表的表使用的磁盘空间，包括索引和压缩数据 |
| pg_total_relation_size(text) | 指定名字的表所使用的全部磁盘空间，包括索引和压缩数据。表名字可以用模式名修饰。 |
| pg_size_pretty(bigint) | 把字节计算的尺寸转换成一个人类易读的尺寸单位 |
| oid | 获取指定对象的文件节点编号(通常为对象的oid值)。 |
| pg_relation_filepath(relationregclass) | 获取指定对象的完整路径名。 |
mydatabase=# select pg_relation_filenode('testtable');
@H_404_43@ pg_relation_filenode
----------------------
17877
(1 row) mydatabase=# select pg_relation_filepath('testtable');
@H_404_43@ pg_relation_filepath
----------------------------------------------
pg_tblspc/17633/PG_9.1_201105231/17636/17877
(1 row) @H_404_43@
(索引)
一、索引的类型：
Postgresql提供了多 种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。
1. B-Tree:
CREATE TABLE test1 (
id integer,255)"> content varchar
CREATE INDEXtest1_id_indexONtest1 (id);
B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" <、<=、=、>=和>"作为查询条件时，Postgresql的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，Postgresql也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。
2. Hash：
CREATE INDEXnameONtableUSING hash(column);
散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。
这里需要额外说明的是，Postgresql散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。
3. GiST：
GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。
4. GIN：
GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，Postgresql的标准发布中包含了用于一维数组的GIN操作符类型，如：<@、@>、=、&&等。
二、复合索引：
Postgresql中的索引可以定义在数据表的多个字段上，如：
CREATE TABLE test2 (
major int,255)"> minor int,255)"> name varchar
}
CREATE INDEX test2_mm_idx ON test2 (major,minor);@H_404_43@
在当前的版本中，只有B-tree、GiST和GIN支持复合索引，其中最多可以声明32个字段。
1. B-Tree类型的复合索引：
在B-Tree类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段(最左边)被包含其中时，才可以获得最高效率。
2. GiST类型的复合索引：
在GiST类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值，那么此时应用GiST索引就会比较低效。
3. GIN类型的复合索引：
与B-Tree和GiST索引不同的是，GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得到相同的效率。
使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引几乎没什么用处。
三、组合多个索引：
Postgresql可以在查询时组合多个索引(包括同一索引的多次使用)，来处理单个索引扫描不能实现的场合。与此同时，系统还可以在多个索引扫描之间组成AND和OR的条件。比如，一个类似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查询，可以被分解成四个独立的基于x字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x = 5 AND y = 6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND操作并生成最终的结果行。
为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行AND或者OR的操作并得出最终的BITMAP。最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用也会如此。
四、唯一索引：
目前，只有B-Tree索引可以被声明为唯一索引。
CREATE UNIQUE INDEXnameONtable (column [,...]);
如果索引声明为唯一索引，那么就不允许出现多个索引值相同的行。我们认为NULL值相互间不相等。
五、表达式索引：
表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况，如：
SELECT * FROM test1 WHERE lower(col1) = 'value';@H_404_43@
此时，如果我们仅仅是在col1字段上建立索引，那么该查询在执行时一定不会使用该索引，而是直接进行全表扫描。如果该表的数据量较大，那么执行该查询也将会需要很长时间。解决该问题的办法非常简单，在test1表上建立基于col1字段的表达式索引，如：
CREATE INDEX test1_lower_col1_idx ON test1(lower(col1));@H_404_43@
如果我们把该索引声明为UNIQUE，那么它会禁止创建那种col1数值只是大小写有区别的数据行，以及col1数值完全相同的数据行。因此，在表达式上的索引可以用于强制那些无法定义为简单唯一约束的约束。现在让我们再看一个应用表达式索引的例子。
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';@H_404_43@
和上面的例子一样，尽管我们可能会为first_name和last_name分别创建独立索引，或者是基于这两个字段的复合索引，在执行该查询语句时，这些索引均不会被使用，该查询能够使用的索引只有我们下面创建的表达式索引。
CREATE INDEX people_names ON people((first_name || ' ' || last_name));@H_404_43@
CREATE INDEX命令的语法通常要求在索引表达式周围书写圆括弧，就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用，那么可以省略，就像我们在第一个例子里显示的那样。
从索引维护的角度来看，索引表达式要相对低效一些，因为在插入数据或者更新数据的时候，都必须为该行计算表达式的结果，并将该结果直接存储到索引里。然而在查询时，Postgresql就会把它们看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于简单索引的查询。通常而言，我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。
六、部分索引：
部分索引(partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。
由于不是在所有的情况下都需要更新索引，因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小，因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例：
1. 索引字段和谓词条件字段一致：
CREATE INDEXaccess_log_client_ip_ixONaccess_log(client_ip)
WHERENOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');@H_404_43@
下面的查询将会用到该部分索引：
SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';@H_404_43@
下面的查询将不会用该部分索引：
一个不能使用这个索引的查询可以是∶
SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';@H_404_43@
2. 索引字段和谓词条件字段不一致：
Postgresql支持带任意谓词的部分索引，唯一的约束是谓词的字段也要来自于同样的数据表。注意，如果你希望你的查询语句能够用到部分索引，那么就要求该查询语句的条件部分必须和部分索引的谓词完全匹配。 准确说，只有在Postgresql能够识别出该查询的WHERE条件在数学上涵盖了该索引的谓词时，这个部分索引才能被用于该查询。
CREATE INDEXorders_unbilled_indexONorders(order_nr)WHEREbilled is not true;@H_404_43@
下面的查询一定会用到该部分索引：
SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;@H_404_43@
那么对于如下查询呢？
SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;@H_404_43@
这个查询将不像上面那个查询这么高效，毕竟查询的条件语句中没有用到索引字段，然而查询条件"billed is not true"却和部分索引的谓词完全匹配，因此Postgresql将扫描整个索引。这样只有在索引数据相对较少的情况下，该查询才能更有效一些。
下面的查询将不会用到部分索引。
SELECT * FROM orders WHERE order_nr = 3501;@H_404_43@
3. 数据表子集的唯一性约束：
CREATE TABLE tests (
subject text,255)"> target text,255)"> success boolean,255)"> ...
CREATE UNIQUE INDEXtests_success_constraintONtests(subject,target)WHEREsuccess;@H_404_43@
该部分索引将只会对success字段值为true的数据进行唯一性约束。在实际的应用中，如果成功的数据较少，而不成功的数据较多时，该实现方法将会非常高效。
七、检查索引的使用：
见以下四条建议：
1. 总是先运行ANALYZE。
该命令将会收集表中数值分布状况的统计。在估算一个查询返回的行数时需要这个信息，而规划器则需要这个行数以便给每个可能的查询规划赋予真实的开销值。如果缺乏任何真实的统计信息，那么就会使用一些缺省数值，这样肯定是不准确的。因此，如果还没有运行ANALYZE就检查一个索引的使用状况，那将会是一次失败的检查。
2. 使用真实的数据做实验。
用测试数据填充数据表，那么该表的索引将只会基于测试数据来评估该如何使用索引，而不是对所有的数据都如此使用。比如从100000行中选1000行，规划器可能会考虑使用索引，那么如果从100行中选1行就很难说也会使用索引了。因为100行的数据很可能是存储在一个磁盘页面中，然而没有任何查询规划能比通过顺序访问一个磁盘页面更加高效了。与此同时，在模拟测试数据时也要注意，如果这些数据是非常相似的数据、完全随机的数据，或按照排序顺序插入的数据，都会令统计信息偏离实际数据应该具有的特征。
3. 如果索引没有得到使用，那么在测试中强制它的使用也许会有些价值。有一些运行时参数可以关闭各种各样的查询规划。
4. 强制使用索引用法将会导致两种可能：一是系统选择是正确的，使用索引实际上并不合适，二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引的查询进行计时，这个时候EXPLAIN ANALYZE命令就很有用了。
(事物隔离)
在sql的标准中事物隔离级别分为以下四种：
1. 读未提交(Read uncommitted)
2. 读已提交(Read committed)
3. 可重复读(Repeatable read)
4. 可串行化(Serializable)
然而Postgresql在9.1之前的版本中只是实现了其中两种，即读已提交和可串行化，如果在实际应用中选择了另外两种，那么Postgresql将会自动向更严格的隔离级别调整。在Postgresql v9.1的版本中提供了三种实现方式，即在原有的基础上增加了可重复读。在这篇博客中我们将只是针对2)和4)进行说明和比较，因为在9.1中，3)和4)的差别也是非常小的。
| 读已提交 | 可串行化 | |
| Postgresql缺省隔离级别 | 是 | 否 |
| 其它事物未提交数据是否可见 | 不可见 | 不可见 |
| 执行效率 | 高 | 低 |
| 适用场景 | 简单sql逻辑，如果sql语句中含有嵌套查询，那么在多次SQL查询中将极有可能获得不同版本的数据。 | 复杂sql逻辑，特别是带有嵌套的查询比较适用。 |
| SELECT查询一致性时间点 | 从该SELECT查询开始执行时，在此查询执行期间，任何其它并发事物针对该查询结果集的数据操作都将不会被本次查询读到，即本次查询获取的数据版本是与查询开始执行时的数据版本相一致。 | 从该SELECT查询所在事物开始时，在此查询执行期间，任何其它并发事物针对该查询结果集的数据操作都将不会被本次查询读到，即本次查询获取的数据版本是与查询所在事物开始时的数据版本相一致。 |
| 同事物内的数据操作是否可见 | 比如在同一个事物内存在update和select操作，即使当前事物尚未提交，update所作的修改，在当前事物后面的select中依然可见。 | 和读已提交相同。 |
| 同事物内多次相同的select所见的数据是否相同 | 不同，由于该级别select的一致性时间点是该查询开始执行时，而多次查询的时间点将肯定不相同，如果在第一次查询开始到第二次查询开始之间，其它的并发事物修改并提交或当前事物仅修改了查询将要获取的数据，那么这些数据操作的结果将会在第二个查询中有所体现。 | 需要分两步来说，对于同一事物内的修改如果发生在两次查询语句之间，那么第二个查询将会看到这些修改的结果。然而对于其它并发事物的修改，将不会造成任何影响，即两次select的结果是相同的。原因显而易见，该隔离级别的select一致性时间点是与事物开始时相一致的。 |
| 相同行数据的修改 | 如果此时两个并发事物在修改同一行数据，先修改的事物将会给该行加行级锁，另外一个事物将进入等待状态，直到第一个事物操作该行结束。那么倘若第一个针对该行的修改操作最终被其事物回滚，第二个修改操作在结束等待后，将直接修改该数据。然而如果第一个操作是被正常提交的话，那么就需要进一步判断该操作的类型，如果是删除(delete)该行，第二个修改操作将直接被忽略。如果是update该行的记录，第二个修改操作则需要重新评估该行是否依然符合之前定义的修改条件。 | 和读已提交隔离级别的机制基本相同，只是在第一个修改操作提交后，第二个操作将不再区分之前的修改是delete还是update，而是直接并返回下面信息：Error: Can't serialize access due to concurrent update. 这是因为一个可串行化的事务在可串行化事务开始之后不能更改或者锁住被其他事务更改过的行。因此，当应用收到这样的错误信息时，它应该退出当前的事务然后从头开始重新进行整个事务。在应用程序中，也应该有必要的代码来专门处理该类错误。 |
最后需要说明的是，在绝大多数的情况下，读已提交级别均可适用，而且该级别的并发效率更高。只有在比较特殊的情况下，才手工将当前的事物隔离级别调整为可串行化或可重复读。
Postgresql为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。Postgresql本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。
Postgresql中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同的扫描节点类型代表着不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连接、聚集、排序，或者是对原始行的其它操作，那么就会在扫描节点"之上"有其它额外的节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每个节点都输出一行信息，显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计，这个数值就是规划器试图最小化的数值。
这里有一个简单的例子，如下：
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
EXPLAIN引用的数据是：
1). 预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点里做排续的时间)。
2). 预计的总开销。
3). 预计的该规划节点输出的行数。
4). 预计的该规划节点的行平均宽度(单位：字节)。
这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出行数(rows)并不是规划节点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际返回的行数。
现在我们执行下面基于系统表的查询：
SELECT relpages,reltuples FROM pg_class WHERE relname = 'tenk1';
从查询结果中可以看出tenk1表占有358个磁盘页面和10000条记录，然而为了计算cost的值，我们仍然需要知道另外一个系统参数值。
postgres=# show cpu_tuple_cost;
cpu_tuple_cost
0.01
cost = 358(磁盘页面数) + 10000(行数) * 0.01(cpu_tuple_cost系统参数值)
下面我们再来看一个带有WHERE条件的查询规划。
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 < 7000)
EXPLAIN的输出显示，WHERE子句被当作一个"filter"应用，这表示该规划节点将扫描表中的每一行数据，之后再判定它们是否符合过滤的条件，最后仅输出通过过滤条件的行数。这里由于WHERE子句的存在，预计的输出行数减少了。即便如此，扫描仍将访问所有10000行数据，因此开销并没有真正降低，实际上它还增加了一些因数据过滤而产生的额外cpu开销。
上面的数据只是一个预计数字，即使是在每次执行ANALYZE命令之后也会随之改变，因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。
如果我们将上面查询的条件设置的更为严格一些的话，将会得到不同的查询规划，如：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
这里，规划器决定使用两步规划，最内层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点再从表里读取这些行。单独地读取数据行比顺序地读取它们的开销要高很多，但是因为并非访问该表的所有磁盘页面，因此该方法的开销仍然比一次顺序扫描的开销要少。这里使用两层规划的原因是因为上层规划节点把通过索引检索出来的行的物理位置先进行排序，这样可以最小化单独读取磁盘页面的开销。节点名称里面提到的"位图(bitmap)"是进行排序的机制。
现在我们还可以将WHERE的条件设置的更加严格，如：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 < 3)
在该sql中，表的数据行是以索引的顺序来读取的，这样就会令读取它们的开销变得更大，然而事实上这里将要获取的行数却少得可怜，因此没有必要在基于行的物理位置进行排序了。
现在我们需要向WHERE子句增加另外一个条件，如：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Filter: (stringu1 = 'xxx'::name)
新增的过滤条件stringu1 = 'xxx'只是减少了预计输出的行数，但是并没有减少实际开销，因为我们仍然需要访问相同数量的数据行。而该条件并没有作为一个索引条件，而是被当成对索引结果的过滤条件来看待。
如果WHERE条件里有多个字段存在索引，那么规划器可能会使用索引的AND或OR的组合，如：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=11.27..11.27 rows=11 width=0)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 > 9000)
这样的结果将会导致访问两个索引，与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。
现在让我们来看一下基于索引字段进行表连接的查询规划，如：
EXPLAIN SELECT * FROM tenk1 t1,tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: ("outer".unique2 = t2.unique2)
从查询规划中可以看出(Nested Loop)该查询语句使用了嵌套循环。外层的扫描是一个位图索引，因此其开销与行计数和之前查询的开销是相同的，这是因为条件unique1 < 100发挥了作用。 这个时候t1.unique2 = t2.unique2条件子句还没有产生什么作用，因此它不会影响外层扫描的行计数。然而对于内层扫描而言，当前外层扫描的数据行将被插入到内层索引扫描中，并生成类似的条件t2.unique2 = constant。所以，内层扫描将得到和EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42一样的计划和开销。最后，以外层扫描的开销为基础设置循环节点的开销，再加上每个外层行的一个迭代(这里是 106 * 3.01)，以及连接处理需要的一点点cpu时间。
如果不想使用嵌套循环的方式来规划上面的查询，那么我们可以通过执行以下系统设置，以关闭嵌套循环，如：
SET enable_nestloop = off;
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: ("outer".unique2 = "inner".unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
这个规划仍然试图用同样的索引扫描从tenk1里面取出符合要求的100行，并把它们存储在内存中的散列(哈希)表里，然后对tenk2做一次全表顺序扫描，并为每一条tenk2中的记录查询散列(哈希)表，寻找可能匹配t1.unique2 = t2.unique2的行。读取tenk1和建立散列表是此散列联接的全部启动开销，因为我们在开始读取tenk2之前不可能获得任何输出行。
此外，我们还可以用EXPLAIN ANALYZE命令检查规划器预估值的准确性。这个命令将先执行该查询，然后显示每个规划节点内实际运行时间，以及单纯EXPLAIN命令显示的预计开销，如：
EXPLAIN ANALYZE SELECT * FROM tenk1 t1,tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2; ----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
Total runtime: 14.452 ms
注意"actual time"数值是以真实时间的毫秒来计算的，而"cost"预估值是以磁盘页面读取数量来计算的，所以它们很可能是不一致的。然而我们需要关注的只是两组数据的比值是否一致。
在一些查询规划里，一个子规划节点很可能会运行多次，如之前的嵌套循环规划，内层的索引扫描会为每个外层行执行一次。在这种情况下，"loops"将报告该节点执行的总次数，而显示的实际时间和行数目则是每次执行的平均值。这么做的原因是令这些真实数值与开销预计显示的数值更具可比性。如果想获得该节点所花费的时间总数，计算方式是用该值乘以"loops"值。
EXPLAIN ANALYZE显示的"Total runtime"包括执行器启动和关闭的时间，以及结果行处理的时间，但是它并不包括分析、重写或者规划的时间。
如果EXPLAIN命令仅能用于测试环境，而不能用于真实环境，那它就什么用都没有。比如，在一个数据较少的表上执行EXPLAIN，它不能适用于数量很多的大表，因为规划器的开销计算不是线性的，因此它很可能对大些或者小些的表选择不同的规划。一个极端的例子是一个只占据一个磁盘页面的表，在这样的表上，不管它有没有索引可以使用，你几乎都总是得到顺序扫描规划。规划器知道不管在任何情况下它都要进行一个磁盘页面的读取，所以再增加几个磁盘页面读取用以查找索引是毫无意义的。
二、批量数据插入：
有以下几种方法用于优化数据的批量插入。
1. 关闭自动提交：
在批量插入数据时，如果每条数据都被自动提交，当中途出现系统故障时，不仅不能保障本次批量插入的数据一致性，而且由于有多次提交操作的发生，整个插入效率也会受到很大的打击。解决方法是，关闭系统的自动提交，并且在插入开始之前，显示的执行begin transaction命令，在全部插入操作完成之后再执行commit命令提交所有的插入操作。
2. 使用COPY：
使用COPY在一条命令里装载所有记录，而不是一系列的INSERT命令。COPY命令是为装载数量巨大的数据行优化过的，它不像INSERT命令那样灵活，但是在装载大量数据时，系统开销也要少很多。因为COPY是单条命令，因此在填充表的时就没有必要关闭自动提交了。
3. 删除索引：
如果你正在装载一个新创建的表，最快的方法是创建表，用COPY批量装载，然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间，其它有关该表的查询操作的性能将会受到一定的影响，唯一性约束也有可能遭到破坏。
4. 删除外键约束：
和索引一样，"批量地"检查外键约束比一行行检查更加高效。因此，我们可以先删除外键约束，装载数据，然后在重建约束。
5. 增大maintenance_work_mem：
在装载大量数据时，临时增大maintenance_work_mem系统变量的值可以改进性能。这个系统参数可以提高CREATE INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的执行效率，但是它不会对COPY操作本身产生多大的影响。
6. 增大checkpoint_segments：
临时增大checkpoint_segments系统变量的值也可以提高大量数据装载的效率。这是因为在向Postgresql装载大量数据时，将会导致检查点操作(由系统变量checkpoint_timeout声明)比平时更加频繁的发生。在每次检查点发生时，所有的脏数据都必须flush到磁盘上。通过提高checkpoint_segments变量的值，可以有效的减少检查点的数目。
7. 事后运行ANALYZE：
在增加或者更新了大量数据之后，应该立即运行ANALYZE命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。
(服务器配置)
一、服务器进程的启动和关闭：
下面是pg_ctl命令的使用方法和常用选项，需要指出的是，该命令是postgres命令的封装体，因此在使用上比直接使用postgres更加方便。
pg_ctl init[db] [-D DATADIR] [-s] [-o "OPTIONS"]
pg_ctl start [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
pg_ctl stop [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl restart [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]
pg_ctl promote [-D DATADIR] [-s]
| -D | 指定数据库存储的路径 |
| -l | 指定服务器进程的日志文件 |
| -s | 仅打印错误信息，不打印普通信息 |
| -t SECS | 当使用-w选项时等待的秒数 |
| -w | 等待直到数据库操作完成(对于stop而言，该选项时缺省选项) |
| -W | 不等待任何操作的完成 |
| --help | 显示帮助信息 |
| --version | 显示版本信息 |
| -m | 对于stop和restart操作，可以指定关闭模式 |
| 系统关闭模式 | |
| smart | 不在接收新的连接，直到当前已有的连接都断开之后才退出系统 |
| fast | 不在接收新的连接请求，主动关闭已经建立的连接，之后退出系统 |
| immediate | 立即退出，但是在restart的时候需要有恢复的操作被执行 |
这里我们只是给出最为常用的使用方式，即数据库服务器的正常启动和关闭。
#start表示启动postgres服务器进程。@H_404_43@
#-D指定数据库服务器的初始目录的存放路径。@H_404_43@
#-l指定数据库服务器进程的日志文件@H_404_43@
/> pg_ctl -w start -D /opt/Postgresql/9.1/data -l /opt/Postgresql/9.1/data/pg_log/startup.log@H_404_43@
#stop表示停止postgres服务器进程
#-m fast在关闭系统时，使用fast的关闭模式。@H_404_43@
/> pg_ctl stop -m fast -w -D /opt/Postgresql/9.1/data@H_404_43@
二、服务器配置：
1. 设置参数：
在Postgresql中，所有配置参数名都是大小写不敏感的。每个参数都可以接受四种类型的值，它们分别是布尔、整数、浮点数和字符串。其中布尔值可以是ON、OFF、TRUE、FALSE、YES、NO、1和0。包含这些参数的配置文件是postgresql.conf，该文件通常存放在initdb初始化的数据(data)目录下，见如下配置片段：
# 这是一个注释
log_connections = yes
log_destination = 'syslog'
search_path = '$user,public'
井号(#)开始的行为注释行，如果配置值中包含数字，则需要用单引号括起。如果参数值本身包含单引号，我们可以写两个单引号(推荐方法)或用反斜扛包围。
这里需要注意的是，并非所有配置参数都可以在服务器运行时执行动态修改，有些参数在修改后，只能等到服务器重新启动后才能生效。
Postgresql还提供了另外一种修改配置参数的方法，即在命令行上直接执行修改命令，如：
/> postgres -c log_connections=yes -c log_destination='syslog'@H_404_43@
如果此时命令行设置的参数和配置文件中的参数相互冲突，那么命令行中给出的参数将覆盖配置文件中已有的参数值。除此之外，我们还可以通过ALTER DATABASE和ALTER USER等Postgresql的数据定义命令来分别修改指定数据库或指定用户的配置信息。其中针对数据库的设置将覆盖任何从postgres命令行或者配置文件从给出的设置，然后又会被针对用户的设置覆盖，最后又都会被每会话的选项覆盖。下面是当服务器配置出现冲突时，Postgresql服务器将会采用哪种方式的优先级，如：
1). 基于会话的配置；
2). 基于用户的配置；
3). 基于数据库的配置；
4). postgres命令行指定的配置；
5). 配置文件postgresql.conf中给出的配置。
最后需要说明的是，有些设置可以通过Postgresql的set命令进行设置，如在psql中我们可以输入：
SET ENABLE_SEQSCAN TO OFF;@H_404_43@
也可以通过show命令来显示指定配置的当前值，如：
SHOW ENABLE_SEQSCAN;@H_404_43@
与此同时，我们也可以手工查询pg_settings系统表的方式来检索感兴趣的系统参数。
三、内存相关的参数配置：
1. shared_buffers(integer)：
设置数据库服务器可以使用的共享内存数量。缺省情况下可以设置为32MB，但是不要少于128KB。因为该值设置的越高对系统的性能越有好处。该配置参数只能在数据库启动时设置。
此时，如果你有一台专用的数据库服务器，其内存为1G或者更多，那么我们推荐将该值设置为系统内存的25%。
2. work_mem(integer)：
Postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和work_mem差不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。然而需要指出的是，如果系统中同时存在多个排序操作，那么每个操作在排序时使用的内存数量均为work_mem，因此在我们设置该值时需要注意这一问题。
3. maintence_work_mem(integer)：
指定在维护性操作中使用的最大内存数，如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等，该配置的缺省值为16MB。因为每个会话在同一时刻只能执行一个该操作，所以使用的频率不高，但是这些指令往往消耗较多的系统资源，因此应该尽快让这些指令快速执行完毕。
一、数据库角色：
1. 创建角色：
CREATE ROLErole_name;
2. 删除角色：
DROP ROLErole_name;
3. 查询角色：
检查系统表pg_role，如：
SELECT usename FROMpg_role;
也可以在psql中执行\du命令列出所有角色。
二、角色属性：
一个数据库角色可以有一系列属性，这些属性定义他的权限，以及与客户认证系统的交互。
1. 登录权限：
只有具有LOGIN属性的角色才可以用于数据库连接，因此我们可以将具有该属性的角色视为登录用户，创建方法有如下两种：
CREATE ROLEnameLOGIN PASSWORD'123456‘;
CREATE USERnamePASSWORD'123456';
2. 超级用户：
数据库的超级用户拥有该数据库的所有权限，为了安全起见，我们最好使用非超级用户完成我们的正常工作。和创建普通用户不同，创建超级用户必须是以超级用户的身份执行以下命令：
CREATE ROLEname SUPERUSER ;
3. 创建数据库：
角色要想创建数据库，必须明确赋予创建数据库的属性，见如下命令：
CREATE ROLEnameCREATEDB;
4. 创建角色：
一个角色要想创建更多角色，必须明确给予创建角色的属性，见如下命令：
CREATEROLE 三、权限：
数据库对象在被创建时都会被赋予一个所有者，通常而言，所有者就是执行对象创建语句的角色。对于大多数类型的对象，其初始状态是只有所有者(或超级用户)可以对该对象做任何事情。如果要允许其它用户可以使用该对象，必须赋予适当的权限。Postgresql中预定义了许多不同类型的内置权限，如： SELECT、INSERT、UPDATE、DELETE、RULE、REFERENCES、TRIGGER、CREATE、TEMPORARY、EXECUTE和USAGE 。
我们可以使用GRANT命令来赋予权限，如：
GRANT UPDATE ONaccountsTOjoe;
对于上面的命令，其含义为将accounts表的update权限赋予joe角色。此外，我们也可以用特殊的名字PUBLIC把对象的权限赋予系统中的所有角色。在权限声明的位置上写ALL，表示把适用于该对象的所有权限都赋予目标角色。
要撤销权限，使用合适的REVOKE命令：
REVOKE ALL ONaccountsFROM PUBLIC;
其含义为：对所有角色(PUBLIC)撤销在accounts对象上的所有权限(ALL)。
四、角色成员：
在系统的用户管理中，通常会把多个用户赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤消。在Postgresql中，首先需要创建一个代表组的角色，之后再将该角色的membership权限赋给独立的用户角色即可。
1. 创建一个组角色，通常而言，该角色不应该具有LOGIN属性，如：
CREATE ROLE name; 2. 使用GRANT和REVOKE命令添加和撤消权限：
GRANT group_role TO role1,... ;
REVOKE group_role FROM role1,sans-serif; line-height:18px"> 一个角色成员可以通过两种方法使用组角色的权限，如：
1. 每个组成员都可以用SET ROLE命令将自己临时"变成"该组成员，此后再创建的任何对象的所有者将属于该组，而不是原有的登录用户。
2. 拥有INHERIT属性的角色成员自动继承它们所属角色的权限。
见如下示例：
CREATE ROLE joe LOGININHERIT; --INHERIT是缺省属性。
CREATE ROLE adminNOINHERIT;
CREATE ROLE wheelNOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;
现在我们以角色joe的身份与数据库建立连接，那么该数据库会话将同时拥有角色joe和角色admin的权限，这是因为joe"继承(INHERIT)"了admin的权限。然而与此不同的是，赋予wheel角色的权限在该会话中将不可用，因为joe角色只是wheel角色的一个间接成员，它是通过admin角色间接传递过来的，而admin角色却含有NOINHERIT属性，这样wheel角色的权限将无法被joe继承。
这样wheel角色的权限将无法被joe继承。此时，我们可以在该会话中执行下面的命令:
SET ROLE admin;
在执行之后，该会话将只拥有admin角色的权限，而不再包括赋予joe角色的权限。同样，在执行下面的命令之后，该会话只能使用赋予wheel的权限。
SET ROLE wheel;
在执行一段时间之后，如果仍然希望将该会话恢复为原有权限，可以使用下列恢复方式之一：
SET ROLE joe;
SET ROLE NONE;
RESET ROLE;
注意: SET ROLE命令总是允许选取当前登录角色的直接或间接组角色。因此，在变为wheel之前没必要先变成admin。
角色属性 LOGIN、SUPERUSER CREATEROLE 被视为特殊权限，它们不会像其它数据库对象的普通权限那样被继承。如果需要，必须在调用SET ROLE时显示指定拥有该属性的角色。比如，我们也可以给admin角色赋予CREATEDB和CREATEROLE权限，然后再以joe的角色连接数据库，此时该会话不会立即拥有这些特殊权限，只有当执行SET ROLE admin命令之后当前会话才具有这些权限。
要删除一个组角色，执行 DROP ROLE group_role 命令即可。然而在删除该组角色之后，它与其成员角色之间的关系将被立即撤销(成员角色本身不会受影响)。不过需要注意的是，在删除之前，任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，与此同时，任何赋予该组角色的权限也都必须被撤消。
(数据库管理)
数据库可以被看成是sql对象(数据库对象)的命名集合，通常而言，每个数据库对象(表、函数等)只属于一个数据库。不过对于部分系统表而言，如pg_database，是属于整个集群的。更准确地说，数据库是模式的集合，而模式包含表、函数等sql对象。因此完整的对象层次应该是这样的：服务器、数据库、模式、表或其他类型的对象。
在与数据库服务器建立连接时，该连接只能与一个数据库形成关联，不允许在一个会话中进行多个数据库的访问。如以postgres用户登录，该用户可以访问的缺省数据库为postgres，在登录后如果执行下面的sql语句将会收到Postgresql给出的相关错误信息。
postgres=# SELECT * FROM MyTest."MyUser".testtables;
ERROR: cross-database references are not implemented: "otherdb.otheruser.soMetable"
LINE 1: select * from otherdb.otheruser.soMetable
在Postgresql中，数据库在物理上是相互隔离的，对它们的访问控制也是在会话层次上进行的。然而模式只是逻辑上的对象管理结构，是否能访问某个模式的对象是由权限系统来控制的。
执行下面的基于系统表的查询语句可以列出现有的数据库集合。
SELECT datname FROMpg_database;
注： psql 应用程序的 \l 元命令和 -l 命令行选项也可以用来列出当前服务器中已有的数据库。
二、创建数据库：
在Postgresql服务器上执行下面的sql语句可以创建数据库。
CREATE DATABASEdb_name;
在数据库成功创建之后，当前登录角色将自动成为此新数据库的所有者。在删除该数据库时，也需要该用户的特权。如果你想让当前创建的数据库的所有者为其它角色，可以执行下面的sql语句。
CREATE DATABASEdb_nameOWNERrole_name;
三、修改数据库配置：
Postgresql服务器提供了大量的运行时配置变量，我们可以根据自己的实际情况为某一数据库的某一配置变量指定特殊值，通过执行下面的sql命令可以使该数据库的某一配置被设置为指定值，而不再使用缺省值。
ALTER DATABASEdb_nameSETvarnameTOnew_value;
这样在之后基于该数据库的会话中，被修改的配置值已经生效。如果要撤消这样的设置并恢复为原有的缺省值，可以执行下面的sql命令。
ALTER DATABASEdbnameRESETvarname;
四、删除数据库：
只有数据库的所有者和超级用户可以删除数据库。删除数据库将会删除数据库中包括的所有对象，该操作是不可恢复的。见如下删除sql命令：
DROP DATABASEdb_name;
五、表空间：
在Postgresql中，表空间表示一组文件存放的目录位置。在创建之后，就可以在该表空间上创建数据库对象。通过使用表空间，管理员可以控制一个Postgresql服务器的磁盘布局。这样管理员就可以根据数据库对象的数据量和数据使用频度等参照来规划这些对象的存储位置，以便减少IO等待，从而优化系统的整体运行性能。比如，将一个使用频繁的索引放在非常可靠、高效的磁盘设备上，如固态硬盘。而将很少使用的数据库对象存放在相对较慢的磁盘系统上。下面的sql命令用于创建表空间。
CREATE TABLESPACEfastspaceLOCATION'/mnt/sda1/postgresql/data';
需要说明的是，表空间指定的位置必须是一个现有的空目录，且属于Postgresql系统用户，如postgres。在成功创建之后，所有在该表空间上创建的对象都将被存放在这个目录下的文件里。
在Postgresql中只有超级用户可以创建表空间，但是在成功创建之后，就可以允许普通数据库用户在其上创建数据库对象了。要完成此操作，必须在表空间上给这些用户授予CREATE权限。表、索引和整个数据库都可以放在特定的表空间里。见如下sql命令：
CREATE TABLEfoo(i int)TABLESPACEspace1;
此外，我们还可以通过修改 default_tablespace 配置变量，以使指定的表空间成为缺省表空间，这样在创建任何数据库对象时，如果没有显示指定表空间，那么该对象将被创建在缺省表空间中，如：
SET default_tablespace = space1;
CREATE TABLE foo(i int);
与数据库相关联的表空间用于存储该数据库的系统表，以及任何使用该数据库的服务器进程创建的临时文件。
要删除一个空的表空间，可以直接使用 DROP TABLESPACE 命令，然而要删除一个包含数据库对象的表空间，则需要先将该表空间上的所有对象全部删除后，才可以再在删除该表空间。
要检索当前系统中有哪些表空间，可以执行以下查询，其中pg_tablespace为Postgresql中的系统表。
SELECT spcname FROMpg_tablespace;
我们还可以通过 程序的 \db 元命令列出现有的表空间。
(数据库维护)
一、恢复磁盘空间：
在Postgresql中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VACUUM操作。
VACUUM命令存在两种形式，VACUUM和VACUUM FULL，它们之间的区别见如下表格：
| 无VACUUM | VACUUM | VACUUM FULL | |
| 删除大量数据之后 | 只是将删除数据的状态置为已删除，该空间不能记录被重新使用。 | 如果删除的记录位于表的末端，其所占用的空间将会被物理释放并归还操作系统。如果不是末端数据，该命令会将指定表或索引中被删除数据所占用空间重新置为可用状态，那么在今后有新数据插入时，将优先使用该空间，直到所有被重用的空间用完时，再考虑使用新增的磁盘页面。 | 不论被删除的数据是否处于数据表的末端，这些数据所占用的空间都将被物理的释放并归还于操作系统。之后再有新数据插入时，将分配新的磁盘页面以供使用。 |
| 执行效率 | 由于只是状态置为操作，因此效率较高。 | 在当前版本的Postgresql(v9.1)中，该命令会为指定的表或索引重新生成一个数据文件，并将原有文件中可用的数据导入到新文件中，之后再删除原来的数据文件。因此在导入过程中，要求当前磁盘有更多的空间可用于此操作。由此可见，该命令的执行效率相对较低。 | |
| 被删除的数据所占用的物理空间是否被重新规划给操作系统。 | 不会 | 会 | |
| 在执行VACUUM命令时，是否可以并发执行针对该表的其他操作。 | 由于该操作是共享锁，因此可以与其他操作并行进行。 | 由于该操作需要在指定的表上应用排它锁，因此在执行该操作期间，任何基于该表的操作都将被挂起，知道该操作完成。 | |
| 推荐使用方式 | 在进行数据清空是，可以使用truncate操作，因为该操作将会物理的清空数据表，并将其所占用的空间直接归还于操作系统。 | 为了保证数据表的磁盘页面数量能够保持在一个相对稳定值，可以定期执行该操作，如每天或每周中数据操作相对较少的时段。 | 考虑到该操作的开销，以及对其他错误的排斥，推荐的方式是，定期监控数据量变化较大的表，只有确认其磁盘页面占有量接近临界值时，才考虑执行一次该操作。即便如此，也需要注意尽量选择数据操作较少的时段来完成该操作。 |
| 执行后其它操作的效率 | 对于查询而言，由于存在大量的磁盘页面碎片，因此效率会逐步降低。 | 相比于不执行任何VACUUM操作，其效率更高，但是插入的效率会有所降低。 | 在执行完该操作后，所有基于该表的操作效率都会得到极大的提升。 |
@H_404_43@
二、更新规划器统计：
PostgreSQL查询规划器在选择最优路径时，需要参照相关数据表的统计信息用以为查询生成最合理的规划。这些统计是通过ANALYZE命令获得的，你可以直接调用该命令，或者把它当做VACUUM命令里的一个可选步骤来调用，如VACUUM ANAYLYZEtable_name，该命令将会先执行VACUUM再执行ANALYZE。与回收空间(VACUUM)一样，对数据更新频繁的表保持一定频度的ANALYZE，从而使该表的统计信息始终处于相对较新的状态，这样对于基于该表的查询优化将是极为有利的。然而对于更新并不频繁的数据表，则不需要执行该操作。
我们可以为特定的表，甚至是表中特定的字段运行ANALYZE命令，这样我们就可以根据实际情况，只对更新比较频繁的部分信息执行ANALYZE操作，这样不仅可以节省统计信息所占用的空间，也可以提高本次ANALYZE操作的执行效率。这里需要额外说明的是，ANALYZE是一项相当快的操作，即使是在数据量较大的表上也是如此，因为它使用了统计学上的随机采样的方法进行行采样，而不是把每一行数据都读取进来并进行分析。因此，可以考虑定期对整个数据库执行该命令。
事实上，我们甚至可以通过下面的命令来调整指定字段的抽样率，如：
ALTER TABLEtesttableALTER COLUMNtest_colSET STATISTICS200
注意：该值的取值范围是0--1000，其中值越低采样比例就越低，分析结果的准确性也就越低，但是ANALYZE命令执行的速度却更快。如果将该值设置为-1，那么该字段的采样比率将恢复到系统当前默认的采样值，我们可以通过下面的命令获取当前系统的缺省采样值。
postgres=# showdefault_statistics_target;@H_404_43@
default_statistics_target
---------------------------
100
(1 row)
从上面的结果可以看出，该数据库的缺省采样值为100(10%)。
@H_404_43@三、VACUUM和ANALYZE的示例：
@H_404_43@
#1. 创建测试数据表。
postgres=# CREATE TABLE testtable (i integer);@H_404_43@
CREATE TABLE
#2. 为测试表创建索引。
postgres=# CREATE INDEX testtable_idx ON testtable(i);@H_404_43@
CREATE INDEX
#3. 创建批量插入测试数据的函数。
postgres=# CREATE OR REPLACE FUNCTION test_insert() returns integer AS $$
DECLARE
min integer;
max integer;
BEGIN
SELECT COUNT(*) INTO min from testtable;
max := min + 10000;
FOR i IN min..max LOOP
INSERT INTO testtable VALUES(i);
END LOOP;
RETURN 0;
END;
$$ LANGUAGE plpgsql;@H_404_43@
CREATE FUNCTION
#4. 批量插入数据到测试表(执行四次)
postgres=# SELECT test_insert();@H_404_43@
test_insert
-------------
0
(1 row)
#5. 确认四次批量插入都成功。
postgres=# SELECT COUNT(*) FROM testtable;@H_404_43@
count
-------
40004
(1 row)
#6. 分析测试表，以便有关该表的统计信息被更新到Postgresql的系统表。
postgres=# ANALYZE testtable;@H_404_43@
ANALYZE
#7. 查看测试表和索引当前占用的页面数量(通常每个页面为8k)。@H_404_43@
postgres=# SELECT relname,relfilenode,relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';@H_404_43@
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17601 | 157
testtable_idx | 17604 | 90
#8. 批量删除数据。
postgres=# DELETE FROM testtable WHERE i < 30000;@H_404_43@
DELETE 30003
#9. 执行vacuum和analyze，以便更新系统表，同时为该表和索引记录高水标记。
#10. 这里需要额外说明的是，上面删除的数据均位于数据表的前部，如果删除的是末尾部分，
# 如where i > 10000，那么在执行VACUUM ANALYZE的时候，数据表将会被物理的缩小。
postgres=# VACUUM ANALYZE testtable;@H_404_43@
ANALYZE
#11. 查看测试表和索引在删除后，再通过VACUUM ANALYZE更新系统统计信息后的结果(保持不变)。
postgres=# SELECT relname,relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';@H_404_43@
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17601 | 157
testtable_idx | 17604 | 90
(2 rows)
#12. 再重新批量插入两次，之后在分析该表以更新其统计信息。
postgres=# SELECT test_insert(); --执行两次。@H_404_43@
test_insert
-------------
0
(1 row)
postgres=# ANALYZE testtable;@H_404_43@
ANALYZE
#13. 此时可以看到数据表中的页面数量仍然为之前的高水标记数量，索引页面数量的增加
# 是和其内部实现方式有关，但是在后面的插入中，索引所占的页面数量就不会继续增加。
404_43@
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17601 | 157
testtable_idx | 17604 | 173
(2 rows)
postgres=# SELECT test_insert();@H_404_43@
test_insert
-------------
0
(1 row)
#14. 可以看到索引的页面数量确实没有继续增加。
404_43@
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17601 | 157
testtable_idx | 17604 | 173
(2 rows)
#15. 重新批量删除数据。
postgres=# DELETE FROM testtable WHERE i < 30000;@H_404_43@
DELETE 19996
#16. 从后面的查询可以看出，在执行VACUUM FULL命令之后，测试表和索引所占用的页面数量
# 确实降低了，说明它们占用的物理空间已经缩小了。
postgres=# VACUUM FULL testtable;@H_404_43@
VACUUM
404_43@
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17602 | 118
testtable_idx | 17605 | 68
(2 rows)
四、定期重建索引：
在Postgresql中，为数据更新频繁的数据表定期重建索引(REINDEX INDEX)是非常有必要的。对于B-Tree索引，只有那些已经完全清空的索引页才会得到重复使用，对于那些仅部分空间可用的索引页将不会得到重用，如果一个页面中大多数索引键值都被删除，只留下很少的一部分，那么该页将不会被释放并重用。在这种极端的情况下，由于每个索引页面的利用率极低，一旦数据量显著增加，将会导致索引文件变得极为庞大，不仅降低了查询效率，而且还存在整个磁盘空间被完全填满的危险。
对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。见如下示例：
#1. 此时已经在该表中插入了大约6万条数据，下面的sql语句将查询该索引所占用的磁盘空间。
postgres=# SELECT relname,pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';@H_404_43@
relname | size
----------------+------
testtable_idx | 1240K
(1 row)
#2. 删除数据表中大多数的数据。
postgres=# DELETE FROM testtable WHERE i > 20000;@H_404_43@
DELETE 50006
#3. 分析一个该表，以便于后面的sql语句继续查看该索引占用的空间。
postgres=# ANALYZE testtable;@H_404_43@
ANALYZE
#4. 从该查询结果可以看出，该索引所占用的空间并未减少，而是和之前的完全一样。
postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;@H_404_43@
size
------
1240K
(1 row)
#5. 重建索引。
postgres=# REINDEX INDEX testtable_idx;@H_404_43@
REINDEX
#6. 查看重建后的索引实际占用的空间，从结果中可以看出索引的尺寸已经减少。
postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;@H_404_43@
size
------
368K
(1 row)
#7. 最后一点需要记住的是，在索引重建后一定要分析数据表。
postgres=# ANALYZE testtable;@H_404_43@
ANALYZE
五、观察磁盘使用情况：
1. 查看数据表所占用的磁盘页面数量。
#relpages只能被VACUUM、ANALYZE和几个DDL命令更新，如CREATE INDEX。通常一个页面的长度为8K字节。@H_404_43@
postgres=# SELECT relfilenode,relpages FROM pg_class WHERE relname = 'testtable';@H_404_43@
relfilenode | relpages
-------------+----------
16412 | 79
(1 row)
2. 查看指定数据表的索引名称和索引占用的磁盘页面数量。
postgres=# SELECT c2.relname,c2.relpages FROM pg_class c,pg_class c2,pg_index i
WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid
ORDER BY c2.relname;@H_404_43@
relname | relpages
---------------+----------
testtable_idx | 46
(1 row)
(系统表)
一、pg_class:
该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意：不是所有字段对所有对象类型都有意义。
| 名字 | 类型 | 引用 | 描述 |
| relname | name | 数据类型名字。 | |
| relnamespace | oid | pg_namespace.oid | 包含这个对象的名字空间(模式)的OI。 |
| reltype | pg_type.oid | 对应这个表的行类型的数据类型。 | |
| relowner | pg_authid.oid | 对象的所有者。 | |
| relam | pg_am.oid | 对于索引对象，表示该索引的类型(B-tree，hash)。 | |
| relfilenode | oid | 对象存储在磁盘上的文件名，如果没有则为0。 | |
| reltablespace | pg_tablespace.oid | 对象所在的表空间。如果为零，则表示使用该数据库的缺省表空间。(如果对象在磁盘上没有文件，这个字段就没有什么意义) | |
| relpages | int4 | 该数据表或索引所占用的磁盘页面数量，查询规划器会借助该值选择最优路径。 | |
| reltuples | float4 | 表中行的数量，该值只是被规划器使用的一个估计值。 | |
| reltoastrelid | pg_class.oid | 与此表关联的TOAST表的OID，如果没有为0。TOAST表在一个从属表里"离线"存储大字段。 | |
| reltoastidxid | 如果是TOAST表，该字段为它索引的OID，如果不是TOAST表则为0。 | ||
| relhasindex | bool | 如果这是一个数据表而且至少有(或者最近有过)一个索引，则为真。它是由CREATE INDEX设置的，但DROP INDEX不会立即将它清除。如果VACUUM发现一个表没有索引，那么它清理 relhasindex。 | |
| relisshared | 如果该表在整个集群中由所有数据库共享，则为真。 | ||
| relkind | char | r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表 | |
| relnatts | int2 | 数据表中用户字段的数量(除了系统字段以外，如oid)。在pg_attribute里肯定有相同数目的数据行。见pg_attribute.attnum. | |
| relchecks | 表中检查约束的数量，参阅pg_constraint表。 | ||
| reltriggers | 表中触发器的数量；参阅pg_trigger表。 | ||
| relhasoids | 如果我们为对象中的每行都生成一个OID，则为真。 | ||
| relhaspkey | 如果该表存在主键，则为真。 | ||
| relhasrules | 如表有规则就为真；参阅pg_rewrite表。 | ||
| relhassubclass | bool | 如果该表有子表，则为真。 | |
| relacl | aclitem[] | 访问权限。 |
见如下应用示例：
#查看指定表对象testtable的模式
404_43@
relname | relnamespace | nspname
-------------+--------------+---------
testtable | 2200 | public
(1 row)
#查看指定表对象testtable的owner(即role)。
postgres=# select relname,rolname from pg_class c,pg_authid au where relname = 'testtable' and relowner = au.oid;@H_404_43@
relname | rolname
-------------+----------
testtable | postgres
(1 row)
二、pg_attribute:
该系统表存储所有表(包括系统表，如pg_class)的字段信息。数据库中的每个表的每个字段在pg_attribute表中都有一行记录。
| 此字段所属的表。 | |
| attname | 字段名。 |
| atttypid | 字段的数据类型。 |
| attstattarget | attstattarget控制ANALYZE为这个字段设置的统计细节的级别。零值表示不收集统计信息，负数表示使用系统缺省的统计对象。正数值的确切信息是和数据类型相关的。 |
| attlen | 该字段所属类型的长度。(pg_type.typlen的拷贝) |
| attnum | 字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数。 |
| attndims | 如果该字段是数组，该值表示数组的维数，否则是0。 |
| attcacheoff | 在磁盘上总是-1，但是如果装载入内存中的行描述器中， 它可能会被更新为缓冲在行中字段的偏移量。 |
| atttypmod | 表示数据表在创建时提供的类型相关的数据(比如，varchar字段的最大长度)。其值对那些不需要atttypmod的类型而言通常为-1。 |
| attbyval | pg_type.typbyval字段值的拷贝。 |
| attstorage | pg_type.typstorage字段值的拷贝。 |
| attalign | pg_type.typalign字段值的拷贝。 |
| attnotnull | 如果该字段带有非空约束，则为真，否则为假。 |
| atthasdef | 该字段是否存在缺省值，此时它对应pg_attrdef表里实际定义此值的记录。 |
| attisdropped | 该字段是否已经被删除。如果被删除，该字段在物理上仍然存在表中，但会被分析器忽略，因此不能再通过sql访问。 |
| attislocal | 该字段是否局部定义在对象中的。 |
| attinhcount | 该字段所拥有的直接祖先的个数。如果一个字段的祖先个数非零，那么它就不能被删除或重命名。 |
#查看指定表中包含的字段名和字段编号。
404_43@
relname | attname | attnum
-------------+----------+--------
testtable | tableoid | -7
testtable | cmax | -6
testtable | xmax | -5
testtable | cmin | -4
testtable | xmin | -3
testtable | ctid | -1
testtable | i | 1
(7 rows)
#只查看用户自定义字段的类型
404_43@
relname | attname | typname
-------------+----------+---------
testtable | i | int4
(7 rows)
三、pg_attrdef:
该系统表主要存储字段缺省值，字段中的主要信息存放在pg_attribute系统表中。注意：只有明确声明了缺省值的字段在该表中才会有记录。
| 这个字段所属的表 | |||
| adnum | int2 | pg_attribute.attnum | 字段编号，其规则等同于pg_attribute.attnum |
| adbin | text | 字段缺省值的内部表现形式。 | |
| adsrc | 缺省值的人可读的表现形式。 |
见如下应用示例：
#查看指定表有哪些字段存在缺省值，同时显示出字段名和缺省值的定义方式@H_404_43@
postgres=# CREATE TABLE testtable2 (i integer DEFAULT 100);@H_404_43@
CREATE TABLE
postgres=# SELECT c.relname,a.attname,ad.adnum,ad.adsrc FROM pg_class c,pg_attrdef ad WHERE relname = 'testtable2' AND ad.adrelid = c.oid AND adnum = a.attnum AND attrelid = c.oid;@H_404_43@
relname | attname | adnum | adsrc
-------------+----------+---------+-------
testtable2 | i | 1 | 100
(1 row)
四、pg_authid:
该系统表存储有关数据库认证的角色信息，在Postgresql中角色可以表现为用户和组两种形式。对于用户而言只是设置了rolcanlogin标志的角色。由于该表包含口令数据，所以它不是公共可读的。Postgresql中提供了另外一个建立在该表之上的系统视图pg_roles，该视图将口令字段填成空白。
| 角色名称。 | |||
| rolsuper | 角色是否拥有超级用户权限。 | ||
| rolcreaterole | 角色是否可以创建其它角色。 | ||
| rolcreatedb | 角色是否可以创建数据库。 | ||
| rolcatupdate | 角色是否可以直接更新系统表(如果该设置为假，即使超级用户也不能更新系统表)。 | ||
| rolcanlogin | 角色是否可以登录，换句话说，这个角色是否可以给予会话认证标识符。 | ||
| rolpassword | 口令(可能是加密的)；如果没有则为NULL。 | ||
| rolvaliduntil | timestamptz | 口令失效时间(只用于口令认证)；如果没有失效期，则为NULL。 | |
| rolconfig | text[] | 运行时配置变量的会话缺省。 |
见如下应用示例：
#从输出结果可以看出口令字段已经被加密。@H_404_43@
postgres=# SELECT rolname,rolpassword FROM pg_authid;@H_404_43@
rolname | rolpassword
-----------+-------------------------------------
postgres | md5a3556571e93b0d20722ba62be61e8c2d
五、pg_auth_members:
该系统表存储角色之间的成员关系。
| 组角色的ID。 | |
| member | 属于组角色roleid的成员角色的ID。 |
| grantor | 赋予此成员关系的角色的ID。 |
| admin_option | 如果具有把其它成员角色加入组角色的权限，则为真。 |
#1. 先查看角色成员表中有哪些角色之间的隶属关系，在当前结果集中只有一个成员角色隶属于一个组角色，
# 如果有多个成员角色隶属于同一个组角色，这样将会有多条记录。
postgres=# SELECT * FROM pg_auth_members ;@H_404_43@
roleid | member | grantor | admin_option
--------+--------+---------+--------------
16446 | 16445 | 10 | f
(1 row)
#2. 查看组角色的名字。
postgres=# SELECT rolname FROM pg_authid a,pg_auth_members am WHERE a.oid = am.roleid;@H_404_43@
rolname
---------
mygroup
(1 row)
#3. 查看成员角色的名字。
#4. 如果需要用一个结果集获取角色之间的隶属关系，可以将这两个结果集作为子查询后再进行关联。
404_43@
rolname
---------
myuser
(1 row)
六、pg_constraint:
该系统表存储Postgresql中表对象的检查约束、主键、唯一约束和外键约束。
| 约束名字(不一定是唯一的)。 | ||
| connamespace | 包含这个约束的名字空间(模式)的OID。 | |
| contype | c = 检查约束， f = 外键约束， p = 主键约束， u = 唯一约束 | |
| condeferrable | 该约束是否可以推迟。 | |
| condeferred | 缺省时这个约束是否是推迟的？ | |
| conrelid | 该约束所在的表，如果不是表约束则为0。 | |
| contypid | 该约束所在的域，如果不是域约束则为0。 | |
| confrelid | 如果为外键，则指向参照的表，否则为0。 | |
| confupdtype | 外键更新动作代码。 | |
| confdeltype | 外键删除动作代码。 | |
| confmatchtype | 外键匹配类型。 | |
| conkey | int2[] | 如果是表约束，则是约束控制的字段列表。 |
| confkey | 如果是外键，则是参照字段的列表。 | |
| conbin | 如果是检查约束，则表示表达式的内部形式。 | |
| consrc | 如果是检查约束，则是表达式的人可读的形式。 |
七、pg_tablespace:
该系统表存储表空间的信息。注意：表可以放在特定的表空间里，以帮助管理磁盘布局和解决IO瓶颈。
| 表空间名称。 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| spcowner | 表空间的所有者，通常是创建它的角色。 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| spclocation | 表空间的位置(目录路径)。 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| spcacl | #1. 创建表空间。 postgres=# CREATE TABLESPACE my_tablespace LOCATION '/opt/Postgresql/9.1/mydata';@H_404_43@ CREATE TABLESPACE #2. 将新建表空间的CREATE权限赋予public。 postgres=# GRANT CREATE ON TABLESPACE my_tablespace TO public;@H_404_43@ GRANT #3. 查看系统内用户自定义表空间的名字、文件位置和创建它的角色名称。 #4. 系统创建时自动创建的两个表空间(pg_default和pg_global)的文件位置为空(不是NULL)。 postgres=# SELECT spcname,rolname,spclocation FROM pg_tablespace ts,pg_authid a WHERE ts.spcowner = a.oid AND spclocation <> '';@H_404_43@ spcname | rolname | spclocation ---------------+----------+---------------------------- my_tablespace | postgres | /opt/Postgresql/9.1/mydata (1 row) 八、pg_namespace: 该系统表存储名字空间(模式)。
|
