本文共 63168 字，大约阅读时间需要 210 分钟。
本文档为PostgreSQL 9.6.0文档，本转载已得到原译者彭煜玮授权。
1. 数字类型
数字类型由2、4或8字节的整数以及4或8字节的浮点数和可选精度小数组成。Table 8-2列出了所有可用类型。
Table 8-2. 数字类型
1.1. 整数类型
类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的值将导致一个错误。
常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用 smallint类型。而只有在integer的范围不够的时候才使用bigint。
SQL只声明了整数类型integer（或int）、smallint和bigint。类型int2、int4和int8都是扩展，也在许多其它SQL数据库系统中使用。
1.2. 任意精度数字
类型numeric可以存储非常多位的数字。我们特别建议将它用于货币金额和其它要求计算准确的数量。numeric值的计算在可能的情况下会得到准确的结果，例如加法、减法、乘法。不过，numeric类型上的算术运算比整数类型或者下一节描述的浮点数类型要慢很多。
在随后的内容里，我们使用了下述术语：一个numeric的比例是到小数部分的位数，numeric的精度是整个数字里全部位的数目，也就是小数点两边的位数目。因此数字 23.5141 的精度为6而比例为4。你可以认为整数的比例为零。
numeric列的最大精度和最大比例都是可以配置的。要声明一个类型为numeric的列，你可以用下面的语法：
NUMERIC(precision, scale)
精度必须为正数，比例可以为零或者正数。另外：
NUMERIC(precision)
选择比例为 0 。如果使用
NUMERIC
创建一个列时不使用精度或比例，则该列可以存储任何精度和比例的数字值，并且值的范围最多可以到实现精度的上限。一个这种列将不会把输入值转化成任何特定的比例，而带有比例声明的numeric列将把输入值转化为该比例（SQL标准要求缺省的比例是 0，即转化成整数精度。我们觉得这样做有点没用。如果你关心移植性，那你最好总是显式声明精度和比例）。
Note:
显式指定类型精度时的最大允许精度为 1000，没有指定精度的NUMERIC受到Table 8-2中描述的限制所控制。如果一个要存储的值的比例比列声明的比例高，那么系统将尝试圆整（四舍五入）该值到指定的分数位数。 然后，如果小数点左边的位数超过了声明的精度减去声明的比例，那么抛出一个错误。
数字值在物理上是以不带任何前导或者后缀零的形式存储。 因此，列上声明的精度和比例都是最大值，而不是固定分配的 （在这个方面，numeric类型更类似于varchar(n)， 而不像char(n)）。 实际存储要求是每四个十进制位组用两个字节， plus three to eight bytes overhead.
除了普通的数字值之外，numeric类型允许特殊值NaN， 表示"不是一个数字"。任何在 NaN上面的操作都生成另外一个NaN。 如果在 SQL 命令里把这些值当作一个常量写，你必须在其周围放上单引号，例如UPDATE table SET x = 'NaN'。在输入时，字串NaN被识别为大小写无关。
Note: 在"不是一个数字"概念的大部分实现中，NaN被认为不等于任何其他数字值（包括NaN）。为了允许numeric值可以被排序和使用基于树的索引，PostgreSQL把NaN值视为相等，并且比所有非NaN值都要大。
类型decimal和numeric是等效的。两种类型都是SQL标准的一部分。
在对值进行圆整时，numeric类型会圆到远离零的整数，而（在大部分机器上）real和double precision类型会圆到最近的偶数上。例如：
SELECT x, round(x::numeric) AS num_round, round(x::double precision) AS dbl_roundFROM generate_series(-3.5, 3.5, 1) as x; x | num_round | dbl_round------+-----------+----------- -3.5 | -4 | -4 -2.5 | -3 | -2 -1.5 | -2 | -2 -0.5 | -1 | -0 0.5 | 1 | 0 1.5 | 2 | 2 2.5 | 3 | 2 3.5 | 4 | 4(8 rows)
1.3. 浮点类型
数据类型real和double precision是不准确的、变精度的数字类型。实际上，这些类型是IEEE标准 754 二进制浮点算术（分别对应单精度和双精度）的一般实现， 一直到下层处理器、操作系统和编译器对它的支持。
不准确意味着一些值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储和检索一个值可能出现一些缺失。 处理这些错误以及这些错误是如何在计算中传播的主题属于数学和计算机科学的一个完整的分支， 我们不会在这里进一步讨论它，这里的讨论仅限于如下几点：
如果你要求准确的存储和计算（例如计算货币金额），应使用numeric类型。
如果你想用这些类型做任何重要的复杂计算，尤其是那些你对范围情况（无穷、下溢）严重依赖的事情，那你应该仔细评诂你的实现。
用两个浮点数值进行等值比较不可能总是按照期望地进行。
在大部分平台上，real类型的范围是至少 -1E+37 到 +1E+37，精度至少是 6 位小数。double precision类型通常有 -1E+308 到 +1E+308 的范围，精度是至少 15 位数字。太大或者太小的值都会导致错误。 如果输入数字的精度太高，那么可能发生园整。太接近零的数字，如果无法与零值的表现形式相区分就会产生下溢错误。
Note:
extra_float_digits设置控制当一个浮点值被转换为文本输出时要包括的额外有效数字的数目。其默认值为0，在每一个PostgreSQL支持的平台上输出都相同。增加该设置将产生能更精确表示存储值的输出，但是可能无法移植。除了普通的数字值之外，浮点类型还有几个特殊值：
- Infinity
- -Infinity
- NaN
这些值分别表示 IEEE 754 特殊值"正无穷大"、"负无穷大"以及"不是一个数字"（在不遵循 IEEE 754 浮点算术的机器上，这些值的含义可能不是预期的）。如果在 SQL 命令里把这些数值当作常量写，你必须在它们周围放上单引号，例如UPDATE table SET x = 'Infinity'。 在输入时，这些串是以大小写无关的方式识别的。
Note:
IEEE754指定NaN不应该与任何其他浮点值（包括NaN）相等。为了允许浮点值被排序或者在基于树的索引中使用，PostgreSQL将NaN值视为相等，并且比所有非NaN值要更大。PostgreSQL还支持 SQL 标准表示法float和float(p)用于声明非精确的数字类型。在这里，p指定以二进制位表示的最低可接受精度。 在选取real类型的时候，PostgreSQL接受float(1)到float(24)，在选取double precision的时候，接受float(25)到float(53)。在允许范围之外的p值将导致一个错误。没有指定精度的float将被当作是double precision。
Note: 认为real和double precision分别有 24 和 53 个二进制位的假设对 IEEE 标准的浮点实现来说是正确的。在非 IEEE 平台上，这个数值可能略有偏差，但是为了简化，我们在所有平台上都用了同样的p值范围。
1.4. 序数类型
smallserial、serial和bigserial类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号（类似其它一些数据库中支持的AUTO_INCREMENT属性）。 在目前的实现中，下面一个语句：
CREATE TABLE tablename ( colname SERIAL);
等价于以下语句：
CREATE SEQUENCE tablename_colname_seq;CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
因此，我们就创建了一个整数列并且把它的缺省值安排为从一个序列发生器取值。应用了一个NOT NULL约束以确保空值不会被插入（在大多数情况下你可能还希望附加一个UNIQUE或者PRIMARY KEY约束避免意外地插入重复的值，但这个不是自动发生的）。最后，该序列被标记为"属于"该列，这样当列或表被删除时该序列也会被删除。Note: 因为smallserial、serial和bigserial是用序列实现的，所以即使没有删除过行，在出现在列中的序列值可能有“空洞”或者间隙。如果一个从序列中分配的值被用在一行中，即使该行最终没有被成功地插入到表中，该值也被“用掉”了。例如，当插入事务回滚时就会发生这种情况。更多信息参见Section 9.16中的nextval()。
要使用serial列插入序列的下一个数值到表中， 请指定serial列应该被赋予其缺省值。我们可以通过在INSERT语句中把该列排除在列列表之外来实现，也可以通过使用DEFAULT关键字来实现。
类型名serial和serial4是等效的： 两个都创建integer列。类型名bigserial和serial8也一样，只不过它们创建一个 bigint列。如果你预计在表的生存期中使用的标识符数目超过 231 个，那么你应该使用bigserial。类型名smallserial和serial2也以相同方式工作，只不过它们创建一个smallint列。
为一个serial列创建的序列在所属的列被删除的时候自动删除。你可以在不删除列的情况下删除序列，但是这会强制删除该列的默认值表达式。
2. 货币类型
money类型存储固定小数精度的货币数字，参阅Table 8-3。小数的精度由数据库的lc_monetary设置决定。表中展示的范围假设有两个小数位。可接受的输入格式很多，包括整数和浮点数文字，以及常用的货币格式，如'$1,000.00'。 输出通常是最后一种形式，但和区域相关。
Table 8-3. 货币类型
由于这种数据类型的输出是区域敏感的，因此将money数据装入到一个具有不同lc_monetary设置的数据库是不起作用的。为了避免这种问题，在恢复一个转储到一个新数据库中之前，应确保新数据库的lc_monetary设置和被转储数据库的相同或者具有等效值。
数据类型numeric、int和bigint的值可以被造型成money。从数据类型real和double precision的转换可以通过先造型成numeric来实现，例如：
SELECT '12.34'::float8::numeric::money;
但是，我们不推荐这样做。浮点数不应该被用来处理货币，因为浮点数可能会有圆整错误。
一个money值可以在不损失精度的情况下被造型成numeric。转换到其他类型可能会丢失精度，并且必须采用两个阶段完成：
SELECT '52093.89'::money::numeric::float8;
当一个money值被另一个money值除时，结果是double precision（即一个纯数字，而不是金额），在除法中货币单位被约掉了。
3. 字符类型
Table 8-4. 字符类型
SQL定义了两种基本的字符类型： character varying(n)和character(n)， 其中n是一个正整数。两种类型都可以存储最多n个字符长的串。试图存储更长的串到这些类型的列里会产生一个错误， 除非超出长度的字符都是空白，这种情况下该串将被截断为最大长度（这个看上去有点怪异的例外是SQL标准要求的）。 如果要存储的串比声明的长度短，类型为character的值将会用空白填满；而类型为character varying的值将只是存储短些的串。
如果我们明确地把一个值造型成character varying(n)或者character(n)， 那么超长的值将被截断成n个字符，而不会抛出错误（这也是SQL标准的要求）。
varchar(n)和char(n)的概念分别是character varying(n)和character(n)的别名。没有长度声明词的character等效于character(1)。如果不带长度说明词使用character varying，那么该类型接受任何长度的串。后者是一个PostgreSQL的扩展。
另外，PostgreSQL提供text类型，它可以存储任何长度的串。尽管类型text不是SQL标准，但是许多其它 SQL 数据库系统也有它。
类型character的值物理上都用空白填充到指定的长度n， 并且以这种方式存储和显示。不过，拖尾的空白被当作是没有意义的，并且在比较两个 character类型值时不会考虑它们。在空白有意义的排序规则中，这种行为可能会 产生意料之外的结果，例如SELECT 'a '::CHAR(2) collate "C" < E'an'::CHAR(2)会返回真（即便C区域会认为一个空格比新行更大）。当把一个character值转换成其他 字符串类型之一时，拖尾的空白会被移除。请注意，在character varying和text值里， 结尾的空白语意上是有含义的，并且在使用模式匹配（如LIKE和正则表达式）时也会被考虑。
这些类型的存储需求是 4 字节加上实际的字串，如果是 character 的话再加上填充的字节。长的字串将会自动被系统压缩， 因此在磁盘上的物理需求可能会更少些。长的数值也会存储在后台表里面，这样它们就不会干扰对短字段值的快速访问。 不管怎样，允许存储的最长字串大概是 1 GB。 （允许在数据类型声明中出现的的 n 的最大值比这还小。 修改这个行为没有甚么意义，因为在多字节编码下字符和字节的数目可能差别很大。 如果你想存储没有特定上限的长字串，那么使用 text 或者没有长度声明词的 character varying， 而不要选择一个任意长度限制。） 一个短串（最长126字节）的存储要求是1个字节外加实际的串，该串在character情况下包含填充的空白。长一些的串在前面需要4个字节而不是1个字节。长串会被系统自动压缩，这样在磁盘上的物理需求可能会更少。非常长的值也会被存储在背景表中，这样它们不会干扰对较短的列值的快速访问。在任何情况下，能被存储的最长的字符串是1GB（数据类型定义中n能允许的最大值比这个值要小。修改它没有用处，因为对于多字节字符编码来说，字符的数量和字节数可能完全不同。如果你想要存储没有指定上限的长串，使用text或没有长度声明的character varying，而不是给出一个任意长度限制）。
Tip: 这三种类型之间没有性能差别，只不过是在使用填充空白的类型的时候需要更多存储尺寸，以及在存储到一个有长度约束的列时需要少量额外CPU周期来检查长度。虽然在某些其它的数据库系统里，character(n)有一定的性能优势，但在PostgreSQL里没有。事实上，character(n)通常是这三种类型之中最慢的一个，因为它需要额外的存储开销。在大多数情况下，应该使用text或者character varying。
Example 8-1. 使用字符类型
CREATE TABLE test1 (a character(4));INSERT INTO test1 VALUES ('ok');SELECT a, char_length(a) FROM test1; -- (1) a | char_length------+------------- ok | 2CREATE TABLE test2 (b varchar(5));INSERT INTO test2 VALUES ('ok');INSERT INTO test2 VALUES ('good ');INSERT INTO test2 VALUES ('too long');ERROR: value too long for type character varying(5)INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncationSELECT b, char_length(b) FROM test2; b | char_length-------+------------- ok | 2 good | 5 too l | 5(1) 在PostgreSQL里另外还有两种定长字符类型，在Table 8-5里显示。 name类型只用于在内部系统目录中存储标识符并且不是给一般用户使用的。该类型长度当前定为 64 字节（63 可用字符加结束符）但在C源代码应该使用常量 NAMEDATALEN引用。这个长度是在编译的时候设置的（因而可以为特殊用途调整），缺省的最大长度在以后的版本可能会改变。类型"char"（注意引号）和 char(1)是不一样的，它只用了一个字节的存储空间。它在系统内部用于系统目录当做简化的枚举类型用。
Table 8-5. 特殊字符类型
4. 二进制数据类型
bytea数据类型允许存储二进制串，参见Table 8-6。
Table 8-6. 二进制数据类型
二进制串是一个八位位组（或字节）的序列。 二进制串和字符串的区别有两个： 首先，二进制串明确允许存储零值的字节以及其它"不可打印的"字节（通常是位于范围 32 到 126 之外的字节）。 字符串不允许零字节，并且也不允许那些对于数据库的选定字符集编码是非法的任何其它字节值或者字节值序列。 第二，对二进制串的操作会处理实际上的字节，而字符串的处理和取决于区域设置。 简单说，二进制字串适用于存储那些程序员认为是"裸字节"的数据，而字符串适合存储文本。 bytea类型支持两种用于输入和输出的外部格式：PostgreSQL的历史的"逃逸"格式和"十六进制"格式。在输入时这两种格式总是会被接受。输出格式则取决于配置参数bytea_output，其默认值为十六进制（注意十六进制格式是在PostgreSQL 9.0中被引入的，早期的版本和某些工具无法理解它）。
SQL标准定义了一种不同的二进制串类型， 叫做BLOB或者BINARY LARGE OBJECT。其输入格式和bytea不同，但是提供的函数和操作符大多一样。
8.4.1. bytea的十六进制格式
"十六进制"格式将二进制数据编码为每个字节2个十六进制位，最高有效位在前。整个串以序列x开头（用以和逃逸格式区分）。在某些情景中，开头的反斜线可能需要通过双写来逃逸，在相同的情况中逃逸格式必须要双写反斜线，下文描述了细节。十六进制位可以是大写也可以是小写，在位对之间可以有空白（但是在位对内部以及开头的x序列中不能有空白）。十六进制格式和很多外部应用及协议相兼容，并且其转换速度要比逃逸格式更快，因此人们更愿意用它。
例子：
SELECT E'\\xDEADBEEF';
8.4.2. bytea的逃逸格式
"逃逸"格式是bytea类型的传统PostgreSQL格式。它采用将二进制串表示成ASCII字符序列的方法，而将那些无法用ASCII字符表示的字节转换成特殊的逃逸语句。从应用的角度来看，如果将字节表示为字符有意义，那么这种表示将很方便。但是在实际中，这常常是令人困扰的，因为它使二进制串和字符串之间的区别变得模糊，并且这种特别的逃逸机制也有点难于处理。因此这种格式可能会在大部分新应用中避免使用。
在逃逸模式下输入bytea值时，某些值的字节必须被逃逸，而所有的字节值都可以被逃逸。通常，要逃逸一个字节，需要把它转换成与它的三位八进制值， 并且前导一个反斜线（或者两个反斜线，如果使用逃逸串语法将值写成一个字面含义）。反斜线本身（字节值92）也可以用双写的反斜线表示。Table 8-7显示了必须被逃逸的字符，并给出了可以使用的替代逃逸序列。
Table 8-7. bytea文字逃逸字节
逃逸"不可打印的"字节的要求取决于区域设置。在某些实例中，你可以不理睬它们，让它们保持未逃逸的状态。注意在Table 8-7的每一个例子中的结果的长度正好是一个字节，即使其输出表示有时超过一个字符。
如Table 8-7中所示，要求多个反斜线的原因是写成一个串文字的输入串在PostgreSQL服务器中必须经过两个分析阶段。每一对中的第一个反斜线被串文字分析器（假设使用了逃逸串语法）解释为一个逃逸字符并且因此被消耗，只留下该对中的第二个反斜线（美元符号包围的串可以被用于防止这一层的逃逸）。剩下的反斜线接着被bytea输入函数识别为开始一个三位八进制值或逃逸另一个反斜线。例如，一个传递给服务器的串文字是E'\001'，它在通过逃逸串分析器后变成001。001接着被送给bytea输入函数，这里它被转换成一个十进制值为1的单字节。注意单引号字符串不会被bytea特殊对待，因此它遵循串文字的正常规则。
Bytea字节有时在输出时被逃逸。通常，每一个"不可打印的"字节会被转换成与之等效的三位八进制值并且前置一个反斜线。大部分"可打印的"字节被表示为它们在客户端字符集中的标准表示形式。十进制值为92（反斜线）的字节在输出时被双写。详情请见Table 8-8。
Table 8-8. bytea输出逃逸字节
5. 日期/时间类型
PostgreSQL支持SQL中所有的日期和时间类型，如Table 8-9所示。这些数据类型上可用的操作如Section 9.9所述。日期根据公历来计算，即使对于该历法被引入之前的年份也一样（见Section B.4）。
Table 8-9. 日期/时间类型
Note: SQL要求只写timestamp等效于timestamp without time zone，并且PostgreSQL鼓励这种行为。timestamptz被接受为timestamp with time zone的一种简写，这是一种PostgreSQL的扩展。 time、timestamp和interval接受一个可选的精度值 p，这个精度值声明在秒域中小数点之后保留的位数。缺省情况下，在精度上没有明确的边界，p允许的范围对timestamp和interval类型是从 0 到 6。
Note: 当timestamp值被存储为八字节整数（目前是默认情况）时，在整个值的范围上微秒精度是可用的。当timestamp值被存储为双精度浮点数（一个已被启用的编译时选项）时，那么精度的有效限制会小于 6。timestamp值是以 2000-01-01 午夜之前或之后以来的秒数存储的。当timestamp值被用浮点数实现时，在2000-01-01前后几年的日期可以达到微秒的精度，但是对于远一些的日子，精度会下降。注意使用浮点日期时间允许显示比上文所述更大范围的timestamp值：从 4713 BC 到 5874897 AD。
同一个编译时选项也决定了time和interval值被存储为浮点数或八字节整数。在浮点数的情况中，当间隔的尺寸增长时，大interval值在精度上会下降。
对于time类型，如果使用了八字节的整数存储，允许的p的范围是从 0 到 6，如果使用的是浮点数存储，那么这个范围是 0 到 10。
interval类型有一个附加选项，它可以通过写下面之一的短语来限制存储的fields的集合：
YEARMONTHDAYHOURMINUTESECONDYEAR TO MONTHDAY TO HOURDAY TO MINUTEDAY TO SECONDHOUR TO MINUTEHOUR TO SECONDMINUTE TO SECOND
注意如果fields和p被指定，fields必须包括SECOND，因为精度只应用于秒。
类型time with time zone是 SQL 标准定义的，但是该定义显示出了一些会影响可用性的性质。在大多数情况下， date、time、timestamp without time zone和timestamp with time zone的组合就应该能提供任何应用所需的全范围的日期/时间功能。
类型abstime和reltime是低精度类型，它们被用于系统内部。 我们不鼓励你在应用里面使用这些类型，这些内部类型可能会在未来的版本里消失。
5.1. 日期/时间输入
日期和时间的输入可以接受几乎任何合理的格式，包括 ISO 8601、SQL-兼容的、传统POSTGRES的和其他的形式。 对于一些格式，日期输入里的日、月和年的顺序会让人混淆， 并且支持指定所预期的这些域的顺序。把DateStyle参数设置为MDY，就是选择“月－日－年”的解释，设置为DMY就是 “日－月－年”，而YMD是 “年－月－日”。
PostgreSQL在处理日期/时间输入上比SQL标准要求的更灵活。
请记住任何日期或者时间的文字输入需要由单引号包围，就象一个文本字符串一样。SQL要求下面的语法
type [ (p) ] 'value'
其中p是一个可选的精度声明，它给出了在秒域中的小数位数目。精度可以被指定给time、timestamp和interval类型。这允许前文所述的值。如果在一个常数声明中没有指定任何精度，它将默认取文字值的精度。
5.1.1. 日期
Table 8-10显示了date类型可能的输入方式。
Table 8-10. 日期输入
当日时间类型是time [ (p) ] without time zone和time [ (p) ] with time zone。 只写time等效于time without time zone。
这些类型的有效输入由当日时间后面跟着可选的时区组成（参阅Table 8-11和Table 8-12）。 如果在time without time zone的输入中指定了时区，那么它会被无声地忽略。你也可以指定一个日期但是它会被忽略，除非你使用了一个涉及到夏令时规则的时区，例如America/New_York。在这种情况下，为了判断是应用了标准时间还是夏令时时间，要求指定该日期。适当的时区偏移被记录在time with time zone值中。
Table 8-11. 时间输入
1.3. 时间戳
时间戳类型的有效输入由一个日期和时间的串接组成，后面跟着一个可选的时区，一个可选的AD或者BC（另外，AD/BC可以出现在时区前面，但这个顺序并非最佳）。 因此：
1999-01-08 04:05:06
和：
1999-01-08 04:05:06 -8:00
都是有效的值，它遵循ISO 8601 标准。另外，使用广泛的格式：
January 8 04:05:06 1999 PST
也被支持。
SQL标准通过"+"或者"-"符号的存在以及时间后面的时区偏移来区分timestamp without time zone和timestamp with time zone文字。因此，根据标准，
TIMESTAMP '2004-10-19 10:23:54'
是一个timestamp without time zone， 而
TIMESTAMP '2004-10-19 10:23:54+02'
是一个timestamp with time zone。PostgreSQL从来不会在确定文字串的类型之前检查其内容，因此会把上面两个都看做是 timestamp without time zone。因此要保证把上面的文字当作timestamp with time zone看待， 就要给它正确的显式类型：
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
如果一个文字已被确定是timestamp without time zone，PostgreSQL将不声不响忽略任何其中指出的时区。 即，结果值是从输入值的日期/时间域衍生出来的，并且没有就时区进行调整。
对于timestamp with time zone，内部存储的值总是 UTC （全球统一时间，以前也叫格林威治时间GMT）。如果一个输入值有明确的时区声明， 那么它将用该时区合适的偏移量转换成 UTC。如果在输入串里没有时区声明， 那么它就被假设是在系统的TimeZone参数里的那个时区，然后使用这个 timezone时区的偏移转换成 UTC。
如果一个timestamp with time zone值被输出，那么它总是从 UTC 转换成当前的timezone时区，并且显示为该时区的本地时间。要看其它时区的时间，要么修改timezone，要么使用AT TIME ZONE构造。
在timestamp without time zone和timestamp with time zone之间的转换通常假设timestamp without time zone值应该以timezone本地时间的形式接受或者写出。为该转换指定一个不同的可以用AT TIME ZONE。
5.1.4. 特殊值
为了方便，PostgreSQL支持一些特殊日期/时间输入值，如Table 8-13所示。这些值中infinity和-infinity被在系统内部以特殊方式表示并且将被原封不动地显示。但是其他的仅仅只是概念上的速写，当被读到的时候会被转换为正常的日期/时间值（特殊地，now及相关串在被读到时立刻被转换到一个指定的时间值）。在作为常量在SQL命令中使用时，所有这些值需要被包括在单引号内。
Table 8-13. 特殊日期/时间输入
下列SQL-兼容的函数可以被用来为相应的数据类型获得当前时间值： CURRENT_DATE、CURRENT_TIME、 CURRENT_TIMESTAMP、LOCALTIME、 LOCALTIMESTAMP。后四种接受一个可选的亚秒精度声明（参见Section 9.9.4）。注意这些是SQL函数并且在数据输入串中不被识别。
5.2. 日期/时间输出
时间/日期类型的输出格式可以设成四种风格之一： ISO 8601、SQL（Ingres）、传统的POSTGRES（Unix的date格式）或 German 。缺省是ISO格式（ISO标准要求使用 ISO 8601 格式。ISO输出格式的名字是历史偶然）。Table 8-14显示了每种输出风格的例子。date和time类型的 输出通常只有日期或时间部分和例子中一致。不过，POSTGRES风格输出的是ISO格式的只有日期的值。
Table 8-14. 日期/时间输出风格
SQL和POSTGRES风格中，如果DMY域顺序被指定，“日”将出现在“月”之前，否则“月”出现在“日”之前。Table 8-15给出了例子。
Table 8-15. 日期顺序习惯
格式化函数to_char（见Section 9.8）也可以作为一个更灵活的方式来格式化日期/时间输出。
5.3. 时区
时区和时区习惯不仅仅受地球几何形状的影响，还受到政治决定的影响。 到了19世纪，全球的时区变得稍微标准化了些，但是还是易于遭受随意的修改，部分是因为夏时制规 则。PostgreSQL使用广泛使用的 IANA (Olson) 时区数据库来得到有关历史时区规则的信息。对于未来的时间，我们假设关于一个给定时区的最新已知 规则将会一直持续到无穷远的未来。
PostgreSQL努力在典型使用中与SQL标准的定义相兼容。但SQL标准在日期和时间类型和功能上有一些奇怪的混淆。两个显而易见的问题是：
- 尽管date类型与时区没有联系，而time类型却可以有。 然而，现实世界的时区只有在与时间和日期都关联时才有意义， 因为偏移（时差）可能因为实行类似夏时制这样的制度而在一年里有所变化。
- 缺省的时区会指定一个到UTC的数字常量偏移（时差）。因此，当跨DST边界做日期/时间算术时， 我们根本不可能适应于夏时制时间。
为了克服这些困难，我们建议在使用时区的时候，使用那些同时包含日期和时间的日期/时间类型。我们不建议使用类型 time with time zone （尽管PostgreSQL出于遗留应用以及与SQL标准兼容性的考虑支持这个类型）。 PostgreSQL假设你用于任何类型的本地时区都只包含日期或时间。
在系统内部，所有时区相关的日期和时间都用UTC存储。它们在被显示给客户端之前会被转换成由TimeZone配置参数指定的本地时间。
PostgreSQL允许你使用三种不同形式指定时区：
- 一个完整的时区名字，例如America/New_York。能被识别的时区名字被列在pg_timezone_names视图中。PostgreSQL用广泛使用的 IANA 时区数据来实现该目的，因此相同的时区名字也可以在很多其他软件中被识别。
- 一个时区缩写，例如PST。这样一种声明仅仅定义了到UTC的一个特定偏移，而不像完整时区名那样指出整套夏令时转换日期规则。能被识别的缩写被列在pg_timezone_abbrevs视图中。你不能将配置参数TimeZone或log_timezone设置成一个时区缩写，但是你可以在日期/时间输入值和AT TIME ZONE操作符中使用时区缩写。
- 除了时区名和缩写，PostgreSQL将接受POSIX-风格的 时区声明，形式为STDoffset或 STDoffsetDST， 其中STD是一个区域缩写、offset是从UTC西 起的以小时计的数字偏移量、DST是一个可选的夏令时区域缩 写（被假定为给定偏移量提前一小时）。例如，如果EST5EDT还不是一 个被识别的区域名，它可以被接受并且可能和美国东海岸时间的功效相同。在这种语法中， 一个时区缩写可以是一个字母的字符串或者由尖括号（<>）包围 的任意字符串。当一个夏令时区域缩写出现时，会假定根据 IANA 时区数据库的 posixrules条目中使用的同一个夏令时转换规则使用它。 在一个标准的PostgreSQL安装中， posixrules和US/Eastern相同， 因此POSIX-风格的时区声明遵循美国的夏令时规则。如果需要，你可以通过替换 posixrules文件来调整这种行为。
简而言之，在缩写和全称之间是有不同的：缩写表示从UTC开始的一个特定偏移量， 而很多全称表示一个本地夏令时规则并且因此具有两种可能的UTC偏移量。例如， 2014-06-04 12:00 America/New_York表示纽约本地时间的中午， 这个特殊的日期是东部夏令时间（UTC-4）。因此2014-06-04 12:00 EDT 指定的是同一个时间点。但是2014-06-04 12:00 EST指定东部标准时间的 中午（UTC-5），不管在那个日期夏令时是否生效。
更要命的是，某些行政区已经使用相同的时区缩写在不同的时间表示不同的 UTC 偏移量。例如， 在莫斯科MSK在某些年份表示 UTC+3 而在另一些年份表示 UTC+4。 PostgreSQL 会根据在指定的日期它们到底表示什么（或者最近表示什么） 来解释这种缩写。但是，正如上面的EST例子所示，这并不是必须和那一天的本地 标准时间相同。
你应该注意到POSIX-风格的时区特性可能导致伪造的输入被接受，因为它没有对区域缩写合理性的检查。例如SET TIMEZONE TO FOOBAR0将会正常工作，让系统实际使用一个相当奇怪的UTC缩写。另一个需要记住的问题是在POSIX时区名中，正值的偏移量被用于格林威治以西的位置。在其他情况下，PostgreSQL将遵循 ISO-8601 惯例，认为正值的时区偏移量是格林威治以东。
在所有情况下，时区名及其缩写都是大小写不敏感的（这是对PostgreSQL 8.2之前版本的一个修改，在这些版本中某些环境下时区名是大小写敏感的而在另外一些环境中却是大小写不敏感的）。
时区名和缩写都不是硬写在服务器中的，它们是从存储在安装目录下的.../share/timezone/和.../share/timezonesets/子目录中获取的。
TimeZone配置参数可以在文件postgresql.conf中被设置，或者使用Chapter 19中描述的任何一种标准方法设置。同时也有一些特殊的方法来设置它：
- SQL命令SET TIME ZONE为会话设置时区。它是SET TIMEZONE TO的另一种拼写，它更加符合SQL的语法。
- libpq客户端使用PGTZ环境变量来通过连接发送一个SET TIME ZONE命令给服务器。
5.4. 间隔输入
interval值可以使用下列语法书写：
[@] quantity unit [quantity unit...] [direction]
其中quantity是一个数字（很可能是有符号的）； unit是毫秒、 millisecond、second、 minute、hour、day、 week、month、year、 decade、century、millennium 或者缩写或者这些单位的复数； direction可以是ago或者为空。At符号（@）是一个可选的噪声。不同单位的数量通过合适的符号计数被隐式地添加。ago对所有域求反。如果IntervalStyle被设置为postgres_verbose，该语法也被用于间隔输出。
日、小时、分钟和秒的数量可以不适用显式的单位标记指定。例如，'1 12:59:10'被读作'1 day 12 hours 59 min 10 sec'。同样，一个年和月的组合可以使用一个横线指定，例如'200-10'被读作'200年10个月'（这些较短的形式事实上是SQL标准唯一许可的形式，并且在IntervalStyle被设置为sql_standard时用于输出）。
间隔值也可以被写成 ISO 8601 时间间隔，使用该标准4.4.3.2小节的"带标志符的格式"或者4.4.3.3小节的"替代格式"。带标志符的格式看起来像这样：
P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]
该串必须以一个P开始，并且可以包括一个引入当日时间单位的T。可用的单位缩写在Table 8-16中给出。单位可以被忽略，并且可以以任何顺序指定，但是小于一天的单位必须出现在T之后。特别地，M的含义取决于它出现在T之前还是之后。
Table 8-16. ISO 8601 间隔单位缩写
P [ years-months-days ] [ T hours:minutes:seconds ]
串必须以P开始，并且一个T分隔间隔的日期和时间部分。其值按照类似于 ISO 8601日期的数字给出。
在用一个域声明书写一个间隔常量时，或者为一个用域声明定义的间隔列赋予一个串时，对于为标记的量的解释依赖于域。例如INTERVAL '1' YEAR被解读成1年，而INTERVAL '1'表示1秒。同样，域声明允许的最后一个有效域"右边"的域值会被无声地丢弃掉。例如书写INTERVAL '1 day 2:03:04' HOUR TO MINUTE将会导致丢弃秒域，而不是日域。
根据SQL标准，一个间隔值的所有域都必须由相同的符号，这样一个领头的负号将会应用到所有域；例如在间隔文字'-1 2:03:04'中的负号会被应用于日、小时、分钟和秒部分。PostgreSQL允许域具有不同的符号，并且在习惯上认为以文本表示的每个域具有独立的符号，因此在这个例子中小时、分钟和秒部分被认为是正值。如果IntervalStyle被设置为sql_standard，则一个领头的符号将被认为是应用于所有域（但是仅当没有额外符号出现）。否则将使用传统的PostgreSQL解释。为了避免混淆，我们推荐在任何域为负值时为每一个域都附加一个显式的符号。
内部的interval值被存储为月、日和秒。这是因为一个月中的天数是变化的，并且在涉及到夏令时调整时一天可以有23或25小时。月和日域是整数，而秒域可以存储分数。因为间隔通常都是从常数字符串或timestamp减法创建而来，这种存储方法在大部分情况都工作良好。函数justify_days和justify_hours可用于调整超过其常见范围的日数和小时数。
在冗长的输入格式中，以及在更紧凑输入格式的某些域中，域值可以有分数部分；例如'1.5 week'或'01:02:03.45'。这样的输入被转换为合适的月数、日数和秒数用于存储。当这样会导致月和日中的分数时，分数被加到低序域中，使用的转换因子是1月=30日和1日=24小时。例如，'1.5 month'会变成1月和15日。只有秒总是在输出时被显示为分数。
Table 8-17展示了一些有效interval输入的例子。
Table 8-17. 间隔输入
间隔类型的输出格式可以被设置为四种风格之一：sql_standard、postgres、postgres_verbose或iso_8601，设置方法使用SET intervalstyle命令。默认值为postgres格式。Table 8-18展示了每种输出风格的例子。
如果间隔值符合SQL标准的限制（仅年-月或仅日-时间，没有正负值部分的混合），sql_standard风格为间隔文字串产生符合SQL标准规范的输出。否则输出将看起来像一个标准的年-月文字串跟着一个日-时间文字串，并且带有显式添加的符号以区分混合符号的间隔。
当DateStyle参数被设置为ISO时，postgres风格的输出匹配PostgreSQL 8.4版本以前的输出。
当DateStyle参数被设置为非ISO输出时，postgres_verbose风格的输出匹配PostgreSQL 8.4版本以前的输出。
iso_8601风格的输出匹配在ISO 8601标准的4.4.3.2小节中描述的"带标志符的格式"。
Table 8-18. 间隔输出风格例子
6. 布尔类型
PostgreSQL提供标准的SQL类型boolean，参见Table 8-19。boolean可以有多个状态："true（真）"、"false（假）"和第三种状态"unknown（未知）"，未知状态由SQL空值表示。
Table 8-19. 布尔数据类型
"真"状态的有效文字值是：
TRUE't''true''y''yes''on''1'
而对于"假"状态，你可以使用下面这些值：
FALSE'f''false''n''no''off''0'
前导或者末尾的空白将被忽略，并且大小写也无关紧要。使用TRUE和FALSE这样的关键词比较好（SQL兼容）。
Example 8-2显示了使用字母t和f输出boolean值的例子。Example 8-2. 使用boolean类型
CREATE TABLE test1 (a boolean, b text);INSERT INTO test1 VALUES (TRUE, 'sic est');INSERT INTO test1 VALUES (FALSE, 'non est');SELECT * FROM test1; a | b---+--------- t | sic est f | non estSELECT * FROM test1 WHERE a; a | b---+--------- t | sic est
7. 枚举类型
枚举（enum）类型是由一个静态、值的有序集合构成的数据类型。它们等效于很多编程语言所支持的enum类型。枚举类型的一个例子可以是一周中的日期，或者一个数据的状态值集合。
7.1. 枚举类型的声明
枚举类型可以使用CREATE TYPE命令创建，例如：
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');一旦被创建，枚举类型可以像很多其他类型一样在表和函数定义中使用：CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');CREATE TABLE person ( name text, current_mood mood);INSERT INTO person VALUES ('Moe', 'happy');SELECT * FROM person WHERE current_mood = 'happy'; name | current_mood------+-------------- Moe | happy(1 row) 7.2. 排序
一个枚举类型的值的排序是该类型被创建时所列出的值的顺序。枚举类型的所有标准的比较操作符以及相关聚集函数都被支持。例如：
INSERT INTO person VALUES ('Larry', 'sad');INSERT INTO person VALUES ('Curly', 'ok');SELECT * FROM person WHERE current_mood > 'sad'; name | current_mood-------+-------------- Moe | happy Curly | ok(2 rows)SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood; name | current_mood-------+-------------- Curly | ok Moe | happy(2 rows)SELECT nameFROM personWHERE current_mood = (SELECT MIN(current_mood) FROM person); name------- Larry(1 row) 7.3. 类型安全性
每一种枚举数据类型都是独立的并且不能和其他枚举类型相比较。看这样一个例子：
CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');CREATE TABLE holidays ( num_weeks integer, happiness happiness);INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');ERROR: invalid input value for enum happiness: "sad"SELECT person.name, holidays.num_weeks FROM person, holidays WHERE person.current_mood = holidays.happiness;ERROR: operator does not exist: mood = happiness 如果你确实需要做这样的事情，你可以写一个自定义的操作符或者在查询中加上显式造型：
SELECT person.name, holidays.num_weeks FROM person, holidays WHERE person.current_mood::text = holidays.happiness::text; name | num_weeks------+----------- Moe | 4(1 row)
7.4. 实现细节
一个枚举值在磁盘上占据4个字节。一个枚举值的文本标签的长度受限于NAMEDATALEN设置，该设置被编译在PostgreSQL中，在标准编译下它表示最多63字节。
枚举标签是大小写敏感的，因此'happy'是不同于'HAPPY'的。标签内的空白也是有效的。
从内部枚举值到文本标签的翻译被保存在系统目录pg_enum中。可以直接查询该目录。
8. 几何类型
几何数据类型表示二维的空间物体。Table 8-20展示了PostgreSQL中可以用的几何类型。
Table 8-20. 几何类型
我们有一系列丰富的函数和操作符可用来进行各种几何操作， 如缩放、平移、旋转和计算相交等 。
8.1. 点
点是几何类型的基本二维构造块。用下面的语法描述point类型的值：
( x , y ) x , y
其中x和y分别是坐标，都是浮点数。
点使用第一种语法输出。
8.2. 线
线由线性方程Ax + By + C = 0 表示，其中A和B都不为零。类型line 的值采用以下形式输入和输出：
{ A, B, C } 另外，还可以用下列任一形式输入：
[ ( x1 , y1 ) , ( x2 , y2 ) ]( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
其中 (x1,y1) 和 (x2,y2) 是线上不同的两点。
8.3. 线段
线段用一对线段的端点来表示。lseg类型的值用下面的语法声明：
[ ( x1 , y1 ) , ( x2 , y2 ) ]( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
其中(x1,y1) 和 (x2,y2) 是线段的端点。
线段使用第一种语法输出。
8.4. 方框
方框用其对角的点对表示。box类型的值使用下面的语法指定：
( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
其中(x1,y1) 和 (x2,y2) 是方框的对角点。
方框使用第二种语法输出。
在输入时可以提供任意两个对角，但是值将根据需要被按顺序记录为右上角和左下角。
8.5. 路径
路径由一系列连接的点组成。路径可能是开放的，也就是认为列表中第一个点和最后一个点没有被连接起来；也可能是封闭的，这时认为第一个和最后一个点被连接起来。
path类型的值用下面的语法声明：
[ ( x1 , y1 ) , ... , ( xn , yn ) ]( ( x1 , y1 ) , ... , ( xn , yn ) ) ( x1 , y1 ) , ... , ( xn , yn ) ( x1 , y1 , ... , xn , yn ) x1 , y1 , ... , xn , yn
其中的点是组成路径的线段的端点。方括弧（[]）表示一个开放的路径，圆括弧（()）表示一个封闭的路径。如第三种到第五种语法所示，当最外面的圆括号被忽略时，路径将被假定为封闭。
路径的输出使用第一种或第二种语法。
8.6. 多边形
多边形由一系列点代表（多边形的顶点）。多边形和封闭路径很像，但是存储方式不一样而且有自己的一套支持例程。
polygon类型的值用下列语法声明：
( ( x1 , y1 ) , ... , ( xn , yn ) ) ( x1 , y1 ) , ... , ( xn , yn ) ( x1 , y1 , ... , xn , yn ) x1 , y1 , ... , xn , yn
其中的点是组成多边形边界的线段的端点。
多边形的输出使用第一种语法。
8.7. 圆
圆由一个圆心和一个半径代表。circle类型的值用下面的语法指定：
< ( x , y ) , r >( ( x , y ) , r ) ( x , y ) , r x , y , r
其中(x,y)是圆心，而r是圆的半径。
圆的输出用第一种语法。
9. 网络地址类型
PostgreSQL提供用于存储 IPv4、IPv6 和 MAC 地址的数据类型，如Table 8-21所示。 用这些数据类型存储网络地址比用纯文本类型好，因为这些类型提供输入错误检查以及特殊的操作符和函数.
Table 8-21. 网络地址类型
在对inet或者cidr数据类型进行排序的时候， IPv4 地址将总是排在 IPv6 地址前面，包括那些封装或者是映射在 IPv6 地址里 的 IPv4 地址，例如 ::10.2.3.4 或者 ::ffff::10.4.3.2。 9.1. inet
inet在一个数据域里保存一个 IPv4 或 IPv6 主机地址，以及一个可选的它的子网。 子网由主机地址中表示的网络地址位数表示（"网络掩码"）。 如果网络掩码为 32 并且地址是 IPv4 ，那么该值不表示任何子网，只是一台主机。在 IPv6 中地址长度是 128 位，因此 128 位指定一个唯一的主机地址。 请注意如果你想只接受网络地址，你应该使用cidr类型而不是inet。
该类型的输入格式是地址/y，其中地址是一个 IPv4 或者 IPv6 地址，y是网络掩码的位数。如果/y部分缺失， 则网络掩码对 IPv4 而言是 32，对 IPv6 而言是 128，所以该值表示只有一台主机。在显示时，如果/y部分指定一个单台主机，它将不会被显示出来。
9.2. cidr
cidr类型保存一个 IPv4 或 IPv6 网络地址声明。其输入和输出遵循无类的互联网域路由（Classless Internet Domain Routing）习惯。声明一个网络的格式是地址/y，其中address是 IPv4 或 IPv6 网络地址而y是网络掩码的位数。如果省略y， 那么掩码部分用旧的有类的网络编号系统进行计算，否则它将至少大到足以包括写在输入中的所有字节。声明一个在其指定的掩码右边置了位的网络地址会导致错误。
Table 8-22展示了一些例子。
Table 8-22. cidr类型输入例子
9.3. inet vs. cidr inet和cidr类型之间的本质区别是inet接受右边有非零位的网络掩码， 而cidr不接受。
Tip: 如果你不喜欢inet或cidr值的输出格式，可以尝试函数host、text和abbrev。
9.4. macaddr
macaddr类型存储 MAC 地址，也就是以太网卡硬件地址 （尽管 MAC 地址还用于其它用途）。可以接受下列格式的输入：
'08:00:2b:01:02:03''08-00-2b-01-02-03''08002b:010203''08002b-010203''0800.2b01.0203''0800-2b01-0203''08002b010203'
这些例子指定的都是同一个地址。对于位a到f，大小写都可以接受。输出总是使用展示的第一种形式。
IEEE Std 802-2001 指定第二种展示的形式（带有连字符）作为MAC地址的标准形式，并且指定第一种形式（带有分号）作为位翻转的记号，因此 08-00-2b-01-02-03 = 01:00:4D:08:04:0C。这种习惯目前已经被广泛地忽略，并且它只与废弃的网络协议（如令牌环）相关。PostgreSQL 没有对位翻转做任何规定，并且所有可接受的格式都使用标准的LSB顺序。剩下的五种输入格式不属于任何标准。
10. 位串类型
位串就是一串 1 和 0 的串。它们可以用于存储和可视化位掩码。我们有两种类型的 SQL 位类型：bit(n)和bit varying(n)，其中 n是一个正整数。
bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的位串都是错误的。bit varying数据是最长n的变长类型，更长的串会被拒绝。写一个没有长度的bit等效于 bit(1)，没有长度的bit varying意味着没有长度限制。
Note:
如果我们显式地把一个位串值转换成bit(n)， 那么它的右边将被截断或者在右边补齐零，直到刚好n位， 而且不会抛出任何错误。类似地，如果我们显式地把一个位串数值转换成bit varying(n)，如果它超过了n位， 那么它的右边将被截断。请参考Section 4.1.2.5获取有关位串常量的语法的信息。还有一些位逻辑操作符和串操作函数可用。
Example 8-3. 使用位串类型
CREATE TABLE test (a BIT(3), b BIT VARYING(5));INSERT INTO test VALUES (B'101', B'00');INSERT INTO test VALUES (B'10', B'101');ERROR: bit string length 2 does not match type bit(3)INSERT INTO test VALUES (B'10'::bit(3), B'101');SELECT * FROM test; a | b-----+----- 101 | 00 100 | 101
一个位串值对于每8位的组需要一个字节，外加总共5个或8个字节，这取决于串的长度（但是长值可能被压缩或者移到线外）。
11. 文本搜索类型
PostgreSQL提供两种数据类型，它们被设计用来支持全文搜索，全文搜索是一种在自然语言的文档集合中搜索以定位那些最匹配一个查询的文档的活动。tsvector类型表示一个为文本搜索优化的形式下的文档，tsquery类型表示一个文本查询。
11.1. tsvector
一个tsvector值是一个排序的可区分词位的列表，词位是被正规化合并了同一个词的不同变种的词。排序和去重是在输入期间自动完成的，如下例所示：
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector; tsvector---------------------------------------------------- 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
要表示包含空白或标点的词位，将它们用引号包围：
SELECT $$the lexeme ' ' contains spaces$$::tsvector; tsvector------------------------------------------- ' ' 'contains' 'lexeme' 'spaces' 'the'
（我们在这个例子中使用美元符号包围的串文字并且下一个用来避免在文字中包含双引号记号产生的混淆）。嵌入的引号和反斜线必须被双写：
SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector; tsvector------------------------------------------------ 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'
可选的，整数位置可以被附加给词位：
SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector; tsvector------------------------------------------------------------------------------- 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4
一个位置通常表示源词在文档中的定位。位置信息可以被用于邻近排名。位置值可以从 1 到 16383，更大的数字会被 16383。对于相同的词位出现的重复位置将被丢弃。
具有位置的词位可以进一步地被标注一个权重，它可以是A、 B、C或D。 D是默认值并且因此在输出中不会显示：
SELECT 'a:1A fat:2B,4C cat:5D'::tsvector; tsvector---------------------------- 'a':1A 'cat':5 'fat':2B,4C
权重通常被用来反映文档结构，例如将主题词标记成与正文词不同。文本搜索排名函数可以为不同的权重标记器分配不同的优先级。
了解tsvector类型本身并不执行任何词正规化这一点很重要，它假定给它的词已经被恰当地为应用正规化过。例如，
SELECT 'The Fat Rats'::tsvector; tsvector-------------------- 'Fat' 'Rats' 'The'
对于大部分英语文本搜索应用，上面的词将会被认为是非正规化的，但是tsvector并不在乎这一点。原始文档文本通常应该经过to_tsvector以恰当地为搜索正规化其中的词：
SELECT to_tsvector('english', 'The Fat Rats'); to_tsvector----------------- 'fat':2 'rat':3 11.2. tsquery
一个tsquery值存储要用于搜索的词位，并且使用布尔操作符&（AND）、|（OR）和!（NOT）来组合它们，还有短语搜索操作符<->（FOLLOWED BY）。也有一种 FOLLOWED BY 操作符的变体<N>，其中N是一个整数常量，它指定要搜索的两个词位之间的距离。<->等效于<1>。
圆括号可以被用来强制对操作符分组。如果没有圆括号，!（NOT）的优先级最高，其次是< ->（FOLLOWED BY），然后是&（AND），最后是|（OR）。
这里有一些例子：
SELECT 'fat & rat'::tsquery; tsquery--------------- 'fat' & 'rat'SELECT 'fat & (rat | cat)'::tsquery; tsquery--------------------------- 'fat' & ( 'rat' | 'cat' )SELECT 'fat & rat & ! cat'::tsquery; tsquery------------------------ 'fat' & 'rat' & !'cat' SELECT '(fat | rat) <-> cat'::tsquery; tsquery----------------------------------- 'fat' <-> 'cat' | 'rat' <-> 'cat'
最后一个例子说明tsquery有时候会把嵌套的操作符重新组织成一个逻辑上等价的公式。
可选地，一个tsquery中的词位可以被标注一个或多个权重字母，这将限制它们只能和具有那些权重之一的tsvector词位相匹配：
SELECT 'fat:ab & cat'::tsquery; tsquery------------------ 'fat':AB & 'cat'此外，一个tsquery中的词位可以被标注为*来指定前缀匹配：SELECT 'super:*'::tsquery; tsquery----------- 'super':*
这个查询将匹配一个tsvector中以"super"开头的任意词。
词位的引号规则和之前描述的tsvector中的词位相同；并且，正如tsvector，任何请求的词正规化必须在转换到tsquery类型之前完成。to_tsquery函数可以方便地执行这种正规化：
SELECT to_tsquery('Fat:ab & Cats'); to_tsquery------------------ 'fat':AB & 'cat'注意to_tsquery将会以和其他词同样的方式处理前缀，这也意味着下面的比较会返回真：SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' ); ?column?---------- t 因为postgres会被处理成postgr：
SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' ); to_tsvector | to_tsquery---------------+------------ 'postgradu':1 | 'postgr':*
这会匹配postgraduate被处理后的形式。
12. UUID类型
数据类型uuid存储由RFC 4122、ISO/IEC 9834-8:2005以及相关标准定义的通用唯一标识符（UUID）（某些系统将这种数据类型引用为全局唯一标识符GUID）。这种标识符是一个128位的量，它由一个精心选择的算法产生，该算法能保证在已知空间中任何其他使用相同算法的人能够产生同一个标识符的可能性非常非常小。因此，对于分布式系统，这些标识符相比序列生成器而言提供了一种很好的唯一性保障，序列生成器只能在一个数据库中保证唯一。
一个UUID被写成一个小写十六进制位的序列，该序列被连字符分隔成多个组：首先是一个8位组，接下来是三个4位组，最后是一个12位组。总共的32位（十六进制位）表示了128个二进制位。一个标准形式的UUID类似于：
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
PostgreSQL也接受另一种输入形式： 使用大写位、标准格式被花括号包围、忽略某些或者全部连字符、在任意4位组后面增加一个连字符。例如：
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}a0eebc999c0b4ef8bb6d6bb9bd380a11a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11} 输出总是采用标准形式。
PostgreSQL为UUID提供了存储和比较函数，但是核心数据库不包含任何用于产生UUID的函数，因为没有一个单一算法能够很好地适应每一个应用。uuid-ossp模块提供了实现一些标准算法的函数。 pgcrypto模块也为随机 UUID 提供了一个生成函数。 此外，UUID可以由客户端应用产生，或者由通过服务器端函数调用的其他库生成。
13. XML类型
xml数据类型可以被用来存储XML数据。它比直接在一个text域中存储XML数据的优势在于，它会检查输入值的结构是不是良好，并且有支持函数用于在其上执行类型安全的操作，参见Section 9.14。使用这种数据类型要求在安装时用configure --with-libxml选项编译。
xml类型可以存储结构良好（如XML标准所定义）的"文档"，以及"内容"片段，它们由XML标准中的XMLDecl? content产品所定义。粗略地看，这意味着内容片段中可以有多于一个的顶层元素或字符节点。表达式xmlvalue IS DOCUMENT可以被用来评估一个特定的xml值是一个完整文档或者仅仅是一个文档片段。
13.1. 创建XML值
要从字符数据中生成一个xml类型的值，可以使用函数xmlparse:
XMLPARSE ( { DOCUMENT | CONTENT } value)
例子：XMLPARSE (DOCUMENT '')XMLPARSE (CONTENT 'abc Manual ... bar foo ')
然而根据SQL标准这是唯一将字符串转换为XML值的方法，PostgreSQL特有的语法：
xml 'bar ''bar '::xml
也可以被使用。
即便输入值指定了一个文档类型声明（DTD），xml类型也不根据DTD来验证输入值。目前也没有内建的支持用于根据其他XML模式语言（如XML模式）来进行验证。
作为一个逆操作，从xml产生一个字符串可以使用函数xmlserialize:
XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type ) type可以是 character、character varying或 text（或者其中之一的一个别名）。再次地，根据SQL标准，这也是在xml类型和字符类型间做转换的唯一方法，但是PostgreSQL也允许你简单地造型这些值。
当一个字符串不是使用XMLPARSE造型成xml或者不是使用XMLSERIALIZE从xml造型得到，对于DOCUMENT和CONTENT两者的选择是根据"XML option" 会话配置参数决定的，它可以使用标准命令来设置：
SET XML OPTION { DOCUMENT | CONTENT }; 或者是更具有PostgreSQL风格的语法
SET xmloption TO { DOCUMENT | CONTENT }; 默认值是CONTENT，因此所有形式的XML数据都被允许。
Note:
在使用默认XML option设置时，如果字符串包含一个文档类型声明，你就不能直接将字符串造型成类型xml，因为XML内容片断的定义不接受它们。如果你需要这样做，要么使用XMLPARSE，要么修改XML option。13.2. 编码处理
在客户端、服务器以及其中流过的XML数据上处理多字符编码时必须要注意。在使用文本模式向服务器传递查询以及向客户端传递查询结果（在普通模式）时，PostgreSQL将所有在客户端和服务器之间传递的字符数据转换为目标端的字符编码。这也包括了表示XML值的串，正如上面的例子所述。这也通常意味着由于字符数据会在客户端和服务器之间传递时被转换成其他编码，包含在XML数据中的编码声明可能是无效的，因为内嵌的编码声明没有被改变。为了处理这种行为，包含在表示xml类型输入的字符串中包含的编码声明会被忽略，并且其内容被假定为当前服务器的编码。接着，为了正确处理，XML数据的字符串必须以当前客户端编码从客户端发出。客户端负责在把文档发送给服务器之前将它们转换为当前客户端编码，或者适当地调整客户端编码。在输出时，xml类型的值将不会有一个编码声明，并且客户端将会假设所有数据都是当前客户端编码。
在使用二进制模式传送查询参数给服务器以及传回查询结果给客户端时，不会执行编码转换，因此情况就有所不同。在这种情况下，XML数据中的编码声明将被注意到，并且如果缺少编码声明时该数据会被假定为UTF-8（由于XML标准的要求，注意PostgreSQL不支持UTF-16）。在输出时，数据将会有一个编码声明来指定客户端编码，除非客户端编码为UTF-8（这种情况下编码声明会被忽略）。
不用说，在PostgreSQL中处理XML数据产生错误的可能性更小，并且在XML数据编码、客户端编码和服务器编码三者相同时效率更高。因为XML数据在内部是以UTF-8处理的，如果服务器编码也是UTF-8时，计算效率将会最高。
Caution
当服务器编码不是UTF-8时，某些XML相关的函数可能在非ASCII数据上完全无法工作。尤其在xpath()上，这是一个已知的问题。
13.3. 访问XML值
xml数据类型有些不同寻常，因为它不提供任何比较操作符。这是因为对于XML数据不存在良定义的和通用的比较算法。这种状况造成的后果就是，你无法通过比较一个xml和一个搜索值来检索行。XML值因此通常应该伴随着一个独立键值域，如一个ID。另一种比较XML值的方案是将它们先转换为字符串，但注意字符串比较对于XML比较方法没有什么帮助。
由于没有可以用于xml数据类型的比较操作符，因此无法直接在这种类型上创建索引。如果需要在XML中快速的搜索，可能的解决方案包括将表达式造型为一个字符串类型然后索引之，或者在一个XPath表达式上索引。当然，实际的查询必须被调整为使用被索引的表达式。
PostgreSQL中的文本搜索功能也可以被用来加速XML数据的全文搜索。但是，所需的预处理支持目前在PostgreSQL发布中还不可用。
14. JSON 类型
根据RFC 7159 中的说明，JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函 数和操作符可以用于存储在这些数据类型中的数据，见 Section 9.15。
有两种 JSON 数据类型：json 和 jsonb。它们 几乎接受完全相同的值集合作为输入。主要的实际区别之一是 效率。json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb在处理时要快很多，因为不需要解析。jsonb也支 持索引，这也是一个令人瞩目的优势。
由于json类型存储的是输入文本的准确拷贝，其中可能会保留在语法 上不明显的、存在于记号之间的空格，还有 JSON 对象内部的键的顺序。还有， 如果一个值中的 JSON 对象包含同一个键超过一次，所有的键/值对都会被保留（ 处理函数会把最后的值当作有效值）。相反，jsonb不保留空格、不 保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键，只有 最后一个值会被保留。
通常，除非有特别特殊的需要（例如遗留的对象键顺序假设），大多数应用应该 更愿意把 JSON 数据存储为jsonb。
PostgreSQL对每个数据库只允许一种 字符集编码。因此 JSON 类型不可能严格遵守 JSON 规范，除非数据库编码 是 UTF8。尝试直接包括数据库编码中无法表示的字符将会失败。反过来，能 在数据库编码中表示但是不在 UTF8 中的字符是被允许的。
RFC 7159 允许 JSON 字符串包含uXXXX 所标记的 Unicode 转义序列。在json类型的输入函数中，不管数据库 编码如何都允许 Unicode 转义，并且只检查语法正确性（即，跟在u 后面的四个十六进制位）。但是，jsonb的输入函数更加严格：它不允 许非 ASCII 字符的 Unicode 转义（高于U+007F的那些），除非数据 库编码是 UTF8。jsonb类型也拒绝u0000（因为 PostgreSQL的text类型无法表示 它），并且它坚持使用 Unicode 代理对来标记位于 Unicode 基本多语言平面之外 的字符是正确的。合法的 Unicode 转义会被转换成等价的 ASCII 或 UTF8 字符进 行存储，这包括把代理对折叠成一个单一字符。
Note:
很多Section 9.15中描述的 JSON 处理函数将把 Unicode 转义转换成常规字符，并且将因此抛出和刚才所描述的同样类型的错误（即使它们 的输入是类型json而不是jsonb）。json的 输入函数不做这些检查是由来已久的，不过它确实允许将 JSON Unicode 转义简单 的（不经处理）存储在一个非 UTF8 数据库编码中。通常，最好尽可能避免在一个非 UTF8 数据库编码的 JSON 中混入 Unicode 转义。在把文本 JSON 输入转换成jsonb时，RFC 7159描述 的基本类型会被有效地映射到原生的 PostgreSQL类型（如 Table 8-23中所示）。因此，在合法 jsonb数据的组成上有一些次要额外约束，它们不适合 json类型和抽象意义上的 JSON，这些约束对应于有关哪些东西不 能被底层数据类型表示的限制。尤其是，jsonb将拒绝位于 PostgreSQL numeric数据类型范 围之外的数字，而json则不会。这类实现定义的限制是 RFC 7159 所允许的。不过，实际上这类问题更可能发生在其他实 现中，因为把 JSON 的number基本类型表示为 IEEE 754 双精度浮点 是很常见的（这也是RFC 7159 明确期待和允许的）。当在这类系 统间使用 JSON 作为一种交换格式时，应该考虑丢失数字精度的风险。
相反地，如表中所述，有一些 JSON 基本类型输入格式上的次要限制并不适用于相 应的PostgreSQL类型。
Table 8-23. JSON 基本类型和相应的PostgreSQL类型
14.1. JSON 输入和输出语法 RFC 7159 中定义了 JSON 数据类型的输入/输出语法。
下列都是合法的json（或者jsonb）表达式：
-- 简单标量/基本值-- 基本值可以是数字、带引号的字符串、true、false或者nullSELECT '5'::json;-- 有零个或者更多元素的数组（元素不需要为同一类型）SELECT '[1, 2, "foo", null]'::json;-- 包含键值对的对象-- 注意对象键必须总是带引号的字符串SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;-- 数组和对象可以被任意嵌套SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json; 如前所述，当一个 JSON 值被输入并且接着不做任何附加处理就输出时， json会输出和输入完全相同的文本，而jsonb 则不会保留语义上没有意义的细节（例如空格）。例如，注意下面的不同：
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json; json ------------------------------------------------- {"bar": "baz", "balance": 7.77, "active":false}(1 row)SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb; jsonb -------------------------------------------------- {"bar": "baz", "active": false, "balance": 7.77}(1 row) 值得一提的一种语义上无意义的细节是，在jsonb中数据会被按照底层 numeric类型的行为来打印。实际上，这意味着用E记号 输入的数字被打印出来时就不会有该记号，例如：
SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb; json | jsonb -----------------------+------------------------- {"reading": 1.230e-5} | {"reading": 0.00001230}(1 row) 不过，如这个例子所示，jsonb将会保留拖尾的小数点后的零，即便这 对于等值检查等目的来说是语义上无意义的。
14.2. 有效地设计 JSON 文档
将数据表示为 JSON 比传统关系数据模型要灵活得多，在需求不固定时 这种优势更加令人感兴趣。在同一个应用里非常有可能有两种方法共存 并且互补。不过，即便是在要求最大灵活性的应用中，我们还是推荐 JSON 文档有固定的结构。该结构通常是非强制的（尽管可能会强制一 些业务规则），但是有一个可预测的结构会使书写概括一个表中的 "文档"（数据）集合的查询更容易。
当被存储在表中时，JSON 数据也像其他数据类型一样服从相同的并发 控制考虑。尽管存储大型文档是可行的，但是要记住任何更新都在整行 上要求一个行级锁。为了在更新事务之间减少锁争夺，可考虑把 JSON 文档限制到一个可管理的尺寸。理想情况下，JSON 文档应该每个表示 一个原子数据，业务规则命令不会进一步把它们划分成更小的可独立修 改的数据。
14.3. jsonb 包含和存在
测试包含是jsonb的一种重要能力。对 json类型没有平行的功能集。包含测试会测试一个 jsonb文档是否被包含在另一个文档中。除了特别注解 之外，这些例子都会返回真：
-- 简单的标量/基本值只包含相同的值：SELECT '"foo"'::jsonb @> '"foo"'::jsonb;-- 右边的数字被包含在左边的数组中：SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;-- 数组元素的顺序没有意义，因此这个例子也返回真：SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;-- 重复的数组元素也没有关系：SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;-- 右边具有一个单一键值对的对象被包含在左边的对象中：SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;-- 右边的数组不会被认为包含在左边的数组中，-- 即使其中嵌入了一个相似的数组：SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- 得到假-- 但是如果同样也有嵌套，包含就成立：SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;-- 类似的，这个例子也不会被认为是包含：SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- 得到假-- 包含一个顶层键和一个空对象：SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb; 一般原则是被包含的对象必须在结构和数据内容上匹配包含对象，这种匹配 可以是从包含对象中丢弃了不匹配的数组元素或者对象键值对之后成立。但 是记住做包含匹配时数组元素的顺序是没有意义的，并且重复的数组元素实 际也只会考虑一次。
结构必须匹配的一般原则有一种特殊情况，一个数组可以包含一个基本值：
-- 这个数组包含基本字符串值：SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;-- 反之不然，下面的例子会报告“不包含”：SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- 得到假
jsonb还有一个存在操作符，它是包含的一种 变体：它测试一个字符串（以一个text值的形式给出）是否出 现在jsonb值顶层的一个对象键或者数组元素中。除非特别注解， 下面这些例子返回真：
-- 字符串作为一个数组元素存在：SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';-- 字符串作为一个对象键存在：SELECT '{"foo": "bar"}'::jsonb ? 'foo';-- 不考虑对象值：SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- 得到假-- 和包含一样，存在必须在顶层匹配：SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- 得到假-- 如果一个字符串匹配一个基本 JSON 字符串，它就被认为存在：SELECT '"foo"'::jsonb ? 'foo'; 当涉及很多键或元素时，JSON 对象比数组更适合于做包含或存在测试， 因为它们不像数组，进行搜索时会进行内部优化，并且不需要被线性搜索。
Tip: 由于 JSON 的包含是嵌套的，因此一个恰当的查询可以跳过对子对象的显式选择。 例如，假设我们在顶层有一个doc列包含着对象，大部分对象 包含着tags域，其中有子对象的数组。这个查询会找到其中出现了 同时包含"term":"paris"和"term":"food"的子对象 的项，而忽略任何位于tags数组之外的这类键：
SELECT doc->'site_name' FROM websites WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}'; 可以用下面的查询完成同样的事情：
SELECT doc->'site_name' FROM websites WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]'; 但是后一种方法灵活性较差，并且常常也效率更低。
在另一方面，JSON 的存在操作符不是嵌套的：它将只在 JSON 值的顶层 查找指定的键或数组元素。
14.4. jsonb 索引
GIN 索引可以被用来有效地搜索在大量jsonb文档（数据）中出现 的键或者键值对。提供了两种 GIN "操作符类"，它们在性能和灵活 性方面做出了不同的平衡。
jsonb的默认 GIN 操作符类支持使用@>、 ?、?&以及?|操作符的查询（这些 操作符实现的详细语义请见Table 9-43）。 使用这种操作符类创建一个索引的例子：
CREATE INDEX idxgin ON api USING gin (jdoc);
非默认的 GIN 操作符类jsonb_path_ops只支持索引 @>操作符。使用这种操作符类创建一个索引的例子：
CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);
考虑这样一个例子：一个表存储了从一个第三方 Web 服务检索到的 JSON 文档，并且有一个模式定义。一个典型的文档：
{ "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", "name": "Angela Barton", "is_active": true, "company": "Magnafone", "address": "178 Howard Place, Gulf, Washington, 702", "registered": "2009-11-07T08:53:22 +08:00", "latitude": 19.793713, "longitude": 86.513373, "tags": [ "enim", "aliquip", "qui" ]} 我们把这些文档存储在一个名为api的表的名为 jdoc的jsonb列中。如果在这个列上创建一个 GIN 索引，下面这样的查询就能利用该索引：
-- 寻找键 "company" 有值 "Magnafone" 的文档SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}'; 不过，该索引不能被用于下面这样的查询，因为尽管操作符? 是可索引的，但它不能直接被应用于被索引列jdoc：
-- 寻找这样的文档：其中的键 "tags" 包含键或数组元素 "qui"SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';
但是，通过适当地使用表达式索引，上述查询也能使用一个索引。 如果对"tags"键中的特定项的查询很常见，可能值得 定义一个这样的索引：
CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));
现在，WHERE 子句 jdoc -> 'tags' ? 'qui' 将被识别为可索引操作符?在索引表达式jdoc -> 'tags' 上的应用。
另一种查询的方法是利用包含，例如：
-- 寻找这样的文档：其中键 "tags" 包含数组元素 "qui"SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}'; jdoc列上的一个简单 GIN 索引就能支持这个查询。 但是注意这样一个索引将会存储jdoc列中每一个键 和值的拷贝，然而前一个例子的表达式索引只存储tags 键下找到的数据。虽然简单索引的方法更加灵活（因为它支持有关任 意键的查询），定向的表达式索引更小并且搜索速度比简单索引更快。
尽管jsonb_path_ops操作符类只支持用 @>操作符的查询，但它比起默认的操作符类 jsonb_ops有更客观的性能优势。一个 jsonb_path_ops索引通常也比一个相同数据上的 jsonb_ops要小得多，并且搜索的专一性更好，特 别是当查询包含频繁出现在该数据中的键时。因此，其上的搜索操作 通常比使用默认操作符类的搜索表现更好。
jsonb_ops和jsonb_path_ops GIN 索引之间的技术区别是前者为数据中的每一个键和值创建独立的索引项， 而后者值为该数据中的每个值创建索引项。 [1] 基本上，每一个jsonb_path_ops索引项是其所对应的值和 键的哈希。例如要索引{"foo": {"bar": "baz"}}，将创建一个 单一的索引项，它把所有三个foo、bar、 和baz合并到哈希值中。因此一个查找这个结构的包含查询可能 导致极度详细的索引搜索。但是根本没有办法找到foo是否作为 一个键出现。在另一方面，一个jsonb_ops会创建三个索引 项分别表示foo、bar和baz。那么要 做同样的包含查询，它将会查找包含所有三个项的行。虽然 GIN 索引能够相当 有效地执行这种 AND 搜索，它仍然不如等效的 jsonb_path_ops搜索那样详细和快速（特别是如果有大量 行包含三个索引项中的任意一个时）。
jsonb_path_ops方法的一个不足是它不会为不包含任何值 的 JSON 结构创建索引项，例如{"a": {}}。如果需要搜索包 含这样一种结构的文档，它将要求一次全索引扫描，那就非常慢。 因此jsonb_path_ops不适合经常执行这类搜索的应用。
jsonb也支持btree和hash索引。 这通常值用于检查完整 JSON 文档等值非常重要的场合。jsonb 数据的btree顺序很少有人关系，但是为了完整性其顺序是：
对象 > 数组 > 布尔 > 数字 > 字符串 > 空值
带有 n 对的对象 > 带有 n - 1 对的对象
带有 n 个元素的数组 > 带有 n - 1 个元素的数组
具有相同数量对的对象这样比较：key-1, value-1, key-2 ...
注意对象键被按照它们的存储顺序进行比较，特别是由于较短的键被存储在 较长的键之前，这可能导致结果不直观，例如：
{ "aa": 1, "c": 1} > {"b": 1, "d": 1} 相似地，具有相同元素数量的数组按照以下顺序比较：
element-1, element-2 ...
基本 JSON 值的比较会使用低层PostgreSQL 数据类型相同的比较规则进行。字符串的比较会使用默认的数据库排序规则。
Notes
[1]
对于这种目的，术语"值"包括数组元素，尽管 JSON 的术语有时 认为数组元素与对象内的值不同。15. 数组
PostgreSQL允许一个表中的列定义为变长多维数组。可以创建任何内建或用户定义的基类、枚举类型或组合类型的数组。 域的数组目前还不支持。
15.1. 数组类型的定义
为了展示数组类型的使用，我们创建这样一个表：
CREATE TABLE sal_emp ( name text, pay_by_quarter integer[], schedule text[][]);
如上所示，一个数组数据类型可以通过在数组元素的数据类型名称后面加上方括号（[]）来命名。上述命令将创建一个名为sal_emp的表，它有一个类型为text的列（name），一个表示雇员的季度工资的一维integer类型数组（pay_by_quarter），以及一个表示雇员每周日程表的二维text类型数组（schedule）。
CREATE TABLE的语法允许指定数组的确切大小，例如：
CREATE TABLE tictactoe ( squares integer[3][3]);
然而，当前的实现忽略任何提供的数组尺寸限制，即其行为与未指定长度的数组相同。
当前的实现也不会强制所声明的维度数。一个特定元素类型的数组全部被当作是相同的类型，而不论其尺寸或维度数。因此，在CREATE TABLE中声明数组的尺寸或维度数仅仅只是文档而已，它并不影响运行时的行为。
另一种符合SQL标准的语法是使用关键词ARRAY，可以用来定义一维数组。pay_by_quarter可以这样定义：
pay_by_quarter integer ARRAY[4],
或者，不指定数组尺寸：
pay_by_quarter integer ARRAY,
但是和前面一样，PostgreSQL在任何情况下都不会强制尺寸限制。
15.2. 数组值输入
要把一个数组值写成一个文字常数，将元素值用花括号包围并用逗号分隔（如果你懂C，这和初始化结构的C语法没什么两样）。在任意元素值周围可以使用双引号，并且在元素值包含逗号或花括号时必须这样做（更多细节如下所示）。因此，一个数组常量的一般格式如下：
'{ val1 delim val2 delim ... }' 这里delim是类型的定界符，记录在类型的pg_type项中。在PostgreSQL发行提供的标准数据类型中，所有的都使用一个逗号（,），除了类型box使用一个分号（;）。每个val可以是数组元素类型的一个常量，也可以是一个子数组。一个数组常量的例子是：
'{ {1,2,3},{4,5,6},{7,8,9}}' 该常量是一个二维的，3乘3数组，它由3个整数子数组构成。
要设置一个数组常量的一个元素为NULL，在该元素值处写NULL（任何NULL的大写或小写变体都有效）。如果你需要一个真正的字符串值"NULL"，你必须在它两边放上双引号。
现在我们可以展示一些INSERT语句：
INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{ {"meeting", "lunch"}, {"training", "presentation"}}');INSERT INTO sal_emp VALUES ('Carol', '{20000, 25000, 25000, 25000}', '{ {"breakfast", "consulting"}, {"meeting", "lunch"}}'); 前两个插入的结果看起来像这样：
SELECT * FROM sal_emp; name | pay_by_quarter | schedule-------+---------------------------+------------------------------------------- Bill | {10000,10000,10000,10000} | { {meeting,lunch},{training,presentation}} Carol | {20000,25000,25000,25000} | { {breakfast,consulting},{meeting,lunch}}(2 rows) 多维数组的每一维都必须有相匹配的长度。不匹配会造成错误，例如：
INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{ {"meeting", "lunch"}, {"meeting"}}');ERROR: multidimensional arrays must have array expressions with matching dimensionsARRAY构造器语法也可以被用于：INSERT INTO sal_emp VALUES ('Bill', ARRAY[10000, 10000, 10000, 10000], ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);INSERT INTO sal_emp VALUES ('Carol', ARRAY[20000, 25000, 25000, 25000], ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]); 注意数组元素是普通SQL常数或表达式，例如，字符串文字使用单引号而不是双引号包围，因为双引号可以出现在一个数组文字中。ARRAY构造器语法的详细讨论请见Section 4.2.12。
15.3. 访问数组
现在，我们可以在该表上运行一些查询。首先，我们展示如何访问一个数组中的一个元素。下面的查询检索在第二季度工资发生变化的雇员的名字：
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name------- Carol(1 row)
数组下标写在方括号内。默认情况下，PostgreSQL为数组使用了一种从1开始的编号习惯，即一个具有n个元素的数组从array[1]开始，结束于array[n]。
下面的查询检索所有员工第三季度的工资：
SELECT pay_by_quarter[3] FROM sal_emp; pay_by_quarter---------------- 10000 25000(2 rows)
我们也可以访问一个数组的任意矩形切片或者子数组。一个数组切片可以通过在一个或多个数组维度上指定下界:上界来定义例如，下面的查询检索Bill在本周头两天日程中的第一项：
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill'; schedule------------------------ { {meeting},{training}}(1 row) 如果任何维度被写成一个切片，即包含一个冒号，那么所有的维度都被看成是切片对待。其中任何只有一个数字（无冒号）的维度被视作是从1到指定的数字。例如，下面例子中的[2]被认为是[1:2]：
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; schedule------------------------------------------- { {meeting,lunch},{training,presentation}}(1 row) 为了避免和非切片情况搞混，最好在所有的维度上都使用切片语法，例如1:2而不是2。
可以省略一个切片说明符的lower-bound或者 upper-bound（亦可两者都省略），缺失的 边界会被数组下标的上下限所替代。例如：
SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill'; schedule------------------------ { {lunch},{presentation}}(1 row)SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill'; schedule------------------------ { {meeting},{training}}(1 row) 如果数组本身为空或者任何一个下标表达式为空，访问数组下标表达式将会返回空值。如果下标超过了数组边界，下标表达式也会返回空值（这种情况不会抛出错误）。例如，如果schedule目前具有的维度是1:3，那么引用schedule3将得到NULL。相似地，使用错误的下标号引用一个数组会得到空值而不是错误。
如果数组本身或者任何一个下标表达式为空，则一个数组切片表达式也会得到空值。但是，在其他情况例如选择一个完全位于当前数组边界之外的切片时，一个切片表达式会得到一个空（零维）数组而不是空值（由于历史原因，这并不符合非切片行为）。 如果所请求的切片和数组边界重叠，那么它会被缩减为重叠的区域而不是返回空。
任何数组值的当前维度可以使用array_dims函数获得：
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; array_dims------------ [1:2][1:2](1 row)
array_dims产生一个text结果，它便于人类阅读但是不便于程序读取。 Dimensions can also be retrieved with 也可以通过array_upper和array_lower来获得维度，它们将分别返回一个指定数组的上界和下界：
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol'; array_upper------------- 2(1 row)
array_length将返回一个指定数组维度的长度：
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol'; array_length-------------- 2(1 row)
cardinality返回一个数组中在所有维度上的元素总数。 这实际上是调用unnest将会得到的行数：
SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol'; cardinality------------- 4(1 row)
15.4. 修改数组
一个数组值可以被整个替换：
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol';或者使用ARRAY表达式语法：UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol';一个数组也可以在一个元素上被更新：UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill';或者在一个切片上被更新：UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' WHERE name = 'Carol'; 也可以使用省略lower-bound或者 upper-bound的切片语法，但是只能用于 更新一个不是 NULL 或者零维的数组值（否则无法替换现有的下标界线）。
一个已存储的数组值可以被通过为其还不存在的元素赋值来扩大之。任何位于之前已存在的元素和新元素之间的位置都将被空值填充。例如，如果数组myarray目前有4个元素，在用一个更新对myarray[6]赋值后它将有6个元素，其中myarray[5]为空值。目前，采用这种方式扩大数组只允许使用在一维数组上。
带下标的赋值方式允许创建下标不是从1开始的数组。例如，我们可以为myarray[-2:7]赋值来创建一个下标值从-2到7的数组。
新的数组值也可以通过串接操作符||构建：
SELECT ARRAY[1,2] || ARRAY[3,4]; ?column?----------- {1,2,3,4}(1 row)SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; ?column?--------------------- { {5,6},{1,2},{3,4}}(1 row) 串接操作符允许把一个单独的元素加入到一个一维数组的开头或末尾。它也能接受两个N维数组，或者一个N维数组和一个N+1维数组。
当一个单独的元素被加入到一个一维数组的开头或末尾时，其结果是一个和数组操作数具有相同下界下标的新数组。例如：
SELECT array_dims(1 || '[0:1]={2,3}'::int[]); array_dims------------ [0:2](1 row)SELECT array_dims(ARRAY[1,2] || 3); array_dims------------ [1:3](1 row) 当两个具有相同维度数的数组被串接时，其结果保留左操作数的外维度的下界下标。结果将是一个数组，它由左操作数的每一个元素以及紧接着的右操作数的每一个元素。例如：
SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]); array_dims------------ [1:5](1 row)SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]); array_dims------------ [1:5][1:2](1 row)
当一个N维数组被放在另一个N+1维数组的前面或者后面时，结果和上面的例子相似。每一个N维子数组实际上是N+1维数组外维度的一个元素。例如：
SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]); array_dims------------ [1:3][1:2](1 row)
一个数组也可以通过使用函数array_prepend、array_append或array_cat构建。前两个函数仅支持一维数组，但array_cat支持多维数组。 一些例子：
SELECT array_prepend(1, ARRAY[2,3]); array_prepend--------------- {1,2,3}(1 row)SELECT array_append(ARRAY[1,2], 3); array_append-------------- {1,2,3}(1 row)SELECT array_cat(ARRAY[1,2], ARRAY[3,4]); array_cat----------- {1,2,3,4}(1 row)SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]); array_cat--------------------- { {1,2},{3,4},{5,6}}(1 row)SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]); array_cat--------------------- { {5,6},{1,2},{3,4}} 在简单的情况中，上面讨论的串接操作符比直接使用这些函数更好。不过，由于 串接操作符需要服务于所有三种情况，所以它的负担比较重，在有些情况下使用 这些函数之一有助于避免混淆。例如：
SELECT ARRAY[1, 2] || '{3, 4}'; -- 没有指定类型的文字被当做一个数组 ?column?----------- {1,2,3,4}SELECT ARRAY[1, 2] || '7'; -- 这个也是ERROR: malformed array literal: "7"SELECT ARRAY[1, 2] || NULL; -- 未修饰的 NULL 也是如此 ?column?---------- {1,2}(1 row)SELECT array_append(ARRAY[1, 2], NULL); -- 这可能才是想要的意思 array_append-------------- {1,2,NULL} 在上面的例子中，解析器看到在串接操作符的一遍看到了一个整数数组，并且在 另一边看到了一个未确定类型的常量。它用来决定该常量类型的启发式规则是假 定它和该操作符的另一个输入具有相同的类型 — 在这种情况中是整数数 组。因此串接操作符表示array_cat而不是 array_append。如果这样做是错误的选择，它可以通过将该常 量造型成数组的元素类型来修复。但是显式地使用array_append 可能是一种最好的方案。
15.5. 在数组中搜索
要在一个数组中搜索一个值，每一个值都必须被检查。这可以手动完成，但是我们必须知道数组的尺寸。例如：
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;
但是这对于大型数组来说太过冗长，且在数组尺寸未知时无法使用。上面的查询可以被替换为：
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
此外，我们还可以查找所有元素值都为10000的数组所在的行：
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
另外，generate_subscripts函数也可以用来完成类似的查找。例如：
SELECT * FROM (SELECT pay_by_quarter, generate_subscripts(pay_by_quarter, 1) AS s FROM sal_emp) AS foo WHERE pay_by_quarter[s] = 10000;
该函数的描述见Table 9-58。
我们也可以使用&&操作符来搜索一个数组，它会检查左操作数是否与右操作数重叠。例如：
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
你也可以使用array_position和array_positions在一个 数组中搜索特定值。前者返回值在数组中第一次出现的位置的下标。后者返回一个数组， 其中有该值在数组中的所有出现位置的下标。例如：
SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon'); array_positions----------------- 2SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1); array_positions----------------- {1,4,8} Tip: 数组不是集合，在其中搜索指定数组元素可能是数据设计失误的表现。考虑使用一个独立的表来替代，其中每一行都对应于一个数组元素。这将更有利于搜索，并且对于大量元素的可扩展性更好。
15.6. 数组输入和输出语法
一个数组值的外部文本表现由根据数组元素类型的I/O转换规则解释的项构成，并在其上加上修饰用于指示数组结构。修饰包括数组值周围的花括号（{和}）以及相邻项之间的定界字符。定界字符通常是一个逗号（,），但是也可能是别的：它由数组元素类型的typdelim设置决定。在PostgreSQL发行版提供的标准数据类型中，除了box类型使用分号（;）之外，其他都是用逗号。在一个多维数组中，每一个维度（行、平面、方体等）都有其自己的花括号层次，且同层的被花括号限定的相邻实体之间也必须有定界符。
如果元素值是空字符串、包含花括号、包含定界字符、包含双引号、包含反斜线、包含空白或者匹配词NULL，数组输出例程将在元素值周围放上双引号。嵌在元素值中的双引号以及反斜线将被反斜线转义。对于数字数据类型可以安全地假设双引号绝不会出现，但是对于文本数据类型我们必须准备好处理可能出现亦可能不出现的引号。
默认情况下，一个数组的一个维度的下界索引值被设置为1。要表示具有其他下界的数组，数组下标的范围应在填充数组内容之前被显式地指定好。这种修饰包括在每个数组维度上下界周围的方括号（[]），以及上下界之间的一个冒号（:）定界符。数组维度修饰后面要跟一个等号（=）。例如：
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2 FROM (SELECT '[1:1][-2:-1][3:5]={ { {1,2,3},{4,5,6}}}'::int[] AS f1) AS ss; e1 | e2----+---- 1 | 6(1 row) 只有当数组的维度中有一个或多个的下界不为1时，数组输出例程才会在结果中包括维度。
如果为一个元素给定的值是NULL（或者是任何变体），该元素将被设置为NULL。任何引号或反斜线的存在将阻止这种行为，而允许为元素值输入"NULL"的字面意思。为了向后兼容PostgreSQL的8.2之前的版本，可将array_nulls配置参数设置为off来阻止将NULL识别为NULL。
如前所示，在写一个数组值时我们可以在任何单独数组元素周围使用引号。如果元素值可能混淆数组值分析器时，我们必须 这样做。例如，包含花括号、逗号（或者数据类型的定界符）、双引号、反斜线或首尾有空白的元素必须使用双引号。空字符串和匹配单词NULL的字符串也必须使用双引号。要把一个双引号或反斜线放在一个使用了双引号的数组元素值中，需要使用转义字符串语法并在其前面使用一个反斜线。作为一种选择，我们可以免去使用引号而使用反斜线转义的方式来保护可能被认为是数组语法的所有数据字符。
我们可以在左括号前面或右括号后面增加空白。我们也可以在任何单独的项之前或之后加上空白。在所有这些情况中空白将被忽略。但是，在被使用了双引号的元素中的空白以及周围有其他非空白字符的空白不会被忽略。
Note:
要记住我们在一个SQL命令中写的东西首先会被解释为一个字面上的字符串，然后再被解释为一个数组。这使得我们需要的反斜线加倍。例如，要插入一个包含一个反斜线和一个双引号的text数组值，我们需要写：INSERT ... VALUES (E'{"\\\\","\\""}'); 转义字符串处理器会移除第一层反斜线，这样到达数组值分析器的值看起来将是{"\","""}。接着，字符串被交给text数据类型的输入例程，从而分别成为和"（如果我们使用的数据类型的输入例程会特殊对待反斜线，例如bytea，我们可能需要在命令中写更多的反斜线来得到存储在数组元素中的一个反斜线）。美元符号引用可以被用来避免使用双反斜线。
Tip: 在SQL命令中写数组值时，ARRAY构造器语法常常比数组文字语法要更容易使用。在ARRAY中，单独的元素值可以使用不属于数组成员时的方式来书写。
16. 组合类型
一个组合类型表示一行或一个记录的结构，它本质上就是一个域名和它们数据类型的列表。PostgreSQL允许把组合类型用在很多能用简单类型的地方。例如，一个表的一列可以被声明为一种组合类型。
16.1. 组合类型的声明
这里有两个定义组合类型的简单例子：
CREATE TYPE complex AS ( r double precision, i double precision);CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric);
该语法堪比CREATE TABLE，不过只能指定域名和类型，当前不能包括约束（例如NOT NULL）。注意AS关键词是必不可少的，如果没有它，系统将认为用户想要的是一种不同类型的CREATE TYPE命令，并且你将得到奇怪的语法错误。
定义了类型之后，我们可以用它们来创建表：
CREATE TABLE on_hand ( item inventory_item, count integer);INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);or functions:CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numericAS 'SELECT $1.price * $2' LANGUAGE SQL;SELECT price_extension(item, 10) FROM on_hand; 只要你创建了一个表，也会自动创建一个组合类型来表示表的行类型，它具有和表一样的名称。例如，如果我们说：
CREATE TABLE inventory_item ( name text, supplier_id integer REFERENCES suppliers, price numeric CHECK (price > 0));
那么和上面所示相同的inventory_item组合类型将成为一种副产品，并且可以按上面所说的进行使用。不过要注意当前实现的一个重要限制：因为没有约束与一个组合类型相关，显示在表定义中的约束不会应用于表外组合类型的值（一种部分的变通方案是用域类型作为组合类型的成员）。
16.2. 组合值输入
要把一个组合值写作一个文字常量，将该域值封闭在圆括号中并且用逗号分隔它们。你可以在任何域值周围放上双引号，并且如果该域值包含逗号或圆括号则必须这样做（更多细节见下文）。这样，一个组合常量的一般格式是下面这样的：
'( val1 , val2 , ... )'
一个例子是：
'("fuzzy dice",42,1.99)' 这将是上文定义的inventory_item类型的一个合法值。要让一个域为 NULL，在列表中它的位置上根本不写字符。例如，这个常量指定其第三个域为 NULL：
'("fuzzy dice",42,)' 如果你写一个空字符串而不是 NULL，写上两个引号：
'("",42,)' 这里第一个域是一个非 NULL 空字符串，第三个是 NULL。
ROW表达式也能被用来构建组合值。在大部分情况下，比起使用字符串语法，这相当简单易用，因为你不必担心多层引用。我们已经在上文用过这种方法：
ROW('fuzzy dice', 42, 1.99)ROW('', 42, NULL) 只要在表达式中有多于一个域，ROW 关键词实际上就是可选的，因此这些可以简化成：
('fuzzy dice', 42, 1.99)('', 42, NULL) Section 4.2.13中更加详细地讨论了ROW表达式语法。
16.3. 访问组合类型
要访问一个组合列的一个域，可以写成一个点和域的名称，更像从一个表名中选择一个域。事实上，它太像从一个表名中选择，这样我们不得不使用圆括号来避免让解析器混淆。例如，你可能尝试从例子表on_hand中选取一些子域：
SELECT item.name FROM on_hand WHERE item.price > 9.99;
这不会有用，因为名称item会被当成是一个表名，而不是on_hand的一个列名。你必须写成这样：
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;
现在加上括号的对象就被正确地解释为对item列的引用，然后可以从中选出子域。
只要你从一个组合值中选择一个域，相似的语法问题就适用。例如，要从一个返回组合值的函数的结果中选取一个域，你需要这样写：
SELECT (my_func(...)).field FROM ...
如果没有额外的圆括号，这将生成一个语法错误。
16.4. 修改组合类型
这里有一些插入和更新组合列的正确语法的例子。首先，插入或者更新一整个列：
INSERT INTO mytab (complex_col) VALUES((1.1,2.2));UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
第一个例子忽略ROW，第二个例子使用它，我们可以用两者之一完成。
我们能够更新一个组合列的单个子域：
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
注意这里我们不需要（事实上也不能）把圆括号放在正好出现在SET之后的列名周围，但是当在等号右边的表达式中引用同一列时确实需要圆括号。
并且我们也可以指定子域作为INSERT的目标：
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
如果我们没有为该列的所有子域提供值，剩下的子域将用空值填充。
16.5. 组合类型输入和输出语法
一个组合值的外部文本表达由根据域类型的 I/O 转换规则解释的项，外加指示组合结构的装饰组成。装饰由整个值周围的圆括号（(和)），外加相邻项之间的逗号（,）组成。圆括号之外的空格会被忽略，但是在圆括号之内空格会被当成域值的一部分，并且根据域数据类型的输入转换规则可能有意义，也可能没有意义。例如，在
'( 42)'
中，如果域类型是整数则空格会被忽略，而如果是文本则空格不会被忽略。如前所示，在写一个组合值时，你可以在任意域值周围写上双引号。如果不这样做会让域值迷惑组合值解析器，你就必须这么做。特别地，包含圆括号、逗号、双引号或反斜线的域必须用双引号引用。要把一个双引号或者反斜线放在一个被引用的组合域值中，需要在它前面放上一个反斜线（还有，一个双引号引用的域值中的一对双引号被认为是表示一个双引号字符，这和 SQL 字符串中单引号的规则类似）。另一种办法是，你可以避免引用以及使用反斜线转义来保护所有可能被当作组合语法的数据字符。
一个全空的域值（在逗号或圆括号之间完全没有字符）表示一个 NULL。要写一个空字符串值而不是 NULL，可以写成""。
如果域值是空串或者包含圆括号、逗号、双引号、反斜线或空格，组合输出例程将在域值周围放上双引号（对空格这样处理并不是不可缺少的，但是可以提高可读性）。嵌入在域值中的双引号及反斜线将被双写。
Note:
记住你在一个 SQL 命令中写的东西将首先被解释为一个字符串，然后才会被解释为一个组合。这就让你所需要的反斜线数量翻倍（假定使用了转义字符串语法）。例如，要在组合值中插入一个含有一个双引号和一个反斜线的text域，你需要写成：INSERT ... VALUES (E'("\\"\\\\")'); 字符串处理器会移除一层反斜线，这样在组合值解析器那里看到的就会是(""\")。接着，字符串被交给text数据类型的输入例程并且变成"（如果我们使用的数据类型的输入例程也会特别处理反斜线，例如bytea，在命令中我们可能需要八个反斜线用来在组合域中存储一个反斜线）。美元引用可以被用来避免双写反斜线。
Tip: 当在 SQL 命令中书写组合值时，ROW构造器语法通常比组合文字语法更容易使用。在ROW中，单个域值可以按照平时不是组合值成员的写法来写。
17. 范围类型
范围类型是表达某种元素类型（称为范围的subtype）的一个值的范围的数据类型。例如，timestamp的范围可以被用来表达一个会议室被保留的时间范围。在这种情况下，数据类型是tsrange（"timestamp range"的简写）而timestamp是 subtype。subtype 必须具有一种总体的顺序，这样对于元素值是在一个范围值之内、之前或之后就是界线清楚的。
范围类型非常有用，因为它们可以表达一种单一范围值中的多个元素值，并且可以很清晰地表达诸如范围重叠等概念。用于时间安排的时间和日期范围是最清晰的例子；但是价格范围、一种仪器的量程等等也都有用。
17.1. 内建范围类型
PostgreSQL 带有下列内建范围类型：
- int4range — integer的范围
- int8range — bigint的范围
- numrange — numeric的范围
- tsrange — 不带时区的 timestamp的范围
- tstzrange — 带时区的 timestamp的范围
- daterange — date的范围
此外，你可以定义自己的范围类型，详见CREATE TYPE。
17.2. 例子
CREATE TABLE reservation (room int, during tsrange);INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');-- 包含SELECT int4range(10, 20) @> 3;-- 重叠SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);-- 抽取上界SELECT upper(int8range(15, 25));-- 计算交集SELECT int4range(10, 20) * int4range(15, 25);-- 范围为空吗？SELECT isempty(numrange(1, 5));
范围类型上的操作符和函数的完整列表可见Table 9-49以及Table 9-50。
17.3. 包含和排除边界
每一个非空范围都有两个界限，下界和上界。这些值之间的所有点都被包括在范围内。一个包含界限意味着边界点本身也被包括在范围内，而一个排除边界意味着边界点不被包括在范围内。
在一个范围的文本形式中，一个包含下界被表达为"["而一个排除下界被表达为"("。同样，一个包含上界被表达为"]"而一个排除下界被表达为")"。
函数lower_inc和upper_inc分别测试一个范围值的上下界。
17.4. 无限（无界）范围
一个范围的下界可以被忽略，意味着所有小于上界的点都被包括在范围中。同样，如果范围的上界被忽略，那么所有比上界大的的都被包括在范围中。如果上下界都被忽略，该元素类型的所有值都被认为在该范围中。
这等效于把下界当作"负无穷"，或者把上界当作"正无穷"。但是注意这些无穷值绝不是范围的元素类型的值，并且绝不是范围的一部分（因此没有所谓的包含无限界限 — 如果你尝试写一个，它将被自动转换成一个排除界限）。
还有，有一些元素类型具有一种"无限"概念，但是那只是范围类型机制所关心的之外的另一种值。例如，在时间戳范围中，[today,]意味着与[today,)相同的东西。但是[today,infinity]意味着与[today,infinity)不同的某种东西 — 后者排除了特殊的timestamp值infinity。
函数lower_inf和upper_inf分别测试一个范围的无限上下界。
17.5. 范围输入/输出
一个范围值的输入必须遵循下列模式之一：
(lower-bound,upper-bound)(lower-bound,upper-bound][lower-bound,upper-bound)[lower-bound,upper-bound]empty
圆括号或方括号指示上下界是否为排除的或者包含的。注意最后一个模式是empty，它表示一个空范围（一个不包含点的范围）。
lower-bound可以是作为 subtype 的合法输入的一个字符串，或者是空表示没有下界。同样，upper-bound可以是作为 subtype 的合法输入的一个字符串，或者是空表示没有上界。
每个界限值可以使用"（双引号）字符引用。如果界限值包含圆括号、方括号、逗号、双引号或反斜线时，这样做是必须的，因为否则那些字符会被认作范围语法的一部分。要把一个双引号或反斜线放在一个被引用的界限值中，就在它前面放一个反斜线（还有，在一个双引号引用的界限值中的一对双引号表示一个双引号字符，这与 SQL 字符串中的单引号规则类似）。此外，你可以避免引用并且使用反斜线转义来保护所有数据字符，否则它们会被当做返回语法的一部分。还有，要写一个是空字符串的界限值，则可以写成""，因为什么都不写表示一个无限界限。
范围值前后允许有空格，但是圆括号或方括号之间的任何空格会被当做上下界值的一部分（取决于元素类型，它可能是也可能不是有意义的）。
Note:
这些规则与组合类型文字中书写域值的规则非常相似。例子：
-- 包括 3，不包括 7，并且包括 3 和 7 之间的所有点SELECT '[3,7)'::int4range;-- 既不包括 3 也不包括 7，但是包括之间的所有点SELECT '(3,7)'::int4range;-- 只包括单独一个点 4SELECT '[4,4]'::int4range;-- 不包括点（并且将被标准化为 '空'）SELECT '[4,4)'::int4range;
17.6. 构造范围
每一种范围类型都有一个与其同名的构造器函数。使用构造器函数常常比写一个范围文字常数更方便，因为它避免了对界限值的额外引用。构造器函数接受两个或三个参数。两个参数的形式以标准的形式构造一个范围（下界是包含的，上界是排除的），而三个参数的形式按照第三个参数指定的界限形式构造一个范围。第三个参数必须是下列字符串之一： "()"、 "(]"、 "[)"或者 "[]"。 例如：
-- 完整形式是：下界、上界以及指示界限包含性/排除性的文本参数。SELECT numrange(1.0, 14.0, '(]');-- 如果第三个参数被忽略，则假定为 '[)'。SELECT numrange(1.0, 14.0);-- 尽管这里指定了 '(]'，显示时该值将被转换成标准形式，因为 int8range 是一种离散范围类型（见下文）。SELECT int8range(1, 14, '(]');-- 为一个界限使用 NULL 导致范围在那一边是无界的。SELECT numrange(NULL, 2.2);
17.7. 离散范围类型
一种范围的元素类型具有一个良定义的"步长"，例如integer或date。在这些类型中，如果两个元素之间没有合法值，它们可以被说成是相邻。这与连续范围相反，连续范围中总是（或者几乎总是）可以在两个给定值之间标识其他元素值。例如，numeric类型之上的一个范围就是连续的，timestamp上的范围也是（尽管timestamp具有有限的精度，并且在理论上可以被当做离散的，最好认为它是连续的，因为通常并不关心它的步长）。
另一种考虑离散范围类型的方法是对每一个元素值都有一种清晰的"下一个"或"上一个"值。了解了这种思想之后，通过选择原来给定的下一个或上一个元素值来取代它，就可以在一个范围界限的包含和排除表达之间转换。例如，在一个整数范围类型中，[4,8]和(3,9)表示相同的值集合，但是对于 numeric 上的范围就不是这样。
一个离散范围类型应该具有一个正规化函数，它知道元素类型期望的步长。正规化函数负责把范围类型的相等值转换成具有相同的表达，特别是与包含或者排除界限一致。如果没有指定一个正规化函数，那么具有不同格式的范围将总是会被当作不等，即使它们实际上是表达相同的一组值。
内建的范围类型int4range、int8range和daterange都使用一种正规的形式，该形式包括下界并且排除上界，也就是[)。不过，用户定义的范围类型可以使用其他习惯。
17.8. 定义新的范围类型
用户可以定义他们自己的范围类型。这样做最常见的原因是为了使用内建范围类型中没有提供的 subtype 上的范围。例如，要创建一个 subtype float8的范围类型：
CREATE TYPE floatrange AS RANGE ( subtype = float8, subtype_diff = float8mi);SELECT '[1.234, 5.678]'::floatrange;
因为float8没有有意义的"步长"，我们在这个例子中没有定义一个正规化函数。
定义自己的范围类型也允许你指定使用一个不同的子类型 B-树操作符类或者集合， 以便更改排序顺序来决定哪些值会落入到给定的范围中。
如果 subtype 被认为是具有离散值而不是连续值，CREATE TYPE命令应当指定一个canonical函数。正规化函数接收一个输入的范围值，并且必须返回一个可能具有不同界限和格式的等价的范围值。对于两个表示相同值集合的范围（例如[1, 7]和[1, 8)），正规的输出必须一样。选择哪一种表达作为正规的没有关系，只要两个具有不同格式的等价值总是能被映射到具有相同格式的相同值就行。除了调整包含/排除界限格式外，假使期望的补偿比 subtype 能够存储的要大，一个正规化函数可能会舍入边界值。例如，一个timestamp之上的范围类型可能被定义为具有一个一小时的步长，这样正规化函数可能需要对不是一小时的倍数的界限进行舍入，或者可能直接抛出一个错误。
另外，任何打算要和 GiST 或 SP-GiST 索引一起使用的范围类型应当定一个 subtype 差异或subtype_diff函数（没有subtype_diff时索引仍然能工作，但是可能效率不如提供了差异函数时高）。subtype 差异函数采用两个 subtype 输入值，并且返回表示为一个float8值的差（即X减Y）。在我们上面的例子中，可以使用常规float8减法操作符之下的函数。但是对于任何其他 subtype，可能需要某种类型转换。还可能需要一些关于如何把差异表达为数字的创新型想法。为了最大的可扩展性，subtype_diff函数应该同意选中的操作符类和排序规则所蕴含的排序顺序，也就是说，只要它的第一个参数根据排序顺序大于第二个参数，它的结果就应该是正值。
subtype_diff函数的一个不那么过度简化的例子：
CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;CREATE TYPE timerange AS RANGE ( subtype = time, subtype_diff = time_subtype_diff);SELECT '[11:10, 23:00]'::timerange;
更多关于创建范围类型的信息请参考CREATE TYPE。
17.9. 索引
可以为范围类型的表列创建 GiST 和 SP-GiST 索引。例如，要创建一个 GiST 索引：
CREATE INDEX reservation_idx ON reservation USING GIST (during);
一个 GiST 或 SP-GiST 索引可以加速涉及以下范围操作符的查询： =、 &&、 <@、 @>、 <<、 >>、 -|-、 &<以及 &> （详见Table 9-49）。
此外，B-树和哈希索引可以在范围类型的表列上创建。对于这些索引类型，基本上唯一有用的范围操作就是等值。使用相应的< 和 >操作符，对于范围值定义有一种 B-树排序顺序，但是该顺序相当任意并且在真实世界中通常不怎么有用。范围类型的 B-树和哈希支持主要是为了允许在查询内部进行排序和哈希，而不是创建真正的索引。
17.10. 范围上的约束
虽然UNIQUE是标量值的一种自然约束，它通常不适合于范围类型。反而，一种排除约束常常更加适合（见CREATE TABLE ... CONSTRAINT ... EXCLUDE）。排除约束允许在一个范围类型上说明诸如"non-overlapping"的约束。例如：
CREATE TABLE reservation ( during tsrange, EXCLUDE USING GIST (during WITH &&));该约束将阻止任何重叠值同时存在于表中：INSERT INTO reservation VALUES ('[2010-01-01 11:30, 2010-01-01 15:00)');INSERT 0 1INSERT INTO reservation VALUES ('[2010-01-01 14:45, 2010-01-01 15:45)');ERROR: conflicting key value violates exclusion constraint "reservation_during_excl"DETAIL: Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflictswith existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")). 你可以使用btree_gist扩展来在纯标量数据类型上定义排除约束，然后把它和范围排除结合可以得到最大的灵活性。例如，安装btree_gist之后，只有会议室号码相等时，下列约束将拒绝重叠的范围：
CREATE EXTENSION btree_gist;CREATE TABLE room_reservation ( room text, during tsrange, EXCLUDE USING GIST (room WITH =, during WITH &&));INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');INSERT 0 1INSERT INTO room_reservation VALUES ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl"DETAIL: Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflictswith existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).INSERT INTO room_reservation VALUES ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');INSERT 0 1 18. 对象标识符类型
对象标识符（OID）被PostgreSQL用来在内部作为多个系统表的主键。OID不会被添加到用户创建的表中，除非在创建表时指定了WITH OIDS或者default_with_oids配置变量被启用。类型oid表示一个对象标识符。也有多个oid的别名类型：regproc、regprocedure、regoper、regoperator、regclass、regtype、regrole、regnamespace、regconfig和regdictionary。Table 8-24显示了一个概览。
oid类型目前被实现为一个无符号4字节整数。因此，在大型数据库中它并不足以提供数据库范围内的唯一性，甚至在一些大型的表中也无法提供表范围内的唯一性。于是，我们不鼓励使用一个用户定义表的OID列作为主键。OID最好只被用于引用系统表。
oid类型本身除了比较之外只有很少的操作。不过，它可以被造型成整数，并且接着可以使用标准的整数操作符进行操纵（这样做时要注意有符号和无符号之间可能出现的混乱）。
OID的别名类型除了特定的输入和输出例程之外没有别的操作。这些例程可以接受并显示系统对象的符号名，而不是类型oid使用的原始数字值。别名类型使查找对象的OID值变得简单。例如，要检查与一个表mytable有关的pg_attribute行，你可以写：
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
而不是：
SELECT * FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');
虽然从它本身看起来并没有那么糟，它仍然被过度简化了。如果有多个名为mytable的表存在于不同的模式中，就可能需要一个更复杂的子选择来选择右边的OID。regclass输入转换器会根据模式路径设置处理表查找，并且因此它会自动地完成这种"右边的事情"。类似地，对于一个数字OID的符号化显示可以很方便地通过将表OID造型成regclass来实现。
Table 8-24. 对象标识符类型
所有用于由名字空间组织的对象的 OID 别名类型都接受模式限定的名字，如果没有被限定的对象在当前搜索路径中无法找到时，将会在输出时显示模式限定的名字。regproc和regoper别名类型将只接受唯一的（非重载的）输入名字，因此它们的使用是受限的；对于大多数使用，regprocedure或regoperator更合适。对于regoperator，通过使用NONE来替代未使用的操作数可以标识一元操作符。
大部分 OID 别名类型的一个附加性质是依赖性的创建。如果这些类型之一的一个常量出现在一个存储的表达式（如一个列默认值表达式或视图）中，它会在被引用的对象上创建一个依赖。例如，如果一个列有一个默认值表达式nextval('my_seq'::regclass)，PostgreSQL会理解该默认值表达式是依赖于序列my_seq的，在删除该默认值表达式之前系统将不允许删除该序列。regrole是这个性质的唯一例外。这种类型的常量不允许出现在这类表达式中。
Note:
OID 别名类型不完全遵循事务隔离规则。规划器也把它们当做简单常量， 这可能会导致次优的规划。另一种系统中使用的标识符类型是xid，或者称为事务（简写为xact）标识符。这是系统列xmin和xmax使用的数据类型。事务标识符是32位量。
系统使用的第三种标识符类型是cid，或者称为命令标识符。这是系统列cmin和cmax使用的数据类型。命令标识符也是32位量。
系统使用的最后一种标识符类型是tid，或者称为元组标识符（行标识符）。这是系统列ctid使用的数据类型。一个元组ID是一个（块号，块内元组索引）对，它标识了行在它的表中的物理位置。
19. pg_lsn Type
pg_lsn数据类型可以被用来存储 LSN（日志序列号）数据，LSN 是一个指向 XLOG 中的位置的指针。这个类型是XLogRecPtr的一种表达并且是 PostgreSQL的一种内部系统类型。
在内部，一个 LSN 是一个 64 位整数，表示在预写式日志流中的一个字节位置。它被打印成 两个最高 8 位的十六进制数，中间用斜线分隔，例如16/B374D848。 pg_lsn类型支持标准的比较操作符，如=和 >。两个 LSN 可以用-操作符做减法， 结果将是分隔两个预写式日志位置的字节数。
20. 伪类型
PostgreSQL类型系统包含了一些特殊目的的项，它们被统称为伪类型。一个伪类型不能被用作一个列的数据类型，但是它可以被用来定义一个函数的参数或者结果类型。每一种可用的伪类型都有其可以发挥作用的情况，这些情况的特点是一个函数的行为并不能符合于简单使用或者返回一种特定SQL数据类型的值。Table 8-25列出了现有的伪类型。
Table 8-25. 伪类型
用C编写的函数（不管是内建的还是动态载入的）可以被声明为接受或返回这些为数据类型的任意一种。函数的作者应当保证当一个伪类型被用作一个参数类型时函数的行为是安全的。
用过程语言编写的函数只有在其实现语言允许的情况下才能使用伪类型。目前大部分过程语言都禁止使用伪类型作为一种参数类型，并且只允许使用void和record作为结果类型（如果函数被用于一个触发器或者事件触发器， trigger或者event_trigger也被允许作为结果类型）。某些过程语言也支持在多态函数中使用类型anyelement、anyarray、anynonarray、anyenum和anyrange。
internal伪类型用于定义只在数据库系统内部调用的函数，这些函数不会被SQL直接调用。如果一个函数拥有至少一个internal类型的参数，则它不能从SQL中被调用。为了保持这种限制的类型安全性，遵循以下编码规则非常重要：不要创建任何被声明要返回internal的函数，除非它有至少一个internal参数。
PostgreSQL有着丰富的本地数据类型可用。用户可以使用CREATE TYPE命令为 PostgreSQL增加新的数据类型。
Table 8-1显示了所有内建的普通数据类型。大部分在"别名"列里列出的可选名字都是因历史原因 被PostgreSQL在内部使用的名字。另外，还有一些内部使用的或者废弃的类型也可以用，但没有在这里列出。
Table 8-1. 数据类型
兼容性: 下列类型（或者及其拼写）是SQL指定的：bigint、bit、bit varying、boolean、char、character varying、character、varchar、date、double precision、integer、interval、numeric、decimal、real、smallint、time（有时区或无时区）、timestamp（有时区或无时区）、xml。 每种数据类型都有一个由其输入和输出函数决定的外部表现形式。许多内建的类型有明显的格式。不过，许多类型要么是PostgreSQL所特有的（例如几何路径），要么可能是有几种不同的格式（例如日期和时间类型）。 有些输入和输出函数是不可逆的，即输出函数的结果和原始输入比较时可能丢失精度。