Postgresql 中文操作指南

9.5. Binary String Functions and Operators #

此部分描述了用于检查和操作二进制字符串（即 bytea 类型的值）的函数和运算符。其中的许多函数在目的和语法上等同于上一部分中描述的文本字符串函数。

SQL 定义了一些字符串函数，该函数使用关键字而不是逗号来分隔参数。详细信息在 Table 9.11 中。PostgreSQL 还提供了使用常规函数调用语法版本的这些函数（请参见 Table 9.12）。

Table 9.11. SQL Binary String Functions and Operators

Function/Operator

Description

Example(s)

bytea _

_ bytea → bytea 连接两个二进制字符串。_'\x123456'::bytea

'\x789a00bcde'::bytea_ → \x123456789a00bcde

bit_length ( bytea ) → integer 返回二进制字符串中位数（8 倍 octet_length ）。 bit_length('\x123456'::bytea) → 24

btrim ( bytes bytea , bytesremoved bytea ) → bytea 从 bytes 的开头和结尾处删除由 bytesremoved 中出现的字节组成的最长字符串。 btrim('\x1234567890'::bytea, '\x9012'::bytea) → \x345678

ltrim ( bytes bytea , bytesremoved bytea ) → bytea 从 bytes 的开头删除由 bytesremoved 中出现的字节组成的最长字符串。 ltrim('\x1234567890'::bytea, '\x9012'::bytea) → \x34567890

octet_length ( bytea ) → integer 返回二进制字符串中的字节数。 octet_length('\x123456'::bytea) → 3

overlay ( bytes bytea PLACING newsubstring bytea FROM start integer [ FOR count integer ] ) → bytea 用 newsubstring 替换 bytes 从第 start ' 个字节开始并延伸 count 个字节的子字符串。如果省略 count ，则默认为 newsubstring 的长度。 overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3) → \x12020390

position ( substring bytea IN bytes bytea ) → integer 返回 bytes 内指定 substring 的第一个开始索引，如果不存在则返回零。 position('\x5678'::bytea in '\x1234567890'::bytea) → 3

rtrim ( bytes bytea , bytesremoved bytea ) → bytea 从 bytes 的结尾处删除由 bytesremoved 中出现的字节组成的最长字符串。 rtrim('\x1234567890'::bytea, '\x9012'::bytea) → \x12345678

substring ( bytes bytea [ FROM start integer ] [ FOR count integer ] ) → bytea 提取从 start ' 个字节开始（如果指定）、并在 count 个字节后停止（如果指定）的 bytes 的子字符串。提供 start 和 count 中至少一个。 substring('\x1234567890'::bytea from 3 for 2) → \x5678

trim ( [ LEADING

TRAILING

BOTH ] bytesremoved bytea FROM bytes bytea ) → bytea 从 bytes 的开头、结尾或两个结尾（ BOTH 为默认）中删除由 bytesremoved 中出现的字节组成的最长字符串。 trim('\x9012'::bytea from '\x1234567890'::bytea) → \x345678

trim ( [ LEADING

TRAILING

BOTH ] [ FROM ] bytes bytea , bytesremoved bytea ) → bytea 这是 trim() 的非标准语法。 trim(both from '\x1234567890'::bytea, '\x9012'::bytea) → \x345678

其他二进制字符串操作函数可用，并列在 Table 9.12 中。其中一些内部用于实现 Table 9.11 中列出的 SQL 标准字符串函数。

Table 9.12. Other Binary String Functions

Function

Description

Example(s)

bit_count ( bytes bytea ) → bigint 返回二进制字符串中设置的位数（也称为“popcount”）。 bit_count('\x1234567890'::bytea) → 15

get_bit ( bytes bytea , n bigint ) → integer 从二进制字符串中提取 n’th 位。 get_bit('\x1234567890'::bytea, 30) → 1

get_byte ( bytes bytea , n integer ) → integer 从二进制字符串中提取 n’th 字节。 get_byte('\x1234567890'::bytea, 4) → 144

length ( bytea ) → integer 返回二进制字符串中的字节数。 length('\x1234567890'::bytea) → 5

length ( bytes bytea , encoding name ) → integer 返回二进制字符串中的字符数，假设它是给定 encoding 中的文本。 length('jose'::bytea, 'UTF8') → 4

md5 ( bytea ) → text 计算二进制字符串的MD5 hash ，结果以十六进制格式写出。 md5('Th\000omas'::bytea) → 8ab2d3c9689aaf18​b4958c334c82d8b1

