PostgreSQL 9.4文档第5章数据定义

最新推荐文章于 2023-12-19 17:36:02 发布

不剪发的Tony老师

最新推荐文章于 2023-12-19 17:36:02 发布

阅读量2.7k

点赞数 1

分类专栏： PostgreSQL 文章标签： postgresql 数据库

PostgreSQL 专栏收录该内容

90 篇文章 77 订阅

订阅专栏

本章讲述如何创建用于存储数据的数据库结构。在关系数据库中，原始数据存储在表中，因此本章主要涉及如何创建和修改表，以及控制表中存储的数据的特性。然后，我们讨论了如何在模式中组织表，以及表的权限管理。最后，我们将简单了解与数据存储有关的其他特性，例如继承，视图，函数和触发器。

5.1. 表的基础知识

关系数据库中的表就像书面上的表格一样：由行和列组成。列的数量和顺序是固定的，并且每个列有一个名称。行的数量是可变的--它反应了某一时刻存储了多少数据。SQL对于行在表中的顺序不做任何保证。读取表时，行将以未知的顺序显示，除非明确指定了排序方式。此外，SQL不会为行赋予唯一标识符，因此一个表中可能包含多个完全相同的行。这是SQL底层数学模型的结果，但通常并不令人满意。本章稍后将会看到如何处理该问题。
每一列都有一个数据类型。数据类型限制了可能赋予改列的值的范围，并且为列中的值赋予特定语义，以便能够进行数据计算。例如，一个声明为数字类型的列不能接受任何文本字符串，并且该列中的值可以用于数学计算。相反，一个声明为字符串类型的列可以接受几乎任何数据，但是不能用于数学计算，然而可以进行其他操作，例如字符串连接。
PostgreSQL包含大量的内置数据类型，能够满足许多应用要求。用户也可以定义自己的数据类型。大多数内置数据类型拥有显而易见的名称和语义，详细说明可以查看第8章。一些常用的数据类型包括用于整数的integer，可能包含分数的numeric，用于字符串的text，用于日期的date，用于时间的time，以及同时包含日期和时间的timestamp。
创建表可以使用CREATE TABLE命令。使用该命令至少需要指定一个表名，列名以及每一列的数据类型。例如：
    CREATE TABLE my_first_table (
        first_column text,
        second_column integer
    );
以上语句创建一个名为my_first_table的表，包含两个列。第一列名称为first_column，数据类型为text；第二列名称为second_column，数据类型为integer。表和列的名称遵循4.1.1节中描述的标识符语法。类型名称通常也是标识符，但是存在一些例外情况。注意，列的列表使用逗号分隔，并且使用括号包围。
很明显，上例中的表完全没有实际意义。通常会按照数据的用途命名表和列。以下是一个更切合实际的示例：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric
    );
numeric类型可以存储分数部分，适合货币数量。
提示：如果创建多个相关的表，最好使用一致的命名模式。例如，可以选择单数名词或者复数名词作为表名，每个人都有自己喜欢的风格。
一个表能够包含的列数量有限。根据列的类型不同，最多可以包含250到1600列。无论如何，定义一个包含如此多的列的表是非常少见的，通常意味着设计存在问题。
如果不再需要一个表，可以使用DROP TABLE命令删除。例如：
    DROP TABLE my_first_table;
    DROP TABLE products;
删除一个不存在的表将会导致错误。然而，在SQL脚本文件中经常会尝试在创建表之前删除每个表，并忽略错误信息，以便脚本能够在表存在或者不存在时都能有效。（可以使用DROP TABLE IF EXISTS形式避免错误信息，但是这不是SQL标准。）
如果要修改一个已经存在的表，参见本章后面的5.5节。

5.2. 默认值

列可以指定一个默认值。当插入一条新的数据并且没有指定某些列的值，将会使用相应的默认值。数据操作命令也可以明确指定将列设置为默认值，而不需要知道默认值是多少。
如果没有明确定义默认值，默认值为空。这种方式是有意义的，因为空值被认为表示未知数据。
在表的定义中，默认值位于列的数据类型之后。例如：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric DEFAULT 9.99
    );
默认值可以是一个表达式，它将会在插入默认值时进行计算（而不是创建表时）。一个常见的例子是timestamp列拥有一个默认值CURRENT_TIMESTAMP，表示行插入时的时间。
另一个常见的示例是为每一行产生一个“序列号”。在PostgreSQL中，通常使用类似以下的方法实现：
    CREATE TABLE products (
        product_no integer DEFAULT nextval('products_product_no_seq'),
        ...
    );
其中，nextval( )函数从一个序列对象（参见9.16节）中获取连续的值。这种方法十分常见，因此存在一种特殊的简写：
CREATE TABLE products (
    product_no SERIAL,
    ...
);
SERIAL简写形式将在8.1.4节中进一步讨论。

5.3. 约束

数据类型是限制表中能够存储的数据种类的一种方式。但是，对于许多应用而言，这种限制过于粗糙。例如，包含产品价格的列应该只包含正数。但是没有只包含正数的标准类型。另一个问题是你可能想要基于其他列或者行限制某列中的数据。例如，在一个包含产品信息的表中，每个产品编号只能有一行数据。
为了实现这些目标，SQL允许在列和表上定义约束。约束能够随意控制表中的数据。如果某个用户尝试存储违反约束的数据，将会导致错误。即使是默认值，违反约束也不例外。

5.3.1. 检查约束

检查约束是最常见的约束类型。它允许你指定某一列中的值必须满足一个布尔表达式（真值）。例如，可以使用以下约束要求正的产品价格：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric CHECK (price > 0)
    );
可以看到，约束定义位于数据类型之后，和默认值类似。默认值和约束可以随意排序。检查约束包含关键字CHECK，以及一个括号包围的表达式。检查约束表达式应该包含需要约束的列，否则约束没有太大的意义。
可以为约束指定一个单独的名称。这样可以使得错误信息更加明确，并且允许你在修改约束时进行引用。语法如下：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric CONSTRAINT positive_price CHECK (price > 0)
    );
所以，要指定一个命名的约束，使用关键字CONSTRAINT，加上一个标识符，然后是约束定义。（如果这里没有指定约束名称，系统将会产生一个名称。）
检查约束还可以涉及多个列。假设存储一个正常价格和一个折扣价格，并且要保证折扣价格小于正常价格：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric CHECK (price > 0),
        discounted_price numeric CHECK (discounted_price > 0),
        CHECK (price > discounted_price)
    );
