PostgreSQL-运算符和函数1

一、逻辑运算符

常用的逻辑运算符可用：

boolean AND boolean → boolean
boolean OR boolean → boolean
NOT boolean → boolean

SQL 使用了真、假、空的三值逻辑系统，null代表“未知”。观察以下真值表：

运算符 AND 和 OR 是可交换的，即可以在不影响结果的情况下切换左右操作数。（但是，不能保证在右操作数之前计算左操作数。）

二、比较函数和运算符

这些比较运算符可用于具有自然排序的所有内置数据类型，包括数字、字符串和日期/时间类型。此外，如果数组、复合类型和范围的组件数据类型具有可比性，则它们可以进行比较。

通常也可以比较相关数据类型的值；例如 integer > bigint 将起作用。这种类型的某些情况是由“跨类型”比较运算符直接实现的，但如果没有这样的运算符可用，解析器会将不太通用的类型强制转换为更通用的类型并应用后者的比较运算符。

谓词：

请注意，BETWEEN 将端点值视为包含在范围内。BETWEEN SYMMETRIC 与 BETWEEN 类似，只是不要求 AND 左侧的参数小于或等于右侧的参数。如果不是，这两个参数会自动交换，因此总是隐含一个非空范围。

当任一输入为空时，普通比较运算符产生空值（表示“未知”），而不是真或假。例如，7 = NULL 产生 null，7 <> NULL 也是如此。当此行为不合适时，请使用 IS [ NOT ] DISTINCT FROM 谓词：

a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

对于非空输入，IS DISTINCT FROM 与 <> 运算符相同。但是，如果两个输入都为 null，则返回 false，如果只有一个输入为 null，则返回 true。同样，对于非空输入，IS NOT DISTINCT FROM 与 = 相同，但当两个输入都为空时返回 true，当只有一个输入为空时返回 false。因此，这些谓词实际上就像 null 是一个正常的数据值一样，而不是“未知”。

如果表达式是row值的，那么当行表达式本身为空或所有行的字段都为空时，IS NULL 为真，而当行表达式本身为非空且所有行的字段为空时，IS NOT NULL 为真非空。由于这种行为，IS NULL 和 IS NOT NULL 并不总是为行值表达式返回相反的结果。特别是，包含 null 和非 null 字段的行值表达式将为两个测试返回 false。在某些情况下，最好写入 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

这些将始终返回 true 或 false，永远不会返回 null 值，即使操作数为 null 也是如此。空输入被视为逻辑值“未知”。请注意，IS UNKNOWN 和 IS NOT UNKNOWN 实际上分别与 IS NULL 和 IS NOT NULL 相同，只是输入表达式必须是布尔类型。

例子：

select null is true;
select true is unknown;

num_nonnulls ：返回非空参数的数量。如num_nonnulls(1, NULL, 2) → 2
num_nulls：返回空参数的数量。如num_nulls(1, NULL, 2) → 1

 select num_nonnulls(2,null,5);
 select num_nulls(3,null,8);

三、数学函数和运算符
。除非另有说明，否则显示为接受 numeric_type 的运算符可用于所有类型 smallint、integer、bigint、numeric、real 和 double precision。显示为接受 integer_type 的运算符可用于 smallint、integer 和 bigint 类型。除非另有说明，每种形式的运算符都返回与其参数相同的数据类型。

random() 函数使用简单的线性同余算法。它速度快但不适合加密应用；请参阅 pgcrypto 模块以获得更安全的替代方案。如果调用了 setseed()，则可以通过重新发出具有相同参数的 setseed() 来重复当前会话中后续 random() 调用的一系列结果。在同一会话中没有任何先前的 setseed() 调用，第一个 random() 调用从依赖于平台的随机位源获取种子。

三、字符串函数
format
函数 format 生成根据格式字符串格式化的输出，其风格类似于 C 函数 sprintf。

format(formatstr text [, formatarg "any" [, ...] ])

formatstr 是一个格式字符串，它指定结果的格式。格式字符串中的文本直接复制到结果中，除非使用格式说明符。格式说明符充当字符串中的占位符，定义应如何格式化后续函数参数并将其插入结果中。每个 formatarg 参数根据其数据类型的通常输出规则转换为文本，然后根据格式说明符格式化并插入结果字符串。

