4.2.7. 聚集表达式

一个聚集表达式代表一个聚集函数对一个查询选出的行的处理。 一个聚集函数把多个输入缩减为一个输出值， 比如给输入求和或平均。一个聚集表达式的语法是下列之一：

aggregate_name (expression)
aggregate_name (ALL expression)
aggregate_name (DISTINCT expression)
aggregate_name ( * )

这里 aggregate_name 是前面定义的聚集，（可能是全称）， 而 expression 是一个本身不包含聚集表达式的任意值表达式。

第一种形式的聚集表达式为所有表达式生成非空值的输入行调用聚集。 （实际上，是否忽略空值由聚集函数决定 — 但是所有标准的聚集函数都忽略它们。） 第二种形式和第一种一样，因为 ALL 是缺省值。 第三种形式为所有输入行里找到表达式的所有唯一的非空值调用聚集。 最后一种形式为每个输入行（不管是空还是非空）调用一次聚集； 因为没有声明特定的输入值。通常它只是对 count() 聚集函数有用。

比如，count(*) 生成输入行的总数； count(f1) 生成 f1 为非空的输入行数； count(distinct f1) 生成 f1 唯一非空的行数。

预定义的聚集函数在 Section 9.15 里描述。 其它聚集函数可以由用户增加。

一个聚集表达式只能在 SELECT 命令的结果列表或者 HAVING 子句里出现。 禁止在其它子句里出现，比如 WHERE 里面，因为这些子句逻辑上在生成聚集结果之前计算。

如果一个聚集表达式出现在一个子查询里（参阅 Section 4.2.9 和 Section 9.16）， 聚集通常是在子查询的行上进行计算。但是如果聚集的参数只包含外层查询的变量则有一个例外： 这个聚集会属于离他最近的外层查询，并且在该查询上进行计算。 该聚集表达式整体上属于它出现的子查询对外层查询的引用，其作用相当于子查询任何一次计算中的一个常量。 这个聚集表达式的有关只能出现在结果列或者 HAVING 子句的限制适用于聚集所属的查询层。
4.2.8. 类型转换

一个类型转换声明一个从一种数据类型到另外一种数据类型的转换。 PostgreSQL 接受两种等效的类型转换语法：

CAST ( expression AS type )
expression::type

CAST 语法遵循 SQL；:: 的语法是 PostgreSQL 传统用法。

如果对一个已知类型的值表达式应用转换，它代表一个运行时类型转换。 只有在定义了合适的类型转换操作的情况下，该转换才能成功。 请注意这一点和用于常量的转换略有区别，如 Section 4.1.2.5 所示。 一个应用于某个未修饰的字串文本的转换表示给一个字串文本数值赋予一个初始化类型， 因此它对于任何类型都会成功（如果字串文本的内容符合该数据类型的输入语法接受。）

如果对于一个值表达式生成的数值对某类型而言不存在混淆的情况， 那么我们可以省略明确的类型转换（比如，在给一个表字段赋值的时候）； 在这样的情况下，系统将自动附加一个类型转换。 不过，自动转换只适用于那些系统表中标记着 "OK to apply implicitly" 的转换函数。 其它转换函数必须用明确的转换语法调用。 这些限制是为了避免一些怪异的转换被应用。

我们也可以用函数样的语法声明一个类型转换：

typename ( expression )

不过，这个方法只能用于那些名字同时也是有效函数名字的类型。 比如，double precision 就不能这么用， 但是等效的 float8 可以。同样，interval， time，和 timestamp 如果加了双引号也只能这么用， 因为存在语法冲突。因此，函数样的类型转换会导致不一致， 所以可能应该避免在新应用中这么用。 （函数样语法实际上就似乎一个函数调用。如果使用两种标准转换语法做运行时转换， 那么它将在内部调用一个已注册得函数执行转换。通常， 这种转换函数和它们得输出类型同名，但是这个要点可不是那些可以移植的程序可以依赖的东西。）
4.2.9. 标量子查询