前两个约束已经见过。第三个约束使用了一个新的语法。它没有关联到某一列，而是作为一个单独的条目。列定义和约束定义可以混合出现。
我们将前两个约束称为列约束，而第三个约束称为表约束，因为它独立于任何列定义。列约束可以写成表约束，但反过来不一定，因为列约束只引用了所属的一个列。（PostgreSQL不强制该规定，但是为了移植到其他数据库系统，最好遵照该规定。）以上示例还可以写成以下形式：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        CHECK (price > 0),
        discounted_price numeric,
        CHECK (discounted_price > 0),
        CHECK (price > discounted_price)
    );
甚至可以写成以下形式：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric CHECK (price > 0),
        discounted_price numeric,
        CHECK (discounted_price > 0 AND price > discounted_price)
    );
表约束可以像列约束一样指定名称：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        CHECK (price > 0),
        discounted_price numeric,
        CHECK (discounted_price > 0),
        CONSTRAINT valid_discount CHECK (price > discounted_price)
    );
需要注意的是检查约束在表达式的值为真或者空时满足条件。因为大多数表达式在任何操作数为空时的结果为空，所以不会阻止在约束列中插入空值。要确保列不包含空值，可以使用下一节中的非空约束。

5.3.2. 非空约束

非空约束指定列不能接受空值。以下是一个示例：
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);
非空约束总是写成列约束。非空约束在功能上等价于创建一个CHECK (column_name is NOT NULL)检查约束。但是在PostgreSQL中，创建一个明确的非空约束效率更高。缺点是这种方式创建的非空约束不能指定明确的名称。
一个列上可以创建多个约束。只需要一个接着一个编写：
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);
约束的顺序无所谓。约束检查的顺序并不确定。
NOT NULL约束有一个相反值：NULL约束。这并不是意味着列必须为空，很明显这没什么用处。相反，这只是说明该列默认可以为空。SQL标准中没有NULL约束，因此不应该用于可移植的应用中。（PostgreSQL中添加该约束仅仅是为了与其他数据库系统兼容。）但是，有些用户喜欢该约束，因为它简化了脚本文件中的约束切换。例如，你可以一开始使用以下语句：
CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);
然后在需要时插入NOT关键字。
提示：在大多数数据库设计中大部分列应该标记为非空。

5.3.3. 唯一约束

唯一约束确保某一列或者一组列中的所有行在表中的唯一性。语法如下：
CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);
以上是列约束的写法。或者写成：
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);
以上是表约束的写法。
如果唯一约束涉及一组列，使用逗号进行分隔：
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);
以上指定多个列的组合值在表中是唯一的，然而任何一列不需要（通常也不是）唯一。
可以为唯一约束指定一个名称，例如：
CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);
添加一个唯一约束会自动在涉及的列或者列组上创建一个唯一btree索引。可以通过创建一个部分约束在部分数据行上实施唯一性约束。
通常，当表中多于一行数据在约束的列上相等时会违反唯一约束。但是，两个空值是不相等的。这意味着，即使存在唯一约束，仍然可能存在至少某个约束列为空的重复行。这种行为符合SQL标准，但是其他SQL数据库可能不遵循这种规则。

5.3.4. 主键约束

从技术上讲，主键约束相当于唯一约束和非空约束的组合。因此，以下两个表的定义接受相同的数据：
CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
主键也可以约束多个列；语法与唯一约束类似：
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);
主键表明某列或者一组列可以作为表中行的唯一标识符。（这是主键定义的直接结果。注意，唯一约束自身不能提供唯一标识符，因为它允许空值。）这对于文件编制和客户端应用都有好处。例如，一个允许修改行值的GUI应用很可能需要知道表的主键，以便能够唯一识别行。
添加主键会自动在主键列或者列组上创建一个唯一btree索引。
一个表最多只能有一个主键。（唯一且非空约束可以有许多，功能上与主键相同，但是只能有一个标识为主键。）关系数据库理论规定每个表都必须有一个主键。PostgreSQL不强制该规定，但是通常最好遵守该规定。

5.3.5. 外键约束

外键约束规定列（或者一组列）中的值必须匹配另一个表中的某些行的值。我们认为这个规定维护了两个相关表之间的参照完整性。
假设拥有以下产品表：
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
假设还有一个表存储了这些产品的订单。我们想要确保订单表只包含实际存在的产品的订单。所以我们在订单表中定义了一个参照产品表的外键约束：
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);
现在，订单表中不可能会创建一个没有出现在产品表中的非空product_no项。
这种情况下，订单表被称为引用表，而产品表被称为被引用表。同样，对应的列分别为引用列和被引用列。
可以将以上命令简写为：
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);
因为在缺少列时将会使用被引用表的主键作为被引用列。
外键同样可以约束和引用一组列。这个通常需要写成表约束的形式。以下是一个简单的语法示例：
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
当然，约束列的数量和类型需要和被引用列的数量和类型一致。
同样，可以为外键约束指定一个名称。
一个表可以有多个外键约束。这可以用于实现表之间的多对多的关系。假设存在产品表和订单表，但是允许一个订单可以包含多个产品（以上表结构不允许）。可以使用以下表结构：
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
注意，最后一个表的主键和外键存在重叠列。
我们知道外键不允许创建不关联任何产品的订单。但是如果删除一个存在订单的产品会怎么样呢？SQL同样允许处理该情况。直观地说，存在以下选择：
• 不允许删除一个被引用的产品
• 同时删除相关订单
• 其他？
为了说明这一点，我们将在以上多对多关系示例中实现以下策略：不允许删除存在订单（通过order_items）的产品。删除订单时，同时删除订单项：
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
限制删除和级联删除是两个最常用的选项。RESTRICT不允许删除一个被引用行。NO ACTION意味着检查约束时，如果存在任何引用行，将会导致错误；如果没有指定任何选项，这是默认值。（这两者的本质区别在于NO ACTION允许检查延迟到事务提交时，而RESTRICT不允许延迟。）CASCADE指定删除一个被引用行时，引用它的行同时自动被删除。还存在两外两个选项：SET NULL和SET DEFAULT。这些选项导致删除被引用行时，引用行中的引用列分别被设置为空或者默认值。注意，这并非意味着可以不遵守约束。例如，如果指定了SET DEFAULT但是默认值不满足外键约束，操作将会失败。
与ON DELETE类似，修改被引用列（更新）将会引起ON UPDATE动作。可选的操作也是一样的。此时的CASCADE意味着被引用列更新后的值需要复制到引用行中。
通常，如果任何引用列为空，引用行不需要满足外键约束。如果外键声明指定了MATCH FULL，只有全部的引用列都为空，引用行才可以不需要满足外键约束（所以空值和非空值的组合不满足MATCH FULL约束）。如果要想避免引用行不满足外键约束，可以将引用列声明为NOT NULL。
外键必须引用一个主键或者唯一约束。这意味着被引用列总是存在一个索引（主键或者唯一约束的基础）；因此检查引用行是否存在匹配值的操作才能高效。由于被引用表中行的DELETE或者被引用列的UPDATE需要扫描引用表，查找匹配旧值的行，所以最好同时在引用列上创建索引。因为这个索引并不总是必须的，而且创建索引的方法有多种，声明外键约束时不会自动在引用列上创建索引。

