PostgreSQL教程（十一）：SQL语言（四）之数据类型

一、数值类型

数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。 下表列出了所有可用类型。

数值类型

名字	存储长度	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	常用的整数	-2147483648 到 +2147483647
bigint	8 字节	大范围整数	-9223372036854775808 到 +9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

1.1 整数类型

smallint，integer和bigint类型存储各种取值范围的整数， 即，没有小数部分的数字。试图存储超出取值范围以外的数值将导致一个错误。

常用的类型是integer，因为它在取值范围、存储空间、性能之间最为平衡。 一般只有在磁盘空间紧张的时候才使用smallint。 当integer的取值范围不够用的时候才使用bigint。

SQL标准只定义了整型integer(或int)， smallint和bigint。名为int2, int4 和int8的类型都是扩展，它们也在许多其它符合SQL标准的数据库系统中使用。

1.2 任意精度数值

numeric类型可以存储非常大的数字并且准确地进行计算。 我们特别建议将它用于货币金额和其它要求精确计算的场合。不过， numeric类型上的算术运算比整数类型或者我们下一节描述的浮点数类型要慢很多。

在随后的内容里，我们使用下述术语：一个numeric类型的标度 (scale)是小数部分的位数，精度(precision) 是全部数据位的数目，也就是小数点两边的位数总和。 因此数字 23.5141 的精度为 6 而标度为 4 。你可以认为整数的标度为零。

numeric字段的最大精度和最大标度都是可以配置的。 要声明一个字段的类型为numeric，你可以用下面的语法：

NUMERIC(precision, scale)

精度必须为正数，标度可以为零或者正数。另外，以下是选择了标度为 0 :

NUMERIC(precision)

不带任何精度与标度的声明：

NUMERIC

如上则创建一个可以存储一个直到实现精度上限的任意精度和标度的数值， 一个这样类型的字段将不会把输入数值转化成任何特定的标度， 而带有标度声明的numeric字段将把输入值转化为该标度。 SQL标准要求缺省的标度是 0(也就是转化成整数精度)。 我们觉得这样做有点没用。如果你关心移植性， 那你最好总是明确声明精度和标度。

NOTES:

        当在类型声明中显示指定精度时允许的最大值为1000；没有指定精度的 NUMERIC遵从表 8-2里的描述。

如果一个要存储的数值的标度比字段声明的标度高， 那么系统将尝试圆整(四舍五入)该数值到指定的小数位。然后， 如果小数点左边的数据位数超过了声明的精度减去声明的标度，那么将抛出一个错误。

numeric 类型的数据值在物理上是不带任何前导或者后缀零的形式存储的。 因此，字段上声明的精度和标度都是最大值，而不是固定分配的。在这个方面， numeric类型更类似于varchar(n)而不是 char(n)。实际存储是每四个十进制位两个字节， 然后在整个数据上加上三到八个字节的额外开销。

除了普通的数字值之外，numeric类型允许用特殊值NaN 表示"不是一个数字"。任何在NaN上面的操作都生成另外一个 NaN。如果在 SQL 命令里把这些值当作一个常量写， 你必须在其周围放上单引号，比如UPDATE table SET x = 'NaN'。 在输入时，字符串NaN是大小写无关的。

NOTES:

        在大多数"not-a-number"概念中，不认为NaN等于其他数值类型 （包括NaN）。为了能够存储numeric类型的值， 并且使用Tree索引，PostgreSQL认为NaN相等， 并且大于所有非NaN值。

类型decimal和numeric是等效的。 两种类型都是SQL标准。

1.3 浮点数类型

数据类型real和double precision是不精确的、变精度的数字类型。 实际上，这些类型是IEEE 754标准二进制浮点数算术(分别对应单和双精度)的一般实现， 外加下层处理器、操作系统和编译器对它的支持。

不精确意味着一些数值不能精确地转换成内部格式并且是以近似值存储的， 因此存储后再把数据打印出来可能有一些差异。 处理这些错误以及这些错误是如何在计算中传播的属于数学和计算机科学的一个完整的分支， 我们不会在这里进一步讨论它，这里的讨论仅限于如下几点：

• 如果你要求精确的计算(比如计算货币金额)，应使用numeric类型。

• 如果你想用这些类型做任何重要的复杂计算， 尤其是那些你对范围情况(无穷/下溢)严重依赖的事情，那你应该仔细评诂你的实现。

• 拿两个浮点数值进行相等性比较可能不像你想像那样运转。

在大多数平台上，real类型的范围是至少1E-37到1E+37， 精度至少是6位小数。double precision的范围通常是1E-307到1E+308， 精度是至少15位数字。太大或者太小的数值都会导致错误。如果输入数据的精度太高， 那么将会发生圆整。太接近零的数字，如果无法与零值的表现形式相区分就会产生下溢错误。

NOTES:

         当一个浮点数值转化为文本输出时，extra_float_digits 设置控制额外有效数字的位数。默认值是0，PostgreSQL 支持的平台上的输出是一样的。增加这个值产生的输出将更精确的表示存储值， 但是可能不利于移植。

除了普通的数字值之外，浮点类型还有几个特殊值：

Infinity
-Infinity
NaN

这些值分别表示 IEEE 754 特殊值"正无穷大"、"负无穷大"、 "不是一个数字"。在不遵循 IEEE 754 浮点算术的机器上， 这些值的含义可能不是预期的。如果在 SQL 命令里把这些数值当作常量写， 你必须在它们周围放上单引号，像这样：UPDATE table SET x = 'Infinity'。 输入时，这些值是以大小写无关的方式识别的。

NOTES:

         IEEE754声明NaN不应该等于任何其他浮点值（包括NaN）。 为了能存储浮点值，并且使用Tree索引，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。

NOTES:

         PostgreSQL 7.4以前，在float(p) 里面的精度会被当作是这么多位数的十进制位。到 7.4 已经被修改成与 SQL 标准匹配， 标准声明这个精度是以二进制位度量的。假设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约束以确保 NULL 不会被插入。 在大多数情况下你可能还希望附加一个UNIQUE或PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动的。最后， 将序列发生器"从属于"那个字段，这样当该字段或表被删除的时候也一并删除它。

NOTES:

        因为smallserial, serial和bigserial 是使用序列实现的，所以显示在字段里的序列值可能有“漏洞”或者缺口，即使没有列曾经被删除。 一个从序列中分配的值仍然会“使用”，即使包含这个值的行没有成功的插入到表格的字段中。 这种情况是有可能发生的，比如，插入事务回滚。参阅后面章节中的nextval()获取详细信息。

        PostgreSQL 7.3以前，serial隐含UNIQUE。 但现在不再如此。如果你希望一个序列字段有一个唯一约束或者一个主键， 那么你现在必须声明，就像其它数据类型一样。

要在serial字段中插入序列中的下一个数值，主要是要注意serial 字段应该赋予缺省值。我们可以通过在INSERT 语句中把该字段排除在字段列表之外来实现，也可以通过使用DEFAULT关键字来实现。

类型名serial和serial4是等效的： 两者都创建integer字段。类型名bigserial和serial8 也一样，只不过它创建一个bigint字段。 如果你预计在表的生存期中使用的标识数目可能超过 231 个， 那么你应该使用bigserial。类型名smallserial和 serial2也一样，只不过它创建一个smallint字段。

一个serial类型创建的序列在所属的字段被删除的时候自动删除。 你可以只删除序列而不删除字段，不过这将删除该字段的缺省值表达式。

---

二、货币类型

money类型存储带有固定小数精度的货币金额，可查阅 下表。小数精度由lc_monetary的设置来决定。表格中显示的范围假设有两位小数。可以以任意格式输入，包括整型， 浮点型，或者典型的货币格式，如'$1,000.00'。 根据区域字符集，输出一般是最后一种形式。

货币类型

名字	存储容量	描述	范围
money	8字节	货币金额	-92233720368547758.08到+92233720368547758.07

由于输出的数据类型对语言环境要求很细，因此，lc_monetary 设置的不同可能会造成无法将money数据输入到数据库中。 为了避免这种问题的发生，在向一个新数据库进行转储之前，确保 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（也就是，一个纯数字，而不是money）； 在结果中货币单位相互取消。

---

三、字符类型

字符类型

名字	描述
character varying(n), varchar(n)	变长，有长度限制
character(n), char(n)	定长，不足补空白
text	变长，无长度限制

上表显示了在PostgreSQL 里可用于一般用途的字符类型。

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 值的时候，填充的空白都不会被关注，在转换成其它字符串类型的时候， character值里面的空白会被删除。请注意， 在character varying和text数值里，结尾的空白是有语意的。 并且当使用模式匹配时，如LIKE，使用正则表达式。