一个标量子查询是一个放在圆括弧里的普通 SELECT查询， 它只返回只有一个字段的一行。（参阅 Chapter 7 获取有关写查询的信息。） 该 SELECT 将被执行， 而其单个返回值将在周围的值表达式中使用。 把一个返回超过一行或者超过一列的查询用做标量查询是错误的。 （不过，在特定的执行中，子查询不返回行则不算错误；标量结果认为是NULL。） 该子查询可以引用周围查询的变量，那些变量也是在计算任意子查询的时候当做常量使用的。 又见 Section 9.16。

比如，下面的查询找出每个州中的最大人口数量的城市：

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
FROM states;

4.2.10. 数组构造器

一个数组构造器是一个表达式，它从它的成员元素上构造一个数组值。 一个简单的数组构造器由关键字 ARRAY，一个左方括弧 [， 一个或多个表达式（用逗号分隔）表示数组圆熟值，以及最后一个右方括弧 ]。 比如

SELECT ARRAY[1,2,3+4];
  array
---------
{1,2,7}
(1 row)

数组元素类型是成员表达式的公共类型，使用和 UNION 或 CASE 构造一样的规则决定。 （参阅 Section 10.5）。

多维数组值可以通过嵌套数组构造器的方法来制作。 在内层构造器里，关键字 ARRAY 可以省略。比如，下面的两句生成同样的结果：

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
     array
---------------
{{1,2},{3,4}}
(1 row)

SELECT ARRAY[[1,2],[3,4]];
     array
---------------
{{1,2},{3,4}}
(1 row)

因为多维数组必须式方形，同层的内层构造器必须生成同维的子数组。

多维数组构造器元素可以是任何生成合适数组的东西，而不仅仅是一个子 ARRAY 构造。 比如：

CREATE TABLE arr(f1 int[], f2 int[]);

INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;
                     array