5.3.6. 排除约束

排除约束确保任何两行在指定列上或者使用指定运算符的表达式上进行比较时，至少有一个比较将会返回假或者空。语法如下：
CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH &&)
);
详细信息参考CREATE TABLE ... CONSTRAINT ... EXCLUDE命令。
创建一个排除约束将会自动创建一个指定类型的索引。

5.4. 系统字段

PostgreSQL系统隐式地为每个表创建几个系统字段。因此，不能将这些名字用于用户定义的列名。（注意这些限制与名字是否是关键字无关;将名字用引号引起来仍然要收到这种限制。）用户实际上不需要关注这些列;只需要知道它们的存在即可。
oid
    行的对象标识符（对象ID）。只有创建表时使用了WITH OIDs时，或者设置了defult_with_oids配置变量时才会存在该列。该列的类型为oid（与列名相同）;关于该类型的更多信息参考8.18节。
tableoid
    包含该行的表的OID。该列对于继承层次查询尤其有用（参见5.8节），因为如果没有该列，很难找出每一行来自哪个独立的表。tableiod可以与表pg_class的oid列进行关联获得表的名称。
xmin
    插入该行版本的事务的标识符（事务ID）。（行版本是数据行的一个独立的状态;行的每次更新将会为同一逻辑行创建一个新的行版本。）
cmin
    插入事务内部的命令标识符（从零开始）。
xmax
    删除事务的标识符（事务ID），对于未删除的行版本该值为0。对于一个可见的行版本，该列可能为非零值。这通常表示删除事务还没有提交，或者删除被回滚。
cmax
    删除事务内部的标识符，或者零。
ctid
    行版本在表中的物理位置。注意，虽然ctid能够非常快速地定位行版本，但是在更新或者被ACUMM FULL移动后，行的ctid会发生变化。因此，ctid不能作为一个长期使用的行标识符。应该使用OID，或者最好是用户定义的序列号来标识逻辑行。
OID是32位的，通过一个单一集群范围的计数器进行赋值。在大型或者长期运行的数据库中，计数器有可能会重复。因此，假定OID唯一是不好的习惯，除非能够确保它是唯一的。如果需要标识表中的行，强烈推荐使用一个序列生成器。尽管如此，如果采取了一些额外的措施，也可以使用OID：
• 对于使用OID标识数据行的表，需要在OID列上创建一个唯一约束。当存在一个这样的唯一约束（或者唯一索引）时，系统会确保不产生重复的OID。（显然，只有当表包含少于232（40亿）行数据才有可能，而且实际中表的大小最好小于这个值，否则性能将会受到影响。）
• 永远不要假设在不同表之间OID是唯一的；如果需要一个数据库范围内的标识符，可以使用tableid和行的OID组合。
• 当然，以上讨论的表必须使用WITH OIDS创建。从PostgreSQL 8.1开始，默认是WITHOUT OIDS。
事务标识符也是32位的。在一个长期运行的数据库中，事务IDs可能存在重复。如果采取了合适的维护流程，这不是一个严重的问题；更多细节参考第23章。尽管如此，在一个长期运行（超过10亿次事务）的环境中依赖事务IDs的唯一性是不明智的。
命令标识符同样是32位的。因此，单个事务中能够执行的SQL命令存在232（40亿）条的硬性限制。实际中这不是什么问题，注意限制的是SQL命令的数量，而不是处理的行数。并且，只有实际修改了数据库内容的命令才会占用命令标识符。

5.5. 修改表

如果你发现创建的表有问题，或者需求发生了改变，你可以删掉该表然后重新创建。但是如果表中已经存在数据，或者其他对象依赖于该表（例如外键约束），删除重建就没有那么简单了。因此PostgreSQL提供了一组修改已有表的命令。注意，这与修改表的数据是两个不同的概念：此处我们的目标是修改表的定义或者结构。
PostgreSQL允许执行以下操作：
• 添加字段
• 删除字段
• 添加约束
• 删除约束
• 修改默认值
• 修改字段数据类型
• 重命名字段
• 重命名表
以上所有操作都是通过ALTER TABLE命令完成，详细信息可以参考命令说明。

5.5.1. 添加字段

添加一个字段的命令如下：
ALTER TABLE products ADD COLUMN description text;
新建字段将会使用默认值（如果没有指定DEFAULT子句，使用空置）进行初始化。
你同时还可以使用常规的语法定义一个列约束：
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
实际上所有在CREATE TABLE中能够用于字段定义的选项都可以用于此处。但是，注意默认值必须满足存在的约束，否则ADD命令将会失败。或者，你可以在正确填充新建的字段之后再添加约束（参见下文）。
提示：添加一个包含默认值的字段需要更新已有的行（存储新建字段的值）。但是，如果没有指定默认值，PostgreSQL可以避免物理更新。因此，如果添加的字段大多数行需要非默认值，最好在添加时不指定默认值，而是使用UPDATE插入所需的值，然后添加默认值，参见下文。

5.5.2. 删除字段

删除一个字段的命令如下：
ALTER TABLE products DROP COLUMN description;
该字段中所有的数据都会被删除。该字段相关的约束也会被删除。但是，如果该字段被另一个表的外键约束所引用，PostgreSQL不会隐含地删除该约束。你可以使用CASCADE选项删除依赖于该字段的所有对象：
ALTER TABLE products DROP COLUMN description CASCADE;
5.12节描述了该操作的原理。

5.5.3. 添加约束

添加约束需要使用表约束语法。例如：
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
添加非空约束不能使用表约束语法，可以使用以下语法：
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
该约束将会立即进行检查，所以在添加约束之前表的数据必须满足约束条件。

5.5.4. 删除约束

删除约束需要指定约束名。如果创建约束时指定了约束名，删除就很简单。否则创建时系统将会指定一个约束名，你需要找出该约束名。psql命令\d tablename可以查看约束名；其他的用户接口也可以查看表的相信信息。删除约束的命令如下：
ALTER TABLE products DROP CONSTRAINT some_name;
（如果删除一个系统生成的约束名，例如$2，你需要使用双引号引用该约束名，这样它才能作为一个有效的标识符。）
与删除字段相同，你需要使用CASCADE删除依赖于该约束的其他对象。例如外键约束依赖于被引用字段上的唯一约束或者主键约束。
以上命令可以用于除非空约束之外的所有约束。删除非空约束的命令如下：
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
（记住，非空约束没有名称。）