一个简短的字符串（最多126个字节）的存储要求是1个字节加上实际的字符串， 其中包括空格填充的character。更长的字符串有4个字节的开销， 而不是1。长的字符串将会自动被系统压缩，因此在磁盘上的物理需求可能会更少些。 更长的数值也会存储在后台表里面，这样它们就不会干扰对短字段值的快速访问。 不管怎样，允许存储的最长字符串大概是1GB 。允许在数据类型声明中出现的n 的最大值比这还小。修改这个行为没有什么意义，因为在多字节编码下字符和字节的数目可能差别很大。 如果你想存储没有特定上限的长字符串，那么使用text 或没有长度声明的character varying，而不要选择一个任意长度限制。

TIPS:

        这三种类型之间没有性能差别，除了当使用填充空白类型时的增加存储空间， 和当存储长度约束的列时一些检查存入时长度的额外的CPU周期。 虽然在某些其它的数据库系统里，character(n) 有一定的性能优势，但在PostgreSQL里没有。 事实上，character(n)通常是这三个中最慢的， 因为额外存储成本。在大多数情况下，应该使用text 或character varying。

例：使用字符串类型

CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)
  a   | char_length
------+-------------
 ok   |           2

CREATE 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)); -- 明确截断
SELECT b, char_length(b) FROM test2;
   b   | char_length
-------+-------------
 ok    |           2
 good  |           5
 too l |           5

char_length函数将在后续讨论。

在PostgreSQL里另外还有两种特殊定长字符类型。 在下表里显示。name 类型只用于在内部系统表中存储标识符并且不是给一般用户使用的。 该类型长度当前定为 64 字节(63 可用字符加结束符)但应该使用C源码中的常量 NAMEDATALEN引用。这个长度是在编译的时候设置的， 因而可以为特殊用途调整，缺省的最大长度在以后的版本可能会改变。 类型"char"(注意引号)和char(1)是不一样的， 它只用了一个字节的存储空间。它在系统内部用于系统表当做过分简单化的枚举类型用。

名字	存储空间	描述
"char"	1 字节	单字节内部类型
name	64 字节	用于对象名的内部类型

---

四、二进制数据类型

bytea数据类型允许存储二进制字符串。见下表

二进制数据类型

名字	存储空间	描述
bytea	1或4字节加上实际的二进制字符串	变长的二进制字符串

二进制字符串是一个字节序列。

二进制字符串和普通字符字符串的区别有两个：

1. 首先，二进制字符串完全可以存储字节零值以及其它"不可打印的" 字节(定义在 32 到 126 范围之外的字节)。字符串不允许字节零值， 并且也不允许那些不符合选定的字符集编码的非法字节值或者字节序列。
2. 第二，对二进制字符串的处理实际上就是处理字节，而对字符串的处理则取决于区域设置。 简单说，二进制字符串适用于存储那些程序员认为是"原始字节"的数据， 而字符串适合存储文本。

bytea类型支持两种输入输出的外部格式："hex" 格式和PostgreSQL的历史"escape"格式。 这两种格式通常在输入中使用，输出格式由butea_output配置参数决定，默认是hex。（需要注意的是hex格式是在PostgreSQL 9.0中引入的，早期版本和一些工具中不识别。）

SQL标准定义了一个不同的二进制字符串格式，称为BLOB 或BINARY LARGE OBJECT。输入格式与bytea不同， 但提供的函数和操作符基本一致。

4.1 bytea十进制格式 

"hex"格式将二进制数据编码为每字节2位十六进制数字， 最重要的四位（半字节）放在开始。整条字符串以\x开始 （与转义格式相区别）。在某些情况下，最初的反斜杠需要再写一次，以转义， 同样，在转义格式中，反斜杠需要写两个，下面会详细介绍。 十六进制数字可以大写也可以小写，并且数字对之间允许有空格（但不能是在一个数对， 也不能是在\x起始序列）。 十六进制格式能够与许多的外部应用程序和协议兼容，并且转换往往比转义快， 因此更倾向于这种用法。

例子：

SELECT E'\\xDEADBEEF';

4.2 bytea转义格式

对于bytea格式来说，"escape"格式是一种传统的 PostgreSQL格式。它采用以ASCII字符序列来表示二进制串的方法， 同时将那些无法表示成ASCII字符的二进制串转换成特殊的转义序列。从应用的角度看， 如果代表字节的字符有意义，那么这种表示方法会很方便。但实际上， 这样做会模糊二进制字符串和字符字符串之间的区别，从而造成困扰， 同时筛选出的转义机制会显得很臃肿。因此对一些新应用应该恰当的避免这种格式。

当以转义格式录入bytea值时，你必须转义某些字节值， 同时可以转义所有字节值。通常，要转义一个字节值， 将它转换成反斜杠+三位八进制值的形式（或两个反斜杠，如果用转义字符串语法将值写成文本形式）。 另外，反斜杠本身（字节值92）可以用双反斜杠表示。下表给出了必须转义的字符串，和替代的转义序列（如适用）。

bytea文本转义八进制