------------------------------------------------
{{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
(1 row)

我们也可以从一个子查询的结果中构造一个数组。在这种形式下， 数组构造器是用关键字 ARRAY 后面跟着一个用圆括弧（不是方括弧）包围的子查询。 比如：

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
                          ?column?
-------------------------------------------------------------
{2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}
(1 row)

子查询必须返回一个字段。生成的一维数组将为子查询里每行结果生成一个元素， 元素类型匹配子查询的输出字段。

用 ARRAY 建立的数组值的脚标总是从一开始。 有关数组的更多信息，参阅 Section 8.10。

4.2.11. 行构造

一个行构造器是一个从提供给它的成员字段数值中制作行数值（也叫复合类型值）的表达式。 一个行构造器由关键字 ROW，一个左圆括弧， 零个或者多个用做行字段值的表达式（用逗号分隔），以及最后一个右圆括弧。比如，

SELECT ROW(1,2.5,'this is a test');

如果在列表里有多个表达式，那么关键字 ROW 是可选的。

缺省时，ROW 表达式创建的值是一个匿名的记录类型。如果必要，你可以把它转换成一个命名的复合类型 — 既可以是一个表的行类型，也可以是一个用 CREATE TYPE AS 创建的复合类型。 可能会需要一个明确的转换以避免歧义。比如：

CREATE TABLE mytable(f1 int, f2 float, f3 text);

CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- 因为只有一个 getf1() 存在，所以不需要类型转换
SELECT getf1(ROW(1,2.5,'this is a test'));
getf1
-------
     1
(1 row)

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- 现在我们需要类型转换以表明调用哪个函数：
SELECT getf1(ROW(1,2.5,'this is a test'));
ERROR:  function getf1(record) is not unique

SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
getf1
-------
     1
(1 row)

SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
getf1
-------
    11
(1 row)

行构造器可以用于制作存储在复合类型表字段里面的复合类型值， 或者是传递给一个接受复合类型参数的函数。还有，我们也可以比较两个行数值或者用 IS NULL 或 IS NOT NULL 测试一个行数值，比如

SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');

SELECT ROW(a, b, c) IS NOT NULL FROM table;

更多的细节，请参阅 Section 9.17。 行构造还可以用于连接子查询，这些在 Section 9.16 里面有详细讨论。
4.2.12. 表达式计算规则

子表达式的计算顺序是没有定义的。特别要指出的是， 一个操作符或者函数的输入并不一定是按照从左向右的顺序或者以某种特定的顺序进行计算的。

另外，如果一个表达式的结果可以通过只判断它的一部分就可以得到， 那么其它子表达式就可以完全不计算了。比如，如果我们这么写

SELECT true OR somefunc();

那么 somefunc() 就（可能）根本不会被调用。 如果我们写下面的，也可能会是这样

SELECT somefunc() OR true;

请注意这里和某些编程语言里的从左向右"短路"是不一样的。

因此，拿那些有副作用的函数作为复杂表达式的一部分是不明智的选择。 在 WHERE 和 HAVING 子句里面依赖副作用或者是计算顺序是特别危险的， 因为这些子句都是作为生成一个执行规划的一部分进行了大量的再处理。 在这些子句里的布尔表达式（AND/OR/NOT 的组合）可以以布尔代数运算律允许的任意方式进行识别。

如果强制计算顺序非常重要，那么可以使用 CASE 构造（参阅 Section 9.13）。 比如，下面是一种视图避免在 WHERE 子句里被零除的不可信的方法：

SELECT ... WHERE x <> 0 AND y/x > 1.5;

但是下面这样的是安全的：

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

用这种风格的 CASE 构造会阻止优化，因此应该只在必要的时候使用。 （在这个特殊的例子里，毫无疑问写成 y > 1.5*x 更好。）

5.1. 表的基本概念

关系型数据库中的表非常类似纸面上的一张表：它由行和列组成。 字段的数目是固定的，每个字段都有一个名字。行的数目是变化的 -- 它反映在任意时刻里存储的数据量。SQL 对表中的行的顺序没有任何承诺---除非你要求明确地进行排序。 这些内容在 Chapter 7 里介绍。另外，SQL 并不给行赋予唯一的标识， 因此我们很可能在一个表中有好几个完全相同的行。 这是作为SQL的基础的下层数学模型的必然结果，但是通常是我们不愿意看到的。 本章稍后的部分将讨论如何处理这个问题。

每个字段都有一个数据类型。数据类型约束可以赋予一个字段的可能数值的集合， 并且约束存储在字段里的数据的赋值语义，这样它就可以用于计算。比如， 一个声明为一个数值类型的字段将不会接受任意文本字串，而存储在这样的字段里的数据可以用于数学计算。 相比之下，一个声明为字符字串类型的字段将接受几乎任意类型的数据， 但是它们自身是不能进行数学计算的，不过我们可以进行其他象字串连接这样的操作。

PostgreSQL 包含一套可剪裁的内置数据类型， 这些类型可以适用于许多应用。用户也可以定义它们自己的数据类型。 大多数内置的数据类型有显而易见的名字和语义，因此我们把详细的解释放在了 Chapter 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。表和字段的名字遵循我们在 Section 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 脚本文件里， 我们常见在创建表之前试图无条件删除它，忽略错误信息。

如果你需要修改一个已经存在的表，那么可以看看本章稍后的 Section 5.6。

使用到目前为止讨论的工具我们可以创建功能完整的表。 本章剩下的部分是有关向表定义中增加特性，保证数据完整性，安全性或者便利性的内容。 如果你急于给你的表填充数据，那么你可以忽略余下的部分直接到 Chapter 6，然后在稍后的时候再阅读本章。

5.2. 系统字段

每个表都有几个系统字段，这些字段是由系统隐含定义的。 因此，这些名字不能用于用户定义的字段名。 （请注意这些限制与这个名字是否关键字无关；把名字用引号括起来并不能让你逃离这些限制。） 你实际上不需要注意这些字段，只要知道它们存在就可以了。

oid

    一行的对象标识符（对象 ID）。这是一个序列号，是由 PostgreSQL 给所有表的行自动追加的 （除非建表的时候用了 WITHOUT OIDS，这种情况下就不会出现这个字段）。 这个字段的类型是 oid（和字段同名）； 参阅 Section 8.12 获取有关这种类型的更多信息。
tableoid

    包含本行的表的 OID。这个字段对那些从继承层次中选取的查询特别有用， 因为如果没有它的话，我们就很难说明一行来自哪个独立的表。 tableoid 可以和 pg_class 的 oid 字段连接起来获取表名字。
xmin

    插入该行版本的事务的标识（事务 ID）。（注意：在这个环境里， 一个行版本是一行的一个状态；一行的每次更新都为同一个逻辑行创建一个新的行版本。）
cmin

    在插入事务内部的命令标识（从零开始）。
xmax

    删除事务的标识（事务ID），如果不是被删除的行版本，那么是零。 在一个可见行版本里，这个字段有可能是非零。这通常意味着删除事务还没有提交， 或者是一个删除的企图被回滚掉了。
cmax

    在删除事务内部的命令标识符，或者是零。
ctid

    一个行版本在它所处的表内的物理位置。请注意，尽管 ctid 可以用于非常快速地定位行版本，但每次 VACUUM FULL 之后， 一个行的 ctid 都会被更新或者移动。 因此 ctid 是不能作为长期的行标识符的。 应该使用OID，或者更好是用户定义的序列号，来标识一个逻辑行。

OID 是 32 位的量，是在同一个集群内通用的计数器上赋值的。 对于一个大型或者长时间使用的数据库，这个计数器是有可能重叠的。 因此，假设 OID 是唯一的是非常错误的，除非你自己采取了措施来保证它们是唯一的。 如果你需要标识表中的行，我们强烈建议使用序列号生成器。 不过，也可以使用 OID，只要采取几个注意事项即可：

    *

      在使用 OID 标识行的每个表的 OID 字段创建一个唯一约束。
    *

      绝对不要假设 OID 是跨表唯一的；如果你需要全数据库范围内的标识，请使用 tableoid 和行的 OID 的组合。
    *

      需要 OID 的表应该带着 WITH OIDS 创建，以确保和将来版本的 PostgreSQL 兼容。计划将来的版本里 WITHOUT OIDS 是缺省。

事务标识符也是 32 位的量。在长时间运转的数据库里，它也可能会重叠。 只要我们采取一些合适的维护步骤，这并不是很要命的问题； 参阅 Chapter 21 获取细节。不过， 在长时间运行的环境里（超过十亿次事务）依赖事务 ID 的唯一性并非明智的做法。

命令标识符也是 32 位的量。这样就在一个事务里有232（四十亿）条 SQL 命令的硬限制。 在现实里这个限制应该不是什么问题 — 注意这个限制是 SQL 命令的条数， 而不是处理的行版本的条数。

5.3. 缺省值

一个字段可以赋予缺省值。如果新创建了一个数据行，而有些字段的数值没有声明，那么这些字段将被填充与它们各自的缺省值。 一条数据修改命令也可以明确地要求把一个字段设置成为它地缺省值， 而不用事先知道这个数值是什么。（有关数据操作的命令在 Chapter 6。）

如果没有明确声明缺省值，那么缺省值是空。 这么做通常是合理的，因为空值可以认为代表未知数据。

在一个表定义里，缺省值是在字段数据类型后面列出。比如：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);

缺省值可以是一个标量表达式，它会在插入缺省值的时候计算（不是创建表的时候）。 一个常见的例子是一个 timestamp 字段可能有缺省值 now()， 这样它就设置为插入行的时刻。 另外一个常见的例子是为每一行生成一个"序列号"。 在　PostgreSQL　里，通常是用类似下面这样的东西生成的

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);