5.5.5. 修改字段默认值

修改字段默认值的命令如下：
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
注意这不会影响表中的现有行，它只是修改了之后的INSERT命令的默认值。
使用以下命令删除默认值：
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
这实际上与将默认值设置为空的结果相同。所以，删除一个未定义的默认值不会产生错误，因为此时默认值就是空值。

5.5.6. 修改字段数据类型

修改字段数据类型的命令如下：
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
只有当该列中已经存在的值能够隐式转换为新的数据类型，修改才会成功。如果需要一个更复杂的类型转换，你可以使用USING子句指定如何为字段的旧值计算新值。
PostgreSQL将会尝试将该列的默认值（如果存在的话）以及任何约束转换为新的数据类型。但是这些转换有可能会失败，或者可能产生意想不到的结果。通常最好在修改字段数据类型之前删除该字段上的约束，然后再添加适当修改后的约束。

5.5.7. 重命名字段

重命名字段的命令如下：
ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.5.8. 重命名表

重命名表的命令如下：
ALTER TABLE products RENAME TO items;

5.6. 权限

对象被创建时，会被指定一个拥有者。通常来说对象的拥有者是执行创建语句的当前角色。对于大多数类型的对象，初始状态下只有拥有者（或者超级用户）能够执行任何操作。其他角色要想使用该对象，必须被赋予相应的权限。
权限分为不同的种类：SELECT，INSERT，UPDATE，DELETE，TRUNCATE，REFERENCES，TRIGGER，CREATE，CONNECT，TEMPORARY，EXECUTE以及USAGE。不同对象支持的权限不尽相同，取决于对象的类型（表，函数等）。关于PostgreSQL支持的不同权限类型的完整信息可以参考GRANT命令说明。一些章节将会演示如何使用这些权限。
修改或者删除对象的权限永远是只属于拥有者的特权。
使用相应的ALTER命令可以修改对象的拥有者，例如ALTER TABLE。超级用户可以修改对象的拥有者；对于普通角色，只有当它既是对象当前的拥有者（或者当前拥有者角色的成员），又是新的拥有者角色的成员时，才能修改对象的拥有者。
GRANT命令用于赋予权限。例如，假设joe是一个已有的用户，并且accounts是一个已有的表，可以使用一些命令赋予joe更新该表的权限：
GRANT UPDATE ON accounts TO joe;
如果使用ALL替换具体的权限，表示赋予该对象类型相关的所有权限。
赋予特殊“用户”PUBLIC某个权限表示赋予系统中所有用户该权限。另外，可以通过创建“组”角色简化管理大量用户的权限，更多信息可以参考第20章。
REVOKE命令用户撤销权限：
REVOKE ALL ON accounts FROM PUBLIC;
对象拥有者的特殊权限（例如DROP，GRANT，REVOKE等权限。）总是隐式属于拥有者，并且不能被授予或者撤销。但是对象拥有者可以选择撤销自己的普通权限，例如将一个表设置为对于自己和其他用户只读。
通常来说，只有对象拥有者（或者超级用户）可以授予或者撤销对象上的权限。但是，可以使用“with grant option”选项授予权限，它允许被授权的用户能够将它转授给其他用户。如果该授权选项随后被撤销，那么所有通过被授权的用户（直接或者通过授权链）获得该权限的用户都将失去该权限。详细信息可以参考GRANT命令和REVOKE命令的说明。

5.7. 模式

一个PostgreSQL数据库集群可以包含一个或者多个命名的数据库。用户和用户组在整个集群范围内共享，但是其他数据是每个数据库私有的。任何连接到服务器的客户端只能访问单个数据库内的数据，在连接请求时指定了要访问的数据库。
注意：集群的用户不一定拥有访问集群内每个数据库的权限。共享用户名意味着在同一个集群中，不允许在两个数据库中存在名字相同（比如joe）的不同用户；但是系统可以配置只允许joe访问某些数据库。
一个数据库包含一个或者多个命名模式，模式包含表。模式还包含其他类型的命名对象，包括数据类型，函数以及运算符。不同的模式中可以存在同名的对象而不冲突；例如，模式schema1和myschema可以同时包含名为mytable的表。与数据库不同，模式没有严格分离：一个用户可以访问当前数据库中的任何模式中的对象，只要他拥有相应权限。
使用模式的原因有以下几点：
• 允许多个用户使用一个数据库，并且互相没有干扰。
• 将数据库对象分为逻辑组，更易于管理。
• 第三方应用可以放入单独的模式中，避免与其他对象的命名冲突。
模式类似于操作系统的目录，只是模式不能嵌套。

5.7.1. 创建模式

使用CREATE SCHEMA命令创建模式。创建时可以指定一个名称。例如：
CREATE SCHEMA myschema;
要创建或者访问模式中的对象，使用一个包含模式名和表名（点号分隔）的限定名：
schema.table
这种方法可以用于任何需要一个表名的位置，包括后面章节讨论的表修改命令和数据访问命令。（为方便起见，我们只考虑表，但是这种方法可以用于其他类型的命名对象，例如类型和函数。）
实际上，更通用的语法如下：
database.schema.table
但是目前这只是用于形式上与SQL标准兼容。如果包含了一个数据库名，它必须是当前数据库。
使用以下命令在模式中创建一个表：
CREATE TABLE myschema.mytable (
...
);
使用以下命令删除一个空模式（该模式中的所有对象已被删除）：
DROP SCHEMA myschema;
使用以下命令删除一个模式和其中的所有对象：
DROP SCHEMA myschema CASCADE;
关于这种方式背后的通用机制可以参考5.12节。
通常你需要创建一个属于其他用户的模式（因为这是将用户操作限制在明确定义的命名空间中一个方法）。该操作的语法如下：
CREATE SCHEMA schemaname AUTHORIZATION username;
你甚至可以省略模式名，这种情况下模式名与用户名相同。5.7.6节描述了这种方法的使用。
以pg_开头的模式名是系统保留的名称，用户不能创建这类模式。

5.7.2. 公共模式

在前面的章节中我们创建表时没有指定任何模式名。默认情况下，这种方式创建的表（以及其他对象）自动被创建到名为“public”的模式中。每个新建的数据库都包含这个模式。因此，以下命名的结果相同：
CREATE TABLE products ( ... );

CREATE TABLE public.products ( ... );

5.7.3. 模式搜索路径