set_bit ( bytes bytea , n bigint , newvalue integer ) → bytea 将二进制字符串中的 n’th 位设置为 newvalue 。 set_bit('\x1234567890'::bytea, 30, 0) → \x1234563890

set_byte ( bytes bytea , n integer , newvalue integer ) → bytea 将二进制字符串中的 n’th 字节设置为 newvalue 。 set_byte('\x1234567890'::bytea, 4, 64) → \x1234567840

sha224 ( bytea ) → bytea 计算二进制字符串的SHA-224 hash 。 sha224('abc'::bytea) → \x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7

sha256 ( bytea ) → bytea 计算二进制字符串的SHA-256 hash 。 sha256('abc'::bytea) → \xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad

sha384 ( bytea ) → bytea 计算二进制字符串的SHA-384 hash 。 sha384('abc'::bytea) → \xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7

sha512 ( bytea ) → bytea 计算二进制字符串的SHA-512 hash 。 sha512('abc'::bytea) → \xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f

substr ( bytes bytea , start integer [, count integer ] ) → bytea 从 start '第字节开始并向后扩展 count 个字节（如果指定的话）来提取 bytes 的子字符串。（对于 count 来说与 start 中的 substring(_bytes 相同）。 substr('\x1234567890'::bytea, 3, 2) → \x5678

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

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

在 Table 9.13 中显示了用于在不同字符集（编码）之间转换字符串以及以文本形式表示任意二进制数据的函数。对于这些函数，类型 text 的参数或结果表示为数据库的默认编码，而类型 bytea 的参数或结果则表示为由另一个参数命名的编码。

Table 9.13. Text/Binary String Conversion Functions

Function

Description

Example(s)

convert ( bytes bytea , src_encoding name , dest_encoding name ) → bytea 将表示编码为 src_encoding 的文本的二进制字符串转换为编码为 dest_encoding 的二进制字符串（有关可用的转换，请参见 Section 24.3.4 ）。 convert('text_in_utf8', 'UTF8', 'LATIN1') → \x746578745f696e5f75746638

convert_from ( bytes bytea , src_encoding name ) → text 将表示编码为 src_encoding 的文本的二进制字符串转换为数据库编码中的 text （有关可用的转换，请参见 Section 24.3.4 ）。 convert_from('text_in_utf8', 'UTF8') → text_in_utf8

convert_to ( string text , dest_encoding name ) → bytea 将 text 字符串（数据库编码中）转换为编码为 dest_encoding 的二进制字符串（有关可用的转换，请参见 Section 24.3.4 ）。 convert_to('some_text', 'UTF8') → \x736f6d655f74657874

encode ( bytes bytea , format text ) → text 将二进制数据编码为文本表示；支持的 format 值为： base64 、 escape 、 hex 。 encode('123\000\001', 'base64') → MTIzAAE=

decode ( string text , format text ) → bytea 从文本表示中解码二进制数据；支持的 format 值与 encode 相同。 decode('MTIzAAE=', 'base64') → \x3132330001

encode 和 decode 函数支持以下文本格式：

  • base64 #

    • _base64_格式为 RFC 2045 Section 6.8的格式。按照 RFC 协议，编码行以 76 个字符换行。但是，只使用一个换行来换行，而没有 MIME CRLF 行结束标记。_decode_函数忽略回车、换行、空格和制表符。除此之外，如果 _decode_提供的 base64 无效（包括尾随填充不正确），则会引发一个错误。

  • escape #

    • escape 格式转换零字节和带有一个高位设置的字节成八进制转义序列 ( __nnn )，并且对其反斜杠进行加倍。其它字节值被精确表示。如果反斜杠后面不是第二个反斜杠或是三个八进制数字， decode 函数将会引发一个错误；它会接受其它不变的字节值。

  • hex #

    • hex 格式将每 4 位数据表示为一个十六进制数字，0 到 f，写入每个字节的高阶数字。encode 函数以小写输出 a-f 十六进制数字。由于最小的数据单位是 8 位，因此 encode 始终返回偶数个字符。decode 函数接受大写或小写的 a-f 字符。当_decode_ 给出无效十六进制数据（包括给出的字符数为奇数时），将引发错误。

另请参见 Section 9.21中的聚合函数 _string_agg_和 Section 35.4中的大对象函数。