这里的　nextval()　从一个序列对象 （参阅　Section 9.12）提供后继的数值。 这种做法非常普遍，以至于我们有一个特殊的缩写用于此目的：

CREATE TABLE products (
    product_no SERIAL,
    ...
);

SERIAL　缩写在Section 8.1.4　里有进一步描述。

5.4. 约束

数据类型是约束我们可以在表里存储什么类型的数据的一种方法。 不过，对于许多应用，它们提供的约束实在是太粗糙。比如， 一个包含产品价格的字段可能应该只接受正数。但是没有哪种数据类型只接受正数。 另外一个问题是你可能需要根据其他字段或者行的数据来约束字段数据。比如，在一个包含产品信息的表中， 每个产品编号都应该只有一行。

对于这些问题，SQL允许你在字段和表上定义约束。 约束给予你所需要对数据施加的一切控制。如果一个用户企图在一个字段里存储会违反约束的数据，那么就会抛出一个错误。 这种情况同时也适用于数值来自缺省值的情况。
5.4.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)
);

头两个约束看上去应该很面熟。第三个使用了一个新的语法。 它没有附着在某个字段上，它在逗号分隔的字段列表中是以一个独立行的形式出现的。 字段定义和这些约束定义可以以混合的顺序列出。

我们说头两个约束是字段约束，而第三个是表约束，因为它和字段定义分开写。 字段约束也可以写成表约束，而反过来很可能不行。上面的例子也可以这么写

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)
);