限定名不便于书写，而且通常最好不要在应用中使用特定的模式名。因此表通常使用非限定的名称进行引用，即直接使用表名。系统通过查找搜索路径决定要访问的表，搜索路径就是一个要查找的模式列表。搜索路径中第一个匹配的表将被系统使用。如果搜索路径中没有匹配的表，将会报告一个错误，即使在数据库的其他模式中存在匹配的表名。
在搜索路径中指定的第一个模式被成为当前模式。除了被首先搜索之外，当前模式还是CREATE TABLE命令没有指定模式名时，新建表的默认模式。
使用以下命令可以显示当前搜索路径：
SHOW search_path;
默认安装时返回以下内容：
search_path
--------------
"$user",public
第一项表示与当前用户同名的模式将会被搜索。如果该模式不存在，忽略该项。第二项表示公共模式。
搜索路径中第一个存在的模式就是创建新对象时的默认位置。这就是为什么默认情况下对象被创建到公共模式中。当在其他上下文中不使用模式限定符引用对象时（表修改，数据修改，或者查询命令），将遍历搜索路径直到找到匹配的对象。因此，默认配置时，任何未限定的访问只能引用公共模式中的对象。
可以使用以下命令设置搜索路径：
SET search_path TO myschema,public;
（我们省略的$user，因为我们不是迫切需要它。）然后我们可以不使用模式限定符访问表：
DROP TABLE mytable;
此外，由于myschema是搜索路径中的第一项，新的对象默认会在该模式中创建。
我们还可以写成：
SET search_path TO myschema;
这样的话，如果不使用显式限定符，我们不再能访问公共模式。公共模式没有任何特殊之处，只是它默认就存在。它同样可以被删除。
关于控制模式搜索路径的其他方法，参考9.25节。
对于数据类型名，函数名，以及运算符名，搜索路径的使用与表名相同。数据类型名和函数名可以使用与表名完全相同的限定方式。如果需要在表达式中使用一个限定的运算符名，必须遵守一个特殊的规定：必须写成以下形式：
OPERATOR(schema.operator)
这是为了避免语法歧义。例如：
SELECT 3 OPERATOR(pg_catalog.+) 4;
实际上我们通常依赖于运算符的搜索路径，所以不用编写这么难看的格式。

5.7.4. 模式与权限

默认情况下，用户无法访问不属于他们的模式中的任何对象。要访问这些对象，模式的拥有者必须将该模式的USAGE权限授予相应用户。用户要想使用该模式中的对象，可能还需要对象上的相应权限。
用于也可能允许在其他用户的模式中创建对象。这需要被授予该模式上的CREATE权限。注意默认情况下，任何用户都拥有public模式上的CREATE和USAGE权限。这将允许连接到数据库的所有用户在public模式中创建对象。如果不希望这种行为，可以撤销该权限：
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
第一个“public”是模式，第二个“public”表示“所有用户”。在第一个语义上它是一个标识符，第二个语义上它是一个关键字，因此使用了不同的大小写；参考4.1.1小节中的大小写规则。

5.7.5. 系统目录模式

除了public模式和用户创建的模式之外，每个数据库都包含一个pg_catalog模式，其中存储了系统表和所有的内置数据类型，函数，已经运算符。pg_catalog总是属于有效搜索路径中的一部分。如果没有在搜索路径中明确指定，它将隐式位于其他模式之前。这样可以确保总是能够找到内置的名称。但是，你可以明确将pg_catalog位于搜索路径的最后，这样可以优先使用用户定义的对象。
由于系统表的名称以pg_开头，为了避免未来版本中的系统表与你的表名冲突，最好不要使用这类名称。（对于默认搜索路径，如果引用用户的表时不加前缀限定，有可能会被解析为同名的系统表。）系统表将会继续遵守pg_开头的惯例，所以只要用户避免创建pg_为前缀的表名，就不会存在冲突。

5.7.6. 模式使用方式

模式可以使用不同的方式组织数据。以下是一些推荐的使用方式，默认配置就可以支持这些方法：
• 如果没有创建任何模式，那么所有用户将会隐式访问公共模式。这样可以模仿模式不可用的情况。这种配置主要用于单用户或少量协作用户的数据库。这种配置还可以从无模式的环境平滑过渡。
• 你可以为每个用户创建一个与用户同名的模式。记住默认搜索路径是从$user，也就是用户名开始的。因此，如果每个用户都拥有一个单独的模式，默认情况下他们将会访问自己的模式。
如果使用这种配置，你可能还想要撤销公共模式的访问权限（或者同时删掉该模式），这样用户才真正只能访问自己的模式。
• 如果要安装共享应用（每个用户都可以使用的表，第三方提供的额外功能，等等），可以将它们放入单独的模式中。记得给其他用户授予访问这些对象的适当权限。然后用户就可以使用模式名限定访问这些额外的对象，或者将额外的模式加入他们的搜索路径。

5.7.7. 可移植性

在SQL标准中，同一个模式中的对象属于不同用户的概念是不存在的。而且，某些实现方式不允许创建一个与用户名不同的模式。事实上，模式和用户的概念几乎等价的数据库只是实现了标准中的基本模式支持。因此，许多用户认为限定名实际是由username.tablename组成的。这就是在PostgreSQL中为每个用户都创建一个专属模式的实际行为。
另外，在SQL标准中没有public模式的概念。为了与标准尽量兼容，不应该使用public模式（甚至删除该模式）。
当然，某些SQL数据库可能更本就没有实现模式，或者通过允许跨数据库访问提供命名空间支持。如果需要使用这些系统，为了最大化可移植性，最好不要使用模式。

5.8. 表继承

PostgreSQL实现了表的继承，这对于数据库设计人员而言是一个非常有用的功能。（SQL:1999以及之后的版本定义了一个类型继承的特性，这与本文描述的继承在许多方面都存在区别。）
考虑这样一个示例：假设我们正在尝试创建一个城市的数据模型。每个州包含多个城市，但是只有一个首府。我们需要能够快速地找到任何特定州的首府城市。这个需求可以通过创建两个表来实现，一个用于州的首府，另一个用于非首府的城市。但是，如果我们想要知道某个城市的数据，无论它是否是一个首府，该怎么办呢？继承特性可以解决这类问题。我们可以将capitals表定义为继承cities表：
CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- in feet
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);
本例中，capitals表继承了它的父表cities的所有列。首府还拥有一个额外的列，state，表示它们所在的州。
在PostgreSQL中，一个表可以继承零个或多个其他表，同时查询既可以引用一个表的所有行，也可以引用一个表及其子表的所有行。默认为后者。例如，以下查询查找所有海拔高于500英尺的城市，包括州的首府：
SELECT name, altitude
    FROM cities
    WHERE altitude > 500;
使用PostgreSQL教程（参见2.1节）中的数据，该查询返回以下数据：
   name    | altitude
-----------+----------
Las Vegas |     2174
Mariposa |     1953
Madison   |      845
另一方面，以下查询查找所有海拔高于500英尺的非首府城市：
SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

   name    | altitude
