本文档为PostgreSQL 9.6.0文档，本转载已得到原译者彭煜玮授权。
1. 逻辑操作符
常用的逻辑操作符有：
- AND
- OR
- NOT
SQL使用三值的逻辑系统，包括真、假和null，null表示"未知"。观察下面的真值表：
操作符AND和OR是可交换的，也就是说，你可以交换左右操作数而不影响结果。
2. 比较函数和操作符
常见的比较操作符都可用，如Table 9-1所示。
Table 9-1. 比较操作符
Note:
!=操作符在分析器阶段被转换成<>。不能把!=和<>操作符实现为做不同的事。
比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符，它们返回boolean类型；类似于1 < 2 < 3的表达式是非法的（因为没有<操作符可以比较一个布尔值和3）。
如Table 9-2所示，也有一些比较谓词。它们的行为和操作符很像，但是具有 SQL 标准所要求的特殊语法。
Table 9-2. 比较谓词
BETWEEN谓词可以简化范围测试：
a BETWEEN x AND y等效于
a >= x AND a <= y注意BETWEEN认为终点值是包含在范围内的。 NOT BETWEEN可以做相反比较：
a NOT BETWEEN x AND y等效于
a < x OR a > yBETWEEN SYMMETRIC和BETWEEN相似，不过BETWEEN SYMMETRIC不要求AND左边的参数小于或等于右边的参数。如果左参数不是小于等于右参数，这两个参数会自动被交换，这样总是会应用一个非空范围。
当有一个输入为空时，普通的比较操作符会得到空（表示"未知"），而不是真或假。例如，7 = NULL得到空，7 <> NULL也一样。如果这种行为不合适，可以使用IS [ NOT ] DISTINCT FROM谓词：
a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b对于非空输入，IS DISTINCT FROM和<>操作符一样。不过，如果两个输入都为空，它会返回假。而如果只有一个输入为空，它会返回真。类似地，IS NOT DISTINCT FROM对于非空输入的行为与=相同，但是当两个输入都为空时它返回真，并且当只有一个输入为空时返回假。因此，这些谓词实际上把空值当作一种普通数据值而不是"unknown"。
要检查一个值是否为空，使用下面的谓词：
expression IS NULL
expression IS NOT NULL或者等效，但并不标准的谓词：
expression ISNULL
expression NOTNULL不要写expression = NULL，因为NULL是不"等于"NULL的（控制代表一个未知的值，因此我们无法知道两个未知的数值是否相等）。
Tip: 有些应用可能要求表达式expression = NULL在expression得出空值时返回真。我们强烈建议这样的应用修改成遵循 SQL 标准。但是，如果这样修改不可能完成，那么我们可以使用配置变量transform_null_equals。如果打开它，PostgreSQL将把x = NULL子句转换成x IS NULL。
如果expression是行值，那么当行表达式本身为非空值或者行的所有域为非空时IS NULL为真。由于这种行为，IS NULL和IS NOT NULL并不总是为行值表达式返回反转的结果，特别是，一个同时包含 NULL 和非空值的域将会对两种测试都返回假。在某些情况下，写成row IS DISTINCT FROM NULL或者row IS NOT DISTINCT FROM NULL会更好，它们只会检查整个行值是否为空而不需要在行的域上做额外的测试。
布尔值也可以使用下列谓词进行测试：
boolean_expression IS TRUE
boolean_expression IS NOT TRUE
boolean_expression IS FALSE
boolean_expression IS NOT FALSE
boolean_expression IS UNKNOWN
boolean_expression IS NOT UNKNOWN这些谓词将总是返回真或假，从来不返回空值，即使操作数是空也如此。空值输入被当做逻辑值"未知"。 请注意实际上IS UNKNOWN和IS NOT UNKNOWN分别与IS NULL和IS NOT NULL相同， 只是输入表达式必须是布尔类型。
如Table 9-3中所示，也有一些比较相关的函数可用。
Table 9-3. 比较函数
3. 数学函数和操作符
PostgreSQL为很多类型提供了数学操作符。对于那些没有标准数学表达的类型（如日期/时间类型），我们将在后续小节中描述实际的行为。
Table 9-4展示了所有可用的数学操作符。
Table 9-4. 数学操作符
按位操作操作符只能用于整数数据类型，而其它的操作符可以用于全部数字数据类型。按位操作的操作符还可以用于位串类型bit和bit varying， 如Table 9-13所示。
Table 9-5显示了可用的数学函数。在该表中，dp表示double precision。这些函数中有许多都有多种不同的形式，区别是参数不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。 处理double precision数据的函数大多数是在宿主系统的 C 库基础上实现的；因此，边界情况下的准确度和行为是根据宿主系统而变化的。
Table 9-5. 数学函数
Table 9-6展示了用于产生随机数的函数。
Table 9-6. 随机函数
random()返回的值的特征取决于系统实现。 它不适合用于加密应用，如果需要用于加密应用请参考pgcrypto模块。
最后，Table 9-7显示了可用的三角函数。所有三角函数都有类型为double precision的参数和返回类型。每一种三角函数都有两个变体，一个以弧度度量角，另一个以角度度量角。
Table 9-7. 三角函数
Note:
另一种使用以角度度量的角的方法是使用早前展示的单位转换函数radians()和degrees()。不过，使用基于角度的三角函数更好，因为这类方法能避免sind(30)等特殊情况下的舍入偏差。
4. 字符串函数和操作符
本节描述了用于检查和操作字符串值的函数和操作符。在这个环境中的串包括所有类型character、character varying和text的值。除非另外说明，所有下面列出的函数都可以处理这些类型，不过要小心的是，在使用character类型的时候， 它有自动填充空白的潜在影响。有些函数还可以处理位串类型。
SQL定义了一些字符串函数，它们使用关键字，而不是逗号来分隔参数。详情请见Table 9-8，PostgreSQL也提供了这些函数使用正常函数调用语法的版本（见Table 9-9）。
Note: 由于存在从那些数据类型到text的隐式强制措施，在PostgreSQL 8.3之前，这些函数也可以接受多种非字符串数据类型。这些强制措施在目前的版本中已经被删除，因为它们常常导致令人惊讶的行为。不过，字符串串接操作符（||）仍然接受非字符串输入，只要至少一个输入是一种字符串类型，如Table 9-8所示。对于其他情况，如果你需要复制之前的行为，可以为text插入一个显式强制措施。
Table 9-8. SQL字符串函数和操作符
还有额外的串操作函数可以用，它们在Table 9-9中列出。它们有些在内部用于实现Table 9-8列出的SQL标准字符串函数。
Table 9-9. 其他字符串函数
concat、concat_ws和format函数是可变的，因此可以把要串接或格式化的值作为一个标记了VARIADIC关键字的数组进行传递（见Section 36.4.5）。数组的元素被当作函数的独立普通参数一样处理。如果可变数组参数为 NULL，concat和concat_ws返回 NULL，但format把 NULL 当作一个零元素数组。
还可以参阅Section 9.20中的string_agg。
Table 9-10. 内建转换
4.1 format
函数format根据一个格式字符串产生格式化的输出，其形式类似于 C 函数sprintf。
format(formatstr text [, formatarg "any" [, ...] ])formatstr是一个格式字符串，它指定了结果应该如何被格式化。格式字符串中的文本被直接复制到结果中，除了使用格式说明符的地方。格式说明符在字符串中扮演着占位符的角色，它定义后续的函数参数如何被格式化及插入到结果中。每一个formatarg参数会被根据其数据类型的常规输出规则转换为文本，并接着根据格式说明符被格式化和插入到结果字符串中。
格式说明符由一个%字符开始并且有这样的形式
%[position][flags][width]type其中的各组件域是：
position（可选）
一个形式为n$的字符串，其中n是要打印的参数的索引。索引 1 表示formatstr之后的第一个参数。如果position被忽略，默认会使用序列中的下一个参数。
flags（可选）
控制格式说明符的输出如何被格式化的附加选项。当前唯一支持的标志是一个负号（-），它将导致格式说明符的输出会被左对齐（left-justified）。除非width域也被指定，否者这个域不会产生任何效果。
width（可选）
指定用于显示格式说明符输出的最小字符数。输出将被在左部或右部（取决于-标志）用空格填充以保证充满该宽度。太小的宽度设置不会导致输出被截断，但是会被简单地忽略。宽度可以使用下列形式之一指定：一个正整数；一个星号（）表示使用下一个函数参数作为宽度；或者一个形式为n$的字符串表示使用第n个函数参数作为宽度。
如果宽度来自于一个函数参数，则参数在被格式说明符的值使用之前就被消耗掉了。如果宽度参数是负值，结果会在长度为abs(width)的域中被左对齐（如果-标志被指定）。
type（必需）
格式转换的类型，用于产生格式说明符的输出。支持下面的类型：
s将参数值格式化为一个简单字符串。一个控制被视为一个空字符串。
I将参数值视作 SQL 标识符，并在必要时用双写引号包围它。如果参数为空，将会是一个错误（等效于quote_ident）。
L将参数值引用为 SQL 文字。一个空值将被显示为不带引号的字符串NULL（等效于quote_nullable）。
除了以上所述的格式说明符之外，要输出一个文字形式的%字符，可以使用特殊序列%%。
下面有一些基本的格式转换的例子：
SELECT format('Hello %s', 'World');
结果：Hello World
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
结果：Testing one, two, three, %
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
结果：INSERT INTO "Foo bar" VALUES('O''Reilly')
SELECT format('INSERT INTO %I VALUES(%L)', 'locations', E'C:\\Program Files');
结果：INSERT INTO locations VALUES(E'C:\\Program Files')下面是使用width域和-标志的例子：
SELECT format('|%10s|', 'foo');
结果：| foo|
SELECT format('|%-10s|', 'foo');
结果：|foo |
SELECT format('|%*s|', 10, 'foo');
结果：| foo|
SELECT format('|%*s|', -10, 'foo');
结果：|foo |
SELECT format('|%-*s|', 10, 'foo');
结果：|foo |
SELECT format('|%-*s|', -10, 'foo');
结果：|foo |这些例子展示了position域的例子：
SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
结果：Testing three, two, one
SELECT format('|%*2$s|', 'foo', 10, 'bar');
结果：| bar|
SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
结果：| foo|不同于标准的 C 函数sprintf，PostgreSQL的format函数允许将带有或者不带有position域的格式说明符被混在同一个格式字符串中。一个不带有position域的格式说明符总是使用最后一个被消耗的参数的下一个参数。另外，format函数不要求所有函数参数都被用在格式字符串中。例如：
SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
结果：Testing three, two, three5. 二进制串函数和操作符
本节描述那些检查和操作类型为bytea的值的函数和操作符。
SQL定义了一些使用关键字而不是逗号来分割参数的串函数。详情请见Table 9-11。PostgreSQL也提供了这些函数使用常规函数调用语法的版本（参阅Table 9-12）。
Note: 本页中显示的示例结果假设服务器参数bytea_output被设置为escape（传统PostgreSQL格式）。
Table 9-11. SQL二进制串函数和操作符
还有一些二进制串处理函数可以使用，在Table 9-12列出。 其中有一些是在内部使用，用于实现Table 9-11列出的 SQL 标准串函数。
Table 9-12. 其他二进制串函数
get_byte和set_byte把一个二进制串中的一个字节计数为字节 0。get_bit和set_bit在每一个字节中从右边起计数位；例如位 0 是第一个字节的最低有效位，而位 15 是第二个字节的最高有效位。
6. 位串函数和操作符
本节描述用于检查和操作位串的函数和操作符，也就是操作类型为bit和bit varying的值的函数和操作符。除了常用的比较操作符之外，还可以使用Table 9-13里显示的操作符。&、|和#的位串操作数必须等长。在移位的时候，保留原始的位串的的长度，如例子所示。
Table 9-13. 位串操作符
下面的SQL标准函数除了可以用于字符串之外，也可以用于位串： length、 bit_length、 octet_length、 position、 substring、 overlay。
下面的函数除了可以用于二进制串之外，也可以用于位串： get_bit、 set_bit。 当使用于一个位串时，这些函数将串的第一（最左）位计数为位 0。
另外，我们可以在整数和bit之间来回转换。一些例子：
44::bit(10) 0000101100
44::bit(3) 100
cast(-44 as bit(12)) 111111010100
'1110'::bit(4)::integer 14请注意，如果只是转换为"bit"，意思是转换成bit(1)，因此只会转换整数的最低有效位。
Note:
把一个整数转换成bit(n)将拷贝整数的最右边的n位。 把一个整数转换成比整数本身长的位串，就会在最左边扩展符号。
7. 模式匹配
PostgreSQL提供了三种独立的实现模式匹配的方法：SQL LIKE操作符、更近一些的SIMILAR TO操作符（SQL:1999 里添加进来的）和POSIX-风格的正则表达式。除了这些基本的"这个串匹配这个模式吗？"操作符外，还有一些函数可用于提取或替换匹配子串并在匹配位置分离一个串。
Tip: 如果你的模式匹配的要求超出了这些，请考虑用 Perl 或 Tcl 写一个用户定义的函数。
Caution
虽然大部分的正则表达式搜索都能被很快地执行，但是正则表达式仍可能被 人为地弄成需要任意长的时间和任意量的内存进行处理。要当心从不怀好意 的来源接受正则表达式搜索模式。如果必须这样做，建议加上语句超时限制。
使用SIMILAR TO模式的搜索具有同样的安全性危险， 因为SIMILAR TO提供了很多和 POSIX-风格正则表达式相同的能力。
LIKE搜索比其他两种选项简单得多，因此在使用 不怀好意的模式来源时要更安全些。
9.7.1. LIKE
string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]如果该string匹配了提供的pattern，那么LIKE表达式返回真（和预期的一样，如果LIKE返回真，那么NOT LIKE表达式返回假， 反之亦然。一个等效的表达式是NOT (string LIKE pattern)）。
如果pattern不包含百分号或者下划线，那么该模式只代表它本身的串；这时候LIKE的行为就象等号操作符。在pattern里的下划线 （_）代表（匹配）任何单个字符； 而一个百分号（%）匹配任何零或更多个字符的序列。
一些例子：
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' falseLIKE模式匹配总是覆盖整个串。因此，要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。
要匹配文本的下划线或者百分号，而不是匹配其它字符， 在pattern里相应的字符必须 前导逃逸字符。缺省的逃逸字符是反斜线，但是你可以用ESCAPE子句指定一个不同的逃逸字符。 要匹配逃逸字符本身，写两个逃逸字符。
Note:
如果你关掉了standard_conforming_strings，你在文串常量中写的任何反斜线都需要被双写。
请注意反斜线在串文本里已经有特殊含义了，所以如果你写一个 包含反斜线的模式常量，那你就要在 SQL 语句里写两个反斜线。 因此，写一个匹配单个反斜线的模式实际上要在语句里写四个反斜线。 你可以通过用 ESCAPE 选择一个不同的逃逸字符 来避免这样；这样反斜线就不再是 LIKE 的特殊字符了。 但仍然是字符文本分析器的特殊字符，所以你还是需要两个反斜线。) 我们也可以通过写ESCAPE ''的方式不选择逃逸字符，这样可以有效地禁用逃逸机制，但是没有办法关闭下划线和百分号在模式中的特殊含义。
关键字ILIKE可以用于替换LIKE， 它令该匹配根据活动区域成为大小写无关。这个不属于SQL标准而是一个PostgreSQL扩展。
操作符 等效于LIKE， 而 *对应ILIKE。 还有 ! 和! *操作符分别代表NOT LIKE和NOT ILIKE。所有这些操作符都是PostgreSQL特有的。
9.7.2. SIMILAR TO正则表达式
string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]SIMILAR TO操作符根据自己的模式是否匹配给定串而返回真或者假。 它和LIKE非常类似，只不过它使用 SQL 标准定义的正则表达式理解模式。 SQL 正则表达式是在LIKE标记和普通的正则表达式标记的奇怪的杂交。
类似LIKE，SIMILAR TO操作符只有在它的模式匹配整个串的时候才能成功；这一点和普通的 正则表达式的行为不同，在普通的正则表达式里，模式匹配串的任意部分。 和LIKE类似的地方还有，SIMILAR TO使用_和%作为分别代表任意单个字符和任意串的通配符（这些可以比得上 POSIX 正则表达式里的.和.*）。
除了这些从LIKE借用的功能之外，SIMILAR TO支持下面这些从 POSIX 正则表达式借用的 模式匹配元字符：
- |表示选择（两个候选之一）。
- *表示重复前面的项零次或更多次。
- +表示重复前面的项一次或更多次。
- ?表示重复前面的项零次或一次。
- {m}表示重复前面的项刚好m次。
- {m,}表示重复前面的项m次或更多次。
- {m,n}表示重复前面的项至少m次并且不超过n次。
- 可以使用圆括号()把多个项组合成一个逻辑项。
- 一个方括号表达式[...]声明一个字符类，就像 POSIX 正则表达式一样。
注意点号（.）不是SIMILAR TO的一个元字符。
和LIKE一样，反斜线禁用所有这些元字符的特殊含义；当然我们也可以用ESCAPE指定一个不同的逃逸字符。
一些例子：
'abc' SIMILAR TO 'abc' true
'abc' SIMILAR TO 'a' false
'abc' SIMILAR TO '%(b|d)%' true
'abc' SIMILAR TO '(b|c)%' false带三个参数的substring，即substring(string from pattern for escape-character)，提供了抽取一个匹配 SQL 正则表达式的子串的方法。和SIMILAR TO一样，声明的模式必须匹配整个数据串，否则函数失败并返回空值。为了标识在成功的时候应该返回的模式部分，模式 必须包含逃逸字符的两次出现，并且后面要跟上双引号（"）。匹配这两个标记之间的模式的文本将被返回。
一些例子，使用#"定界返回串：
substring('foobar' from '%#"o_b#"%' for '#') oob
substring('foobar' from '#"o_b#"%' for '#') NULL9.7.3. POSIX正则表达式
Table 9-14列出了所有可用于 POSIX 正则表达式模式匹配的操作符。
Table 9-14. 正则表达式匹配操作符
POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的含义。许多 Unix 工具，例如egrep、sed或awk使用一种与我们这里描述的类似的模式匹配语言。
正则表达式是一个字符序列，它是定义一个串集合 （一个正则集）的缩写。 如果一个串是正则表达式描述的正则集中的一员时， 我们就说这个串匹配该正则表达式。 和LIKE一样，模式字符准确地匹配串字符， 除非在正则表达式语言里有特殊字符 — 不过正则表达式用的 特殊字符和LIKE用的不同。 和LIKE模式不一样的是，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。
一些例子：
'abc' ~ 'abc' true
'abc' ~ '^a' true
'abc' ~ '(b|d)' true
'abc' ~ '^(b|c)' falsePOSIX模式语言的详细描述见下文。
带两个参数的substring函数，即substring(string from pattern)，提供了抽取一个匹配 POSIX 正则表达式模式的子串的方法。如果没有匹配它返回空值，否则就是文本中匹配模式的那部分。 但是如果该模式包含任何圆括号，那么将返回匹配第一对子表达式（对应第一个左圆括号的） 的文本。如果你想在表达式里使用圆括号而又不想导致这个例外，那么你可以在整个表达式外边放上一对圆括号。 如果你需要在想抽取的子表达式前有圆括号，参阅后文描述的非捕获性圆括号。
一些例子：
substring('foobar' from 'o.b') oob
substring('foobar' from 'o(.)b') oregexp_replace函数提供了将匹配 POSIX 正则表达式模式的子串替换为新文本的功能。 它的语法是 regexp_replace(source, pattern, replacement [, flags ])。 如果没有匹配pattern，那么返回不加修改的source串。 如果有匹配，则返回的source串里面的匹配子串将被replacement串替换掉。replacement串可以包含n， 其中n是 1 到 9， 表明源串里匹配模式里第n个圆括号子表达式的子串应该被插入， 并且它可以包含&表示应该插入匹配整个模式的子串。如果你需要放一个文字形式的反斜线在替换文本里，那么写\。flags参数是一个可选的文本串，它包含另个或更多单字母标志，这些标志可以改变函数的行为。标志i指定大小写无关的匹配，而标志g指定替换每一个匹配的子串而不仅仅是第一个。支持的标志（但不是g）在Table 9-22中描述。
一些例子：
regexp_replace('foobarbaz', 'b..', 'X')
fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
fooXX
regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
fooXarYXazYregexp_matches函数返回一个文本数组，该数组由匹配一个 POSIX 正则表达式模式得到的所有被捕获子串构成。其语法是regexp_matches(string, pattern [, flags ])。该函数可以不返回任何行、返回一行或者返回多行（见下文的g）。如果pattern不匹配，该函数不返回行。如果模式不包含圆括号子表达式，则每一个被返回的行都是一个单一元素的文本数组，其中包括匹配整个模式的子串。如果模式包含圆括号子表达式，该函数返回一个文本数组，它的第n个元素是匹配模式的第n个圆括号子表达式的子串（"非捕获"圆括号不计算在内，详见下文）。flags参数是一个可选的文本字符串，它包含零个或更多个单字母标志，它们可以改变函数的行为。标志g让函数寻找串中的每一个匹配，而不仅仅是第一个，并且为每一个这样的匹配返回一行。支持的标志（但不是g）在Table 9-22中描述。
一些例子：
SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
regexp_matches
----------------
{bar,beque}
(1 row)
SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
regexp_matches
----------------
{bar,beque}
{bazil,barf}
(2 rows)
SELECT regexp_matches('foobarbequebaz', 'barbeque');
regexp_matches
----------------
{barbeque}
(1 row)也可以强制regexp_matches()通过使用一个子选择来总是返回一行。当你希望所有行都被返回（甚至是不能匹配的行）时，把它用在一个SELECT目标列表中会特别有用：
SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;regexp_split_to_table把一个 POSIX 正则表达式模式当作一个定界符来分离一个串。它的语法形式是regexp_split_to_table(string, pattern [, flags ])。如果没有与pattern的匹配，该函数返回string。如果有至少有一个匹配，对每一个匹配它都返回从上一个匹配的末尾（或者串的开头）到这次匹配开头之间的文本。当没有更多匹配时，它返回从上一次匹配的末尾到串末尾之间的文本。flags参数是一个可选的文本串，它包含零个或更多单字母标志，这些标识可以改变该函数的行为。regexp_split_to_table能支持的标志在Table 9-22中描述。
regexp_split_to_array函数的行为和regexp_split_to_table相同，不过regexp_split_to_array会把它的结果以一个text数组的形式返回。它的语法是regexp_split_to_array(string, pattern [, flags ])。这些参数和regexp_split_to_table的相同。
一些例子：
SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;
foo
-------
the
quick
brown
fox
jumps
over
the
lazy
dog
(9 rows)
SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');
regexp_split_to_array
-----------------------------------------------
{the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)
SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;
foo
-----
t
h
e
q
u
i
c
k
b
r
o
w
n
f
o
x
(16 rows)正如上一个例子所示，正则表达式分离函数会忽略零长度的匹配，这种匹配发生在串的开头或结尾或者正好发生在前一个匹配之后。这和正则表达式匹配的严格定义是相悖的，后者由regexp_matches实现，但是通常前者是实际中最常用的行为。其他软件系统如Perl也使用相似的定义。
7.3.1. 正则表达式细节
PostgreSQL的正则表达式是使用 Henry Spencer 写的一个包来实现的。下面的正则表达式的大部分描述都是从他的手册页中逐字拷贝过来的。
正则表达式（RE），在POSIX 1003.2 中定义， 它有两种形式：扩展的RE或者是ERE（大概地说就是那些在egrep里的）， 基本的RE或者是BRE（大概地说就是那些在ed里的）。PostgreSQL支持两种形式，并且还实现了一些POSIX标准中没有但是在类似 Perl 或者 Tcl 这样的语言中得到广泛应用的一些扩展。使用了那些非POSIX扩展的RE叫高级RE， 或者本文档里说的ARE。ARE 几乎完全是 ERE 的超集，但是 BRE 有几个符号上的不兼容（以及更多的限制）。我们首先描述 ARE 和 ERE 形式， 描述那些只适用于 ARE 的特性，然后描述 BRE 的区别是什么。
Note:
PostgreSQL初始时总是推测一个正则表达式遵循 ARE 规则。但是，可以通过为 RE 模式预置一个embedded option来选择限制更多的 ERE 或 BRE 规则，如Section 9.7.3.4中所述。这对为期望准确的POSIX 1003.2 规则的应用提供兼容性很有用。
一个正则表达式被定义为一个或更多分支，它们之间被|分隔。只要能匹配其中一个分支的东西都能匹配正则表达式。
一个分支是一个或多个量化原子或者约束连接而成。一个原子匹配第一个，然后后面的原子匹配第二个， 以此类推；一个空分支匹配空串。
一个量化原子是一个原子， 后面可能跟着一个量词。没有量词的时候，它匹配一个原子， 有量词的时候，它可以匹配若干个原子。一个原子可以是在Table 9-15里面显示的任何可能。 可能的量词和它们的含义在Table 9-16里显示。
一个约束匹配一个空串，但只是在满足特定条件下才匹配。 约束可以在能够使用原子的地方使用，只是它不能跟着量词。简单的约束在Table 9-17里显示； 更多的约束稍后描述。
Table 9-15. 正则表达式原子
RE 不能以反斜线（）结尾。
Note:
如果你关掉了standard_conforming_strings，任何你写在文字串常量中的反斜线都需要被双写。
Table 9-16. 正则表达式量词
使用{...}的形式被称作范围。 一个范围内的数字m和n都是无符号十进制整数， 允许的数值从 0 到 255（包含）。
非贪婪的量词（只在 ARE 中可用）匹配对应的正常 （贪婪）模式，区别是它寻找最少的匹配，而不是最多的匹配。详见Section 9.7.3.5。
Note: 一个量词不能紧跟在另外一个量词后面，例如**是非法的。量词不能作为表达式或者子表达式的开头，也不能跟在^或者|后面。
Table 9-17. 正则表达式约束
ookahead 和 lookbehind 约束不能包含后引用 （参阅Section 7.3.3），并且其中的所有圆括号 都被认为是非捕获的。
7.3.2. 方括号表达式
方括号表达式是一个包围在[]中的字符列表。它通常匹配列表中的任意单个字符（但见下文）。 如果列表以^开头，它匹配任意单个不在该列表参与部分中的字符。如果该列表中两个字符用-隔开， 那它就是那两个字符（包括在内）之间的所有字符范围的缩写，例如，在ASCII中[0-9]匹配任何十进制数字。两个范围共享一个端点是非法的，例如，a-c-e。范围与字符集关系密切， 可移植的程序应该避免依靠它们。
想在列表中包含文本]，可以让它做列表的首字符（如果使用了^，需要放在其后）。 想在列表中包含文本-，可以让它做列表的首字符或者尾字符，或者一个范围的第二个端点。 想在列表中把文本-当做范围的起点， 把它用[.和.]包围起来，这样它就成为一个排序元素（见下文）。 除了这些字符本身、一些用[的组合（见下段）以及逃逸（只在 ARE 中有效）以外，所有其它特殊字符 在方括号表达式里都失去它们的特殊含义。特别是，在 ERE 和 BRE 规则下不是特殊的， 但在 ARE 里，它是特殊的（引入一个逃逸）。
在一个方括号表达式里，一个排序元素（一个字符、一个被当做一个单一字符排序的多字符序列或者一个表示上面两种情况的排序序列名称） 包含在[.和.]里面的时候表示该排序元素的字符序列。该序列被当做该方括号列表 的一个单一元素。这允许一个包含多字符排序元素的方括号表达式去匹配多于一个字符，例如，如果排序序列包含一个ch排序元素， 那么 RE [[.ch.]]*c匹配chchcc的头五个字符。
Note:
PostgreSQL当前不支持多字符排序元素。这些信息描述了将来可能有的行为。
在方括号表达式里，包围在[=和=]里的排序元素是一个等价类， 代表等效于那一个的所有排序元素的字符序列，包括它本身（如果没有其它等效排序元素，那么就好象封装定界符是[.和 .]）。例如，如果o和^是一个等价类的成员，那么[[=o=]]、[[=^=]]和[o^]都是同义的。一个等价类不能是一个范围的端点。
在方括号表达式里，在[:和:]里面封装的字符类的名字代表属于该类的所有字符的列表。 标准的字符类名字是：alnum、 alpha、blank、 cntrl、digit、 graph、lower、 print、punct、 space、upper、 xdigit。 它们代表在ctype中定义的字符类。 一个区域可以会提供其他的类。字符类不能用做一个范围的端点。
方括号表达式里有两个特例：方括号表达式[[:<:]]和[[:>:]]是约束，分别匹配一个单词开头和结束的空串。 单词定义为一个单词字符序列，前面和后面都没有其它单词字符。单词字符是一个alnum字符（和ctype中定义的一样） 或者一个下划线。这是一个扩展，兼容POSIX 1003.2， 但那里面并没有说明， 而且在准备移植到其他系统里去的软件里一定要小心使用。通常下文描述的约束逃逸更好些（它们并非更标准，但是更容易键入）。
7.3.3. 正则表达式逃逸
逃逸是以开头，后面跟着一个字母数字字符得特殊序列。 逃逸有好几种变体：字符项、类缩写、约束逃逸以及后引用。在 ARE 里， 如果一个后面跟着一个字母数字，但是并未组成一个合法的逃逸， 那么它是非法的。在 ERE 中没有逃逸：在方括号表达式之外，一个后面跟着字母数字字符的只是表示该字符是一个普通的字符，而且在一个方括号表达式里，是一个普通的字符（后者实际上在 ERE 和 ARE 不兼容）。
字符项逃逸用于便于我们在 RE 中声明那些不可打印的或其他习惯的字符。它们显示在Table 9-18中。
类缩写逃逸用来提供一些常用的字符类缩写。它们显示在Table 9-19中。
约束逃逸是一个约束，如果满足特定的条件，它匹配该空串。它们显示在Table 9-20中。
后引用（n）匹配数字n指定的被前面的圆括号子表达式匹配的同一个串 （参阅Table 9-21）。例如， ([bc])1匹配bb或者cc， 但是不匹配bc或者cb。RE 中子表达式必须完全在后引用前面。子表达式以它们的先导圆括号的顺序编号。非捕获圆括号并不定义子表达式。
Table 9-18. 正则表达式字符项逃逸
十六进制位是0-9、a-f和A-F。八进制位是0-7。
指定 ASCII 范围（0-127）之外的值的数字字符项转义的含义取决于数据库编码。 当编码是 UTF-8 时，转义值等价于 Unicode 代码点，例如 u1234表示字符U+1234。对于其他多字节编码， 字符项转义通常只是指定该字符的字节值的串接。如果该转义值不对应数据库编码 中的任何合法字符，将不会发生错误，但是它不会匹配任何数据。
字符项逃逸总是被当作普通字符。例如，135是 ASCII 中的]， 但135并不终止一个方括号表达式。
Table 9-19. 正则表达式类缩写逃逸
在方括号表达式里，d、s和w会失去它们的外层方括号，而D、S和 W是非法的（也就是说，例如[a-cd]等效于[a-c[:digit:]]。同样[a-cD]等效于 [a-c^[:digit:]]的，也是非法的）。
Table 9-20. 正则表达式约束逃逸
一个词被定义成在上面[[:<:]]和[[:>:]]中的声明。在方括号表达式里，约束逃逸是非法的。
Table 9-21. 正则表达式后引用
Note: 在八进制字符项逃逸和后引用之间有一个历史继承的歧义存在，这个歧义是 通过下面的启发式规则解决的，像上面描述地那样。前导零总是表示这是一个八进制逃逸。 而单个非零数字，如果没有跟着任何其它位，那么总是被认为后引用。 一个多位的非零开头的序列也被认为是后引用，只要它出现在合适的子表达式后面 （也就是说，在后引用的合法范围中的数），否则就被认为是一个八进制。
7.3.4. 正则表达式元语法
除了上面描述的主要语法之外，还有几种特殊形式和杂项语法。
如果一个 RE 以***:开头，那么剩下的 RE 都被当作 ARE（这在PostgreSQL中通常是无效的，因为 RE 被假定为 ARE，但是如果 ERE 或 BRE 模式通过flags参数被指定为一个正则表达式函数时，它确实能产生效果）。如果一个 RE 以***=开头， 那么剩下的 RE 被当作一个文本串，所有的字符都被认为是一个普通字符。
一个 ARE 可以以嵌入选项开头：一个序列(?xyz)（这里的xyz是一个或多个字母字符）声明影响剩余 RE 的选项。 这些选项覆盖任何前面判断的选项 — 特别地，它们可以覆盖一个正则表达式操作符隐含的大小写敏感的行为，或者覆盖flags参数中的正则表达式函数。可用的选项字母在Table 9-22中显示。注意这些同样的选项字母也被用在正则表达式函数的flags参数中。
Table 9-22. ARE 嵌入选项字母
嵌入选项在)终止序列时发生作用。它们只在 ARE 的开始处起作用 （在任何可能存在的***:控制器后面）。
除了通常的（紧）RE 语法（这种情况下所有字符都有效）， 还有一种扩展语法，可以通过声明嵌入的x选项获得。在扩展语法里，RE 中的空白字符被忽略，就像那些在#和其后的新行（或 RE 的末尾）之间的字符一样。这样就允许我们给一个复杂的 RE 分段和注释。不过这个基本规则有三种例外：
- 空白字符或前置了的#将被保留
- 方括号表达式里的空白或者#将被保留
- 在多字符符号里面不能出现空白和注释，例如(?:
为了这个目的，空白是空格、制表符、新行和任何属于空白字符类的字符。
最后，在 ARE 里，方括号表达式外面，序列(?#ttt)（其中ttt是任意不包含一个))的文本）是一个注释， 它被完全忽略。同样，这样的东西是不允许出现在多字符符号的字符中间的，例如 (?:。这种注释更像是一种历史产物而不是一种有用的设施，并且它们的使用已经被废弃；请使用扩展语法来替代。
如果声明了一个初始的***=控制器，那么所有这些元语法扩展都不能使用，因为这样表示把用户输入当作一个文字串而不是 RE 对待。
7.3.5. 正则表达式匹配规则
在 RE 可以在给定串中匹配多于一个子串的情况下， RE 匹配串中最靠前的那个子串。如果 RE 可以匹配在那个位置开始 的多个子串，要么是取最长的子串，要么是最短的，具体哪种， 取决于 RE 是贪婪的还是非贪婪的。
一个 RE 是否贪婪取决于下面规则：
- 大多数原子以及所有约束，都没有贪婪属性（因为它们毕竟无法匹配个数变化的文本）。
- 在一个 RE 周围加上圆括号并不会改变其贪婪性。
- 带一个固定重复次数量词 （{m}或者{m}?） 的量化原子和原子自身具有同样的贪婪性（可能是没有）。
- 一个带其他普通的量词（包括{m,n}中m等于n的情况）的量化原子是贪婪的（首选最长匹配）。
- 一个带非贪婪量词（包括{m,n}?中m等于 n的情况）的量化原子是非贪婪的（首选最短匹配）。
- 一个分支 — 也就是说，一个没有顶级|操作符的 RE — 和它里面的第一个有贪婪属性的量化原子有着同样的贪婪性。
- 一个由|操作符连接起来的两个或者更多分支组成的 RE 总是贪婪的。
上面的规则所描述的贪婪属性不仅仅适用于独立的量化原子， 而且也适用于包含量化原子的分支和整个 RE。这里的意思是， 匹配是按照分支或者整个 RE 作为一个整体匹配最长或者最短的可能子串。 一旦整个匹配的长度确定，那么匹配任意特定子表达式的部分就基于该子表达式的贪婪属性进行判断，在 RE 里面靠前的子表达式的优先级高于靠后的子表达式。
一个相应的例子：
SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
结果：123
SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
结果：1在第一个例子里，RE 作为整体是贪婪的，因为Y*是贪婪的。它可以匹配从Y开始的东西，并且它匹配从这个位置开始的最长的串， 也就是，Y123。输出是这里的圆括号包围的部分，或者说是123。在第二个例子里， RE 总体上是一个非贪婪的 RE，因为Y*?是非贪婪的。它可以匹配从Y开始的最短的子串，也就是说Y1。子表达式[0-9]{1,3}是贪婪的，但是它不能修改总体匹配长度的决定； 因此它被迫只匹配1。
简而言之，如果一个 RE 同时包含贪婪和非贪婪的子表达式，那么总的匹配长度要么是尽可能长，要么是尽可能短，这取决于给整个 RE 赋予的属性。给子表达式赋予的属性只影响在这个匹配里，各个子表达式之间相互允许"吃掉"的多少。
量词{1,1}和{1,1}?可以分别用于在一个子表达式 或者整个 RE 上强制贪婪或者非贪婪。当需要整个 RE 具有不同于从其元素中 推导出的贪婪属性时，这很有用。例如，假设我们尝试将一个包含一些数字的 字符串分隔成数字以及在它们之前和之后的部分，我们可能会尝试这样做：
SELECT regexp_matches('abc01234xyz', '(.*)(\d+)(.*)');
Result: {abc0123,4,xyz}这不会有用：第一个.*是贪婪的，因此它会"吃掉" 尽可能多的字符而留下d+去匹配在最后一个可能位置上的最 后一个数字。我们可能会通过让它变成非贪婪来修复：
SELECT regexp_matches('abc01234xyz', '(.*?)(\d+)(.*)');
Result: {abc,0,""}这也不会有用：因为现在 RE 作为整体来说是非贪婪的，因此它会尽快结束 全部的匹配。我们可以通过强制 RE 整体是贪婪的来得到我们想要的：
SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
Result: {abc,01234,xyz}独立于 RE 的组件的贪婪性之外控制 RE 的整体贪婪性为处理变长模式提供了 很大的灵活性。
在决定更长或者更短的匹配时，匹配长度是以字符衡量的，而不是排序元素。一个空串会被认为比什么都不匹配长。例如：bb*匹配abbbc的中间三个字符；(week|wee)(night|knights)匹配weeknights的所有十个字符； 而(.*).*匹配 abc的时候，圆括号包围的子表达式匹配所有三个字符；当(a*)*被拿来匹配bc时，整个 RE 和圆括号 子表达式都匹配一个空串。
如果声明了大小写无关的匹配，那么效果就好像所有大小写区别在字母表中消失了。如果在多个情况中一个字母以一个普通字符的形式出现在方括号表达式外面，那么它实际上被转换成 一个包含大小写的方括号表达式，也就是说，x 变成 [xX]。 如果它出现在一个方括号表达式里面，那么它的所有大小写的同族都被加入 方括号表达式中，也就是说，x变成[xX]。当它出现在一个方括号表达式内时，它的所有大小写副本都被加入到方括号表达式中，例如， [x]会变成[xX]，而1会变成2。
如果指定了新行敏感的匹配，.和使用^的方括号表达式 将永远不会匹配新行字符（这样，匹配就绝对不会跨越新行，除非 RE 显式地安排了这样的情况）并且^和$除了分别匹配串开头和结尾之外，还将分别匹配新行后面和前面的空串。但是 ARE 逃逸A和Z仍然只匹配串的开头和结尾。
如果指定了部分新行敏感的匹配，那么它影响.和方括号表达式， 这个时候和新行敏感的匹配一样，但是不影响^和$。
如果指定了逆新行敏感匹配，那么它影响^和$，其作用和在新行敏感的匹配里一样，但是不影响.和方括号表达式。这个并不是很有用，只是为了满足对称性而提供的。
7.3.6. 限制和兼容性
在这个实现里，对 RE 的长度没有特别的限制。但是，那些希望高移植性的程序应该避免使用长度超过 256 字节的 RE，因为 POSIX 兼容 的实现可以拒绝接受这样的 RE。
ARE 实际上和 POSIX ERE 不兼容的唯一的特性是在方括号表达式里并不失去它特殊的含义。所有其它 ARE 特性都使用在 POSIX ERE 里面是非法或者是未定义、未声明效果的语法；指示器的***就是在 POSIX 的 BRE 和 ERE 之外的语法。
许多 ARE 扩展都是从 Perl 那里借来的（但是有些被做了修改来清理它们），以及一些 Perl 里没有出现的扩展。要注意的不兼容性包括b、B、对结尾的新行缺乏特别的处理、对那些被新行敏感匹配的东西附加的补齐方括号表达式、在 lookahead/lookbehind 约束里对圆括号和后引用的限制以及最长/最短 匹配（而不是第一匹配）的语义。
PostgreSQL 7.4 之前的版本中识别的 ARE 和 ERE 语法存在两个非常明显的不兼容：
- 在 ARE 中，后面跟着一个字母数字字符的要么是一个逃逸要么是一个错误， 但是在以前的版本里，它只是写该字母数字字符的另外一种方法。这个应该不是什么问题， 因为在以前的版本里没有什么理由会让我们写这样的序列。
- 在 ARE 里，在[]里还是一个特殊字符， 因此在方括号表达式里的一个文本必须被写成\。
7.3.7. 基本正则表达式
BRE 在几个方面和 ERE 不太一样。在 BRE 中，|、+和?都是普通字符并且没有与它们功能等价的东西。范围的定界符是\{和\}， 因为 {和}本身是普通字符。嵌套的子表达式的圆括号是\(和\)，因为(和)自身是普通字符。除非在 RE 开头或者是圆括号子表达式开头，^都是一个普通字符。 除非在 RE 结尾或者是圆括号子表达式的结尾，$是一个普通字符。如果*出现在 RE 开头或者是圆括号封装的子表达式开头 （前面可能有^），那么它是个普通字符。最后，可以用单数字的后引用，\<和\>分别是[[:<:]]和[[:>:]]的同义词；在 BRE 中没有其它可用的逃逸。8. 数据类型格式化函数
PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型 （日期/时间、整数、浮点、数字） 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。Table 9-23列出了这些函数。这些函数都遵循一个公共的调用规范： 第一个参数是待格式化的值，而第二个是一个定义输出或输入格式的模板。
Table 9-23. 格式化函数
Note:
还有一个单一参数的to_timestamp函数，请见Table 9-30。
在一个to_char输出模板串中，一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样，在一个输入 模板串里（对其他函数），模板模式标识由输入数据串提供的值。
Table 9-24展示了可以用于格式化日期和时间值的模版。
Table 9-24. 用于日期/时间格式化的模板模式
修饰语可以被应用于模板模式来修改它们的行为。例如，FMMonth就是带着FM修饰语的Month模式。Table 9-25展示了可用于日期/时间格式化的修饰语模式。
Table 9-25. 用于日期/时间格式化的模板模式修饰语
日期/时间格式化的使用须知：
- FM抑制前导的零或尾随的空白， 否则会把它们增加到输入从而把一个模式的输出变成固定宽度。在PostgreSQL中，FM只修改下一个声明，而在 Oracle 中，FM影响所有随后的声明，并且重复的FM修饰语将触发填充模式开和关。
- TM不包括结尾空白。to_timestamp和to_date会忽略TM修饰语。
- 如果没有使用FX选项，to_timestamp和to_date会跳过输入字符串中的多个空白。例如，to_timestamp('2000 JUN', 'YYYY MON')是正确的，但to_timestamp('2000 JUN', 'FXYYYY MON')会返回一个错误，因为to_timestamp只期望一个空白。FX必须被指定为模板中的第一个项。
- to_timestamp和to_date的存在是为了 处理无法被简单转换的输入格式。这些函数会从字面上解释输入，并做一点点错误检查。当 它们产生有效输出时，该转换可能得到意料之外的结果。例如，这些函数的输入没有被限制 在正常的范围内，因此to_date('20096040','YYYYMMDD')会返回 2014-01-17而不是报错。而造型不会有这样的行为。
- 在to_char模板里可以有普通文本，并且它们会被照字面输出。你可以把一个子串放到双引号里强迫它被解释成一个文本，即使它里面包含模式关键字也如此。例如，在 '"Hello Year "YYYY'中，YYYY将被年份数据代替，但是Year中单独的Y不会。在to_date、to_number和to_timestamp中，双引号字符串会跳过包含在字符串中字符个数的字符，例如"XX"跳过两个输入字符。
- 如果你想在输出里有双引号，那么你必须在它们前面放反斜线，例如 '"YYYY Month"'。
- 如果年份格式声明少于四位（如YYY）并且提供的年份少于四位，年份将被调整为最接近于 2020 年，例如95会变成 1995。
- 在处理长于 4 位的年份时，从字串向timestamp或者date的YYYY转换有一个限制。你必须在YYYY后面使用一些非数字字符或者模板， 否则年份总是被解释为 4 位数字。例如（对于 20000 年）：to_date('200001131', 'YYYYMMDD')将会被解释成一个 4 位数字的年份，而不是在年份后使用一个非数字分隔符，像to_date('20000-1131', 'YYYY-MMDD')或to_date('20000Nov31', 'YYYYMonDD')。
- 在从字符串向timestamp或date的转换中， 如果有YYY、YYYY或者Y,YYY域， 那么CC（世纪）域会被忽略。如果CC和YY或Y一起使用， 那么年份被计算时会认为年份位于指定的世纪中。如果该世纪被指定但是年份没有被指定，将假定用该世纪的第一年。
-
一个 ISO 8601 周编号的日期（与一个格里高利日期相区别）可以用两种方法之一被指定为to_timestamp和to_date：
- 年、周编号和工作日：例如to_date('2006-42-4', 'IYYY-IW-ID')返回日期2006-10-19。如果你忽略工作日，它被假定为 1（周一）。
- 年和一年中的日：例如to_date('2006-291', 'IYYY-IDDD')也返回2006-10-19。
尝试使用一个混合了 ISO 8601 周编号和格里高利日期的域来输入一个日期是无意义的，并且将导致一个错误。在一个 ISO 周编号的年的环境下，一个"月"或"月中的日"的概念没有意义。在一个格里高利年的环境下，ISO 周没有意义。用户应当避免混合格里高利和 ISO 日期声明。
Caution
虽然to_date将会拒绝混合使用格里高利和 ISO 周编号日期的域， to_char却不会，因为YYYY-MM-DD (IYYY-IDDD) 这种输出格式也会有用。但是避免写类似IYYY-MM-DD的东西，那会得到在 起始年附近令人惊讶的结果。
- 在从字符串到timestamp的转换中，毫秒（MS）和微秒（US）值都被用作小数点后的秒位。例如to_timestamp('12:3', 'SS:MS')不是 3 毫秒, 而是 300，因为该转换把它看做 12 + 0.3 秒。这意味着对于格式SS:MS而言，输入值12:3、12:30和12:300指定了相同数目的毫秒。要得到三毫秒，你必须使用 12:003，转换会把它看做 12 + 0.003 = 12.003 秒。
下面是一个更复杂的例子∶to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')是 15 小时、12 分钟和 2 秒 + 20 毫秒 + 1230微秒 = 2.021230 秒。
- to_char(..., 'ID')的一周中日的编号匹配extract(isodow from ...)函数，但是to_char(..., 'D')不匹配extract(dow from ...)的日编号。
- to_char(interval)格式化HH和HH12为显示在一个 12 小时的时钟上，即零小时和 36 小时输出为12，而HH24会输出完整的小时值，对于间隔它可以超过 23.
Table 9-26展示了可以用于格式化数字值的模版模式。
Table 9-26. 用于数字格式化的模板模式
数字格式化的用法须知：
- 使用SG、PL或MI格式化的符号并不挂在数字上面； 例如，to_char(-12, 'MI9999')生成'- 12'，而to_char(-12, 'S9999')生成 ' -12'。Oracle 里的实现不允许在9前面使用MI，而是要求9在MI前面。
- 9导致一个值，这个值的位数好像有那么多个9在那里。如果一个数字不可用，它将输出一个空格。
- TH不会转换小于零的数值，也不会转换小数。
- PL、SG和TH是PostgreSQL扩展。
- 带有to_char的V会把输入值乘上10^n，其中n是跟在V后面的位数。带有to_number的V以类似的方式做除法。to_char和to_number不支持使用结合小数点的V（例如，不允许99.9V99）。
- EEEE（科学记数法）不能和任何其他格式化模式或修饰语（数字和小数点模式除外）组合在一起使用，并且必须位于格式化字符串的最后（例如9.99EEEE是一个合法的模式）。
- 某些修饰语可以被应用到任何模板来改变其行为。例如，FM9999是带有FM修饰语的9999模式。Table 9-27中展示了用于数字格式化模式修饰语。
Table 9-27. 用于数字格式化的模板模式修饰语
Table 9-28展示了一些使用to_char函数的例子。
Table 9-28. to_char例子
9. 时间/日期函数和操作符
Table 9-30展示了可用于处理日期/时间值的函数，其细节在随后的小节中描述。Table 9-29演示了基本算术操作符 （+、*等）的行为。 而与格式化相关的函数。
所有下文描述的接受time或timestamp输入的函数和操作符实际上都有两种变体： 一种接收time with time zone或timestamp with time zone， 另外一种接受time without time zone或者 timestamp without time zone。为了简化，这些变种没有被独立地展示。此外，+和*操作符都是可交换的操作符对（例如，date + integer 和 integer + date）；我们只显示其中一个。
Table 9-29. 日期/时间操作符
Table 9-30. 日期/时间函数
除了这些函数以外，还支持 SQL 操作符OVERLAPS：
(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)这个表达式在两个时间域（用它们的端点定义）重叠的时候得到真，当它们不重叠时得到假。端点可以用一对日期、时间或者时间戳来指定；或者是用一个后面跟着一个间隔的日期、时间或时间戳来指定。当一对值被提供时，起点或终点都可以被写在前面，OVERLAPS会自动地把较早的值作为起点。每一个时间段被认为是表示半开的间隔start <= time < end，除非start和end相等，这种情况下它表示单个时间实例。例如这表示两个只有一个共同端点的时间段不重叠。
SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
结果：true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
结果：false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
结果：false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
结果：true当把一个interval值添加到timestamp with time zone上（或从中减去）时， days 部分会按照指定的天数增加或减少timestamp with time zone的日期。 对于横跨夏令时的变化（当会话的时区被设置为可识别DST的时区时），这意味着interval '1 day'并 不一定等于interval '24 hours'。例如，当会话的时区设置为CST7CDT时，timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' 的结果是timestamp with time zone '2005-04-03 12:00-06'，而将interval '24 hours'增加到相同的初始timestamp with time zone的结果 则是timestamp with time zone '2005-04-03 13:00-06'， 因为CST7CDT时区在2005-04-03 02:00有一个夏令时变更。
注意age返回的月数域可能有歧义，因为不同的月份有不同的天数。 PostgreSQL的方法是当计算部分月数时，采用两个日期中较早的月。例如：age('2004-06-01', '2004-04-30')使用4月份得到1 mon 1 day，而用5月分时会得到1 mon 2 days，因为5月有31天，而4月只有30天。
日期和时间戳的减法也可能会很复杂。执行减法的一种概念上很简单的方法是，使用 EXTRACT(EPOCH FROM ...)把每个值都转换成秒数，然后执行减法， 这样会得到两个值之间的秒数。这种方法将会适应每个月中天数、 时区改变和夏令时调整。使用"-"操作符的日期或时间 戳减法会返回值之间的天数（24小时）以及时/分/秒，也会做同样的调整。 age函数会返回年、月、日以及时/分/秒，执行按域的减法，然后对 负值域进行调整。下面的查询展示了这些方法的不同。例子中的结果由 timezone = 'US/Eastern'产生，这使得两个使用的日期之间存在着夏令 时的变化：
SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
/ 60 / 60 / 24;
Result: 121.958333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Result: 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
Result: 4 mons
9.9.1. EXTRACT, date_part
EXTRACT(field FROM source)extract函数从日期/时间值中抽取子域，例如年或者小时等。source必须是一个类型 timestamp、time或interval的值表达式（类型为date的表达式将被造型为 timestamp，并且因此也可以被同样使用）。field是一个标识符或者字符串，它指定从源值中抽取的域。extract函数返回类型为double precision的值。 下列值是有效的域名字∶
century
世纪
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
结果：20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：21第一个世纪从 0001-01-01 00:00:00 AD 开始， 尽管那时候人们还不知道这是第一个世纪。这个定义适用于所有使用格里高利历法的国家。其中没有 0 世纪，我们直接从公元前 1 世纪到公元 1 世纪。 如果你认为这个不合理，那么请把抱怨发给：罗马圣彼得教堂，梵蒂冈，教皇收。
day
对于timestamp值，是（月份）里的日域（1-31）；对于interval值，是日数
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：16
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
结果：40decade
年份域除以10
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
结果：200dow
一周中的日，从周日（0）到周六（6）
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
结果：5请注意，extract的一周中的日和to_char(..., 'D')函数不同。
doy
一年的第几天（1 -365/366）
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：47epoch
对于timestamp with time zone值， 是自 1970-01-01 00:00:00 UTC 以来的秒数（结果可能是负数）； 对于date and timestamp值，是自本地时间 1970-01-01 00:00:00 以来的描述；对于interval值，它是时间间隔的总秒数。
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
结果：982384720.12
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
结果：442800不能用to_timestamp把一个 epoch 值转换回成时间戳：
SELECT to_timestamp(982384720.12);
Result: 2001-02-17 04:38:40.12+00hour
小时域（0 - 23）
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
结果：20isodow
一周中的日，从周一（1）到周日（7）
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
结果：7除了周日，这和dow相同。这符合ISO 8601 中一周中的日的编号。
isoyear
日期所落在的ISO 8601 周编号的年（不适用于间隔）
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
结果：2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
结果：2006每一个ISO 8601 周编号的年都开始于包含1月4日的那一周的周一，在早的1月或迟的12月中ISO年可能和格里高利年不同。更多信息见week域。
这个域不能用于 PostgreSQL 8.3之前的版本。
microseconds
秒域，包括小数部分，乘以 1,000,000。请注意它包括全部的秒
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
结果：28500000millennium
千年
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
结果：319xx的年份在第二个千年里。第三个千年从 2001 年 1 月 1 日开始。
milliseconds
秒域，包括小数部分，乘以 1000。请注意它包括完整的秒。
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
结果：28500minute
分钟域（0 - 59）
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
结果：38month
对于timestamp值，它是一年里的月份数（1 - 12）； 对于interval值，它是月的数目，然后对 12 取模（0 - 11）
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
结果：2
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
结果：3
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
结果：1quarter
该天所在的该年的季度（1 - 4）
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
结果：1second
秒域，包括小数部分（0 - 59[1]）
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
结果：40
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
结果：28.5timezone
与 UTC 的时区偏移，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区（从技术上来看，PostgreSQL不使用 UTC，因为其中不处理闰秒）。
timezone_hour时区偏移的小时部分。
timezone_minute时区偏移的分钟部分。
week
该天在所在的ISO 8601 周编号的年份里是第几周。根据定义， 一年的第一周包含该年的 1月 4 日并且 ISO 周从星期一开始。换句话说，一年的第一个星期四在第一周。
在 ISO 周编号系统中，早的 1 月的日期可能位于前一年的第五十二或者第五十三周，而迟的 12 月的日期可能位于下一年的第一周。例如， 2005-01-01位于 2004 年的第五十三周，并且2006-01-01位于 2005 年的第五十二周，而2012-12-31位于 2013 年的第一周。我们推荐把isoyear域和week一起使用来得到一致的结果。
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
结果：7year
年份域。要记住这里没有0 AD，所以从AD年里抽取BC年应该小心处理。
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
结果：2001Note:
当输入值为 +/-Infinity 时，extract对于单调增的域（epoch、julian、year、isoyear、decade、century以及millennium）返回 +/-Infinity。对于其他域返回 NULL。PostgreSQL 9.6 之前的版本对所有输入无穷的情况都返回零。
extract函数主要的用途是做计算性处理。对于用于显示的日期/时间值格式化。
在传统的Ingres上建模的date_part函数等价于SQL标准函数extract：
date_part('field', source)请注意这里的field参数必须是一个串值，而不是一个名字。有效的date_part域名 和extract相同。
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
结果：16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
结果：49.2. date_trunc
date_trunc函数在概念上和用于数字的trunc函数类似。
date_trunc('field', source)source是类型timestamp或interval的值表达式（类型date和 time的值都分别被自动转换成timestamp或者interval）。field选择对输入值选用什么样的精度进行截断。返回的值是timestamp类型或者所有小于选定的 精度的域都设置为零（或者一，对于日期和月份）的interval。
field的有效值是∶
microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium例子：
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
结果：2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
结果：2001-01-01 00:00:00
9.9.3. AT TIME ZONE
AT TIME ZONE结构允许把时间戳转换成不同的时区。Table 9-31展示了其变体。
Table 9-31. AT TIME ZONE Variants
在这些表达式里，我们需要的时区zone可以指定为文本串（例如，'PST'）或者一个间隔 （例如，INTERVAL '-08:00'）。 在文本情况下，可用的时区名字可以用Section 8.5.3中描述的任何方式指定。
例子（假设本地时区是PST8PDT）：
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
结果：2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
结果：2001-02-16 18:38:40第一个例子接受一个无时区的时间戳然后把它解释成 MST 时间（UTC-7），然后这个时间转换为 PST（UTC-8）来显示。 第二个例子接受一个指定为 EST（UTC-5）的时间戳，然后把它 转换成 MST（UTC-7）的当地时间。
函数timezone(zone, timestamp)等效于 SQL 兼容的结构timestamp AT TIME ZONE zone。
9.4. 当前日期/时间
PostgreSQL提供了许多返回当前日期和时间的函数。这些 SQL 标准的函数全部都按照当前事务的开始时刻返回值：
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)CURRENT_TIME和CURRENT_TIMESTAMP传递带有时区的值；LOCALTIME和LOCALTIMESTAMP传递的值不带时区。
CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME和 LOCALTIMESTAMP可以有选择地接受一个精度参数， 该精度导致结果的秒域被园整为指定小数位。如果没有精度参数，结果将被给予所能得到的全部精度。
一些例子：
SELECT CURRENT_TIME;
结果：14:39:53.662522-05
SELECT CURRENT_DATE;
结果：2001-12-23
SELECT CURRENT_TIMESTAMP;
结果：2001-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
结果：2001-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
结果：2001-12-23 14:39:53.662522因为这些函数全部都按照当前事务的开始时刻返回结果，所以它们的值在事务运行的整个期间内都不改变。 我们认为这是一个特性：目的是为了允许一个事务在"当前"时间上有一致的概念， 这样在同一个事务里的多个修改可以保持同样的时间戳。
Note:
许多其它数据库系统可能会更频繁地推进这些值。
PostgreSQL同样也提供了返回当前语句开始时间的函数， 它们会返回函数被调用时的真实当前时间。这些非 SQL 标准的函数列表如下：
transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()transaction_timestamp()等价于CURRENT_TIMESTAMP，但是其命名清楚地反映了它的返回值。statement_timestamp()返回当前语句的开始时刻（更准确的说是收到 客户端最后一条命令的时间）。statement_timestamp()和transaction_timestamp()在一个事务的第一条命令期间返回值相同，但是在随后的命令中却不一定相同。 clock_timestamp()返回真正的当前时间，因此它的值甚至在同一条 SQL 命令中都会变化。timeofday()是一个有历史原因的PostgreSQL函数。和clock_timestamp()相似，timeofday()也返回真实的当前时间，但是它的结果是一个格式化的text串，而不是timestamp with time zone值。now()是PostgreSQL的一个传统，等效于transaction_timestamp()。
所有日期/时间类型还接受特殊的文字值now，用于指定当前的日期和时间（重申，被解释为当前事务的开始时刻）。 因此，下面三个都返回相同的结果：
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now'; -- 对于和 DEFAULT 一起使用是不正确的Tip: 在创建表期间指定一个DEFAULT子句时，你不会希望使用第三种形式。系统将在分析这个常量的时候把now转换为一个timestamp， 这样需要默认值时就会得到创建表的时间！而前两种形式要到实际使用缺省值的时候才被计算， 因为它们是函数调用。因此它们可以给出每次插入行的时刻。
9.5. 延时执行
下面的这些函数可以用于让服务器进程延时执行：
pg_sleep(seconds)
pg_sleep_for(interval)
pg_sleep_until(timestamp with time zone)pg_sleep让当前的会话进程休眠seconds 秒以后再执行。seconds是一个double precision 类型的值，所以可以指定带小数的秒数。pg_sleep_for是针对用 interval指定的较长休眠时间的函数。pg_sleep_until 则可以用来休眠到一个指定的时刻唤醒。例如：
SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');Note:
有效的休眠时间间隔精度是平台相关的，通常 0.01 秒是通用值。休眠延迟将至少持续指 定的时长， 也有可能由于服务器负荷而比指定的时间长。特别地， pg_sleep_until并不保证能刚好在指定的时刻被唤醒，但它不会 在比指定时刻早的时候醒来。
Warning
请确保在调用pg_sleep或者其变体时，你的会话没有持有不必要 的锁。否则其它会话可能必须等待你的休眠会话，因而减慢整个系统速度。
Notes
[1]
如果操作系统实现了闰秒，则为60
10. 枚举支持函数
对于枚举类型(在Section 8.7中描述）， 有一些函数允许更清洁的编码，而不需要为一个枚举类型硬写特定的值。它们被列在Table 9-32中。本例假定一个枚举类型被创建为：
CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');Table 9-32. 枚举支持函数
请注意，除了双参数形式的enum_range外， 这些函数忽略传递给它们的具体值，它们只关心声明的数据类型。 空值或类型的一个特定值可以通过，并得到相同的结果。这些函数更多地被用于一个表列或函数参数，而不是一个硬写的类型名，如例子中所建议。
11. 几何函数和操作符
几何类型point、box、 lseg、line、path、 polygon和circle有一大堆本地支持函数和操作符，如Table 9-33、Table 9-34和Table 9-35中所示。
Caution
请注意"same as"操作符（~=），表示point、box、polygon和circle类型的一般相等概念。这些类型中的某些还有一个=操作符，但是=只比较相同的面积。其它的标量比较操作符 （<=等等）也是为这些类型比较面积。
Table 9-33. 几何操作符
Note:
在PostgreSQL之前，包含操作符@>和<@被分别称为~和@。 这些名字仍然可以使用，但是已被废除并且最终将被移除。
Table 9-34. 几何函数
Table 9-35. 几何类型转换函数
我们可以把一个point的两个组成数字当作具有索引 0 和 1 的数组访问。例如，如果t.p是一个point列，那么SELECT p[0] FROM t检索 X 座标而 UPDATE t SET p[1] = ...改变 Y 座标。同样，box或者lseg类型的值可以当作两个point值的数组值看待。
函数area可以用于类型box、circle和path。area函数操作path数据类型的时候， 只有在path的点没有交叉的情况下才可用。例如，path '((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))'::PATH是不行的， 而下面的视觉上相同的 path '((0,0),(0,1),(1,1),(1,2),(2,2),(2,1),(1,1),(1,0),(0,0))'::PATH就可以。 如果交叉和不交叉的path概念让你疑惑，那么把上面两个path都画在一张图纸上，你就明白了。
12. 网络地址函数和操作符
Table 9-36展示了可以用于cidr和 inet类型的操作符。 操作符<<、<<=、 >>、>>=和 &&测试用于子网包含。它们只考虑两个地址的网 络部分（忽略任何主机部分），然后判断其中一个网络部分是等于另外一个或者是 另外一个的子网。
Table 9-36. cidr和inet操作符
Table 9-37展示了所有可以用于cidr和inet类型的函数。函数abbrev、host和text主要是为了提供可选的显示格式用的。
Table 9-37. cidr和inet函数
任何cidr值都能够被隐式或显式地转换为inet值， 因此上述能够操作inet值的函数也同样能够操作cidr值（也有独立的用于inet和cidr的函数，因为它的行为应当和这两种情况不同）。inet值也可以转换为cidr值。完成时，该网络掩码右侧的任何位都将无声地转换为零以获得一个有效的cidr值。另外，你还可以使用常规的造型语法将一个文本字符串转换为inet或cidr值：例如，inet(expression)或colname::cidr。
Table 9-38展示了可以用于macaddr类型的函数。 函数trunc(macaddr)返回一个 MAC 地址，该地址的最后三个字节设置为零。这样可以把剩下的前缀与一个制造商相关联。
Table 9-38. macaddr函数
macaddr类型还支持标准关系操作符 （>、<=等） 用于编辑次序，并且按位算术操作符（~、&和|）用于 NOT、AND 和 OR。
13. 文本搜索函数和操作符
Table 9-39、 Table 9-40和 Table 9-41总结了为全文搜索提供的函数和操作符。PostgreSQL的文本搜索功能的详细解释可参考Chapter 12。
Table 9-39. 文本搜索操作符
Note:
tsquery的包含操作符只考虑两个查询中的词位，而忽略组合操作符。
除了显示在表中的操作符，还定义了tsvector和tsquery类型的普通B-tree比较操作符（=、<等）。它们对于文本搜索不是很有用，但是允许使用。例如，建在这些类型列上的唯一索引。
Table 9-40. 文本搜索函数
Note:
所有接受一个可选的regconfig参数的文本搜索函数在该参数被忽略时，使用由default_text_search_config指定的配置。
Table 9-41中的函数被单独列出，因为它们通常不被用于日常的文本搜索操作。 它们有助于开发和调试新的文本搜索配置。
Table 9-41. 文本搜索调试函数
14. XML 函数
本节中描述的函数以及类函数的表达式都在类型xml的值上操作。类型xml的详细信息请参见Section 8.13。用于在值和类型xml之间转换的类函数的表达式xmlparse和xmlserialize就不在这里重复介绍。使用大部分这些函数要求安装时使用了configure --with-libxml进行编译。
14.1. 产生 XML 内容
有一组函数和类函数的表达式可以用来从 SQL 数据产生 XML 内容。它们特别适合于将查询结果格式化成 XML 文档以便于在客户端应用中处理。
14.1.1. xmlcomment
xmlcomment(text)
函数xmlcomment创建了一个 XML 值，它包含一个使用指定文本作为内容的 XML 注释。该文本不包含"--"或者也不会以一个"-"结尾，这样结果的结构是一个合法的 XML 注释。如果参数为空，结果也为空。
例子：
SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->14.1.2. xmlconcat
xmlconcat(xml[, ...])
函数xmlconcat将由单个 XML 值组成的列表串接成一个单独的值，这个值包含一个 XML 内容片断。空值会被忽略，只有当没有参数为非空时结果才为空。
例子：
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');
xmlconcat
----------------------
<abc/><bar>foo</bar>如果 XML 声明存在，它们会按照下面的方式被组合。如果所有的参数值都有相同的 XML 版本声明，该版本将被用在结果中，否则将不使用版本。如果所有参数值有独立声明值"yes"，那么该值将被用在结果中。如果所有参数值都有一个独立声明值并且至少有一个为"no"，则"no"被用在结果中。否则结果中将没有独立声明。如果结果被决定要要求一个独立声明但是没有版本声明，将会使用一个版本 1.0 的版本声明，因为 XML 要求一个 XML 声明要包含一个版本声明。编码声明会被忽略并且在所有情况中都会被移除。
例子：
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>14.1.3. xmlelement
xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...])
表达式xmlelement使用给定名称、属性和内容产生一个 XML 元素。
例子：
SELECT xmlelement(name foo);
xmlelement
------------
<foo/>
SELECT xmlelement(name foo, xmlattributes('xyz' as bar));
xmlelement
------------------
<foo bar="xyz"/>
SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');
xmlelement
-------------------------------------
<foo bar="2007-01-26">content</foo>不是合法 XML 名字的元素名和属性名将被逃逸，逃逸的方法是将违反的字符用序列_xHHHH_替换，其中HHHH是被替换字符的 Unicode 代码点的十六进制表示。例如：
SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));
xmlelement
----------------------------------
<foo_x0024_bar a_x0026_b="xyz"/>如果属性值是一个列引用，则不需要指定一个显式的属性名，在这种情况下列的名字将被默认用于属性的名字。在其他情况下，属性必须被给定一个显式名称。因此这个例子是合法的：
CREATE TABLE test (a xml, b xml);
SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;但是下面这些不合法：
SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;如果指定了元素内容，它们将被根据其数据类型格式化。如果内容本身也是类型xml，就可以构建复杂的 XML 文档。例如：
SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
xmlelement(name abc),
xmlcomment('test'),
xmlelement(name xyz));
xmlelement
----------------------------------------------
<foo bar="xyz"><abc/><!--test--><xyz/></foo>其他类型的内容将被格式化为合法的 XML 字符数据。这意味着字符 <, >, 和 & 将被转换为实体。二进制数据（数据类型bytea）将被表示成 base64 或十六进制编码，具体取决于配置参数xmlbinary的设置。为了将 SQL 和 PostgreSQL 数据类型和 XML 模式声明对齐，我们期待单独数据类型的特定行为能够改进，到那时将会出现一个更为精确的描述。
14.1.4. xmlforest
xmlforest(content [AS name] [, ...])
表达式xmlforest使用给定名称和内容产生一个元素的 XML 森林（序列）。
例子：
SELECT xmlforest('abc' AS foo, 123 AS bar);
xmlforest
------------------------------
<foo>abc</foo><bar>123</bar>
SELECT xmlforest(table_name, column_name)
FROM information_schema.columns
WHERE table_schema = 'pg_catalog';
xmlforest
-------------------------------------------------------------------------------------------
<table_name>pg_authid</table_name><column_name>rolname</column_name>
<table_name>pg_authid</table_name><column_name>rolsuper</column_name>
...如我们在第二个例子中所见，如果内容值是一个列引用，元素名称可以被忽略，这种情况下默认使用列名。否则，必须指定一个名字。
如上文xmlelement所示，非法 XML 名字的元素名会被逃逸。相似地，内容数据也会被逃逸来产生合法的 XML 内容，除非它已经是一个xml类型。
注意如果 XML 森林由多于一个元素组成，那么它不是合法的 XML 文档，因此在xmlelement中包装xmlforest表达式会有用处。
14.1.5. xmlpi
xmlpi(name target [, content])
表达式xmlpi创建一个 XML 处理指令。如果存在内容，内容不能包含字符序列?>。
例子：
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>14.1.6. xmlroot
xmlroot(xml, version text | no value [, standalone yes|no|no value])
表达式xmlroot修改一个 XML 值的根结点的属性。如果指定了一个版本，它会替换根节点的版本声明中的值；如果指定了一个独立设置，它会替换根节点的独立声明中的值。
SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),
version '1.0', standalone yes);
xmlroot
----------------------------------------
<?xml version="1.0" standalone="yes"?>
<content>abc</content>14.1.7. xmlagg
xmlagg(xml)
和这里描述的其他函数不同，函数xmlagg是一个聚集函数。它将聚集函数调用的输入值串接起来，非常像xmlconcat所做的事情，除了串接是跨行发生的而不是在单一行的多个表达式上发生。聚集表达式的更多信息请见Section 9.20。
例子：
CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, '<foo>abc</foo>');
INSERT INTO test VALUES (2, '<bar/>');
SELECT xmlagg(x) FROM test;
xmlagg
----------------------
<foo>abc</foo><bar/>为了决定串接的顺序，可以为聚集调用增加一个ORDER BY子句，如Section 4.2.7中所述。例如：
SELECT xmlagg(x ORDER BY y DESC) FROM test;
xmlagg
----------------------
<bar/><foo>abc</foo>我们推荐在以前的版本中使用下列非标准方法，并且它们在特定情况下仍然有用：
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>14.2. XML 谓词
这一节描述的表达式检查xml值的属性。
14.2.1. IS DOCUMENT
xml IS DOCUMENT
如果参数 XML 值是一个正确的 XML 文档，则IS DOCUMENT返回真，如果不是则返回假（即它是一个内容片断），或者是参数为空时返回空。文档和内容片断之间的区别请见Section 8.13。
14.2.2. XMLEXISTS
XMLEXISTS(text PASSING [BY REF] xml [BY REF])
如果第一个参数中的 XPath 表达式返回任何结点，则函数xmlexists返回真，否则返回假（如果哪一个参数为空，则结果就为空）。
例子：
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Toronto</town><town>Ottawa</town></towns>');
xmlexists
------------
t
(1 row)BY REF子句在 PostgreSQL 中没有作用，但是为了和其他实现保持 SQL 一致性和兼容性还是允许它出现。每一种 SQL 标准，第一个BY REF是被要求的，而第二个则是可选的。也要注意 SQL 标准指定xmlexists结构来将一个 XQuery 表达式作为第一个参数，但 PostgreSQL 目前只支持 XPath，它是 XQuery的一个子集。
14.2.3. xml_is_well_formed
xml_is_well_formed(text)
xml_is_well_formed_document(text)
xml_is_well_formed_content(text)
这些函数检查一个text串是不是一个良构的 XML，返回一个布尔结果。xml_is_well_formed_document检查一个良构的文档，而xml_is_well_formed_content检查良构的内容。如果xmloption配置参数被设置为DOCUMENT，xml_is_well_formed会做第一个函数的工作；如果配置参数被设置为CONTENT，xml_is_well_formed会做第二个函数的工作。这意味着xml_is_well_formed对于检查一个到类型xml的简单造型是否会成功非常有用，而其他两个函数对于检查XMLPARSE的对应变体是否会成功有用。
例子：
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
xml_is_well_formed
--------------------
f
(1 row)
SELECT xml_is_well_formed('<abc/>');
xml_is_well_formed
--------------------
t
(1 row)
SET xmloption TO CONTENT;
SELECT xml_is_well_formed('abc');
xml_is_well_formed
--------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
xml_is_well_formed_document
-----------------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
xml_is_well_formed_document
-----------------------------
f
(1 row)最后一个例子显示了这些检查也包括名字空间是否正确地匹配。
14.3. 处理 XML
要处理数据类型xml的值， PostgreSQL 提供了函数xpath和xpath_exists，它们计算 XPath 1.0 表达式。
xpath(xpath, xml [, nsarray])
函数xpath在 XML 值xml上计算 XPath 表达式xpath (a text value)。它返回一个 XML 值的数组，该数组对应于该 XPath 表达式产生的结点集合。如果该 XPath 表达式返回一个标量值而不是一个结点集合，将会返回一个单一元素的数组。
第二个参数必须是一个良构的 XML 文档。特殊地，它必须有一个单一根结点元素。
该函数可选的第三个参数是一个名字空间映射的数组。这个数组应该是一个二维text数组，其第二轴长度等于2（即它应该是一个数组的数组，其中每一个都由刚好 2 个元素组成）。每个数组项的第一个元素是名字空间的名称（别名），第二个元素是名字空间的 URI。并不要求在这个数组中提供的别名和在 XML 文档本身中使用的那些名字空间相同（换句话说，在 XML 文档中和在xpath函数环境中，别名都是本地的）。
例子：
SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath
--------
{test}
(1 row)要处理默认（匿名）命名空间，做这样的事情：
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
ARRAY[ARRAY['mydefns', 'http://example.com']]);
xpath
--------
{test}
(1 row)
xpath_exists(xpath, xml [, nsarray])函数xpath_exists是xpath函数的一种特殊形式。这个函数不是返回满足 XPath 的单一 XML 值，它返回一个布尔值表示查询是否被满足。这个函数等价于标准的XMLEXISTS谓词，不过它还提供了对一个名字空间映射参数的支持。
例子：
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath_exists
--------------
t
(1 row)14.4. 将表映射到 XML
下面的函数将会把关系表的内容映射成 XML 值。它们可以被看成是 XML 导出功能：
table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xml(cursor refcursor, count int, nulls boolean,
tableforest boolean, targetns text)每一个函数的返回值都是xml。
table_to_xml映射由参数tbl传递的命名表的内容。regclass类型接受使用常见标记标识表的字符串，包括可选的模式限定和双引号。query_to_xml执行由参数query传递的查询并且映射结果集。cursor_to_xml从cursor指定的游标中取出指定数量的行。如果需要映射一个大型的表，我们推荐这种变体，因为每一个函数都是在内存中构建结果值的。
如果tableforest为假，则结果的 XML 文档看起来像这样：
<tablename>
<row>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</row>
<row>
...
</row>
...
</tablename>如果tableforest为真，结果是一个看起来像这样的 XML 内容片断：
<tablename>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</tablename>
<tablename>
...
</tablename>
...如果没有表名可用，在映射一个查询或一个游标时，在第一种格式中使用串table，在第二种格式中使用row。
这几种格式的选择由用户决定。第一种格式是一个正确的 XML 文档，它在很多应用中都很重要。如果结果值要被重组为一个文档，第二种格式在cursor_to_xml函数中更有用。前文讨论的产生 XML 内容的函数（特别是xmlelement）可以被用来把结果修改成符合用户的要求。
数据值会被以前文的函数xmlelement中描述的相同方法映射。
参数nulls决定空值是否会被包含在输出中。如果为真，列中的空值被表示为：
<columnname xsi:nil="true"/>其中xsi是 XML 模式实例的 XML 名字空间前缀。一个合适的名字空间声明将被加入到结果值中。如果为假，包含空值的列将被从输出中忽略掉。
参数targetns指定想要的结果的 XML 名字空间。如果没有想要的特定名字空间，将会传递一个空串。
下面的函数返回 XML 模式文档，这些文档描述上述对应函数所执行的映射：
table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text)最重要的是相同的参数被传递来获得匹配的 XML 数据映射和 XML 模式文档。
下面的函数产生 XML 数据映射和对应的 XML 模式，并把产生的结果链接在一起放在一个文档（或森林）中。在要求自包含和自描述的结果是它们非常有用：
table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)另外，下面的函数可用于产生相似的整个模式或整个当前数据库的映射：
schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
database_to_xml(nulls boolean, tableforest boolean, targetns text)
database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)
database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text)注意这些函数可能产生很多数据，它们都需要在内存中被构建。在请求大型模式或数据库的内容映射时，可以考虑分别映射每一个表，甚至通过一个游标来映射。
一个模式内容映射的结果看起来像这样：
<schemaname>
table1-mapping
table2-mapping
...
</schemaname>其中一个表映射的格式取决于上文解释的tableforest参数。
一个数据库内容映射的结果看起来像这样：
<dbname>
<schema1name>
...
</schema1name>
<schema2name>
...
</schema2name>
...
</dbname>其中的模式映射如上所述。
作为一个使用这些函数产生的输出的例子，Figure 9-1展示了一个 XSLT 样式表，它将table_to_xml_and_xmlschema的输出转换为一个包含表数据的扁平转印的 HTML 文档。以一种相似的方式，这些函数的结果可以被转换成其他基于 XML 的格式。
Figure 9-1. 转换 SQL/XML 输出到 HTML 的 XSLT 样式表
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.w3.org/1999/xhtml"
>
<xsl:output method="xml"
doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN"
indent="yes"/>
<xsl:template match="/*">
<xsl:variable name="schema" select="//xsd:schema"/>
<xsl:variable name="tabletypename"
select="$schema/xsd:element[@name=name(current())]/@type"/>
<xsl:variable name="rowtypename"
select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/>
<html>
<head>
<title><xsl:value-of select="name(current())"/></title>
</head>
<body>
<table>
<tr>
<xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name">
<th><xsl:value-of select="."/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="row">
<tr>
<xsl:for-each select="*">
<td><xsl:value-of select="."/></td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>15. JSON 函数和操作符
Table 9-42展示了可以用于两种 JSON 数据类型（见Section 8.14）的操作符。
Table 9-42. json和jsonb 操作符
Note:
对json和jsonb类型，这些操作符都有其并行变体。 域/元素/路径抽取操作符返回与其左手输入（json或jsonb） 相同的类型，不过那些被指定为返回text的除外，它们的返回值会被强制 为文本。如果该 JSON 输入没有匹配请求的正确结构（例如那样的元素不存在），这些 域/元素/路径抽取操作符会返回 NULL 而不是失败。 接受整数 JSON 数组下标的 域/元素/路径抽取操作符都支持表示从数组末尾开始的负值下标形式。
Table 9-1中展示的标准比较操作符只对 jsonb有效，而不适合json。
如Table 9-43中所示，还存在一些只适合 jsonb的操作符。这些操作符中的很多可以用jsonb 操作符类索引。
Table 9-43. 额外的jsonb操作符
Note:
||操作符将其每一个操作数的顶层的元素串接起来。它不会递归 操作。例如，如果两个操作数都是具有公共域名称的对象，结果中的域值将 只是来自右手操作数的值。
Table 9-44展示了可用于创建 json 和 jsonb值的函数（没有用于 jsonb的与row_to_json和 array_to_json等价的函数。不过，to_jsonb函数 提供了这些函数的很大一部分相同的功能）。
Table 9-44. JSON 创建函数
Note:
array_to_json和row_to_json与to_json 具有相同的行为，不过它们提供了更好的打印选项。针对to_json所描述 的行为同样也适用于由其他 JSON 创建函数转换的每个值。
Note: hstore扩展有一个从hstore到json 的造型，因此通过 JSON 创建函数转换的hstore值将被表示为 JSON 对象，而不是原始字符串值。
Table 9-45展示了可用来处理json 和jsonb值的函数。
Table 9-45. JSON 处理
Note:
很多这些函数和操作符将把 JSON 字符串中的 Unicode 转义转换成合适的单一字符。如果 输入类型是jsonb，这就没有问题，因为该转换已经完成了。但是对于json 输入，这可能会导致抛出一个错误（如Section 8.14所述）。
Note:
在json_populate_record、json_populate_recordset、 json_to_record和json_to_recordset中，来自 JSON 的 类型强制是"尽力而为"并且对于某些类型可能得不到想要的值。JSON 键会被 匹配目标行类型中相同的列名。没有出现在目标行类型中的 JSON 域将会被从输出中忽略， 并且不匹配任何 JSON 域的目标列将被简单地作为 NULL。
Note:
jsonb_set和jsonb_insert的path参数中除最后一项之外的所有项都必须存在于target中。如果create_missing为假，jsonb_set的path参数的所有项都必须存在。如果这些条件不满足，则返回的target不会被改变。
如果最后的路径项是一个对象键，在它不存在且给定了新值的情况下会创建它。如果最后的路径项是一个数组索引，为正值则表示从左边开始计数，为负值表示从右边开始计数 - -1表示最右边的元素，以此类推。如果该项超过范围 -array_length .. array_length -1 并且 create_missing 为真，则该项为负时把新值加载数组的开始处，而该项为正时把新值加在数组的末尾处。
Note:
不要把json_typeof函数的null返回值与 SQL 的 NULL 弄混。 虽然调用json_typeof('null'::json)将会返回null，但调用 json_typeof(NULL::json)将会返回一个 SQL 的 NULL。
Note:
如果json_strip_nulls的参数在任何对象中包含重复的域名称， 结果在语义上可能有所不同，具体取决于它们发生的顺序。这不是 jsonb_strip_nulls的一个问题，因为jsonb值 不能具有重复的对象域名称。
16. 序列操作函数
本节描述用于操作序列对象的函数，序列对象也被称为序列生成器或者就是序列。序列对象都是用CREATE SEQUENCE创建的特殊的单行表。序列对象通常用于为表的行生成唯一的标识符。Table 9-46中列出的这些序列函数，可以为我们从序列对象中获取连续的序列值提供了简单的、多用户安全的 方法。
Table 9-46. 序列函数
将要由序列函数调用操作的序列是用一个regclass参数声明的， 它只是序列在pg_class系统表里面的 OID。不过，你不需要手工查找 OID， 因为regclass数据类型的输入转换器会帮你做这件事情。 只要写出用单引号包围的序列名字即可，因此它看上去像文本常量。为了和普通SQL名字处理兼容，这个字串将转换成小写形式， 除非在序列名字周围包含双引号。因此：
nextval('foo') 操作序列foo
nextval('FOO') 操作序列foo
nextval('"Foo"') 操作序列Foo必要时序列名可以用模式限定∶
nextval('myschema.foo') 操作myschema.foo
nextval('"myschema".foo') 同上
nextval('foo') 在搜索路径中查找foo
Note:
在PostgreSQL 8.1 之前，序列函数的参数类型是text， 而不是 regclass，并且前文所述的从文本串到 OID 值的转换将在每次调用的时候发生。 为了向后兼容，这个处理仍然存在，但是在内部实际上是通过在函数调用前隐式地将text转换成regclass实现的。
当你把一个序列函数的参数写成一个无修饰的文字串，那么它将变成类型为regclass的常量。因为这只是一个 OID，它将跟踪最初标识的序列，而不管后面是否改名、模式变化等等。 这种"早期绑定"的行为通常是列默认值和视图中引用的序列所需要的。 但是有时候你可能想要"延迟绑定"，其中序列的引用是在运行时解析的。要得到延迟绑定的行为，我们可以强制常量被存储为text常量，而不是regclass：
nextval('foo'::text) foo在运行时查找请注意，延迟绑定是PostgreSQL版本 8.1 之前唯一被支持的行为， 因此你可能需要做这些来保留旧应用的语义。
当然，序列函数的参数也可以是表达式。如果它是一个文本表达式，那么隐式的转换将导致运行时的查找。
可用的序列函数有∶
nextval
递增序列对象到它的下一个值并且返回该值。这个动作是自动完成的： 即使多个会话并发执行nextval，每个进程也会安全地收到一个唯一的序列值。
如果一个序列对象是用默认参数创建的，连续的nextval调用将会返回从 1 开始的连续的值。其他的行为可以通过在CREATE SEQUENCE命令中使用特殊参数来获得；详见该命令的参考页。
Important:
为了避免阻塞从同一个序列获取序号的并发事务，nextval操作从来不会被回滚。也就是说，一旦一个值被取出就视同被用掉并且不会被再次返回给调用者，即便调用该操作的外层事务后来中止或者调用查询后来没有使用取得的值也是这样。例如一个带有ON CONFLICT子句的INSERT会计算要被插入的元组，其中可能就包括调用nextval，然后才会检测到导致它转向ON CONFLICT规则的冲突。这种情况就会在已分配值的序列中留下未被使用的"空洞"。因此，PostgreSQL的序列对象不能被用来得到"无间隙"的序列。
currval
在当前会话中返回最近一次nextval取到的该序列的值（如果在本会话中从未在该序列上调用过nextval，那么会报告一个错误）。请注意因为此函数返回一个会话本地的值，不论其它会话是否在当前会话之后执行过nextval，它都能给出一个可预测的回答。
lastval
返回当前会话里最近一次nextval返回的值。 这个函数等效于currval，只是它不用序列名作为参数， 它会引用当前会话里面最近一次被应用的序列的nextval。如果当前会话还没有调用过nextval，那么调用lastval会报错。
setval
重置序列对象的计数器值。双参数的形式设置序列的last_value域为指定值并且将其is_called域设置为 true，表示下一次nextval将在返回值之前递增该序列。currval报告的值也被设置为指定的值。在三参数形式里，is_called可以设置为true或false。true具有和双参数形式相同的效果。如果你把它设置为false，那么下一次nextval将返回指定的值，而从随后的nextval才开始递增该序列。此外，在这种情况中currval报告的值不会被改变。 例如：
SELECT setval('foo', 42); 下一次nextval会返回 43
SELECT setval('foo', 42, true); 同上
SELECT setval('foo', 42, false); 下一次nextval将返回 42setval返回的结果就是它的第二个参数的值。
Important:
因为序列是非事务的，setval造成的改变不会由于事务的回滚而撤销。
17. 条件表达式
本节描述在PostgreSQL中可用的SQL兼容的条件表达式。
Tip: 如果你的需求超过这些条件表达式的能力，你可能会希望用一种更富表现力的编程语言写一个存储过程。
17.1. CASE
SQL CASE表达式是一种通用的条件表达式，类似于其它编程语言中的 if/else 语句：
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
ENDCASE子句可以用于任何表达式可以出现的地方。每一个condition是一个返回boolean结果的表达式。如果结果为真，那么CASE表达式的结果就是符合条件的result，并且剩下的CASE表达式不会被处理。如果条件的结果不为真，那么以相同方式搜寻任何随后的WHEN子句。如果没有WHEN condition为真，那么CASE表达式的值就是在ELSE子句里的result。如果省略了ELSE子句而且没有条件为真，结果为空。
例子：
SELECT * FROM test;
a
---
1
2
3
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other所有result表达式的数据类型都必须可以转换成单一的输出类型。 参阅Section 10.5获取细节。
下面这个"简单"形式的CASE表达式是上述通用形式的一个变种：
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END第一个expression会被计算，然后与所有在WHEN子句中的每一个value对比，直到找到一个相等的。如果没有找到匹配的，则返回在ELSE子句中的result（或者控制）。 这类似于 C 里的switch语句。
上面的例子可以用简单CASE语法来写：
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | otherCASE表达式并不计算任何无助于判断结果的子表达式。例如，下面是一个可以避免被零除错误的方法：
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;Note: 如Section 4.2.14中所述，在有几种情况中一个表达式的子表达式 会被计算多次，因此"CASE只计算必要的表达式"这 一原则并非不可打破。例如一个常量子表达式1/0通常将会在规划时导致一次 除零错误，即便它位于一个执行时永远也不会进入的CASE分支时也是 如此。
17.2. COALESCE
COALESCE(value [, ...])
COALESCE函数返回它的第一个非空参数的值。当且仅当所有参数都为空时才会返回空。它常用于在为显示目的检索数据时用缺省值替换空值。例如：
SELECT COALESCE(description, short_description, '(none)') ...如果description不为空，这将会返回它的值，否则如果short_description非空则返回short_description的值，如果前两个都为空则返回(none)。
和CASE表达式一样，COALESCE将不会 计算无助于判断结果的参数；也就是说，在第一个非空参数右边的参数不会被计算。这个 SQL 标准函数提供了类似于NVL和IFNULL的能力，它们被用在某些其他数据库系统中。
17.3. NULLIF
NULLIF(value1, value2)
当value1和value2相等时，NULLIF返回一个空值。 否则它返回value1。 这些可以用于执行前文给出的COALESCE例子的逆操作：
SELECT NULLIF(value, '(none)') ...在这个例子中，如果value是(none)，将返回空值，否则返回value的值。
17.4. GREATEST和LEAST
GREATEST(value [, ...])
LEAST(value [, ...])GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。 这些表达式必须都可以转换成一个普通的数据类型，它将会是结果类型 （参阅Section 10.5获取细节）。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。
请注意GREATEST和LEAST都不是 SQL 标准，但却是很常见的扩展。某些其他数据库让它们在任何参数为 NULL 时返回 NULL，而不是在所有参数都为 NULL 时才返回 NULL。
18. 数组函数和操作符
Table 9-47显示了可以用于数组类型的操作符。
Table 9-47. 数组操作符
数组比较使用默认的 B-Tree 在元素数据类型上的比较函数对数组内容按元素逐一进行。 多维数组的元素按照行序进行访问（最后的下标变化最快）。如果两个数组的内容相同但维数不等，那么维度信息中的第一个不同将决定排序顺序（这是对PostgreSQL 8.2 之前版本的修改： 老版本认为内容相同的两个数组相等，即使它们的维数或下标范围并不相同）。
Table 9-48展示了可以用于数组类型的函数。
Table 9-48. 数组函数
在array_position和array_positions中， 每一个数组元素都使用IS NOT DISTINCT FROM 语义与要搜索的值比较。
在array_position中，如果值没有找到则返回 NULL。
在array_positions中，只有当数组为 NULL时才返回NULL，如果该值 没有在该数组中找到则返回一个空数组。
在string_to_array中，如果定界符参数为 NULL，输入字符串中的每一个字符将变成结果数组中的一个独立元素。如果定界符是一个空串，则整个输入字符串被作为一个单一元素的数组返回。否则输入字符串会被在每一个出现定界符字符串的位置分裂。
在string_to_array中，如果空值串参数被忽略或者为 NULL，输入中的子串不会被替换成 NULL。在array_to_string中，如果空值串参数被忽略或者为 NULL，该数组中的任何空值元素会被简单地跳过并且不会在输出串中被表示。
Note:
string_to_array的行为中有两点与PostgreSQL 9.1之前的版本不同。首先，当输入串的长度为零时，它将返回一个空（无元素）数组而不是 NULL。其次，如果定界符串为 NULL，该函数会将输入划分成独立字符，而不是像以前那样返回 NULL。
19. 范围函数和操作符
范围类型的概述请见Section 8.17。
Table 9-49展示了范围类型可用的操作符。
Table 9-49. 范围操作符
简单比较操作符<、 >、<=和 >=首先比较下界，并且只有在下界相等时才比较上界。这些比较通常对范围不怎么有用，但是还是提供它们以便能够在范围上构建 B树索引。
当涉及一个空范围时，左部/右部/相邻操作符总是返回假；即一个空范围被认为不在任何其他范围前面或者后面。
如果结果范围可能需要包含两个分离的子范围，并和差操作符将会失败，因为这样的范围无法被表示。
Table 9-50显示可用于范围类型的函数。
Table 9-50. 范围函数
如果范围为空或者被请求的界是无限的，lower和upper函数返回空值。函数lower_inc、upper_inc、lower_inf和upper_inf对一个空范围全部返回假。
20. 聚集函数
聚集函数从一个输入值的集合计算一个单一结果。内建的普通聚集函数被列在Table 9-51和Table 9-52中。内建的顺序集聚集函数被列在Table 9-53和 Table 9-54中。分组聚集（与聚集函数更相关）被列在Table 9-55中。
Table 9-51. 通用聚集函数
请注意，除了count以外，这些函数在没有行被选中时返回控制。尤其是sum函数在没有输入行时返回空值，而不是零，并且array_agg在这种情况返回空值而不是一个空数组。必要时可以用coalesce把空值替换成零或一个空数组。
支持部分模式的聚集函数有资格参与到各种优化中，例如并行聚集。
Note:
布尔聚集bool_and和bool_or对应于标准的 SQL 聚集every和any或some。而对于any 和some，似乎在标准语法中有一个歧义：
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
如果子查询返回一行有一个布尔值的结果，这里的ANY可以被认为是引入一个子查询，或者是作为一个聚集函数。因而标准的名称不能指定给这些聚集。
Note:
在把count聚集应用到整个表上时，习惯于使用其他 SQL 数据管理系统的用户可能会对它的性能感到失望。一个如下的查询：
SELECT count(*) FROM sometable;将会要求与整个表大小成比例的工作：PostgreSQL将需要扫描整个表或者整个包含表中所有行的索引。
与相似的用户定义的聚集函数一样，聚集函数array_agg、 json_agg、jsonb_agg、json_object_agg、jsonb_object_agg、string_agg和xmlagg会依赖输入值的顺序产生有意义的不同结果值。这个顺序默认是不用指定的，但是可以在聚集调用时使用ORDER BY子句进行控制，如Section 4.2.7中所示。作为一种选择，从一个排序号的子查询来提供输入值通常会有帮助。例如：
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;注意如果外面的查询层次包含额外的处理（例如连接），这种方法可能会失败，因为这可能导致子查询的输出在计算聚集之前被重新排序。
Table 9-52展示了通常被用在统计分析中的聚集函数（这些被隔离出来是为了不和常用聚集混淆）。其中描述提到了N，它表示对应于所有非空输入表达式的输入行的数目。在所有情况中，如果计算是无意义的，将会返回空值，例如当N为零。
Table 9-52. 用于统计的聚集函数
Table 9-53展示了一些使用 有序集聚集语法的聚集函数。这些函数有时也被称为 "逆分布"函数。
Table 9-53. 有序集聚集函数
所有列在Table 9-53中的聚集会忽略它们的已 排序输入中的空值。对那些有一个fraction参数的聚 集来说，该分数值必须位于 0 和 1 之间，否则会抛出错误。不过，一个空分数值会 产生一个空结果。
每个列在Table 9-54中的聚集都与一个定义在 Section 9.21中的同名窗口函数相关联。在每种情况中，聚集 结果的计算方法是：假设根据args构建的"假想"行已 经被增加到从sorted_args计算得到的已排序行分组中， 然后用相关联的窗口函数针对该行返回的值就是聚集的结果。
Table 9-54. 假想集聚集函数
对于这些假想集聚集的每一个，args中给定的直接参数 列表必须匹配sorted_args中给定的聚集参数的 数量和类型。与大部分的内建聚集不同，这些聚集并不严格，即它们不会丢弃包含空值 的输入行。空值的排序根据ORDER BY子句中指定的规则进行。
Table 9-55. 分组操作
分组操作用来与分组集合（见Section 7.2.4）共同来 区分结果行。GROUPING操作的参数并不会被实际计算，但是它们必 须准确地匹配在相关查询层次的GROUP BY子句中给定的表达式。 最右边参数指派的位是最低有效位，如果对应的表达式被包括在产生结果行的分组 集合的分组条件中则每一位是 0，否则是 1。例如：
=> SELECT * FROM items_sold;
make | model | sales
-------+-------+-------
Foo | GT | 10
Foo | Tour | 20
Bar | City | 15
Bar | Sport | 5
(4 rows)
=> SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);
make | model | grouping | sum
-------+-------+----------+-----
Foo | GT | 0 | 10
Foo | Tour | 0 | 20
Bar | City | 0 | 15
Bar | Sport | 0 | 5
Foo | | 1 | 30
Bar | | 1 | 20
| | 3 | 50
(7 rows)
- 窗口函数
窗口函数提供在与当前查询行相关的行集合上执行计算的能力。
Table 9-56列出了内建的窗口函数。注意必须使用窗口函数的语法调用这些函数； 一个OVER子句是必需的。
除了这些函数外，任何内建的或用户定义的普通聚集函数（但有序集或假想集聚集除外）都可以作为窗口函数。仅当调用跟着OVER子句时，聚集函数才会作为窗口函数；否则它们作为常规的聚集。
Table 9-56. 通用窗口函数
在Table 9-56中列出的所有函数都依赖于相关窗口定义的ORDER BY子句指定的排序顺序。 在ORDER BY排序中不能区分的行被称为是同等行。定义的这四个排名函数，对于任何两个同等行的答案相同。
注意first_value、last_value和nth_value只考虑"窗口帧"内的行，它默认情况下包含从分区的开始行直到当前行的最后一个同等行。这对last_value可能不会给出有用的结果，有时对nth_value也一样。你可以通过向OVER子句增加一个合适的帧声明（RANGE或ROWS）来重定义帧。
当一个聚集函数被用作窗口函数时，它将在当前行的窗口帧内的行上聚集。 一个使用ORDER BY和默认窗口帧定义的聚集产生一种"运行时求和"类型的行为，这可能是或者不是想要的结果。为了获取在整个分区上的聚集，忽略ORDER BY或者使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。 其它窗口帧声明可以用来获得其它的效果。
Note:
SQL 标准为lead、lag、first_value、last_value和nth_value定义了 一个RESPECT NULLS或IGNORE NULLS选项。 这在PostgreSQL中没有实现：行为总是与标准的默认相同，即RESPECT NULLS。 同样，标准中用于nth_value的FROM FIRST或FROM LAST选项没有实现： 只有支持默认的FROM FIRST行为（你可以通过反转ORDER BY的排序达到FROM LAST的结果）。
22. 子查询表达式
本节描述PostgreSQL中可用的SQL兼容的子查询表达式。所有本节中成文的表达式都返回布尔值（真/假）结果。
22.1. EXISTS
EXISTS (subquery)
EXISTS的参数是一个任意的SELECT语句， 或者说子查询。系统对子查询进行运算以判断它是否返回行。如果它至少返回一行，那么EXISTS的结果就为"真"； 如果子查询没有返回行，那么EXISTS的结果是"假"。
子查询可以引用来自周围的查询的变量，这些变量在该子查询的任何一次计算中都起常量的作用。
这个子查询通常只是运行到能判断它是否可以返回至少一行为止， 而不是等到全部结束。在这里写任何有副作用的子查询都是不明智的（例如调用序列函数）；这些副作用是否发生是很难判断的。
因为结果只取决于是否会返回行，而不取决于这些行的内容， 所以这个子查询的输出列表通常是无关紧要的。一个常用的编码习惯是用EXISTS(SELECT 1 WHERE ...)的形式写所有的EXISTS测试。不过这条规则有例外，例如那些使用INTERSECT的子查询。
下面这个简单的例子类似在col2上的一次内联接，但是它为每个 tab1的行生成最多一个输出，即使存在多个匹配tab2的行也如此∶
SELECT col1
FROM tab1
WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);22.2. IN
expression IN (subquery)
右手边是一个圆括弧括起来的子查询， 它必须正好只返回一个列。左手边表达式将被计算并与子查询结果逐行进行比较。 如果找到任何等于子查询行的情况，那么IN的结果就是"真"。 如果没有找到相等行，那么结果是"假"（包括子查询没有返回任何行的情况）。
请注意如果左手边表达式得到空值，或者没有相等的右手边值， 并且至少有一个右手边行得到空值，那么IN结构的结果将是空值，而不是假。这个行为是遵照 SQL 处理空值的一般规则的。
和EXISTS一样，假设子查询将被完成运行完全是不明智的。
row_constructor IN (subquery)
这种形式的IN的左手边是一个行构造器， 如Section 4.2.13中所述。 右手边是一个圆括弧子查询，它必须返回和左手边返回的行中表达式所构成的完全一样多的列。 左手边表达式将被计算并与子查询结果逐行进行比较。如果找到任意相等的子查询行，则IN的结果为"true"。如果没有找到相等行， 那么结果为"假"（包括子查询不返回行的情况）。
通常，表达式或者子查询行里的空值是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么这两行不等； 否则这样的行比较的结果是未知（空值）。如果所有行的结果要么是不等， 要么是空值，并且至少有一个空值，那么IN的结果是空值。
22.3. NOT IN
expression NOT IN (subquery)
右手边是一个用圆括弧包围的子查询，它必须返回正好一个列。左手边表达式将被计算并与子查询结果逐行进行比较。 如果只找到不相等的子查询行（包括子查询不返回行的情况），那么NOT IN的结果是"真"。 如果找到任何相等行，则结果为"假"。
请注意如果左手边表达式得到空值，或者没有相等的右手边值， 并且至少有一个右手边行得到空值，那么NOT IN结构的结果将是空值，而不是真。这个行为是遵照 SQL 处理空值的一般规则的。
和EXISTS一样，假设子查询会完全结束是不明智的。
row_constructor NOT IN (subquery)
这种形式的NOT IN的左手边是一个行构造器， 如Section 4.2.13中所述。 右手边是一个圆括弧子查询，它必须返回和左手边返回的行中表达式所构成的完全一样多的列。 左手边表达式将被计算并与子查询结果逐行进行比较。如果找到不等于子查询行的行，则NOT IN的结果为"true"。如果找到相等行， 那么结果为"假"（包括子查询不返回行的情况）。
通常，表达式或者子查询行里的空值是按照 SQL 布尔表达式的一般规则进行组合的。 如果两个行对应的成员都非空并且相等，那么认为这两行相等；如果任意对应成员为非空且不等，那么这两行不等； 否则这样的行比较的结果是未知（空值）。如果所有行的结果要么是不等， 要么是空值，并且至少有一个空值，那么NOT IN的结果是空值。
22.4. ANY/SOME
expression operator ANY (subquery)
expression operator SOME (subquery)
这种形式的右手边是一个圆括弧括起来的子查询， 它必须返回正好一个列。左手边表达式将被计算并使用给出的 操作符对子查询结果逐行进行比较。如果获得任何真值结果，那么ANY的结果就是"真"。 如果没有找到真值结果，那么结果是"假"（包括子查询没有返回任何行的情况）。
SOME是ANY的同义词。IN等价于= ANY。
请注意如果没有任何成功并且至少有一个右手边行为该操作符结果生成空值， 那么ANY结构的结果将是空值，而不是假。 这个行为是遵照 SQL 处理空值布尔组合的一般规则制定的。
和EXISTS一样，假设子查询将被完全运行是不明智的。
row_constructor operator ANY (subquery)
row_constructor operator SOME (subquery)
这种形式的左手边是一个行构造器，如Section 4.2.13所述。右手边是一个圆括弧括起来的子查询， 它必须返回和左手边列表给出的表达式一样多的列。左手边表达式将被计算并使用给出的操作符对子查询结果逐行进行比较。如果比较为任何子查询行返回真，则ANY的结果为"真"。如果比较对每一个子查询行都返回假，则结果为"假"（包括子查询不返回行的情况）。如果比较不对任何行返回真并且至少对一行返回 NULL，则结果为 NULL。
22.5. ALL
expression operator ALL (subquery)
ALL 的这种形式的右手边是一个圆括弧括起来的子查询， 它必须只返回一列。左手边表达式将被计算并使用给出的 操作符对子查询结果逐行进行比较。该操作符必须生成布尔结果。 如果所有行得到真（包括子查询没有返回任何行的情况），ALL的结果就是"真"。如果没有存在任何假值结果，那么结果是"假"。如果比较为任何行都不返回假并且对至少一行返回 NULL，则结果为 NULL。
NOT IN等价于<> ALL。
和EXISTS一样，假设子查询将被完全运行是不明智的。
row_constructor operator ALL (subquery)
ALL的这种形式的左手边是一个行构造器， 右手边是一个圆括弧括起来的子查询，它必须返回和左手边行中表达式一样多的列。 左手边表达式将被计算并使用给出的 操作符对子查询结果逐行进行比较。如果对所有子查询行该比较都返回真，那么ALL的结果就是"真"（包括子查询没有返回任何行的情况）。如果对任何子查询行比较返回假，则结果为"假"。如果比较对任何子查询行都不返回假并且对至少一行返回 NULL，则结果为 NULL。
22.6. 单一行比较
row_constructor operator (subquery)
左手边是一个行构造器，右手边是一个圆括弧括起来的子查询，该查询必须返回和左手边行中表达式数目完全一样的列。 另外，该子查询不能返回超过一行的数量（如果它返回零行，那么结果就是空值）。 左手边被计算并逐行与右手边的子查询结果行比较。
23. 行和数组比较
本节描述几个特殊的结构，用于在值的组之间进行多重比较。这些形式语法上和前面一节的子查询形式相关，但是不涉及子查询。 这种形式涉及的数组子表达式是PostgreSQL的扩展； 其它的是SQL兼容的。所有本节记录的表达式形式都返回布尔（Boolean）结果（真/假）。
23.1. IN
expression IN (value [, ...])
右手边是一个圆括弧包围的标量列表。如果左手边表达式的结果等于任何右手边表达式中的一个，结果为"真"。它是下面形式的缩写
expression = value1
OR
expression = value2
OR
...请注意如果左手边表达式得到空值，或者没有相等的右手边值并且至少有一个右手边的表达式得到空值，那么IN结构的结果将为空值，而不是假。这符合 SQL 处理空值的布尔组合的一般规则。
23.2. NOT IN
expression NOT IN (value [, ...])
右手边是一个圆括弧包围的标量列表。如果左手边表达式的结果不等于所有右手边表达式，结果为"真"。它是下面形式的缩写
expression <> value1
AND
expression <> value2
AND
...请注意如果左手边表达式得到空值，或者没有相等的右手边值并且至少有一个右手边的表达式得到空值，那么NOT IN结构的结果将为空值， 而不是我们可能天真地认为的真值。这符合 SQL 处理空值的布尔组合的一般规则。
Tip: x NOT IN y在所有情况下都等效于NOT (x IN y)。但是，在处理空值的时候，用NOT IN比用IN更可能迷惑新手。最好尽可能用正逻辑来表达你的条件。
23.3. ANY/SOME (array)
expression operator ANY (array expression)
expression operator SOME (array expression)
右手边是一个圆括弧包围的表达式，它必须得到一个数组值。左手边表达式被计算并且使用给出的操作符对数组的每个元素进行比较，这个操作符必须得到布尔结果。如果得到了任何真值结果，那么ANY的结果是"真"。 如果没有找到真值结果（包括数组只有零个元素的情况），那么结果是"假"。
如果数组表达式得到一个空数组，ANY的结果将为空值。如果左手边的表达式得到空值，ANY通常是空值（尽管一个非严格比较操作符可能得到一个不同的结果）。另外，如果右手边的数组包含任何空值元素或者没有得到真值比较结果，ANY的结果将是空值而不是假（再次，假设是一个严格的比较操作符）。这符合 SQL 对空值的布尔组合的一般规则。
SOME是ANY的同义词。
23.4. ALL (array)
expression operator ALL (array expression)
右手边是一个圆括弧包围的表达式，它必须得到一个数组值。左手边表达式将被计算并使用给出的操作符与数组的每个元素进行比较，这个操作符必须得到一个布尔结果。如果所有比较都得到真值结果，那么ALL的结果是 "真"（包括数组只有零个元素的情况）。如果有任何假值结果，那么结果是"假"。
如果数组表达式得到一个空数组，ALL的结果将为空值。如果左手边的表达式得到空值，ALL通常是空值（尽管一个非严格比较操作符可能得到一个不同的结果）。另外，如果右手边的数组包含任何空值元素或者没有得到假值比较结果，ALL的结果将是空值而不是真（再次，假设是一个严格的比较操作符）。这符合 SQL 对空值的布尔组合的一般规则。
23.5. 行构造器比较
row_constructor operator row_constructor
每一边都是一个行构造器，如Section 4.2.13所述。两个行值必须具有相同数量的域。每一边被计算并且被逐行比较。当操作符是 =、 <>、 < <=、 >、 >=时，允许进行行构造器比较。每一个行元素必须是具有一个默认 B 树操作符类的类型，否则尝试比较会产生一个错误。
Note: Errors related to the number or types of elements might not occur if the comparison is resolved using earlier columns.
=和<>情况略有不同。如果两行的所有对应成员都是非空且相等则这两行被认为相等；如果任何对应成员是非空但是不相等则这两行不相等；否则行比较的结果为未知（空值）。
对于<、<=、>和>=情况，行元素被从左至右比较，在找到一处不等的或为空的元素对就立刻停下来。如果这一对元素都为空值，则行比较的结果为未知（空值）；否则这一对元素的比较结果决定行比较的结果。例如，ROW(1,2,NULL) < ROW(1,3,0)得到真，而不是空值，因为第三对元素并没有被考虑。
Note:
在PostgreSQL 8.2之前，<、<=、>和>=情况不是按照每个 SQL 声明来处理的。一个像ROW(a,b) < ROW(c,d)的比较会被实现为a < c AND b < d，而结果行为等价于a < c OR (a = c AND b < d)。
row_constructor IS DISTINCT FROM row_constructor
这个结构与<>行比较相似，但是它对于空值输入不会得到空值。任何空值被认为和任何非空值不相等（有区别），并且任意两个空值被认为相等（无区别）。因此结果将总是为真或为假，永远不会是空值。
row_constructor IS NOT DISTINCT FROM row_constructor
这个结构与=行比较相似，但是它对于空值输入不会得到空值。任何空值被认为和任何非空值不相等（有区别），并且任意两个空值被认为相等（无区别）。因此结果将总是为真或为假，永远不会是空值。
23.6. 组合类型比较
record operator record
SQL 规范要求在结果依赖于比较两个 NULL 值或者一个 NULL 与一个非 NULL 时逐行比较返回 NULL。PostgreSQL只有在比较两个行构造器（如Section 9.23.5）的结果或者比较一个行构造器与一个子查询的输出时才这样做（如Section 9.22中所述）。在其他比较两个组合类型值的环境中，两个 NULL 域值被认为相等，并且一个 NULL 被认为大于一个非 NULL。为了得到组合类型的一致的排序和索引行为，这样做是必要的。
每一边都会被计算并且它们会被逐行比较。当操作符是 =、 <>、 <、 <=、 >或者 >=时或者具有与这些类似的语义时，允许组合类型的比较（更准确地说，如果一个操作符是一个 B 树操作符类的成员，或者是一个 B 树操作符类的=成员的否定词，它就可以是一个行比较操作符）。 上述操作符的行为与用于行构造器（见Section 9.23.5）的IS [ NOT ] DISTINCT FROM相同。
为了支持包含无默认 B 树操作符类的元素的行匹配，为组合类型比较定义了下列操作符： =, <>, <, <=, >, and >=. 这些操作符比较两行的内部二进制表达。即使两行用相等操作符的比较为真，两行也可能 具有不同的二进制表达。行在这些比较操作符之下的排序是决定性的，其他倒没什么意义。 这些操作符在内部被用于物化视图并且可能对其他如复制之类的特殊功能有用，但是它们 并不打算用在书写查询这类普通用途中。
24. 集合返回函数
本节描述那些可能返回多于一行的函数。目前这个类中被使用最广泛的是级数生成函数， 如Table 9-57和Table 9-58所述。其他更特殊的集合返回函数在本手册的其他地方描述。
Table 9-57. 级数生成函数
当step为正时，如果start大于stop则返回零行。相反，当step为负时，如果start小于stop则返回零行。对于NULL输入也会返回零行。step为零是一个错误。下面是一些例子：
SELECT * FROM generate_series(2,4);
generate_series
-----------------
2
3
4
(3 rows)
SELECT * FROM generate_series(5,1,-2);
generate_series
-----------------
5
3
1
(3 rows)
SELECT * FROM generate_series(4,3);
generate_series
-----------------
(0 rows)
SELECT generate_series(1.1, 4, 1.3);
generate_series
-----------------
1.1
2.4
3.7
(3 rows)
-- 这个例子依赖于日期+整数操作符
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
dates
------------
2004-02-05
2004-02-12
2004-02-19
(3 rows)
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
'2008-03-04 12:00', '10 hours');
generate_series
---------------------
2008-03-01 00:00:00
2008-03-01 10:00:00
2008-03-01 20:00:00
2008-03-02 06:00:00
2008-03-02 16:00:00
2008-03-03 02:00:00
2008-03-03 12:00:00
2008-03-03 22:00:00
2008-03-04 08:00:00
(9 rows)Table 9-58. 下标生成函数
generate_subscripts是一个快捷函数，它为给定数组的指定维度生成一组合法的下标。对于不具有请求维度的数组返回零行，对于 NULL 数组也返回零行（但是会对 NULL 数组元素返回合法的下标）。下面是一些例子：
-- 基本使用
SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s;
s
---
1
2
3
4
(4 rows)
-- 表示一个数组，下标和被下标的值需要一个子查询
SELECT * FROM arrays;
a
--------------------
{-1,-2}
{100,200,300}
(2 rows)
SELECT a AS array, s AS subscript, a[s] AS value
FROM (SELECT generate_subscripts(a, 1) AS s, a FROM arrays) foo;
array | subscript | value
---------------+-----------+-------
{-1,-2} | 1 | -1
{-1,-2} | 2 | -2
{100,200,300} | 1 | 100
{100,200,300} | 2 | 200
{100,200,300} | 3 | 300
(5 rows)
-- 平面化一个 2D 数组
CREATE OR REPLACE FUNCTION unnest2(anyarray)
RETURNS SETOF anyelement AS
$$
select $1[i][j]
from generate_subscripts($1,1) g1(i),
generate_subscripts($1,2) g2(j);
$$
LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
unnest2
---------
1
2
3
4
(4 rows)
When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().
-- set returning function WITH ORDINALITY
SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
ls | n
-----------------+----
pg_serial | 1
pg_twophase | 2
postmaster.opts | 3
pg_notify | 4
postgresql.conf | 5
pg_tblspc | 6
logfile | 7
base | 8
postmaster.pid | 9
pg_ident.conf | 10
global | 11
pg_clog | 12
pg_snapshots | 13
pg_multixact | 14
PG_VERSION | 15
pg_xlog | 16
pg_hba.conf | 17
pg_stat_tmp | 18
pg_subtrans | 19
(19 rows)25. 系统信息函数
Table 9-59展示了多个可以抽取会话和系统信息的函数。
除了本节列出的函数，还有一些与统计系统相关的函数也提供系统信息。
Table 9-59. 会话信息函数
Note: current_catalog、current_schema、current_user、session_user和user在SQL里有特殊的语意状态： 它们被调用时结尾不要跟着园括号（在 PostgreSQL 中，圆括号可以有选择性地被用于current_schema，但是不能和其他的一起用）。
session_user通常是发起当前数据库连接的用户，不过超级用户可以用SET SESSION AUTHORIZATION修改这个设置。current_user是用于权限检查的用户标识。通常， 它总是等于会话用户，但是可以被SET ROLE改变。它也会在函数执行的过程中随着属性SECURITY DEFINER的改变而改变。在 Unix 的说法里，那么会话用户是"真实用户"，而当前用户是"有效用户"。
current_schema返回在搜索路径中的第一个模式名（如果搜索路径是空则返回空值）。 如果创建表或者其它命名对象时没有声明目标模式，那么它将是被用于这些对象的模式。current_schemas(boolean)返回一个在搜索路径中出现的所有模式名的数组。布尔选项决定pg_catalog这样的隐式包含的系统模式是否包含在返回的搜索路径中。
Note:
搜索路径可以在运行时修改。命令是：
SET search_path TO schema [, schema, ...]
inet_client_addr返回当前客户端的 IP 地址，inet_client_port返回它的端口号。inet_server_addr返回接受当前连接的服务器的 IP 地址，而inet_server_port返回对应的端口号。如果连接是通过 Unix 域套接字进行的，那么所有这些函数都返回 NULL。
pg_blocking_pids返回一个进程 ID 的数组，数组中的进程中的会话阻塞了指定进程 ID 所代表的服务器进程，如果指定的服务器进程不存在或者没有被阻塞则返回空数组。如果一个进程持有与另一个进程加锁请求冲突的锁（硬锁），或者前者正在等待一个与后者加锁请求冲突的锁并且前者在该锁的等待队列中位于后者的前面（软锁），则前者会阻塞后者。在使用并行查询时，这个函数的结果总是会列出客户端可见的进程 ID（即pg_backend_pid的结果），即便实际的锁是由工作者进程所持有或者等待也是如此。这样造成的后果是，结果中可能会有很多重复的 PID。还要注意当一个预备事务持有一个冲突锁时，这个函数的结果中它将被表示为一个为零的进程 ID。对这个函数的频繁调用可能对数据库性能有一些影响，因为它需要短时间地独占访问锁管理器的共享状态。
pg_conf_load_time返回服务器配置文件最近被载入的timestamp with time zone（如果当前会话在那时就已经存在，这个值将是该会话自己重新读取配置文件的时间，因此在不同的会话中这个读数会有一点变化。如果不是这样，这个值就是 postmaster 进程重读配置文件的时间）。
pg_my_temp_schema返回当前会话临时模式的 OID，如果没有使用临时模式（因为它没有创建任何临时表）则返回零。如果给定的 OID 是另一个会话的临时模式的 OID，则pg_is_other_temp_schema返回真（这是有用的，例如，要将其他会话的临时表从一个目录显示中排除）。
pg_listening_channels返回当前会话正在监听的异步通知频道的名称的集合。pg_notification_queue_usage返回等待处理的通知占可用的通知空间的比例，它是一个 0-1 范围内的double值。详见LISTEN和NOTIFY。
pg_postmaster_start_time返回服务器启动的timestamp with time zone。
version返回一个描述PostgreSQL服务器版本的字符串。你也可以从server_version或者一个机器可读的版本server_version_num得到这个信息。软件开发者应该使用server_version_num（从 8.2 开始可用）或者 PQserverVersion ，而不必解析文本形式的版本。
Table 9-60列出那些允许用户编程查询对象访问权限的函数。参阅Section 5.6获取更多有关权限的信息。
Table 9-60. 访问权限查询函数
has_table_privilege判断一个用户是否可以用某种特定的方式访问一个表。 该用户可以通过名字或者 OID （pg_authid.oid） 来指定，也可以用public表示 PUBLIC 伪角色。如果省略该参数，则使用current_user。 该表可以通过名字或者 OID 指定（因此，实际上有六种 has_table_privilege的变体，我们可以通过它们的参数数目和类型来区分它们） 。如果用名字指定，那么在必要时该名字可以是模式限定的。 所希望的权限类型是用一个文本串来指定的，它必须是下面的几个值之一： SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES或TRIGGER。WITH GRANT OPTION可以被选择增加到一个权限类型来测试是否该权限是使用转授选项得到。另外，可以使用逗号分隔来列出多个权限类型，在这种情况下只要具有其中之一的权限则结果为真（权限字符串的大小写并不重要，可以在权限名称之间出现额外的空白，但是在权限名内部不能有空白）。一些例子：
SELECT has_table_privilege('myschema.mytable', 'select');
SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION');
has_sequence_privilege检查一个用户是否能以某种特定方式访问一个序列。它的参数可能性和has_table_privilege相似。所希望测试的访问权限类型必须是下列之一：USAGE、SELECT或UPDATE。
has_any_column_privilege检查一个用户是否能以特定方式访问一个表的任意列。其参数可能性和has_table_privilege类似，除了所希望的访问权限类型必须是下面值的某种组合：SELECT、INSERT、UPDATE或REFERENCES。注意在表层面上具有这些权限的任意一个都会隐式地把它授权给表中的每一列，因此如果has_table_privilege对同样的参数返回真则has_any_column_privilege将总是返回真。但是如果在至少一列上有一个该权限的列级授权，has_any_column_privilege也会成功。
has_column_privilege检查一个用户是否能以特定方式访问一个列。它的参数可能性与has_table_privilege类似，并且列还可以使用名字或者属性号来指定。希望的访问权限类型必须是下列值的某种组合：SELECT、INSERT、UPDATE或REFERENCES。注意在表级别上具有这些权限中的任意一种将会隐式地把它授予给表上的每一列。
has_database_privilege检查一个用户是否能以特定方式访问一个数据库。它的参数可能性类似 has_table_privilege。希望的访问权限类型必须是以下值的某种组合：CREATE、CONNECT、TEMPORARY或TEMP（等价于TEMPORARY）。
has_function_privilege检查一个用户是否能以特定方式访问一个函数。其参数可能性类似has_table_privilege。在用一个文本串而不是 OID 指定一个函数时，允许的输入和regprocedure数据类型一样（参阅 Section 8.18）。希望的访问权限类型必须是EXECUTE。一个例子：
SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');has_foreign_data_wrapper_privilege检查一个用户是否能以特定方式访问一个外部数据包装器。它的参数可能性类似于has_table_privilege。希望的访问权限类型必须是USAGE。
has_language_privilege检查一个用户是否可以以某种特定的方式访问一个过程语言。 其参数可能性类似 has_table_privilege。希望的访问权限类型必须是USAGE。
has_schema_privilege检查一个用户是否可以以某种特定的方式访问一个模式。 其参数可能性类似 has_table_privilege。希望的访问权限类型必须是CREATE或USAGE。
has_server_privilege检查一个用户是否可以以某种特定的方式访问一个外部服务器。 其参数可能性类似 has_table_privilege。希望的访问权限类型必须是USAGE。
has_tablespace_privilege检查一个用户是否可以以某种特定的方式访问一个表空间。其参数可能性类似 has_table_privilege。希望的访问权限类型必须是CREATE。
has_type_privilege检查一个用户是否能以特定的方式访问一种类型。其参数的可能性类同于has_table_privilege。在用字符串而不是 OID 指定类型时，允许的输入和regtype数据类型相同（见Section 8.18）。期望的访问特权类型必须等于USAGE。
pg_has_role检查一个用户是否可以以某种特定的方式访问一个角色。其参数可能性类似 has_table_privilege，除了public不能被允许作为一个用户名。希望的访问权限类型必须是下列值的某种组合：MEMBER或USAGE。MEMBER表示该角色中的直接或间接成员关系（即使用SET ROLE的权力），而USAGE表示不做SET ROLE的情况下该角色的权限是否立即可用。
row_security_active检查在 current_user的上下文和环境中是否为指定的 表激活了行级安全性。表可以用名称或者 OID 指定。
Table 9-61展示了决定是否一个特定对象在当前模式搜索路径中可见的函数。例如，如果一个表所在的模式在当前搜索路径中并且在它之前没有出现过相同的名字，这个表就被说是可见的。这等价于在语句中表可以被用名称引用但不加显式的模式限定。要列出所有可见表的名字：
SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);Table 9-61. 模式可见性查询函数
每一个函数对一种数据库对象执行可见性检查。注意pg_table_is_visible也可被用于视图、物化视图、索引、序列和外部表，pg_type_is_visible也可以被用于域。对于函数和操作符，如果在路径中更早的地方没有出现具有相同名称和参数数据类型的对象，该对象在搜索路径中是可见的。对于操作符类，名称和相关的索引访问方法都要考虑。
所有这些函数都要求用对象 OID 来标识将被检查的对象。如果你想用名称来测试一个对象，使用 OID 别名类型（regclass、regtype、regprocedure、regoperator、regconfig或regdictionary）将会很方便。例如：
SELECT pg_type_is_visible('myschema.widget'::regtype);注意以这种方式测试一个非模式限定的类型名没什么意义 — 如果该名称完全能被识别，它必须是可见的。
Table 9-62列出了从系统目录抽取信息的函数。
Table 9-62. 系统目录信息函数
format_type返回一个数据类型的 SQL 名称，它由它的类型 OID 标识并且可能是一个类型修饰符。如果不知道相关的修饰符，则为类型修饰符传递 NULL。
pg_get_keywords返回一组记录描述服务器识别的 SQL 关键字。word列包含关键字。catcode列包含一个分类码：U为未被预定，C 为列名，T类型或函数名，R为预留。catdesc列包含一个可能本地化的描述分类的字符串。
pg_get_constraintdef、pg_get_indexdef、pg_get_ruledef和pg_get_triggerdef分别重建一个约束、索引、规则或触发器的创建命令（注意这是一个反编译的重构，而不是命令的原始文本）。pg_get_expr反编译一个表达式的内部形式，例如一个列的默认值。在检查系统目录内容时有用。如果表达式可能包含 Var，在第二个参数中指定它们引用的关系的 OID；如果不会出现 Var，第二个参数设置为 0 即可。pg_get_viewdef重构定义一个视图的SELECT查询。这些函数的大部分都有两种变体，一种可以可选地"优质打印"结果。优质打印的格式可读性更强，但是默认格式更可能被未来版本的PostgreSQL以相同的方式解释。在转出目的中避免使用优质打印输出。为优质打印参数传递假将得到和不带该参数的变体相同的结果。
pg_get_functiondef为一个函数返回一个完整的CREATE OR REPLACE FUNCTION语句。pg_get_function_arguments返回一个函数的参数列表，形式按照它们出现在CREATE FUNCTION中的那样。pg_get_function_result类似地返回函数的合适的RETURNS子句。pg_get_function_identity_arguments返回标识一个函数必要的参数列表，形式和它们出现在ALTER FUNCTION中的一样。这种形式忽略默认值。
pg_get_serial_sequence返回与一个列相关联的序列的名称，如果与列相关联的序列则返回 NULL。第一个输入参数是一个带可选模式的表名，第二个参数是一个列名。因为第一个参数可能是一个模式和表，它不能按照一个双引号包围的标识符来对待，意味着它默认情况下是小写的。而第二个参数只是一个列名，将被当作一个双引号包围的来处理并且会保留其大小写。函数返回的值会被适当地格式化以便传递给序列函数（参见Section 9.16）。这种关联可以使用ALTER SEQUENCE OWNED BY来改变或移除（该函数应该曾经被称作pg_get_owned_sequence，它目前的名字反映了它通常和serial或bigserial列一起使用）。
pg_get_userbyid抽取给定 OID 的角色的名称。
pg_index_column_has_property、pg_index_has_property和pg_indexam_has_property返回指定的索引列、索引或者索引访问方法是否具有指定性质。如果性质的名称找不到或者不适用于特定的对象，亦或者 OID 或者列号不表示合法的对象，则返回NULL。列的性质可参见Table 9-63，索引的性质可参见Table 9-64，访问方法的性质可参见Table 9-65（注意扩展访问方法可以为其索引定义额外的性质）。
Table 9-63. 索引列属性
Table 9-64. 索引性质
Table 9-65. 索引访问方法性质
当传入pg_class.reloptions或pg_attribute.attoptions时，pg_options_to_table返回存储选项名称/值对（option_name/option_value）的集合。
pg_tablespace_databases允许一个表空间被检查。它返回一组数据库的 OID，这些数据库都有对象存储在该表空间中。如果这个函数返回任何行，则该表空间为非空并且不能被删除。为了显示该表空间中的指定对象，你将需要连接到pg_tablespace_databases标识的数据库并且查询它们的pg_class目录。
pg_typeof返回传递给它的值的数据类型的 OID。这在检修或者动态构建 SQL 查询时有用。函数被声明为返回regtype，它是一个 OID 别名类型（见Section 8.18）；这表明它和一个用于比较目的的 OID 相同，但是作为一个类型名称显示。例如：
SELECT pg_typeof(33);
pg_typeof
-----------
integer
(1 row)
SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
typlen
--------
4
(1 row)表达式collation for返回传递给它的值的排序规则。例子：
SELECT collation for (description) FROM pg_description LIMIT 1;
pg_collation_for
------------------
"default"
(1 row)
SELECT collation for ('foo' COLLATE "de_DE");
pg_collation_for
------------------
"de_DE"
(1 row)值可能被加上引号并且变成模式限定的。如果从参数表达式得不到排序规则，则返回一个空值。如果参数不是一个可排序的数据类型，则抛出一个错误。
to_regclass、to_regproc、to_regprocedure, to_regoper、to_regoperator、to_regtype、to_regnamespace和to_regrole函数把关系、函数、操作符、类型、模式和角色的名称（以text给出）分别转换成、regclass、regproc、regprocedure、regoper、regoperator、regtype、regnamespace和regrole对象。这些函数与 text 转换的不同在于它们不接受数字 OID，并且在名称无法找到时不会抛出错误而是返回空。对于to_regproc和to_regoper，如果给定名称匹配多个对象时返回空。
Table 9-66列出了与数据库对象 标识和定位有关的函数。
Table 9-66. 对象信息和定位函数
pg_describe_object会返回一个数据库对象的文本描述， 它由目录 OID、对象 OID 和一个（可能为零的）子对象 ID 所指定。这种描述是为 了人类可读的，并且可能是被翻译过的，具体取决于服务器配置。这有助于确定一 个存储在pg_depend目录中的对象的标识。
pg_identify_object返回一行，其中包含有足以唯一标识 由目录 OID、对象 OID 和一个（可能为零的）子对象 ID 指定的数据库对象的信息。 该信息是共机器读取的，并且不会被翻译。type标识数据库对象 的类型；schema是该对象所属的模式名，如果对象类型不属于 模式则为NULL；name是对象的名称，必要时会被 加上引号，只有在它可以被用作该对象的唯一标识符（可能还要带上模式名）时才 存在，否则为NULL；identity是完整的对象标识， 它会表现为与对象类型相关的精确格式，并且如有必要，该格式中的每个部分都会 被模式限定。
pg_identify_object_as_address返回一行，其中包含有 足以唯一标识由目录 OID、对象 OID 和一个（可能为零的）子对象 ID 指定的数据 库对象的信息。返回的信息独立于当前服务器，也就是说，它可以被用来在另一个 服务器中标识一个具有相同命名的对象。type标识数据库对象 的类型；name和args是文本数组，它们一起 构成了对对象的引用。这三个列可以被传递给 pg_get_object_address以获得该对象的内部地址。这个函数是 pg_get_object_address的逆函数。
pg_get_object_address返回一行，其中包含有足以唯一 标识由类型、对象名和参数数组指定的数据库对象的信息。返回值可以被用在诸如 pg_depend等系统目录中并且可以被传递给 pg_identify_object或pg_describe_object等其他 系统函数。catalog_id是包含该对象的系统目录 OID； object_id是对象本身的 OID，而 object_sub_id是子对象 ID，如果没有则为零。这个函数是 pg_identify_object_as_address的逆函数。
Table 9-67中展示的函数抽取注释，注释是由COMMENT命令在以前存储的。如果对指定参数找不到注释，则返回空值。
Table 9-67. 注释信息函数
col_description为一个表列返回注释，该表列由所在表的 OID 和它的列号指定（obj_description不能被用在表列，因为表列没有自己的 OID）。
obj_description的双参数形式返回一个由其 OID 和所在系统目录名称指定的数据库对象的注释。例如，obj_description(123456,'pg_class')将会检索出 OID 为123456的表的注释。obj_description的单参数形式只要求对象 OID。它已经被废弃，因为无法保证 OID 在不同系统目录之间是唯一的；这样可能会返回错误的注释。
shobj_description用起来就像obj_description，但是前者是用于检索共享对象上的注释。某些系统目录对于一个集簇中的所有数据库是全局的，并且其中的对象的描述也是全局存储的。
Table 9-68中展示的函数以一种可导出的形式提供了服务器事务信息。这些函数的主要用途是判断在两个快照之间哪些事务被提交。
Table 9-68. 事务 ID 和快照
内部事务 ID 类型（xid）是 32 位宽并且每 40 亿个事务就会回卷。但是，这些函数导出一种 64 位格式，它被使用一个"世代"计数器，这样在一个安装的生命期内不会回卷。这些函数使用的数据类型txid_snapshot存储了在一个特定时刻有关事务 ID 可见性的信息。它的成分在Table 9-69中描述。
Table 9-69. 快照成分
txid_snapshot的文本表示是xmin:xmax:xip_list。例如10:20:10,14,15表示xmin=10, xmax=20, xip_list=10, 14, 15。
Table 9-70中展示的函数提供了有关于 已经提交事务的信息。这些函数主要提供有关事务何时被提交的信息。只有当 track_commit_timestamp配置选项被启用时它们才能 提供有用的数据，并且只对已提交事务提供数据。
Table 9-70. 已提交事务信息
Table 9-71中所展示的函数能打印initdb期间初始化的信息，例如系统目录版本。它们也能显示有关预写式日志和检查点处理的信息。这些信息是集簇范围内的，不与任何特定的一个数据库相关。对于同一种来源，它们返回和pg_controldata大致相同的信息，不过其形式更适合于SQL函数。
Table 9-71. 控制数据函数
pg_control_checkpoint返回一个Table 9-72中所示的记录
Table 9-72. pg_control_checkpoint列
pg_control_system返回一个Table 9-73中所示的记录
Table 9-73. pg_control_system列
pg_control_init返回一个Table 9-74中所示的记录
Table 9-74. pg_control_init列
pg_control_recovery返回一个Table 9-75中所示的记录
Table 9-75. pg_control_recovery列
26. 系统管理函数
这一节描述的函数被用来控制和监视一个PostgreSQL安装。
26.1. 配置设定函数
Table 9-76展示了那些可以用于查询以及修改运行时配置参数的函数。
Table 9-76. 配置设定函数
current_setting得到setting_name设置的当前值。它对应于SQL命令SHOW。一个例子：
SELECT current_setting('datestyle');
current_setting
-----------------
ISO, MDY
(1 row)如果没有名为setting_name的设置，除非提供missing_ok并且其值为true，current_setting会抛出错误。
set_config将参数setting_name设置为new_value。如果 is_local设置为true，那么新值将只应用于当前事务。 如果你希望新值应用于当前会话，那么应该使用false。 它等效于 SQL 命令 SET。例如：
SELECT set_config('log_statement_stats', 'off', false);
set_config
------------
off
(1 row)26.2. 服务器信号函数
在Table 9-77中展示的函数向其它服务器进程发送控制信号。默认情况下这些函数只能被超级用户使用，但是如果需要，可以利用GRANT把访问特权授予给其他用户。
Table 9-77. 服务器信号函数
这些函数中的每一个都在成功时返回true，并且在失败时返回false。
pg_cancel_backend和pg_terminate_backend向由进程 ID 标识的后端进程发送信号（分别是SIGINT或SIGTERM）。一个活动后端的进程 ID可以从pg_stat_activity视图的pid列中找到，或者通过在服务器上列出postgres进程（在 Unix 上使用ps或者在Windows上使用任务管理器）得到。一个活动后端的角色可以在pg_stat_activity视图的usename列中找到。
pg_reload_conf给服务器发送一个SIGHUP信号， 导致所有服务器进程重载配置文件。
pg_rotate_logfile给日志文件管理器发送信号，告诉它立即切换到一个新的输出文件。这个函数只有在内建日志收集器运行时才能工作，因为否则就不存在日志文件管理器子进程。 subprocess.
26.3. 备份控制函数
Table 9-78中展示的函数可以辅助制作在线备份。这些函数不能在恢复期间执行（pg_is_in_backup、pg_backup_start_time和pg_xlog_location_diff除外）。
Table 9-78. 备份控制函数
pg_start_backup接受一个参数，这个参数可以是备份的任意用户定义的标签（通常这是备份转储文件将被存储的名字）。当被用在排他模式中时，该函数向数据库集簇的数据目录写入一个备份标签文件（backup_label）和一个表空间映射文件（tablespace_map，如果在pg_tblspc/目录中有任何链接），执行一个检查点，然后以文本方式返回备份的起始事务日志位置。用户可以忽略这个结果值，但是为了可能需要的场合我们还是提供该值。 当在非排他模式中使用时，这些文件的内容会转而由pg_stop_backup函数返回，并且应该由调用者写入到备份中去。
postgres=# select pg_start_backup('label_goes_here');
pg_start_backup
-----------------
0/D4445B8
(1 row)第二个参数是可选的，其类型为boolean。如果为true，它指定尽快执行pg_start_backup。这会强制一个立即执行的检查点，它会导致 I/O 操作的峰值，拖慢任何并发执行的查询。
在一次排他备份中，pg_stop_backup会移除标签文件以及pg_start_backup创建的tablespace_map文件（如果存在）。在一次非排他备份中，backup_label和tablespace_map的内容会包含在该函数返回的结果中，并且应该被写入到该备份的文件中（这些内容不在数据目录中）。
该函数还在事务日志归档区里创建一个备份历史文件。这个历史文件包含给予pg_start_backup的标签、备份的起始与终止事务日志位置以及备份的起始和终止时间。返回值是备份的终止事务日志位置（同样也可以被忽略）。在记录结束位置之后，当前事务日志插入点被自动地推进到下一个事务日志文件，这样结束的事务日志文件可以立即被归档来结束备份。
pg_switch_xlog移动到下一个事务日志文件，允许当前文件被归档（假定你正在使用连续归档）。返回值是在甘冈完成的事务日志文件中结束事务日志位置 + 1。如果从上一次事务日志切换依赖没有事务日志活动，pg_switch_xlog不会做任何事情并且返回当前正在使用的事务日志文件的开始位置。
pg_create_restore_point创建一个命名事务日志记录，它可以被用作恢复目标，并且返回相应的事务日志位置。这个给定的名字可以用于recovery_target_name来指定恢复要进行到的点。避免使用同一个名称创建多个恢复点，因为恢复会停止在第一个匹配名称的恢复目标。
pg_current_xlog_location以上述函数所使用的相同格式显示当前事务日志的写位置。类似地，pg_current_xlog_insert_location显示当前事务日志插入点，而pg_current_xlog_flush_location显示当前事务日志的刷写点。在任何情况下，插入点是事务日志的"逻辑"终止点，而写入位置是已经实际从服务器内部缓冲区写出的日志的终止点，刷写位置则是被确保写入到持久存储中的日志的终止点。写入位置是可以从服务器外部检查的终止点，对那些关注归档部分完成事务日志文件的人来说，这就是他们需要的位置。插入和刷写点主要是为了服务器调试目的而存在的。这些都是只读操作并且不需要超级用户权限。
你可以使用pg_xlogfile_name_offset从任何上述函数的结果中抽取相应的事务日志文件名称以及字节偏移。例如：
postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
file_name | file_offset
--------------------------+-------------
00000001000000000000000D | 4039624
(1 row)相似地，pg_xlogfile_name只抽取事务日志文件名。当给定的事务日志位置正好在一个事务日志文件的边界，这些函数都返回之前的事务日志文件的名称。这对管理事务日志归档行为通常是所希望的行为，因为前一个文件是当前需要被归档的最后一个文件。
pg_xlog_location_diff以字节数计算两个事务日志位置之间的差别。它可以和pg_stat_replication或Table 9-78中其他的函数一起使用来获得复制延迟。
26.4. 恢复控制函数
Table 9-79中展示的函数提供有关后备机当前状态的信息。这些函数可以在恢复或普通运行过程中被执行。
Table 9-79. 恢复信息函数
Table 9-80中展示的函数空值恢复的进程。这些函数只能在恢复过程中被执行。
Table 9-80. 恢复控制函数
在恢复被暂停时，不会有进一步的数据库改变被应用。如果在热备模式，所有新的查询将看到数据库的同一个一致快照，并且在恢复被继续之前不会有更多查询冲突会产生。
如果流复制被禁用，暂停状态可以无限制地继续而不出问题。在流复制进行时，WAL 记录将继续被接收，最后将会填满可用的磁盘空间，取决于暂停的持续时间、WAL 的产生率和可用的磁盘空间。
26.5. 快照同步函数
PostgreSQL允许数据库会话同步它们的快照。一个快照决定对于正在使用该快照的事务哪些数据是可见的。当两个或者更多个会话需要看到数据库中的相同内容时，就需要同步快照。如果两个会话独立开始其事务，就总是有可能有某个第三事务在两个START TRANSACTION命令的执行之间提交，这样其中一个会话就可以看到该事务的效果而另一个则看不到。
为了解决这个问题，PostgreSQL允许一个事务导出它正在使用的快照。只要导出的事务仍然保持打开，其他事务可以导入它的快照，并且因此可以保证它们可以看到和第一个事务看到的完全一样的数据库视图。但是注意这些事务中的任何一个对数据库所作的更改对其他事务仍然保持不可见，和未提交事务所作的修改一样。因此这些事务是针对以前存在的数据同步，而对由它们自己所作的更改则采取正常的动作。
如Table 9-81中所示，快照通过pg_export_snapshot函数导出，并且通过SET TRANSACTION命令导入。
Table 9-81. 快照同步函数
在恢复被暂停时，不会有进一步的数据库改变被应用。如果在热备模式，所有新的查询将看到数据库的同一个一致快照，并且在恢复被继续之前不会有更多查询冲突会产生。
如果流复制被禁用，暂停状态可以无限制地继续而不出问题。在流复制进行时，WAL 记录将继续被接收，最后将会填满可用的磁盘空间，取决于暂停的持续时间、WAL 的产生率和可用的磁盘空间。
26.5. 快照同步函数
PostgreSQL允许数据库会话同步它们的快照。一个快照决定对于正在使用该快照的事务哪些数据是可见的。当两个或者更多个会话需要看到数据库中的相同内容时，就需要同步快照。如果两个会话独立开始其事务，就总是有可能有某个第三事务在两个START TRANSACTION命令的执行之间提交，这样其中一个会话就可以看到该事务的效果而另一个则看不到。
为了解决这个问题，PostgreSQL允许一个事务导出它正在使用的快照。只要导出的事务仍然保持打开，其他事务可以导入它的快照，并且因此可以保证它们可以看到和第一个事务看到的完全一样的数据库视图。但是注意这些事务中的任何一个对数据库所作的更改对其他事务仍然保持不可见，和未提交事务所作的修改一样。因此这些事务是针对以前存在的数据同步，而对由它们自己所作的更改则采取正常的动作。
如Table 9-81中所示，快照通过pg_export_snapshot函数导出，并且通过SET TRANSACTION命令导入。
Table 9-81. 快照同步函数
函数pg_export_snapshot保存当前的快照并且返回一个text串标识该快照。该字符串必须被传递（到数据库外）给希望导入快照的客户端。直到导出快照的事务的末尾，快照都可以被导入。如果需要，一个事务可以导出多于一个快照。注意这样做只在 READ COMMITTED事务中有用，因为在REPEATABLE READ和更高隔离级别中，事务在它们的生命期中都使用同一个快照。一旦一个事务已经导出了任何快照，它不能使用PREPARE TRANSACTION。
关于如何使用一个已导出快照的细节请见SET TRANSACTION.
26.6. 复制函数
Table 9-82中展示的函数 用于控制以及与复制特性交互。这些函数只限于超级 用户使用。
很多这些函数在复制协议中都有等价的命令。
Table 9-82. 复制 SQL 函数
26.7. 数据库对象管理函数
Table 9-83中展示的函数计算数据库对象使用的磁盘空间。
Table 9-83. 数据库对象尺寸函数
pg_column_size显示用于存储任意独立数据值的空间。
pg_total_relation_size接受一个表或 TOAST 表的 OID 或名称，并返回该表所使用的总磁盘空间，包括所有相关的索引。这个函数等价于pg_table_size + pg_indexes_size。
pg_table_size接受一个表的 OID 或名称，并返回该表所需的磁盘空间，但是排除索引（TOAST 空间、空闲空间映射和可见性映射包含在内）
pg_indexes_size接受一个表的 OID 或名称，并返回附加到该表的所有索引所使用的全部磁盘空间。
pg_database_size和 pg_tablespace_size接受一个数据库或表空间的 OID 或 名称， 并且返回它们所使用的全部磁盘空间。要使用 pg_database_size，你必须具有在指定数据库上的 CONNECT权限（默认会被授予）。要使用 pg_tablespace_size，你必须具有指定表空间上的 CREATE权限，除非它是当前数据库的默认表空间。
pg_relation_size接受一个表、索引或 TOAST 表的 OID 或者名称，并且返回那个关系的一个分叉所占的磁盘空间的字节尺寸（注意 对于大部分目的，使用更高层的函数pg_total_relation_size 或者pg_table_size会更方便，它们会合计所有分叉的尺寸）。 如果只得到一个参数，它会返回该关系的主数据分叉的尺寸。提供第二个参数 可以指定要检查哪个分叉：
- 'main'返回该关系主数据分叉的尺寸。
- 'fsm'返回与该关系相关的空闲空间映射（见 Section 65.3）的尺寸。
- 'vm'返回与该关系相关的可见性映射（见 Section 65.4）的尺寸。
- 'init'返回与该关系相关的初始化分叉（如 果有）的尺寸。
pg_size_pretty可以用于把其它函数之一的结果格式化成一种人类易读的格式，可以根据情况使用字节、kB、MB、GB 或者 TB。
pg_size_bytes可以被用来从人类可读格式的字符串得到其中所表示的字节数。其输入可能带有的单位包括字节、kB、MB、GB 或者 TB，并且对输入进行解析时是区分大小写的。如果没有指定单位，会假定单位为字节。
Note:
函数pg_size_pretty和pg_size_bytes所使用的单位 kB、MB、GB 和 TB 是用 2 的幂而不是 10 的幂来定义，因此 1kB 是 1024 字节，1MB 是 10242 = 1048576 字节，以此类推。
上述操作表和索引的函数接受一个regclass参数，它是该表或索引在pg_class系统目录中的 OID。你不必手工去查找该 OID，因为regclass数据类型的输入转换器会为你代劳。只写包围在单引号内的表名，这样它看起来像一个文字常量。为了与普通SQL名称的处理相兼容，该字符串将被转换为小写形式，除非其中在表名周围包含双引号。
如果一个 OID 不表示一个已有的对象并且被作为参数传递给了上述函数，将会返回 NULL。
Table 9-84中展示的函数帮助标识数据库对象相关的磁盘文件。
Table 9-84. 数据库对象定位函数
pg_relation_filenode接受一个表、索引、序列或 TOAST 表的 OID 或名称，返回当前分配给它的"filenode"号。文件结点是关系的文件名的基本组件。对于大多数表结果和pg_class.relfilenode相同，但是对于某些系统目录relfilenode为零，并且必须使用此函数获取正确的值。 如果传递一个没有存储的关系（如视图），此函数将返回 NULL。
pg_relation_filepath与pg_relation_filenode类似，但是它返回关系的整个文件路径名（相对于数据库集簇的数据目录PGDATA）。
pg_filenode_relation是pg_relation_filenode的反向函数。给定一个"tablespace" OID 以及一个 "filenode"，它会返回相关关系的 OID。对于一个在数据库的默认表空间中的表，该表空间可以指定为 0。
26.8. 索引维护函数
Table 9-85展示了可用于 索引维护任务的函数。这些函数不能在恢复期间执行。只有超级用户以及给定索引的拥有者才能是用这些函数。
Table 9-85. 索引维护函数
brin_summarize_new_values接收一个 BRIN 索引的 OID 或者名称作为参数并且检查该索引以找到基表中当前还没有被该索引汇总的页面范围。对任意一个这样的范围，它将通过扫描那些表页面创建一个新的摘要索引元组。它会返回被插入到该索引的新页面范围摘要的数量。
gin_clean_pending_list接受一个 GIN 索引的 OID 或者名字，并且通过把指定索引的待处理列表中的项批量移动到主 GIN 数据结构来清理该索引的待处理列表。它会返回从待处理列表中移除的页数。注意如果其参数是一个禁用fastupdate选项构建的 GIN 索引，那么不会做清理并且返回值为 0，因为该索引根本没有待处理列表。
26.9. 通用文件访问函数
Table 9-86中展示的函数提供了对数据库服务器所在机器上的文件的本地访问。 只有那些在数据库集簇目录和log_directory目录中的文件可以访问。使用相对路径访问集簇目录里面的文件，以及匹配 log_directory配置设置的路径访问日志文件。只有超级用户才能使用这些函数。
Table 9-86. 通用文件访问函数
所有这些函数都有一个可选的missing_ok参数， 它指定文件或者目录不存在时的行为。如果为true， 函数会返回 NULL （pg_ls_dir除外，它返回一个空 结果集）。如果为false，则发生一个错误。默认是 false。
pg_ls_dir返回指定目录中所有文件（以及目录和其他特殊文件） 的名称。include_dot_dirs指示结果集中是否包括 "."和".."。默认是排除它们（false），但是 当missing_ok为true时把它们包括在内是 有用的，因为可以把一个空目录与一个不存在的目录区分开。
pg_read_file返回一个文本文件的一部分，从给定的offset开始，返回最多length字节（如果先到达文件末尾则会稍短）。如果offset为负，它相对于文件的末尾。如果offset和length被忽略，整个文件都被返回。从文件中读的字节被使用服务器编码解释成一个字符串；如果它们在编码中不合法则抛出一个错误。
pg_read_binary_file与pg_read_file相似，除了前者的结果是一个bytea值；相应地，不会执行编码检查。通过与convert_from函数结合，这个函数可以用来读取一个指定编码的文件：
SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');pg_stat_file返回一个记录，其中包含文件尺寸、最后访问时间戳、最后修改时间戳、最后文件状态改变时间戳（只支持 Unix 平台）、文件创建时间戳（只支持 Windows）和一个boolean指示它是否为目录。通常的用法包括：
SELECT * FROM pg_stat_file('filename');
SELECT (pg_stat_file('filename')).modification;26.10. 咨询锁函数
Table 9-87中展示的函数管理咨询锁。
Table 9-87. 咨询锁函数
pg_advisory_lock锁住一个应用定义的资源，可以使用一个单一64位键值或两个32位键值标识（注意这些两个键空间不重叠）。如果另一个会话已经在同一个资源标识符上持有了一个锁，这个函数将等待直到该资源变成可用。该锁是排他的。多个锁请求会入栈，因此如果同一个资源被锁住三次，则它必须被解锁三次来被释放给其他会话使用。
pg_advisory_lock_shared的工作和pg_advisory_lock相同，不过该锁可以与其他请求共享锁的会话共享。只有想要排他的锁请求会被排除。
pg_try_advisory_lock与pg_advisory_lock相似，不过该函数将不会等待锁变为可用。它要么立刻获得锁并返回true，要么不能立即获得锁并返回false。
pg_try_advisory_lock_shared的工作和pg_try_advisory_lock相同，不过它尝试获得一个共享锁而不是一个排他锁。
pg_advisory_unlock将会释放之前获得的排他会话级别咨询锁。如果锁被成功释放，它返回true。如果锁没有被持有，它将返回false并且额外由服务器报告一个 SQL 警告。
pg_advisory_unlock_shared的工作和pg_advisory_unlock相同，除了它释放一个共享的会话级别咨询锁。
pg_advisory_unlock_all将释放当前会话所持有的所有会话级别咨询锁（这个函数隐式地在会话末尾被调用，即使客户端已经不雅地断开）。
pg_advisory_xact_lock的工作和pg_advisory_lock相同，不过锁是在当前事务的末尾被自动释放的并且不能被显式释放。
pg_advisory_xact_lock_shared的工作和pg_advisory_lock_shared相同，除了锁是在当前事务的末尾自动被释放的并且不能被显式释放。
pg_try_advisory_xact_lock的工作和pg_try_advisory_lock相同，不过锁（若果获得）是在当前事务的末尾被自动释放的并且不能被显式释放。
pg_try_advisory_xact_lock_shared的工作和pg_try_advisory_lock_shared相同，不过锁（若果获得）是在当前事务的末尾被自动释放的并且不能被显式释放。
27. 触发器函数
当前PostgreSQL提供一个内建的触发器函数suppress_redundant_updates_trigger， 它将阻止任何不会实际更改行中数据的更新发生，这与正常的行为不管数据是否改变始终执行更新相反（这是正常的行为，使得更新运行速度更快，因为不需要检查，并在某些情况下也是有用的）。
理想的情况下，你通常应该避免运行实际上并没有改变记录中数据的更新。 冗余更新会花费大量不必要的时间，尤其是如果有大量索引要改变， 并将最终不得不清理被死亡行占用的空间。但是，在客户端代码中检测这种情况并不总是容易的，甚至不可能做到。 而写表达式来检测它们容易产生错误。作为替代，使用suppress_redundant_updates_trigger可以跳过不改变数据的更新。 但是，你需要小心使用它。触发器需要很短但不能忽略的时间来处理每条记录，所以如果大多数被一个更新影响的记录确实被更改，此触发器的使用将实际上使更新运行得更慢。
suppress_redundant_updates_trigger函数可以像这样被加到一个表：
CREATE TRIGGER z_min_update
BEFORE UPDATE ON tablename
FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();在大部分情况下，你可能希望在最后为每行触发这个触发器。考虑到触发器是按照名字顺序被触发，你需要选择一个位于该表所有其他触发器之后的触发器名字。
28. 事件触发器函数
PostgreSQL提供了这些助手函数来从 事件触发器检索信息。
更多有关事件触发器的信息请见Chapter 38。
28.1. 在命令结束处捕捉更改
当在一个ddl_command_end事件触发器的函数中调用时， pg_event_trigger_ddl_commands返回被每一个用户动作 执行的DDL命令的列表。如果在其他任何环境中 调用这个函数，会发生错误。 pg_event_trigger_ddl_commands为每一个被执行的基本 命令返回一行，某些只有一个单一 SQL 句子的命令可能会返回多于一行。 这个函数返回下面的列：
28.2. 处理被 DDL 命令删除的对象
pg_event_trigger_dropped_objects返回其sql_drop事件中命令所删除的所有对象的列表。如果在任何其他环境中被调用，pg_event_trigger_dropped_objects将抛出一个错误。pg_event_trigger_dropped_objects返回下列列：
pg_event_trigger_dropped_objects可以被这样用在一个事件触发器中：
CREATE FUNCTION test_event_trigger_for_drops()
RETURNS event_trigger LANGUAGE plpgsql AS
$$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
RAISE NOTICE '% dropped object: % %.% %',
tg_tag,
obj.object_type,
obj.schema_name,
obj.object_name,
obj.object_identity;
END LOOP;
END
$$
;
CREATE EVENT TRIGGER test_event_trigger_for_drops
ON sql_drop
EXECUTE PROCEDURE test_event_trigger_for_drops();28.3. 处理表重写事件
The functions shown in Table 9-88 中所示的函数提供刚刚被调用过table_rewrite 事件的表的信息。如果在任何其他环境中调用，会发生错误。
Table 9-88. 表重写信息
可以在一个这样的事件触发器中使用 pg_event_trigger_table_rewrite_oid函数：
CREATE FUNCTION test_event_trigger_table_rewrite_oid()
RETURNS event_trigger
LANGUAGE plpgsql AS
$$
BEGIN
RAISE NOTICE 'rewriting table % for reason %',
pg_event_trigger_table_rewrite_oid()::regclass,
pg_event_trigger_table_rewrite_reason();
END;
$$
;
CREATE EVENT TRIGGER test_table_rewrite_oid
ON table_rewrite
EXECUTE PROCEDURE test_event_trigger_table_rewrite_oid();