这只是风格的不同。

我们还要知道一个检查约束在表达式计算出真或者空值的时候是得到满足的。 因为大多数表达式在其中一个操作数是空的时候都会得出空值， 所以这些约束不能在受约数字段上禁止空值。要确保一个字段不包含空值，我们可以使用下一节介绍的非空约束。
5.4.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.4.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
);

通常，如果在表中有两行或更多行，而这些行中包含在唯一约束里面的那几个字段都相等，那么就算违反了唯一约束。 但是在这种比较中，空值是认为不相等的。这就意味着，在多字段唯一约束的情况下， 如果在至少一个字段上存在空值，那么这样的行我们可以存储无限多个。 这种行为遵循 SQL 标准，但是我们听说其它 SQL 数据库可能不遵循这个标准。因此如果你要开发可移植的程序， 那么最好仔细些。

5.4.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 应用可能需要知道一个表的主键才能唯一地标识一个行。

一个表最多可以有一个主键（但是它可以有多个唯一和非空约束）。 关系型数据库理论告诉我们，每个表都必须有一个主键。PostgreSQL 并不强制这个规则，但我们最好还是遵循它。
5.4.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 选项，它是在被引用字段修改（更新）的时候调用的。可用的动作是一样的。

有关更新和删除数据的更多信息可以在 Chapter 6 里找到。

最后，我们应该说明的是，一个外键必须要么引用一个主键，要么引用一个唯一约束。 如果外键引用了一个唯一约束，那么在如何匹配空值这个问题上还有一些其它的可能性。 这些东西都在 CREATE TABLE 里的 CREATE TABLE 中解释。

5.5. 继承

让我们创建两个表。首府表包含每个州的首府，它们也是城市。通常，首府表应该从城市表中继承过来。

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- (单位：英尺)
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

在这种情况下，一行首府从它的父表，城市表中继承所有属性（名字，人口以及海拔）。 州首府有一个额外的属性，state，显示它们所在的州。在 PostgreSQL 里， 一个表可以从零个或多个其它表中继承属性，而且一个查询既可以引用一个表中的所有行， 也可以引用一个表的所有行加上所有其后代表的行。

    注意: 继承层次实际上是有向开环图。

比如，下面的查询查找所有海拔 500 英尺以上的所有城市的名字，包括州首府：

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

它返回：

   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