-----------+----------
Las Vegas |     2174
Mariposa |     1953
其中的ONLY关键字表示只查找cities，而不包括继承层次中低于cities的表。许多已经讨论过的命令，SELECT，UPDATE和DELETE都支持ONLY关键字。
也可以在表名后加上一个*明确指定包含子孙表：
SELECT name, altitude
    FROM cities*
    WHERE altitude > 500;
*不是必须的，因为这是默认方式（除非修改了sql_inheritance配置选项的设置）。不过*可以强调将会查询额外的表。
有时候你可能想要知道具体的行来自于哪个表。每个表中都存在一个系统字段，名为tableoid，表示行所在的表：
SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;
结果为：
tableoid |   name    | altitude
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa |     1953
   139798 | Madison   |      845
你的查询很可能返回的是其他的OIDs。通过连接pg_class可以得到真实的表名：
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
继承不会自动将INSERT或者COPY的数据传递给继承层次中的其他表。在我们的示例中，以下语句将会失败：
INSERT INTO cities (name, population, altitude, state)
VALUES ('New York', NULL, NULL, 'NY');
我们可能希望数据能够通过某种方式路由到capitials表中，但是实际上却不会：INSERT总是插入到指定的表中。在某些情况下可以使用规则（参考第38章）将插入进行重定向。但是以上示例无法重定向，因为cities表不包含state字段，所以该命令在应用规则之前就会被拒绝。
父表上的所有检查约束和非空约束将会自动被子表继承。其他类型的约束（唯一约束，主键约束。以及外键约束）不会被继承。
一个表可以继承多个父表，这种情况下子表拥有父表的字段的并集。在子表上声明的任何字段也将属于子表。如果多个父表或者父表与子表存在相同的字段名，这些字段将会进行“合并”，最终在子表中只存在一个这样的字段。为了执行合并，这些字段必须具有相同的数据类型，否则将会产生错误。合并后的字段将会拥有每个源字段的所有约束，如何任何源字段非空，该字段也将非空。
表继承通常在使用CREATE TABLE语句的INHERITS子句创建子表时建立。或者，对于一个已经创建的能够兼容的表，可以使用ALTER TABLE的INHERIT形式添加一个父表。这种情况下子表必须已经包含与父表中名称和类型都相同的字段。子表还必须已经包含与父表中名称和检查表达式都相同的检查约束。同样，子表的继承链接可以使用ALTER TABLE的NO INHERIT形式删除。这种继承链接的动态添加和删除可以用于通过继承关系实现的表分区（参考5.9节）。
创建将来作为子表的具有兼容性的表的一个简单方法就是使用CREATE TABLE的LIKE子句。这将创建一个与源表具有相同字段的新表。如果源表存在任何CHECK约束，可以指定INCLUDING CONSTRAINTS选项，因为子表必须与父表具有相同的约束才可能是兼容的。
如果父表存在任何子表，不能删除父表。如果子表的字段和检查约束继承自任何父表，也不能进行删除或修改。如果要删除一个表和它所有的后代，一个简单的方式就是使用CASCADE选项删除父表。
ALTER TABLE会将任何字段数据定义和检查约束在继承层次中向下传播。同样，删除被其他表依赖的字段必须使用CASCADE选项。ALTER TABLE遵守与CREATE TABLE相同的重复字段合并和拒绝规则。
注意表访问权限的处理方式。查询父表将会自动访问子表中的数据，而不会进一步检查访问权限。这将导致数据（也）存在于父表中的表象。不过，不允许直接访问子表，而是需要相关授权。

5.8.1. 注意事项

注意，不是所有的SQL命令都能用于继承层次。用于数据查询，数据修改，或者模式修改的命令（例如SELECT，UPDATE，DELETE，大部分ALTER TABLE形式，但是不包括INSERT或ALTER TABLE ... RENAME）通常都默认为包含子表，同时支持使用ONLY排除子表。执行数据库维护和优化的命令（例如REINDEX，VACUUM）通常只作用于单独的物理表，并且不支持继承层次递归。每个命令各自的方式记录在相应的参考页面中（命令参考I. SQL命令）。
继承特性的一个重要的局限性就是索引（包括唯一约束）和外键约束只能应用于单个表，而不包括子表。这种限制对于外键约束的参考表和被参考表同样适用。因此，对于以上示例：
• 如果将cities.name声明为UNIQUE或者PRIMARY KEY，不会阻止capitals表中存在与cities中同名的城市。而且这些重复的行默认会显示在cities表的查询中。实际上，默认情况下capitals更本不存在唯一约束，因此可能存在多行名称相同的数据。你可以为capitals添加一个唯一约束，但是这样仍然无法阻止capitals中存在与cities重复的数据。
• 同样，如果指定了cities.name REFERENCES参考其他表，该约束不会字段作用于capitals。这种情况下可以手段为capitals添加相同的REFERENCES约束。
• 指定其他表的字段REFERENCES cities(name)将会允许其他表包含城市名，但不能包含首府名。这种情况没有什么好的解决办法。
这些缺陷可能会在未来的版本中修复，但是同时需要认真考虑是否需要使用继承。

5.9. 表分区

PostgreSQL支持基本的表分区。本节描述为什么要在数据库设计中使用分区，以及如何实现分区。

5.9.1. 概述

分区是指将一个逻辑上的大表分割成多个小的物理片段。分区拥有以下优点：
• 某些情况下能够显著提高查询性能，尤其是表中频繁访问的行大部分位于单个分区或者少量分区的情况下。分区替代了索引的前导列，能够减少索引的大小，并且更有可能将频繁使用的一部分索引固定在内存中。
• 当查询或者更新访问单个分区的大部分数据时，利用分区顺序扫描的优势，而不是先使用索引然后进行整个表的随机访问，能够提高性能。
• 如果使用分区设计，可以通过添加和删除分区进行数据的批量加载和删除。ALETR TABLE NO INHERIT和DROP TABLE比批量操作快得多。这些命令还可以完全避免批量DELETE带来的VACUUM负载。
• 很少使用的数据可以移动到更廉价更缓慢的存储介质中。
这些优点通常只有在表非常大的时候才会体现。具体多大的表能够从分区获得好处取决于应用，但是通常的规则是表的大小超过了数据库服务器的物理内存。
目前PostgreSQL通过表继承支持分区。每个分区必须创建为单个父表的子表。父表自身通常是空的；它的存在仅仅是为了代表整个数据集。
PostgreSQL支持以下分区形式：
范围分区
表被分割为“范围”，范围由关键列或者一组列定义，不同分区的范围值之间没有重叠。例如，可以按照日期范围进行分区，或者按照特殊业务对象的标识符范围进行分区。
列表分区
通过明确指定每个分区中的关键列值进行分区。