格式说明符由 % 字符引入，形式为

%[position][flags][width]type

其中组件字段是：

position（可选）
n$ 形式的字符串，其中 n 是要打印的参数的索引。索引 1 表示 formatstr 之后的第一个参数。如果省略该位置，则默认为依次使用下一个参数。

flags （可选）
控制格式说明符的输出格式的附加选项。目前唯一支持的标志是减号 (-)，它将导致格式说明符的输出左对齐。除非还指定了宽度字段，否则这无效。

width （可选）
指定用于显示格式说明符输出的最小字符数。输出在左侧或右侧（取决于 - 标志）根据需要填充宽度以填充宽度。太小的宽度不会导致输出截断，而只是被忽略。可以使用以下任何一种来指定宽度：正整数；星号 (*) 使用下一个函数参数作为宽度；或 *n$ 形式的字符串以使用第 n 个函数参数作为宽度。

如果宽度来自函数参数，则该参数在用于格式说明符值的参数之前使用。如果宽度参数为负，则结果在长度为 abs(width) 的字段内左对齐（就像指定了 - 标志一样）。

type （必需）
用于生成格式说明符输出的格式转换类型。支持以下类型：
s 将参数值格式化为一个简单的字符串。空值被视为空字符串。
I 将参数值视为 SQL 标识符，必要时将其双引号。值为 null（相当于 quote_ident）是错误的。
L 将参数值引用为 SQL 文字。空值显示为字符串 NULL，不带引号（相当于 quote_nullable）。

除了上述格式说明符之外，特殊序列 %% 可用于输出文字 % 字符。

例子：

SELECT format('Hello %s', 'World');
SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');

SELECT format('|%10s|', 'foo');
SELECT format('|%-10s|', 'foo'); 
SELECT format('|%*s|', 10, 'foo');
SELECT format('|%*s|', -10, 'foo');
SELECT format('|%-*s|', 10, 'foo');
SELECT format('|%-*s|', -10, 'foo');

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
SELECT format('|%*2$s|', 'foo', 10, 'bar');
SELECT format('|%1$*2$s|', 'foo', 10, 'bar');

与标准 C 函数 sprintf 不同，PostgreSQL 的格式函数允许在同一格式字符串中混合带有和不带有位置字段的格式说明符。没有位置字段的格式说明符总是在使用的最后一个参数之后使用下一个参数。此外，格式函数不需要在格式字符串中使用所有函数参数。例如：

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');

四、二进制字符串函数

函数 get_byte 和 set_byte 将二进制字符串的第一个字节编号为字节 0。函数 get_bit 和 set_bit 在每个字节内从右开始编号；例如，第 0 位是第一个字节的最低有效位，第 15 位是第二个字节的最高有效位。

由于历史原因，函数 md5 返回文本类型的十六进制编码值，而 SHA-2 函数返回类型 bytea。使用函数 encode 和 decode 在两者之间进行转换。例如，编写 encode(sha256('abc'), 'hex') 来获取十六进制编码的文本表示，或 decode(md5('abc'), 'hex') 来获取 bytea 值。

文本/二进制字符串转换函数：

编码和解码函数支持以下文本格式：

escape
转义格式将零字节和设置了高位的字节转换为八进制转义序列 (\nnn)，并将反斜杠加倍。其他字节值按字面意思表示。如果反斜杠后面没有跟第二个反斜杠或三个八进制数字，则 decode 函数将引发错误；它接受其他字节值不变。

base64
base64 格式是 RFC 2045 第 6.8 节的格式。根据 RFC，编码的行在 76 个字符处中断。然而，代替 MIME CRLF 行尾标记，只有换行符用于行尾。decode 函数忽略回车、换行、空格和制表符。否则，当 decode 提供无效的 base64 数据时会引发错误——包括尾随填充不正确时。

hex
十六进制格式将每 4 位数据表示为一个十六进制数字，从 0 到 f，首先写入每个字节的高位数字。编码函数以小写形式输出 a-f 十六进制数字。因为数据的最小单位是 8 位，所以 encode 返回的字符数总是偶数。decode 函数接受大写或小写的 a-f 字符。当 decode 给出无效的十六进制数据时会引发错误——包括给定奇数个字符时。