这里的 cities 前面的 "ONLY" 表面该查询应该只对 cities 进行查找而不包括继承级别低于 cities 的表。 许多我们已经讨论过的命令 -- SELECT， UPDATE 和 DELETE -- 支持这个 "ONLY" 符号。

    废弃: 以前版本的 PostgreSQL 里，缺省是不访问子表。 我们发现这样是容易出错的而且违背 SQL:1999 标准。在旧语法里面，要访问子表，你需要附加一个 * 到表名后面。例如

    SELECT * from cities*;

    你仍然可以通过附加*明确声明需要扫描子表， 也可以通过写 "ONLY" 声明明确声明不扫描子表。 不过，从版本 7.1 开始，对那些不带修饰的表名子的缺省行为是同时扫描它的子表， 而以前的缺省是正相反。要获得老的缺省行为， 把配置选项 SQL_Inheritance 关闭，也就是∶

    SET SQL_Inheritance TO OFF;

    或者向你的 postgresql.conf 文件里面加一行。

有时候你可能想知道某条行版本来自哪个表。在每个表里我们都有一个系统属性叫 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

(如果你想复现这个例子，你可能会得到不同的数字 OID。) 通过和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

一个表可以从多于一个父表中继承，在这种情况下，它拥有它的父表们定义的字段的和 （加上任何为这个子表单独定义的字段）。

继承特性的一个严重的局限性是索引（包括唯一约束）和外键约束只施用于单个表， 而不包括它们的继承的子表。这一点不管对引用表还是被引用表都是事实，因此，在上面的例子里：

    *

      如果我们声明 cities.name 为 UNIQUE 或者是一个 PRIMARY KEY， 那么也不会阻止 capitals 表拥有重复了名字的 cities 数据行。 并且这些重复的行缺省时在查询 cities 表的时候会显示出来。 实际上，缺省时 capitals 将完全没有唯一约束，因此可能包含带有同名的多个行。 你应该给 capitals 增加唯一约束，但是这样做也不会避免与 cities 的重复。
    *

      类似，如果我们声明 cities.name REFERENCES 某些其它的表， 这个约束不会自动广播到 capitals。在这种条件下，你可以通过手工给 capitals 增加同样的 REFERENCES 约束来做到这点。
    *

      声明其它一个表的字段为 REFERENCES cities(name) 将允许其它表包含城市名， 但是不包含首府名。这种情况下没有很好的绕开办法。

这些缺点很可能在将来的版本中修补，但同时你也需要考虑一下，继承是否对你的问题真正有用。

5.6. 修改表

如果你创建了一个表后发现自己犯了一些错误，或者是应用的需求 发生了变化，那么你可以删除这个表然后重新创建它。但是如果这个 表已经填充了好多数据了，或者是该表已经被其它数据库对象引用， （比如一个外键约束） 那这可不是一个方便的选项。因此 PostgreSQL 提供了一族命令用于修改现有表。

你可以

    *

      增加字段，
    *

      删除字段，
    *

      增加约束，
    *

      删除约束，
    *

      修改缺省值，
    *

      修改字段数据类型，
    *

      重命名字段，
    *

      重命名表。

所有这些动作都是用 ALTER TABLE 命令执行的。
5.6.1. 增加字段

要增加一个字段，使用这条命令：

ALTER TABLE products ADD COLUMN description text;

新增的字段对于表中已经存在的行而言最初将先填充所给出的缺省值 （如果你没有声明DEFAULT子句，那么缺省是空值）。

你也可以同时在该字段上定义约束，使用通常的语法：

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

实际上，所有在　CREATE TABLE　里描述的可以应用于字段之选项都可以在这里使用。 不过，我们要注意的是缺省值必须满足给出的约束，否则　ADD　将会失败。 另外，你可以在你正确填充了新字段的数值之后再增加约束（见下文）。
5.6.2. 删除字段

要删除一个字段，使用这个命令：

ALTER TABLE products DROP COLUMN description;