5.9.2. 实现分区

创建分区表的步骤如下：
1. 创建“主”表，所有的分区都将继承该主表。
    主表不存储数据。不要在该表上创建任何检查约束，除非想要在所有分区上同样使用该约束。主表上的任何索引或者唯一约束没有意义。
2. 创建几个继承主表的“子”表。通常这些表不会再添加任何列。
    这些子表被称为分区，虽然它们都是普通的PostgreSQL表。
3. 为分区表添加约束，定义每个分区允许的关键列的值。
    以下是一些典型的示例：
        CHECK ( x = 1 )
        CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
        CHECK ( outletID >= 100 AND outletID < 200 )
    确认约束能够保证不同分区允许的关键列值没有重叠。一个常见的错误是按照以下方式创建范围约束：
        CHECK ( outletID BETWEEN 100 AND 200 )
        CHECK ( outletID BETWEEN 200 AND 300 )
    以上方法是错误的，因为没有明确关键列值200属于哪个分区。
    注意，范围分区和列表分区的语法没有差别；这些只是描述性的术语。
4. 为每个分区创建一个关键列的索引，以及任何其他需要的索引。（关键列索引不是严格必须的，但是大多数情况下是有用的。如果想要关键列值唯一，必须为每个分区创建一个唯一约束或者主键约束。）
5. 可选，定义一个触发器或者规则，将插入主表的数据重定向到相应的分区。
6. 确认文件postgresql.conf中的配置参数constraint_exclusion没有被禁用。如果被禁用，查询将不会进行分区优化。
例如，假设为一个大型冰淇淋公司创建一个数据库。该公司每天需要测量每个地区的峰值温度和冰淇淋销量。从概念上讲，需要一个这样的表：
    CREATE TABLE measurement (
        city_id         int not null,
        logdate         date not null,
        peaktemp        int,
        unitsales       int
    );
我们知道大多数查询只是访问最近几周、最近几月或者季度的数据，因为该表的主要用途是产生在线管理报告。为了减少需要存储的旧数据量，我们决定只保留最近3年的数据资产。每个月初将会删除最早一个月的数据。
在这种情况下，我们可以为测量表创建分区，以满足所有不同的需求。按照以上给出的步骤，可以使用如下方法创建分区：
1. 创建主表measurement，定义如上。
2. 下一步为每个有效月份创建一个分区：
    CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
    ...
    CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
    每个分区自身都是完整的表，但是从measurement表继承了定义。
    这可以解决一个问题：删除旧数据。对于每个月，我们只需要执行DROP TABLE删除最旧的子表，并为新的月份创建一个新的子表。