十进制数值	描述	输入转义形式	例子	输出形式
0	八进制的零	E'\\000'	SELECT E'\\000'::bytea;	\000
39	单引号	'''' 或 E'\\047'	SELECT E'\''::bytea;	'
92	反斜杠	E'\\\\' 或 E'\\134'	SELECT E'\\\\'::bytea;	\\
0 to 31 and 127 to 255	"不可打印"八进制字符	E'\\xxx' (八进制值)	SELECT E'\\001'::bytea;	\001

转义不可打印字节的要求因区域设置而异。在某些场合下， 你可以不转义它们。请注意上表 里的每个例子都是刚好一个字节长，虽然输出形式比一个字符要长。

你必须写这么多反斜杠的原因，如上表所示， 是因为一个写成字符串文本的输入字符串必须通过PostgreSQL 服务器里的两个分析阶段。每一对反斜杠中的第一个会被字符串文本分析器理解成一个转义字符而消耗掉， 于是剩下的第二个反斜杠被bytea输入函数当作一个三位八进制值或者是转义另外一个反斜杠的开始。 比如，一个传递给服务器的字符串文本E'\\001'在通过字符串分析器之后会当作\001 发送给bytea输入函数，在这里它被转换成一个十进制值为 1 的单个字节。请注意， 单引号字符(')不会被bytea特殊对待，它遵循字符串文本的普通规则。

Bytea字节也在输出中转义。通常，每个"不可打印" 的字节值都转化成对应的前导反斜杠的三位八进制数值。大多数"可打印的" 字节值是以客户端字符集的标准表现形式出现的。十进制值为 92(反斜杠)的字节在输出中双写。 细节在下表里描述。

bytea输出转义序列

字节的十进制值	描述	转义的输出形式	例子	输出结果
92	反斜杠	\\	SELECT E'\\134'::bytea;	\\
0 to 31 and 127 to 255	"不可打印"八进制字符	\xxx (八进制值)	SELECT E'\\001'::bytea;	\001
32 to 126	"可打印"八进制字符	客户端字符集表现形式	SELECT E'\\176'::bytea;	~

根据你使用的前端不同，在是否转义bytea字符串的问题上你可能有一些额外的工作要做。 比如，如果你的接口自动转换换行和回车，那你可能还要转义它们。

---

五、日期/时间类型

下表显示了PostgreSQL 支持的SQL中所有日期和时间类型。 这些数据类型上可以进行的操作在下一章节中描述。 日期是按照公历计算的，甚至日历之前的年份也介绍了 （参阅第 B.4 节获取更多信息）。

日期/时间类型

名字	存储空间	描述	最低值	最高值	分辨率
timestamp [ (p) ] [ without time zone ]	8 字节	日期和时间(无时区)	4713 BC	294276 AD	1 毫秒 / 14 位
timestamp [ (p) ] with time zone	8 字节	日期和时间，有时区	4713 BC	294276 AD	1 毫秒 / 14 位
date	4 字节	只用于日期	4713 BC	5874897 AD	1 天
time [ (p) ] [ without time zone ]	8 字节	只用于一日内时间	00:00:00	24:00:00	1 毫秒 / 14 位
time [ (p) ] with time zone	12 字节	只用于一日内时间，带时区	00:00:00+1459	24:00:00-1459	1 毫秒 / 14 位
interval [ fields ] [ (p) ]	12 字节	时间间隔	-178000000 年	178000000 年	1 毫秒 / 14 位

NOTES:

         SQL标准要求仅仅将timestamp类型等于timestamp without time zone 类型，PostgreSQL遵守这个行为。（7.3之前的版本将其看做 timestamp with time zone。） timestamptz作为 timestamp with time zone的缩写被接受；这是PostgreSQL 的一个扩展。

time, timestamp和interval接受一个可选的精度值 p以指明秒域中小数部分的位数。没有明确的缺省精度， p的范围对timestamp和interval类型是从0到6。

NOTES:

        如果timestamp数值是以8字节整数(目前的缺省)的方式存储的， 那么微秒的精度就可以在数值的全部范围内都可以获得。如果timestamp 数值是以双精度浮点数(一个废弃的编译时的选项)的方式存储的， 那么有效精度会小于 6 。timestamp值是以 2000-01-01 午夜之前或之后的秒数存储的。 当timestamp值用浮点数实现时，微秒的精度是为那些在 2000-01-01 前后几年的日期实现的， 对于那些远一些的日子，精度会下降。注意，使用浮点时间允许一个比上面显示的更大的 timestamp值变化范围：从4713BC到5874897AD。

同一个编译时选项也决定time和interval值是保存成浮点数还是八字节整数。 在以浮点数存储的时候，随着时间间隔的增加，大的 interval数值的精度会降低。

对于time类型，如果使用了八字节的整数存储，那么p 允许的范围是从 0 到 6 ，如果使用的是浮点数存储，那么这个范围是 0 到 10 。

interval类型有一个额外的选项，通过下下面词组之一限制存储的字段值：

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE 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 标准要求的更灵活。参阅附录 B 获取关于日期/时间输入的准确分析规则和可识别文本字段，包括月份、星期几、时区。

请记住任何日期或者时间的文本输入需要由单引号包围，就像一个文本字符串一样。 。 SQL要求使用下面的语法：

type [ (p) ] 'value'

可选的精度声明中的p是一个整数，表示在秒域中小数部分的位数， 我们可以对time,timestamp,interval类型声明精度。 允许的精度在上面已经说明。如果在常量声明中没有声明精度，缺省是文本值的精度。

5.1.1 日期

下表显示了date类型可能的输入方式。

 Date Input

例子	描述
1999-01-08	ISO 8601格式(建议格式)，任何方式下都是 1999 年 1 月 8 号
January 8, 1999	在任何datestyle输入模式下都无歧义
1/8/1999	有歧义，在MDY下是一月八号；在DMY模式下是八月一日
1/18/1999	MDY模式下是一月十八日，其它模式下被拒绝
01/02/03	MDY模式下的 2003 年 1 月 2 日； DMY模式下的 2003 年 2 月 1 日； YMD模式下的 2001 年 2 月 3 日
1999-Jan-08	任何模式下都是 1 月 8 日
Jan-08-1999	任何模式下都是 1 月 8 日
08-Jan-1999	任何模式下都是 1 月 8 日
99-Jan-08	YMD模式下是 1 月 8 日，否则错误
08-Jan-99	一月八日，除了在YMD模式下是错误的之外
Jan-08-99	一月八日，除了在YMD模式下是错误的之外
19990108	ISO 8601；任何模式下都是 1999 年 1 月 8 日
990108	ISO 8601；任何模式下都是 1999 年 1 月 8 日
1999.008	年和年里的第几天
J2451187	儒略日
January 8, 99 BC	公元前 99 年

5.1.2 时间

当日时间类型是time [ (p) ] without time zone 和time [ (p) ] with time zone。 只写time等效于time without time zone。

这些类型的有效输入由当日时间后面跟着可选的时区组成(参阅 表5.1.2.1和 表5.1.2.2)。 如果在time without time zone类型的输入中声明了时区，那么它会被悄悄地忽略。 同样指定的日期也会被忽略，除非使用了一个包括夏令时规则的时区名，比如 America/New_York，在这种情况下， 必须指定日期以确定这个时间是标准时间还是夏令时。时区偏移将记录在 time with time zone中。

表5.1.2.1 时间输入

例子	描述
04:05:06.789	ISO 8601
04:05:06	ISO 8601
04:05	ISO 8601
040506	ISO 8601
04:05 AM	与 04:05 一样；AM 不影响数值
04:05 PM	与 16:05 一样；输入小时数必须<= 12
04:05:06.789-8	ISO 8601
04:05:06-08:00	ISO 8601
04:05-08:00	ISO 8601
040506-08	ISO 8601
04:05:06 PST	缩写的时区
2003-04-12 04:05:06 America/New_York	用名字声明的时区

表5.1.2.2 时区输入

例子	描述
PST	太平洋标准时间(Pacific Standard Time)
America/New_York	完整时区名称
PST8PDT	POSIX 风格的时区
-8:00	ISO-8601 与 PST 的偏移
-800	ISO-8601 与 PST 的偏移
-8	ISO-8601 与 PST 的偏移
zulu	军方对 UTC 的缩写(译注：可能是美军)
z	zulu的缩写

参考下面5.3节以获取如何指定时区的更多信息。

5.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为方便起见支持在 下表里面显示的几个特殊输入值。 值infinity和-infinity是特别在系统内部表示的， 并且将按照同样的方式显示；但是其它的都只是符号缩写， 在读取的时候将被转换成普通的日期/时间值。特别是now 和相关的字符串在读取的时候就被转换成对应的数值。 所有这些值在 SQL 命令里当作普通常量对待时，都需要包围在单引号里面。

特殊日期/时间输入

输入字符串	适用类型	描述
epoch	date, timestamp	1970-01-01 00:00:00+00 (Unix 系统零时)
infinity	date, timestamp	比任何其它时间戳都晚
-infinity	date, timestamp	比任何其它时间戳都早
now	date, time, timestamp	当前事务的开始时间
today	date, timestamp	今日午夜
tomorrow	date, timestamp	明日午夜
yesterday	date, timestamp	昨日午夜
allballs	time	00:00:00.00 UTC

下列SQL兼容函数也可以用于获取对应数据类型的当前时间值： CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP。后四个接受一个可选的精度声明。不过，请注意这些 SQL 函数不是 被当作数据输入字符串识别的。

5.2 日期/时间输出

日期/时间类型的输出格式可以设成 ISO 8601(默认)、SQL(Ingres)、 传统的POSTGRES(Unix date格式)或German四种风格之一。 SQL标准要求使用 ISO 8601 格式。"SQL"输出格式的名字是历史偶然。 下表显示了每种输出风格的例子。 date和time类型的输出当然只是给出的例子里面的日期和时间部分。

日期/时间输出风格

风格	描述	例子
ISO	ISO 8601，SQL 标准	1997-12-17 07:37:16-08
SQL	传统风格	12/17/1997 07:37:16.00 PST
Postgres	原始风格	Wed Dec 17 07:37:16 1997 PST
German	地区风格	17.12.1997 07:37:16.00 PST

注意: ISO 8601指定用大写字母T分隔日期和时间。PostgreSQL 在输入上接受这种格式，但是在输出上使用一个空格而不是T，就像上面显示的。 这样更易读而且和RFC 3399或者其他的数据库系统一致。

如果声明了 DMY 顺序，那么在SQL和 POSTGRES 风格里， 日期在月份之前出现，否则月份出现在日期之前(参阅 5.1节 看看这个设置如何影响对输入值的解释)。下表显示了一个例子。

日期顺序习惯

datestyle设置	输入顺序	输出样例
SQL, DMY	日/月/年	17/12/1997 15:37:16.00 CET
SQL, MDY	月/日/年	12/17/1997 07:37:16.00 PST
Postgres, DMY	日/月/年	Wed 17 Dec 07:37:16 1997 PST

用户可以用SET datestyle命令选取日期/时间的风格， 也可以在配置文件postgresql.conf 中的DateStyle参数中设置，或者在服务器或客户端的 PGDATESTYLE环境变量中设置。

我们也可以用格式化函数to_char 来更灵活地控制时间/日期地输出。

5.3 时区

时区和时区习惯不仅仅受地球几何形状的影响，还受到政治决定的影响。 到了 19 世纪，全球的时区变得稍微标准化了些，但是还是易于遭受随意的修改， 部分是因为夏时制规则。PostgreSQL 使用广泛使用的zoneinfo（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 使用广泛使用的zoneinfo时区数据， 所以这些时区名在其它软件里也能被轻松的识别。

• 时区缩写。例如PST。这种缩写名通常只是定义了相对于 UTC 的偏移量， 而前一种完整的时区名可能还隐含着一组夏时制转换规则。 所有可以识别的时区缩写在pg_timezone_abbrevs视图中列出(参见 第 47.70 节)。你不能设置 TimeZone或log_timezone 配置参数为时区缩写，但是你可以在日期/时间输入值中结合AT TIME ZONE 操作符使用时区缩写。

• 除完整的时区名及其缩写之外，PostgreSQL还接受 POSIX 风格的 STDoffset或STDoffsetDST 格式的时区，其中的STD是时区缩写、offset 是一个相对于 UTC 的小时偏移量、DST是一个可选的夏时制时区缩写 (假定相对于给定的偏移量提前一小时)。例如，如果EST5EDT不是一个已识别的时区名， 那么它将等同于美国东部时间。如果存在夏时制时区名是当前时区名，根据zoneinfo 时区数据库的 posixrules条目中相同的夏时制事务规则，可以考虑使用这个特性。 在一个PostgreSQL标准安装中，posixrules与US/Eastern 相同，因此POSIX格式的时区声明遵循USA夏时制规则。如果需要，可以通过替换posixrules 文件来调整该习惯。

简言之，这就是完整的时区名与时区缩写之间的差异：时区缩写总是代表一个相对于 UTC 的固定偏移量， 然而大多数完整的时区名隐含着一个本地夏令时规则，因此就有可能有两个相对于 UTC 的不同偏移量。

需要警惕的是，由于没有合理的时区缩写检查，POSIX格式的时区特点能导致静默的伪输入。 例如，使用SET TIMEZONE TO FOOBAR0时，实际上系统使用的是一个很特别的UTC缩写。 另一个需要注意的是，在POSIX时区名中，积极的偏移用于west格林尼治位置。 在其他地方，PostgreSQL遵循ISO-8601规定， 即积极的时区偏移east格林威治。

总体而言，PostgreSQL 8.2 版本以后时区名在所有情况下都是大小写无关的。 而之前的版本在某些情况下是大小写敏感的。

无论是完整的时区名还是时区缩写都不是硬连接进服务器的，它们都是从安装目录下的 .../share/timezone/和.../share/timezonesets/ 配置文件中获取的(参见第 B.3 节)。

可以在postgresql.conf文件里设置TimeZone 配置参数，或者用任何其它描述的标准方法。 除此之外，还有好几种特殊方法可以设置它：

• 使用SQL命令SET TIME ZONE为会话设置时区， 这是SET TIMEZONE TO的一个可选的拼写方式，更加兼容标准。

• 如果在客户端设置了PGTZ环境变量，那么libpq 在连接时将使用这个环境变量给后端发送一个SET TIME ZONE命令。

5.4 间隔输入

interval类型值可以用下面的详细语法写：

[@] quantity unit [quantity unit...] [direction]

这里quantity是一个数字（可能已标记）；unit 可以是microsecond，millisecond，second， minute，hour，day， week，month，year， decade，century，millennium 或这些单位的缩写或复数。direction可以是ago或为空。 @标记是可选的。不同的单位的数量被隐式地添加适当的计算符号。 ago否定所有。如果 IntervalStyle 设置为postgres_verbose，那么这个语法同样用于间隔输出。

可以在没有明确单位标记的情况下声明天，小时，分钟和秒。例如，'1 12:59:10' 等同于'1 day 12 hours 59 min 10 sec'。同样， 可以用一个破折号来声明一个年和月的组合，例如'200-10'等同于 '200 years 10 months'。（事实上，SQL标准值允许短的格式， 并且当IntervalStyle设置为sql_standard时，用于输出）。

要么使用4.4.3.2的"format with designators"，要么使用4.4.3.3的 "alternative format"，间隔值可以写为ISO 8601的时间间隔。格式如下：

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

字符串必须以P开始，并且可以含有一个T用以指明一天中时间的格式。 可用单位的缩写在下表有说明。可以忽略单位， 也可以以任意顺序声明，但单位小于一天时必须在T之后。 尤其M的含义依赖于它在T之前或之后。

 ISO 8601 间隔单位的缩写

缩写	含义
Y	年
M	月（日期部分）
W	周
D	日
H	小时
M	分钟（时间部分）
S	秒

以缩写格式：

P [ years-months-days ] [ T hours:minutes:seconds ]

一个字符串必须以P开始，然后以T隔开日期和时间。 给出的值是如同ISO 8601日期的数字。

当用fields规范写一个时间间隔常数，或将一个字符串标记为用 fields规范定义的一个间隔列时，未标记单位的解释由fields 解释。如INTERVAL '1' YEAR读作1年，然而INTERVAL '1'代表1秒。 同样，fields规范中"最低"有效字段值规定会被静默的忽略。 如，INTERVAL '1 day 2:03:04' HOUR TO MINUTE会导致删除秒字段，而不是天字段。

根据SQL标准，间隔值的所有字段必须有相同的符号，因此前导负号可以用于所有字段； 如'-1 2:03:04'中负号同时应用于天和小时/分钟/秒。PostgreSQL 允许字段有不同的标记，并且传统上，文本表述中的每个字段会被认为是独立标记的， 因此在这个例子中的小时/分钟/秒被认为是正值。如果IntervalStyle被设置为 sql_standard，那么前导标记被认为是应用于所有字段的 （当然前提是没有再出现其他标记），否则会使用传统的PostgreSQL解释。 为了避免这种歧义，如果任何字段是负的，建议为每个字段附上一个明确的标记。

PostgreSQL内部，interval值被存储为月，日，秒的格式， 这是因为月中包含天数不同，并且如果进行了夏令时调整，那么一天可以有23或25小时。 当秒字段可以存储分数时，月和天字段可以是整数型。 由于时间间隔通常是由常量字符串或timestamp减法来定义的， 这种存储方法在大多数情况下很有效。justify_days和justify_hours 函数可用于调整溢出正常范围值的天和小时。

在详细的输入格式，以及更紧凑的输入格式中，字段值可以有小数部分， 例如'1.5 week'或'01:02:03.45'。这种输入被转换成恰当的月， 天和秒来存储。由于这样会产生小数的月或天，因此在低阶字段中引入了分数， 使用1 month = 30 days 和 1 day = 24 hours的转换。例如，'1.5 month' 即1个月15天。输出中，只有秒可以写成分数形式。

下表中有一些有效的interval输入的例子。

间隔输入

示例	说明
1-2	SQL标准格式：一年两个月
3 4:05:06	SQL标准格式：3天4小时5分6秒
1 year 2 months 3 days 4 hours 5 minutes 6 seconds	传统Postgres格式: 1年2个月3天4小时5分钟6秒
P1Y2M3DT4H5M6S	ISO 8601 "带标识符格式":与上面相同含义
P0001-02-03T04:05:06	ISO 8601 "缩写格式":与上面相同含义

5.5 间隔输出

间隔类型的输出格式可以用命令SET intervalstyle设置为下面四种类型： sql_standard，postgres，postgres_verbose或 iso_8601。默认是postgres格式， 下表中有每种格式的示例。

sql_standard格式产生的输出结果符合SQL的间隔字符串标准， 如果间隔值满足标准的限制（无论只有年-月，或只有天-时间，没有积极和消极的构成的混合）。 否则输出类似一个标准年-月文本字符串后跟有一个天-时间文本字符串， 带有添加明确标记的消除歧义混合信号的时间间隔。

当参数DateStyle设置为ISO时， postgres格式的输出与PostgreSQL 8.4之前的版本一致。

当参数DateStyle设置为非-ISO， postgres_verbose格式的输出与PostgreSQL 8.4之前的版本一致。

iso_8601格式的输出与ISO 8601标准4.4.3.2节中的"format with designators"一致。

间隔输出格式示例

格式	年-月间隔	天-时间间隔	混合间隔
sql_standard	1-2	3 4:05:06	-1-2 +3 -4:05:06
postgres	1 年 2 个月	3 天 04:05:06	-1 年 -2 个月 +3 天 -04:05:06
postgres_verbose	@ 1 年 2 个月	@ 3 天 4 小时 5 分 6 秒	@ 1 年 2 个月 -3 天 4 小时 5 分 6 秒以前
iso_8601	P1Y2M	P3DT4H5M6S	P-1Y-2M3DT-4H-5M-6S

---

六、布尔类型

PostgreSQL支持SQL标准的 boolean数据类型。参阅下表。 boolean只能有"true"(真)或"false"(假)两个状态， 或第三种"unknown"(未知)状态，用 NULL 表示。

布尔数据类型

名称	存储格式	描述
boolean	1 字节	真/假

"真"值的有效文本值是：

TRUE

't'

'true'

'y'

'yes'

'on'

'1'

对于"假"，你可以使用下面这些：

FALSE

'f'

'false'

'n'

'no'

'off'

'0'

前导或尾随空白将被忽略，大小写无关。使用TRUE和FALSE 这样的字眼比较好(也是SQL兼容的用法)。

下面例子显示了用字母t 和f输出boolean值的例子。

例  使用 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 est

SELECT * FROM test1 WHERE a;
 a |    b
---+---------
 t | sic est

---

七、枚举类型

枚举（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 name
FROM person
WHERE 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');

如果你确实需要做这样的事情，你可以写一个自定义的操作符或者在查询中加上显式造型：

SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood::text = holidays.happiness::text;

7.4 实现细节

枚举标签是大小写敏感的，因此'happy'与'HAPPY'是不同的。标签中的空格也是有意义的。

尽管枚举类型的主要目的是用于值的静态集合，但也有方法在现有枚举类型中增加新值和重命名值（见ALTER TYPE）。不能从枚举类型中去除现有的值，也不能更改这些值的排序顺序，如果要那样做可以删除并且重建枚举类型。

一个枚举值在磁盘上占据4个字节。一个枚举值的文本标签的长度受限于NAMEDATALEN设置，该设置被编译在PostgreSQL中，在标准编译下它表示最多63字节。

从内部枚举值到文本标签的翻译被保存在系统目录pg_enum中。可以直接查询该目录。

---

八、几何类型

几何数据类型表示二维的空间物体。下表展示了PostgreSQL中可以用的几何类型。

几何类型

名字	存储尺寸	表示	描述
point	16字节	平面上的点	(x,y)
line	32字节	无限长的线	{A,B,C}
lseg	32字节	有限线段	((x1,y1),(x2,y2))
box	32字节	矩形框	((x1,y1),(x2,y2))
path	16+16n字节	封闭路径（类似于多边形）	((x1,y1),...)
path	16+16n字节	开放路径	[(x1,y1),...]
polygon	40+16n字节	多边形（类似于封闭路径）	((x1,y1),...)
circle	24字节	圆	<(x,y),r>（中心点和半径）

我们有一系列丰富的函数和操作符可用来进行各种几何操作， 如缩放、平移、旋转和计算相交等 它们在下一章中解释。

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是圆的半径。

圆的输出用第一种语法。

---

九、网络地址类型

PostgreSQL提供用于存储 IPv4、IPv6 和 MAC 地址的数据类型，如下表所示。 用这些数据类型存储网络地址比用纯文本类型好，因为这些类型提供输入错误检查以及特殊的操作符和函数。

网络地址类型

名字	存储尺寸	描述
cidr	7或19字节	IPv4和IPv6网络
inet	7或19字节	IPv4和IPv6主机以及网络
macaddr	6字节	MAC地址
macaddr8	8 bytes	MAC地址（EUI-64格式）

在对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， 那么掩码部分用旧的有类的网络编号系统进行计算，否则它将至少大到足以包括写在输入中的所有字节。声明一个在其指定的掩码右边置了位的网络地址会导致错误。

下表展示了一些例子。

cidr类型输入例子

cidr输入	cidr输出	abbrev(cidr)
192.168.100.128/25	192.168.100.128/25	192.168.100.128/25
192.168/24	192.168.0.0/24	192.168.0/24
192.168/25	192.168.0.0/25	192.168.0.0/25
192.168.1	192.168.1.0/24	192.168.1/24
192.168	192.168.0.0/24	192.168.0/24
128.1	128.1.0.0/16	128.1/16
128	128.0.0.0/16	128.0/16
128.1.2	128.1.2.0/24	128.1.2/24
10.1.2	10.1.2.0/24	10.1.2/24
10.1	10.1.0.0/16	10.1/16
10	10.0.0.0/8	10/8
10.1.2.3/32	10.1.2.3/32	10.1.2.3/32
2001:4f8:3:ba::/64	2001:4f8:3:ba::/64	2001:4f8:3:ba::/64
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128	2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128	2001:4f8:3:ba:2e0:81ff:fe22:d1f1
::ffff:1.2.3.0/120	::ffff:1.2.3.0/120	::ffff:1.2.3/120
::ffff:1.2.3.0/128	::ffff:1.2.3.0/128	::ffff:1.2.3.0/128

9.3 inet  vs. cidr

inet和cidr类型之间的本质区别是inet接受右边有非零位的网络掩码， 而cidr不接受。例如，192.168.0.1/24对inet是有效的，但对cidr是无效的。

TIPS:  

如果你不喜欢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顺序。

剩下的五种输入格式不属于任何标准。

9.5 macaddr8

macaddr8类型以EUI-64格式存储MAC地址，例如以太网卡的硬件地址（尽管MAC地址也被用于其他目的）。这种类型可以接受6字节和8字节长度的MAC地址，并且将它们存储为8字节长度的格式。以6字节格式给出的MAC地址被存储为8字节长度格式的方式是吧第4和第5字节分别设置为FF和FE。 注意IPv6使用一种修改过的EUI-64格式，其中从EUI-48转换过来后的第7位应该被设置为一。函数macaddr8_set7bit被用来做这种修改。 一般而言，任何由16进制数（字节边界上）对构成的输入（可以由':'、'-'或者'.'统一地分隔）都会被接受。16进制数的数量必须是16（8字节）或者12（6字节）。前导和拖尾的空格会被忽略。 下面是可以被接受的输入格式的例子：

'08:00:2b:01:02:03:04:05'

'08-00-2b-01-02-03-04-05'

'08002b:0102030405'

'08002b-0102030405'

'0800.2b01.0203.0405'

'0800-2b01-0203-0405'

'08002b01:02030405'

'08002b0102030405'

这些例子都指定相同的地址。数字a到f的大小写形式都被接受。输出总是以上面显示的第一种形式。 上述的后六种输入格式不属于任何标准。 要把EUI-48格式的传统48位MAC地址转换成修改版EUI-64格式（包括在IPv6地址中作为主机部分），可以使用下面的macaddr8_set7bit：

SELECT macaddr8_set7bit('08:00:2b:01:02:03');

    macaddr8_set7bit     
-------------------------
 0a:00:2b:ff:fe:01:02:03
(1 row)

---

十、位串类型

位串就是一串 1 和 0 的串。它们可以用于存储和可视化位掩码。我们有两种类型的 SQL 位类型：bit(n)和bit varying(n)，其中 n是一个正整数。

bit类型的数据必须准确匹配长度n; 试图存储短些或者长一些的位串都是错误的。bit varying数据是最长n的变长类型，更长的串会被拒绝。写一个没有长度的bit等效于 bit(1)，没有长度的bit varying意味着没有长度限制。

NOTES:     

        如果我们显式地把一个位串值转换成bit(n)， 那么它的右边将被截断或者在右边补齐零，直到刚好n位， 而且不会抛出任何错误。类似地，如果我们显式地把一个位串数值转换成bit varying(n)，如果它超过了n位， 那么它的右边将被截断。

请参考前面获取有关位串常量的语法的信息。还有一些位逻辑操作符和串操作函数可用。

使用位串类型

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个字节，这取决于串的长度（但是长值可能被压缩或者移到线外，如前面对字符串的解释一样）。

---

十一、文本搜索类型

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'

可选地，一个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被处理后的形式。

---

十二、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}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

输出总是采用标准形式。

PostgreSQL为UUID提供了存储和比较函数，但是核心数据库不包含任何用于产生UUID的函数，因为没有一个单一算法能够很好地适应每一个应用。uuid-ossp模块提供了实现一些标准算法的函数。 pgcrypto模块也为随机 UUID 提供了一个生成函数。 此外，UUID可以由客户端应用产生，或者由通过服务器端函数调用的其他库生成。

---

十三、XML类型

xml数据类型可以被用来存储XML数据。它比直接在一个text域中存储XML数据的优势在于，它会检查输入值的结构是不是良好，并且有支持函数用于在其上执行类型安全的操作。使用这种数据类型要求在安装时用configure --with-libxml选项编译。

xml类型可以存储结构良好（如XML标准所定义）的“文档”，以及“内容”片段，它们由XML标准中的XMLDecl? content产品所定义。粗略地看，这意味着内容片段中可以有多于一个的顶层元素或字符节点。表达式xmlvalue IS DOCUMENT可以被用来评估一个特定的xml值是一个完整文档或者仅仅是一个文档片段。

13.1 创建XML值

要从字符数据中生成一个xml类型的值，可以使用函数xmlparse:

XMLPARSE ( { DOCUMENT | CONTENT } value)

例子：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

然而根据SQL标准这是唯一将字符串转换为XML值的方法，PostgreSQL特有的语法：

xml '<foo>bar</foo>'
'<foo>bar</foo>'::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数据都被允许。

NOTES:

        在使用默认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时，计算效率将会最高。

WARNING：

        当服务器编码不是UTF-8时，某些XML相关的函数可能在非ASCII数据上完全无法工作。尤其在xmltable()和xpath()上，这是一个已知的问题。

13.3 访问XML值

xml数据类型有些不同寻常，因为它不提供任何比较操作符。这是因为对于XML数据不存在良定义的和通用的比较算法。这种状况造成的后果就是，你无法通过比较一个xml和一个搜索值来检索行。XML值因此通常应该伴随着一个独立键值域，如一个ID。另一种比较XML值的方案是将它们先转换为字符串，但注意字符串比较对于XML比较方法没有什么帮助。

由于没有可以用于xml数据类型的比较操作符，因此无法直接在这种类型上创建索引。如果需要在XML中快速的搜索，可能的解决方案包括将表达式造型为一个字符串类型然后索引之，或者在一个XPath表达式上索引。当然，实际的查询必须被调整为使用被索引的表达式。

PostgreSQL中的文本搜索功能也可以被用来加速XML数据的全文搜索。但是，所需的预处理支持目前在PostgreSQL发布中还不可用。

---

十四、JSON类型

JSON 数据类型是用来存储 JSON（JavaScript Object Notation） 数据的。这种数据也可以被存储为text，但是 JSON 数据类型的 优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函 数和操作符可以用于存储在这些数据类型中的数据。

有两种 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 字符进 行存储，这包括把代理对折叠成一个单一字符。

 NOTES：

        下一章节中描述的 JSON 处理函数将把 Unicode 转义转换成常规字符，并且将因此抛出和刚才所描述的同样类型的错误（即使它们 的输入是类型json而不是jsonb）。json的 输入函数不做这些检查是由来已久的，不过它确实允许将 JSON Unicode 转义简单 的（不经处理）存储在一个非 UTF8 数据库编码中。通常，最好尽可能避免在一个非 UTF8 数据库编码的 JSON 中混入 Unicode 转义。

在把文本 JSON 输入转换成jsonb时，RFC 7159描述 的基本类型会被有效地映射到原生的 PostgreSQL类型（如 下表中所示）。因此，在合法 jsonb数据的组成上有一些次要额外约束，它们不适合 json类型和抽象意义上的 JSON，这些约束对应于有关哪些东西不 能被底层数据类型表示的限制。尤其是，jsonb将拒绝位于 PostgreSQL numeric数据类型范 围之外的数字，而json则不会。这类实现定义的限制是 RFC 7159 所允许的。不过，实际上这类问题更可能发生在其他实 现中，因为把 JSON 的number基本类型表示为 IEEE 754 双精度浮点 是很常见的（这也是RFC 7159 明确期待和允许的）。当在这类系 统间使用 JSON 作为一种交换格式时，应该考虑丢失数字精度的风险。

相反地，如表中所述，有一些 JSON 基本类型输入格式上的次要限制并不适用于相 应的PostgreSQL类型。

JSON 基本类型和相应的PostgreSQL类型

JSON 基本类型	PostgreSQL类型	注释
string	text	不允许\u0000，如果数据库编码不是 UTF8，非 ASCII Unicode 转义也是这样
number	numeric	不允许NaN 和 infinity值
boolean	boolean	只接受小写true和false拼写
null	(无)	SQL NULL是一个不同的概念

14.1 JSON输入和输出语法

下列都是合法的json（或者jsonb）表达式：

-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '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 对象比数组更适合于做包含或存在测试， 因为它们不像数组，进行搜索时会进行内部优化，并且不需要被线性搜索。

TIPS:

        由于 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 值的顶层 查找指定的键或数组元素。

下一章中会详细介绍更多个包含和存在操作符，以及所有其他JSON操作符和函数。

14.4 jsonb索引

GIN 索引可以被用来有效地搜索在大量jsonb文档（数据）中出现 的键或者键值对。提供了两种 GIN “操作符类”，它们在性能和灵活 性方面做出了不同的平衡。

jsonb的默认 GIN 操作符类支持使用@>、 ?、?&以及?|操作符的查询（这些 操作符实现的详细语义请见下一章）。 使用这种操作符类创建一个索引的例子：

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"]}';

doc列上的一个简单 GIN 索引就能支持这个查询。 但是注意这样一个索引将会存储jdoc列中每一个键 和值的拷贝，然而前一个例子的表达式索引只存储tags 键下找到的数据。虽然简单索引的方法更加灵活（因为它支持有关任 意键的查询），定向的表达式索引更小并且搜索速度比简单索引更快。

尽管jsonb_path_ops操作符类只支持用 @>操作符的查询，但它比起默认的操作符类 jsonb_ops有更客观的性能优势。一个 jsonb_path_ops索引通常也比一个相同数据上的 jsonb_ops要小得多，并且搜索的专一性更好，特 别是当查询包含频繁出现在该数据中的键时。因此，其上的搜索操作 通常比使用默认操作符类的搜索表现更好。

jsonb_ops和jsonb_path_ops GIN 索引之间的技术区别是前者为数据中的每一个键和值创建独立的索引项， 而后者值为该数据中的每个值创建索引项。 基本上，每一个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 数据类型相同的比较规则进行。字符串的比较会使用默认的数据库排序规则。

14.5 转换

有一些附加的扩展可以为不同的过程语言实现jsonb类型的转换。

PL/Perl的扩展被称作jsonb_plperl和jsonb_plperlu。如果使用它们，jsonb值会视情况被映射为Perl的数组、哈希和标量。

PL/Python的扩展被称作jsonb_plpythonu、jsonb_plpython2u和jsonb_plpython3u。如果使用它们，jsonb值会视情况被映射为Python的词典、列表和标量。

---

十五、数组

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 dimensions

ARRAY构造器语法也可以被用于：

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常数或表达式，例如，字符串文字使用单引号而不是双引号包围，因为双引号可以出现在一个数组文字中。

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][1:1]而不是[2][1:1]。

可以省略一个切片说明符的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][1:2]，那么引用schedule[3][3]将得到NULL。相似地，使用错误的下标号引用一个数组会得到空值而不是错误。

如果数组本身或者任何一个下标表达式为空，则一个数组切片表达式也会得到空值。但是，在其他情况例如选择一个完全位于当前数组边界之外的切片时，一个切片表达式会得到一个空（零维）数组而不是空值（由于历史原因，这并不符合非切片行为）。 如果所请求的切片和数组边界重叠，那么它会被缩减为重叠的区域而不是返回空。

任何数组值的当前维度可以使用array_dims函数获得：

ELECT 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来获得维度，它们将分别返回一个指定数组的上界和下界：

# 上界  array_upper(字段名, 数组下标)

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)


# 下界   array_lower(字段名, 数组下标)

SELECT array_lower(schedule, 1) FROM sal_emp WHERE name = 'Carol';
 array_lower 
-------------
           1
(1 rows)

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

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;

我们也可以使用&&操作符来搜索一个数组，它会检查左操作数是否与右操作数重叠。例如：

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
-----------------
 2

SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
 array_positions
-----------------
 {1,4,8}

TIPS:

        数组不是集合，在其中搜索指定数组元素可能是数据设计失误的表现。考虑使用一个独立的表来替代，其中每一行都对应于一个数组元素。这将更有利于搜索，并且对于大量元素的可扩展性更好。

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的字符串也必须使用双引号。要把一个双引号或反斜线放在一个使用了双引号的数组元素值中，需要在它前面放一个反斜线。作为一种选择，我们可以免去使用引号而使用反斜线转义的方式来保护可能被认为是数组语法的所有数据字符。

我们可以在左括号前面或右括号后面增加空白。我们也可以在任何单独的项之前或之后加上空白。在所有这些情况中空白将被忽略。但是，在被使用了双引号的元素中的空白以及周围有其他非空白字符的空白不会被忽略。

TIPS:

        在SQL命令中写数组值时，ARRAY构造器语法常常比数组文字语法要更容易使用。在ARRAY中，单独的元素值可以使用不属于数组成员时的方式来书写。

---

十六、组合类型

一个组合类型表示一行或一个记录的结构，它本质上就是一个域名和它们数据类型的列表。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);

或函数:

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS '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组合类型将成为一种副产品，并且可以按上面所说的进行使用。不过要注意当前实现的一个重要限制：因为没有约束与一个组合类型相关，显示在表定义中的约束不会应用于表外组合类型的值（要解决这个问题，可以在该组合类型上创建一个域，并且把想要的约束应用为这个域上的CHECK约束）。

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)

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 在查询中使用组合类型

对于查询中的组合类型有各种特殊的语法规则和行为。这些规则提供了有用的捷径，但是如果你不懂背后的逻辑就会被此困扰。

在PostgreSQL中，查询中对一个表名（或别名）的引用实际上是对该表的当前行的组合值的引用。例如，如果我们有一个如上所示的表inventory_item，我们可以写：

SELECT c FROM inventory_item c;

这个查询产生一个单一组合值列，所以我们会得到这样的输出：

        c
------------------------
 ("fuzzy dice",42,1.99)
(1 row)

不过要注意简单的名称会在表名之前先匹配到列名，因此这个例子可行的原因仅仅是因为在该查询的表中没有名为c的列。

普通的限定列名语法table_name.column_name可以理解为把字段选择应用在该表的当前行的组合值上（由于效率的原因，实际上不是以这种方式实现）。

当我们写

SELECT c.* FROM inventory_item c;

时，根据SQL标准，我们应该得到该表展开成列的内容：

   name    | supplier_id | price
------------+-------------+-------
 fuzzy dice |          42 |  1.99
(1 row)

就好像查询是

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

PostgreSQL将对任何组合值表达式应用这种展开行为，但只要.*所应用的值不是一个简单的表名，你就需要把该值写在圆括号内。例如，如果myfunc()是一个返回组合类型的函数，该组合类型由列a、b和c组成，那么这两个查询有相同的结果：

SELECT (myfunc(x)).* FROM some_table;
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;

TIPS:

        PostgreSQL实际上通过将第一种形式转换为第二种来处理列展开。因此，在这个例子中，用两种语法时对每行都会调用myfunc()三次。如果它是一个开销很大的函数，你可能希望避免这样做，所以可以用一个这样的查询：

        SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;

        把该函数放在一个LATERAL FROM项中会防止它对每一行被调用超过一次。m.*仍然会被展开为m.a, m.b, m.c，但现在那些变量只是对这个FROM项的输出的引用（这里关键词LATERAL是可选的，但我们在这里写上它是为了说明该函数从some_table中得到x）。

当composite_value.*出现在一个SELECT输出列表的顶层中、INSERT/UPDATE/DELETE中的一个RETURNING列表中、一个VALUES子句中或者一个行构造器中时，该语法会导致这种类型的列展开。在所有其他上下文（包括被嵌入在那些结构之一中时）中，把.*附加到一个组合值不会改变该值，因为它表示“所有的列”并且因此同一个组合值会被再次产生。例如，如果somefunc()接受一个组合值参数，这些查询是相同的：

SELECT somefunc(c.*) FROM inventory_item c;
SELECT somefunc(c) FROM inventory_item c;

在两种情况中，inventory_item的当前行被传递给该函数作为一个单一的组合值参数。即使.*在这类情况中什么也不做，使用它也是一种好的风格，因为它说清了一个组合值的目的是什么。特别地，解析器将会认为c.*中的c是引用一个表名或别名，而不是一个列名，这样就不会出现混淆。而如果没有.*，就弄不清楚c到底是表示一个表名还是一个列名，并且在有一个名为c的列时会优先选择按列名来解释。

另一个演示这些概念的例子是下面这些查询，它们表示相同的东西：

SELECT * FROM inventory_item c ORDER BY c;
SELECT * FROM inventory_item c ORDER BY c.*;
SELECT * FROM inventory_item c ORDER BY ROW(c.*);

所有这些ORDER BY子句指定该行的组合值，不过，如果inventory_item包含一个名为c的列，第一种情况会不同于其他情况，因为它表示仅按那一列排序。给定之前所示的列名，下面这些查询也等效于上面的那些查询：

SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);
SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);

（最后一种情况使用了一个省略关键字ROW的行构造器）。

另一种与组合值相关的特殊语法行为是，我们可以使用函数记法来抽取一个组合值的字段。解释这种行为的简单方式是记法field(table)和table.field是可以互换的。例如，这些查询是等效的：

SELECT c.name FROM inventory_item c WHERE c.price > 1000;
SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;

此外，如果我们有一个函数接受单一的组合类型参数，我们可以以任意一种记法来调用它。这些查询全都是等效的：

SELECT somefunc(c) FROM inventory_item c;
SELECT somefunc(c.*) FROM inventory_item c;
SELECT c.somefunc FROM inventory_item c;

这种函数记法和字段记法之间的等效性使得我们可以在组合类型上使用函数来实现“计算字段”。 一个使用上述最后一种查询的应用不会直接意识到somefunc不是一个真实的表列。

TIPS:

        由于这种行为，让一个接受单一组合类型参数的函数与该组合类型的任意字段具有相同的名称是不明智的。出现歧义时，如果使用了字段名语法，则字段名解释将被选择，而如果使用的是函数调用语法则会选择函数解释。不过，PostgreSQL在版本11之前总是选择字段名解释，除非该调用的语法要求它是一个函数调用。在老的版本中强制函数解释的一种方法是用方案限定函数名，也就是写成schema.func(compositevalue)。

16.6 组合类型输入和输出语法

一个组合值的外部文本表达由根据域类型的 I/O 转换规则解释的项，外加指示组合结构的装饰组成。装饰由整个值周围的圆括号（(和)），外加相邻项之间的逗号（,）组成。圆括号之外的空格会被忽略，但是在圆括号之内空格会被当成域值的一部分，并且根据域数据类型的输入转换规则可能有意义，也可能没有意义。例如，在

'(  42)'

中，如果域类型是整数则空格会被忽略，而如果是文本则空格不会被忽略。

如前所示，在写一个组合值时，你可以在任意域值周围写上双引号。如果不这样做会让域值迷惑组合值解析器，你就必须这么做。特别地，包含圆括号、逗号、双引号或反斜线的域必须用双引号引用。要把一个双引号或者反斜线放在一个被引用的组合域值中，需要在它前面放上一个反斜线（还有，一个双引号引用的域值中的一对双引号被认为是表示一个双引号字符，这和 SQL 字符串中单引号的规则类似）。另一种办法是，你可以避免引用以及使用反斜线转义来保护所有可能被当作组合语法的数据字符。

一个全空的域值（在逗号或圆括号之间完全没有字符）表示一个 NULL。要写一个空字符串值而不是 NULL，可以写成""。

如果域值是空串或者包含圆括号、逗号、双引号、反斜线或空格，组合输出例程将在域值周围放上双引号（对空格这样处理并不是不可缺少的，但是可以提高可读性）。嵌入在域值中的双引号及反斜线将被双写。

WARNING:

        记住你在一个 SQL 命令中写的东西将首先被解释为一个字符串，然后才会被解释为一个组合。这就让你所需要的反斜线数量翻倍（假定使用了转义字符串语法）。例如，要在组合值中插入一个含有一个双引号和一个反斜线的text域，你需要写成：

        INSERT ... VALUES ('("\"\\")');

        字符串处理器会移除一层反斜线，这样在组合值解析器那里看到的就会是("\"\\")。接着，字符串被交给text数据类型的输入例程并且变成"\（如果我们使用的数据类型的输入例程也会特别处理反斜线，例如bytea，在命令中我们可能需要八个反斜线用来在组合域中存储一个反斜线）。美元引用（见第 4.1.2.4 节）可以被用来避免双写反斜线。

TIPS:

        当在 SQL 命令中书写组合值时，ROW构造器语法通常比组合文字语法更容易使用。在ROW中，单个域值可以按照平时不是组合值成员的写法来写。

---

十七、范围类型

范围类型是表达某种元素类型（称为范围的subtype）的一个值的范围的数据类型。例如，timestamp的范围可以被用来表达一个会议室被保留的时间范围。在这种情况下，数据类型是tsrange（“timestamp range”的简写）而timestamp是 subtype。subtype 必须具有一种总体的顺序，这样对于元素值是在一个范围值之内、之前或之后就是界线清楚的。

范围类型非常有用，因为它们可以表达一种单一范围值中的多个元素值，并且可以很清晰地表达诸如范围重叠等概念。用于时间安排的时间和日期范围是最清晰的例子；但是价格范围、一种仪器的量程等等也都有用。

17.1 内建范围类型

PostgreSQL 带有下列内建范围类型：

• int4range — integer的范围

• int8range — bigint的范围

• numrange — numeric的范围

• tsrange — 不带时区的 timestamp的范围

• tstzrange — 带时区的 timestamp的范围

• daterange — date的范围

此外，你可以定义自己的范围类型

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

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 字符串中的单引号规则类似）。此外，你可以避免引用并且使用反斜线转义来保护所有数据字符，否则它们会被当做返回语法的一部分。还有，要写一个是空字符串的界限值，则可以写成""，因为什么都不写表示一个无限界限。

范围值前后允许有空格，但是圆括号或方括号之间的任何空格会被当做上下界值的一部分（取决于元素类型，它可能是也可能不是有意义的）。

例子：

-- 包括 3，不包括 7，并且包括 3 和 7 之间的所有点
SELECT '[3,7)'::int4range;

-- 既不包括 3 也不包括 7，但是包括之间的所有点
SELECT '(3,7)'::int4range;

-- 只包括单独一个点 4
SELECT '[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;

17.9 索引

可以为范围类型的表列创建 GiST 和 SP-GiST 索引。例如，要创建一个 GiST 索引：

CREATE INDEX reservation_idx ON reservation USING GIST (during);

一个 GiST 或 SP-GiST 索引可以加速涉及以下范围操作符的查询： =、 &&、 <@、 @>、 <<、 >>、 -|-、 &<以及 &> 。

此外，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 1

INSERT 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")) conflicts
with 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 1

INSERT 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")) conflicts
with 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

---

十八、域类型

域是一种用户定义的数据类型，它基于另一种底层类型。根据需要，它可以有约束来限制其有效值为底层类型所允许值的一个子集。如果没有约束，它的行为就和底层类型一样 — 例如，任何适用于底层类型的操作符或函数都对该域类型有效。底层类型可以是任何内建或者用户定义的基础类型、枚举类型、数组类型、组合类型、范围类型或者另一个域。

例如，我们可以在整数之上创建一个域，它只接受正整数：

CREATE DOMAIN posint AS integer CHECK (VALUE > 0);
CREATE TABLE mytable (id posint);
INSERT INTO mytable VALUES(1);   -- works
INSERT INTO mytable VALUES(-1);  -- fails

当底层类型的一个操作符或函数适用于一个域值时，域会被自动向下造型为底层类型。因此，mytable.id - 1的结果会被认为是类型integer而不是posint。我们可以写成(mytable.id - 1)::posint来把结果转换回posint，这会导致域的约束被重新检查。在这种情况下，如果该表达式被应用于一个值为1的id就会错误。把底层类型的值赋给域类型的一个字段或者变量不需要写显式的造型，但是域的约束将会被检查。

---

十九、对象标识符类型

对象标识符（OID）被PostgreSQL用来在内部作为多个系统表的主键。OID不会被添加到用户创建的表中，除非在创建表时指定了WITH OIDS或者default_with_oids配置变量被启用。类型oid表示一个对象标识符。也有多个oid的别名类型：regproc、regprocedure、regoper、regoperator、regclass、regtype、regrole、regnamespace、regconfig和regdictionary。下表显示了一个概览。

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来实现。

对象标识符类型

名字	引用	描述	值示例
oid	任意	数字形式的对象标识符	564182
regproc	pg_proc	函数名字	sum
regprocedure	pg_proc	带参数类型的函数	sum(int4)
regoper	pg_operator	操作符名字	+
regoperator	pg_operator	带参数类型的操作符	*(integer,integer) or -(NONE,integer)
regclass	pg_class	关系名字	pg_type
regtype	pg_type	数据类型名字	integer
regrole	pg_authid	角色名	smithee
regnamespace	pg_namespace	名字空间名称	pg_catalog
regconfig	pg_ts_config	文本搜索配置	english
regdictionary	pg_ts_dict	文本搜索字典	simple

所有用于由名字空间组织的对象的 OID 别名类型都接受模式限定的名字，如果没有被限定的对象在当前搜索路径中无法找到时，将会在输出时显示模式限定的名字。regproc和regoper别名类型将只接受唯一的（非重载的）输入名字，因此它们的使用是受限的；对于大多数使用，regprocedure或regoperator更合适。对于regoperator，通过使用NONE来替代未使用的操作数可以标识一元操作符。

大部分 OID 别名类型的一个附加性质是依赖性的创建。如果这些类型之一的一个常量出现在一个存储的表达式（如一个列默认值表达式或视图）中，它会在被引用的对象上创建一个依赖。例如，如果一个列有一个默认值表达式nextval('my_seq'::regclass)，PostgreSQL会理解该默认值表达式是依赖于序列my_seq的，在删除该默认值表达式之前系统将不允许删除该序列。regrole是这个性质的唯一例外。这种类型的常量不允许出现在这类表达式中。

NOTES:

        OID 别名类型不完全遵循事务隔离规则。规划器也把它们当做简单常量， 这可能会导致次优的规划。

另一种系统中使用的标识符类型是xid，或者称为事务（简写为xact）标识符。这是系统列xmin和xmax使用的数据类型。事务标识符是32位量。

系统使用的第三种标识符类型是cid，或者称为命令标识符。这是系统列cmin和cmax使用的数据类型。命令标识符也是32位量。

系统使用的最后一种标识符类型是tid，或者称为元组标识符（行标识符）。这是系统列ctid使用的数据类型。一个元组ID是一个（块号，块内元组索引）对，它标识了行在它的表中的物理位置。

---

二十、pg_lsn类型

pg_lsn数据类型可以被用来存储 LSN（日志序列号）数据，LSN 是一个指向WAL中的位置的指针。这个类型是XLogRecPtr的一种表达并且是 PostgreSQL的一种内部系统类型。

在内部，一个 LSN 是一个 64 位整数，表示在预写式日志流中的一个字节位置。它被打印成 两个最高 8 位的十六进制数，中间用斜线分隔，例如16/B374D848。 pg_lsn类型支持标准的比较操作符，如=和 >。两个 LSN 可以用-操作符做减法， 结果将是分隔两个预写式日志位置的字节数。

---

二十一、伪类型

PostgreSQL类型系统包含了一些特殊目的的项，它们被统称为伪类型。一个伪类型不能被用作一个列的数据类型，但是它可以被用来定义一个函数的参数或者结果类型。每一种可用的伪类型都有其可以发挥作用的情况，这些情况的特点是一个函数的行为并不能符合于简单使用或者返回一种特定SQL数据类型的值。下表列出了现有的伪类型。

伪类型

名字	描述
any	表示一个函数可以接受任意输入数据类型。
anyelement	表示一个函数可以接受任意数据类型。
anyarray	表示一个函数可以接受任意数组数据类型
anynonarray	表示一个函数可以接受任意非数组数据类型
anyenum	表示一个函数可以接受任意枚举数据类型
anyrange	表示一个函数可以接受任意范围数据类型
cstring	表示一个函数接受或者返回一个非空结尾的C字符串。
internal	表示一个函数接受或返回一个服务器内部数据类型。
language_handler	一个被声明为返回language_handler的过程语言调用处理器。
fdw_handler	一个被声明为返回fdw_handler的外部数据包装器处理器。
index_am_handler	一个被声明为返回index_am_handler索引访问方法处理器。
tsm_handler	一个被声明为返回tsm_handler的表采样方法处理器。
record	标识一个接收或者返回一个未指定的行类型的函数。
trigger	一个被声明为返回trigger的触发器函数。
event_trigger	一个被声明为返回event_trigger的事件触发器函数。
pg_ddl_command	标识一种对事件触发器可用的 DDL 命令的表达。
void	表示一个函数不返回值。
unknown	标识一种还未被解析的类型，例如一个未修饰的字符文本。
opaque	一种已被废弃的类型名称，以前它用于实现以上的很多种目的。

用C编写的函数（不管是内建的还是动态载入的）可以被声明为接受或返回这些为数据类型的任意一种。函数的作者应当保证当一个伪类型被用作一个参数类型时函数的行为是安全的。

用过程语言编写的函数只有在其实现语言允许的情况下才能使用伪类型。目前大部分过程语言都禁止使用伪类型作为一种参数类型，并且只允许使用void和record作为结果类型（如果函数被用于一个触发器或者事件触发器， trigger或者event_trigger也被允许作为结果类型）。某些过程语言也支持在多态函数中使用类型anyelement、anyarray、anynonarray、anyenum和anyrange。

internal伪类型用于定义只在数据库系统内部调用的函数，这些函数不会被SQL直接调用。如果一个函数拥有至少一个internal类型的参数，则它不能从SQL中被调用。为了保持这种限制的类型安全性，遵循以下编码规则非常重要：不要创建任何被声明要返回internal的函数，除非它有至少一个internal参数。