5.6.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.6.4. 删除约束

要删除一个约束，你需要知道它的名字。如果你给了它一个名字， 那么事情就好办了。否则系统会分配一个生成的名字，这样你就需要 把它找出来了。psql 的命令 \d tablename 在这儿可以帮忙； 其它接口可能也提供了检查表的细节的方法。然后就是这条命令：

ALTER TABLE products DROP CONSTRAINT some_name;

（如果你在处理一个生成的约束名，比如 $2，别忘了你需要给它 添加双引号，让它成为一个有效的标识符。）

除了非空约束外，所有约束类型都这么用。要删除非空类型，用

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

（要记得非空约束没有名字。）
5.6.5. 改变一个字段的缺省值

要给一个字段设置缺省值，使用一个象下面这样的命令：

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

请注意这么做不会影响任何表中现有的数据行， 它只是为将来　INSERT　命令改变缺省值。

要删除缺省值，用

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

这样相当于把缺省设置为空。 结果是，如果我们删除一个还没有定义的缺省值不算错误，因为缺省隐含就是空值。
5.6.6. 修改一个字段的数据类型

把一个字段转换成另外一种数据类型，使用下面的命令：

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

只有在字段里现有的每个项都可以用一个隐含的类型转换转换城新的类型时才可能成功。 如果需要更复杂的转换，你可以增加一个 USING 子句，它声明如何从旧值里计算新值。

PostgreSQL 将试图把字段的缺省值（如果存在）转换成新的类型， 还有涉及该字段的任何约束。但是这些转换可能失败，或者可能生成奇怪的结果。 在修改某字段类型之前，你最好删除那些约束，然后再把自己手工修改过的添加上去。
5.6.7. 给字段改名字

重命名一个字段：

ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.6.8. 给字段改名字

给一个字段重命名：

ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.6.9. 给表改名字

给一个表重命名：

ALTER TABLE products RENAME TO items;

5.7. 权限

如果你创建了一个数据库对象，那么你就成为它的所有者。 缺省时，只有一个对象的所有者可以在对象上做任何事情。 为了允许其它用户使用它，我们必须赋予他们权限。 （不过拥有超级用户权限的用户总是可以访问任何对象。）

    注意: 为了改变一个表，索引，序列，或者视图的所有者， 使用 ALTER TABLE 命令。

有好几种不同的权限：SELECT，INSERT， UPDATE，DELETE，RULE， REFERENCES，TRIGGER，CREATE， TEMPORARY，EXECUTE，USAGE。 适用于特定对象的权限因对象类型（表、函数等）而异。 和 ALL PRIVILEGES。有关 PostgreSQL 所支持的不同类型的权限的完整信息，请参考 GRANT 的手册页。下面的章节将为你展示如何利用这些权限。

修改或者删除一个对象的权限永远是所有者独有的权限。

    注意: 要改变一个表，索引，序列，或者视图的所有者，使用 ALTER TABLE 命令。 还有对应其它对象类型的 ALTER 命令。

要赋予一个权限，我们使用 GRANT 命令。 因此，如果 joe 是一个现存的用户，而 accounts 是一个已经存在的表， 更新表的权限可以用下面的命令赋予

GRANT UPDATE ON accounts TO joe;

要给一个组赋予权限，使用

GRANT SELECT ON accounts TO GROUP staff;

名字叫PUBLIC的特殊"用户"可以用于 将权限赋予系统中的每一个用户。在声明权限的位置写 ALL 则将所有的与该对象类型相关的权限都赋予出去。

要撤销一个权限，使用合适的 REVOKE 命令：

REVOKE ALL ON accounts FROM PUBLIC;

对象所有者的特殊权限（也就是做 DROP，GRANT， REVOKE，等等的权限）总是隐含地属于所有者，并且不能 赋予或者撤销。但是对象所有者可以选择撤销自己的普通权限， 比如把一个表做成对他自己和别人都是只读的。

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