3. 我们必须创建不重叠的表约束。实际创建分区表的脚本如下：
    CREATE TABLE measurement_y2006m02 (
        CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2006m03 (
        CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
    ) INHERITS (measurement);
    ...
    CREATE TABLE measurement_y2007m11 (
        CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2007m12 (
        CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2008m01 (
        CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
    ) INHERITS (measurement);
4. 很可能还需要创建关键列的索引：
    CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
    CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
    ...
    CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
    CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
    CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
5. 我们想要让应用的INSERT INTO measurement ... 能够将数据重定向到合适的分区表中。可以通过将一个触发器函数关联到主表，达到该效果。如果数据只添加到最新的分区中，可以使用一个非常简单的触发器函数：
    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    然后创建一个调用该函数的触发器：
    CREATE TRIGGER insert_measurement_trigger
        BEFORE INSERT ON measurement
        FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
    我们必须每个月重定义触发器函数，以便总是指向当前分区。然而，触发器定义不许要更新。
    我们可能想要插入数据时让服务器自动决定将行添加到哪个分区。可以使用一个更复杂的触发器函数做到这一点，例如：
    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        IF ( NEW.logdate >= DATE '2006-02-01' AND
             NEW.logdate < DATE '2006-03-01' ) THEN
            INSERT INTO measurement_y2006m02 VALUES (NEW.*);
        ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
                NEW.logdate < DATE '2006-04-01' ) THEN
            INSERT INTO measurement_y2006m03 VALUES (NEW.*);
        ...
        ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
                NEW.logdate < DATE '2008-02-01' ) THEN
            INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        ELSE
            RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
        END IF;
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    触发器的定义不变。注意IF测试必须完全匹配分区表的CHECK约束。
    虽然该函数比上面单个月更新的例子更复杂，它不需要经常更新，因为一次性提前添加多个IF分支。
    注意：实践中，最好先检查最新的分区，如果大多数插入的是最新数据的话。
    可以看出，一个复杂的分区方案可能需要大量的DDL语句。以上示例中需要每个月创建一个新的分区，所以最好编写一个脚本自动生成所需的DDL。

5.9.3. 管理分区

一般最初定义表时创建的一组分区不会一直保持不变。通常会删除旧的数据分区，并定期添加新的数据分区。分区最重要的优势之一是通过控制分区结构，而不是物理上删除大量数据，能够瞬间完成这种对于其他方式而言非常痛苦的任务。
删除旧数据最简单的方法就是直接删除不需要的分区：
    DROP TABLE measurement_y2006m02;
这种方法能够非常快速地删除数百万的记录，因为它不需要单个地删除每条记录。
通常更好的一个方法就是从分区表中删除该分区，但是将它作为一个独立的表进行保留：
    ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
这种方法允许在删除数据之前进行进一步处理。例如，这时通常可以使用COPY、pg_dump或者类似工具进行数据的备份。这时还可以将数据聚合成更小的格式，执行其它数据操作或者运行报告。
同样可以添加一个新的分区以处理新的数据。可以像创建原始的分区一样创建一个新的空白分区：
    CREATE TABLE measurement_y2008m02 (
        CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
    ) INHERITS (measurement);
或者，有时候更方便在分区结构之外创建新表，之后再修改为分区。这样允许新表出现在分区表中之前加载、检查和转换数据。
    CREATE TABLE measurement_y2008m02
          (LIKE measurement INCLUDING 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'
    -- possibly some other data preparation work
    ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.9.4. 分区和约束排除

约束排除是一种查询优化技术，能够改善分区表的属性。例如：
SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
如果没有约束排除，以上查询将会扫描表measurement表的每个分区。如果启用了约束排除，计划器将会检查每个分区的约束，并尝试证明不需要进行扫描的分区，因为这些分区不可能包含满足查询WEHRE子句的行。如果计划器能够证明这一点，则会将分区从查询计划中排除。
可以使用EXPLAIN命令显示constraint_exclusion启用和禁用时查询计划的差别。一个典型未优化的查询计划如下：
SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-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 >= '2008-01-01'::date)
         -> Seq Scan on measurement_y2006m02 measurement (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         -> Seq Scan on measurement_y2006m03 measurement (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         -> Seq Scan on measurement_y2007m12 measurement (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         -> Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
部分或者全部的分区可能使用索引扫描而不是全表顺序扫描，但是这里的重点是不需要扫描旧的分区。启用约束排除之后，可以得到极低成本的查询计划：
SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-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 >= '2008-01-01'::date)
         -> Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
注意，约束排除只会受到CHECK约束的影响，而不是索引。因此不需要在关键列上定义索引。是否需要在特定分区上创建索引取决于你期望扫描分区的查询通常扫描该分区的大部分数据或者少量数据。索引对于后者有用，而前者无用。
constraint_exclusion的默认（推荐）设置既不是on也不是off，而是中间值partition，这样只将该技术应用于可能访问分区表的查询。设置为on将会导致查询计划器检查所有查询的CHECK约束，即使是不会带来好处的简单查询。

5.9.5. 替代分区方法

另一个将插入重定向的方法是在主表上创建规则，而不是触发器。例如：
CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
规则比触发器的开销大很多，但是每个查询只需要付出一次开销，而不是每行都需要一次开销，因此该方法可能更有利于批量插入的情况。但是，大多数情况下，触发器将会提供更好的性能。
需要注意的是COPY将会忽略规则。如果想要使用COPY插入数据，需要将数据复制到正确的分区表，而不是主表。COPY会激活触发器，因此使用触发器方法时可以正常使用COPY。
使用规则方法的另一个劣势是当规则集没有覆盖到插入的数据时，没有简单的方法产生一个错误；数据将会默认插入主表。
分区还可以使用UNION ALL视图实现。例如：
CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;
但是，添加和删除单个分区需要重建视图。实践中该方法很少推荐使用。

5.9.6. 注意事项

使用分区表时需要注意以下事项：
• 没有自动验证所有的CHECK约束之间是否互相排斥的方法。创建生成分区和创建/修改相关对象的代码比手动创建每个分区更加安全。
• 本节的分区方案假设分区表的关键列的值不会发生变化，或者至少变化不会导致数据移动到另一个分区。尝试移动数据的UPDATE将会失败，因为存在CHECK的约束。如果需要处理这种情况，可以使用合适的更新触发器，但是这将导致结构的管理更加复杂。
• 如果使用手动的VACUUM或者ANALYZE命令，不要忘记为每个分区单独运行这些命令。以下命令只会处理主表：
ANALYZE measurement;
使用约束排除时需要注意以下事项：
• 约束排除只有当查询的WHERE子句包含常量（或者外部提供的参数）时有效。例如，与一个可变函数，例如CURRENT_TIMESTAMP，的比较不会进行优化，因为计划器不知道运行时的函数值会满足哪个分区。
• 尽量保持分区约束简单，否则计划器可能不能证明不需要访问哪些分区。对于列表分区使用简单的等式条件，对于范围分区使用简单的范围测试，如上文示例所示。一个好的经验方法是分区约束仅仅包含关键列和常量的可使用B-tree索引的比较运算符。
• 主表的所有分区的所有约束都将进行检验，因此大量的分区可能明显增加查询计划时间。使用这些分区技术在多达上百个分区时能够运行的很好，不要尝试使用几千个分区。

5.10. 外部数据

PostgreSQL实现了部分SQL/MED规范，允许使用常规SQL查询访问位于PostgreSQL外部的数据。这类数据被称为外部数据。（注意不要将它与外键混淆，外键是数据库中的一种约束。）
外部数据通过一个外部数据封装器进行访问。外部数据封装器是一个能够与外部数据源通讯的一个程序库，隐藏了数据源连接和数据获取的细节。在contrib模块中包含了一些外部数据封装器；参见附录F。其他类型的外部数据封装器由第三方产品提供。如果没有适合你的需求的外部数据封装器，你可以自己编写一个；参考第53章。
要访问外部数据，你需要创建一个外部服务器对象，它定义了如何根据外部数据封装器的配置选项连接到一个特定外部数据源。然后你需要创建一个或者多个外部表，它定义了远程数据的结构。外部表可以像常规表一样用于查询，但是外部表没有存储在PostgreSQL服务器中。每次使用外部表时，PostgreSQL请求外部数据封装器从外部数据源获取数据，或者在使用更新命令时将数据传输到外部数据源。
访问远程数据可能需要外部数据源的权限认证。认证信息可以通过一个用户映射提供，它可以基于当前PostgreSQL角色提供额外的数据，例如用户名和密码。
更多信息可以参考CREATE FOREIGN DATA WRAPPER，CREATE SERVER，CREATE USER MAPPING，以及CREATE FOREIGN TABLE。

5.11. 其他数据库对象

表是关系数据库结构中的核心对象，因为表中存储了数据。但是它们不是数据库中唯一的对象。数据库中可以创建许多其他类型的对象，它们使得数据的使用和管理更加高效和方便。本章不会讨论这些对象，但是此处给出了一个对象的列表，以便你能有一个感性的认识：
• 视图
• 函数和运算符
• 数据类型和域
• 触发器和重写规则
关于这些主题的相信信息可以参考第V部分。

5.12. 依赖追踪

当你创建了一个包含许多带外键约束的表，视图，触发器，函数等的复杂数据库结构时，就隐式创建了一个对象之间互相依赖的网络。例如，一个带外键约束的表就依赖于它所参照的表。
为了确保整个数据库结构的完整性，PostgreSQL确保了你无法删除被其他对象所依赖的对象。例如，尝试删除5.3.5节中讨论的产品表（订单表依赖于该表）将会导致一个类似如下的错误信息：
DROP TABLE products;

NOTICE: constraint orders_product_no_fkey on table orders depends on table products

ERROR: cannot drop table products because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
错误信息中包含了一个有用的提示：如果你不想分别删除所有的依赖对象，你可以使用以下命令：
DROP TABLE products CASCADE;
这样所有的依赖对象都将被删除。在这个示例中，不会删除订单表，只是删除了外键约束。（如果你想要确认DROP ... CASCADE所做的操作，可以运行不带CASCADE的DROP命令，然后查看NOTICE信息。）
PostgreSQL中的所以删除命令都支持CASCADE。当然，不同的对象类型支持的依赖性的本质不同。你也可以使用RESTRICT替代CASCADE以采用默认行为，即阻止删除被其他对象依赖的对象。
注意：根据SQL标准，必须指定RESTRICT或者CASCADE。实际上没有数据库系统强制要求该规则，但是默认行为是RESTRICT还是CASCADE，不同的系统采取的方案不同。
注意：PostgreSQL 7.3之前的外键约束依赖和序列字段依赖在升级过程中不会进行维护或创建。其他所有类型的依赖在从7.3之前的版本升级的过程中将会正确地创建。