> For the complete documentation index, see [llms.txt](https://docs.postgresql.tw/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.postgresql.tw/the-sql-language/functions/functions-binarystring.md). # 9.5. Binary String Functions and Operators \# This section describes functions and operators for examining and manipulating binary strings, that is values of type `bytea`. Many of these are equivalent, in purpose and syntax, to the text-string functions described in the previous section. SQL defines some string functions that use key words, rather than commas, to separate arguments. Details are in [Table 9.11](#FUNCTIONS-BINARYSTRING-SQL). PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see [Table 9.12](#FUNCTIONS-BINARYSTRING-OTHER)). **Table 9.11. SQL Binary String Functions and Operators** |

Function/Operator

Description

Example(s)

| | | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | - | ------------------------------------------------------------------ | |

bytea | | bytea → bytea

Concatenates the two binary strings.

'\x123456'::bytea | | '\x789a00bcde'::bytea → \x123456789a00bcde

| |

bit\_length ( bytea ) → integer

Returns number of bits in the binary string (8 times the octet\_length).

bit\_length('\x123456'::bytea) → 24

| | | | | |

btrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start and end of bytes.

btrim('\x1234567890'::bytea, '\x9012'::bytea) → \x345678

| | | | | |

ltrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start of bytes.

ltrim('\x1234567890'::bytea, '\x9012'::bytea) → \x34567890

| | | | | |

octet\_length ( bytea ) → integer

Returns number of bytes in the binary string.

octet\_length('\x123456'::bytea) → 3

| | | | | |

overlay ( bytes bytea PLACING newsubstring bytea FROM start integer \[ FOR count integer ] ) → bytea

Replaces the substring of bytes that starts at the start'th byte and extends for count bytes with newsubstring. If count is omitted, it defaults to the length of newsubstring.

overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3) → \x12020390

| | | | | |

position ( substring bytea IN bytes bytea ) → integer

Returns first starting index of the specified substring within bytes, or zero if it's not present.

position('\x5678'::bytea in '\x1234567890'::bytea) → 3

| | | | | |

rtrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the end of bytes.

rtrim('\x1234567890'::bytea, '\x9012'::bytea) → \x12345678

| | | | | |

substring ( bytes bytea \[ FROM start integer ] \[ FOR count integer ] ) → bytea

Extracts the substring of bytes starting at the start'th byte if that is specified, and stopping after count bytes if that is specified. Provide at least one of start and count.

substring('\x1234567890'::bytea from 3 for 2) → \x5678

| | | | | |

trim ( \[ LEADING | TRAILING | BOTH ] bytesremoved bytea FROM bytes bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start, end, or both ends (BOTH is the default) of bytes.

trim('\x9012'::bytea from '\x1234567890'::bytea) → \x345678

| | | |

trim ( \[ LEADING | TRAILING | BOTH ] \[ FROM ] bytes bytea, bytesremoved bytea ) → bytea

This is a non-standard syntax for trim().

trim(both from '\x1234567890'::bytea, '\x9012'::bytea) → \x345678

| | |
Additional binary string manipulation functions are available and are listed in [Table 9.12](#FUNCTIONS-BINARYSTRING-OTHER). Some of them are used internally to implement the SQL-standard string functions listed in [Table 9.11](#FUNCTIONS-BINARYSTRING-SQL). **Table 9.12. Other Binary String Functions** |

Function

Description

Example(s)

| | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |

bit\_count ( bytes bytea ) → bigint

Returns the number of bits set in the binary string (also known as “popcount”).

bit\_count('\x1234567890'::bytea) → 15

| |

crc32 ( bytea ) → bigint

Computes the CRC-32 value of the binary string.

crc32('abc'::bytea) → 891568578

| |

crc32c ( bytea ) → bigint

Computes the CRC-32C value of the binary string.

crc32c('abc'::bytea) → 910901175

| |

get\_bit ( bytes bytea, n bigint ) → integer

Extracts n'th bit from binary string.

get\_bit('\x1234567890'::bytea, 30) → 1

| |

get\_byte ( bytes bytea, n integer ) → integer

Extracts n'th byte from binary string.

get\_byte('\x1234567890'::bytea, 4) → 144

| |

length ( bytea ) → integer

Returns the number of bytes in the binary string.

length('\x1234567890'::bytea) → 5

| |

length ( bytes bytea, encoding name ) → integer

Returns the number of characters in the binary string, assuming that it is text in the given encoding.

length('jose'::bytea, 'UTF8') → 4

| |

md5 ( bytea ) → text

Computes the MD5 hash of the binary string, with the result written in hexadecimal.

md5('Th\000omas'::bytea) → 8ab2d3c9689aaf18b4958c334c82d8b1

| |

reverse ( bytea ) → bytea

Reverses the order of the bytes in the binary string.

reverse('\xabcd'::bytea) → \xcdab

| |

set\_bit ( bytes bytea, n bigint, newvalue integer ) → bytea

Sets n'th bit in binary string to newvalue.

set\_bit('\x1234567890'::bytea, 30, 0) → \x1234563890

| |

set\_byte ( bytes bytea, n integer, newvalue integer ) → bytea

Sets n'th byte in binary string to newvalue.

set\_byte('\x1234567890'::bytea, 4, 64) → \x1234567840

| |

sha224 ( bytea ) → bytea

Computes the SHA-224 hash of the binary string.

sha224('abc'::bytea) → \x23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7

| |

sha256 ( bytea ) → bytea

Computes the SHA-256 hash of the binary string.

sha256('abc'::bytea) → \xba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad

| |

sha384 ( bytea ) → bytea

Computes the SHA-384 hash of the binary string.

sha384('abc'::bytea) → \xcb00753f45a35e8bb5a03d699ac65007272c32ab0eded1631a8b605a43ff5bed8086072ba1e7cc2358baeca134c825a7

| |

sha512 ( bytea ) → bytea

Computes the SHA-512 hash of the binary string.

sha512('abc'::bytea) → \xddaf35a193617abacc417349ae20413112e6fa4e89a97ea20a9eeee64b55d39a2192992a274fc1a836ba3c23a3feebbd454d4423643ce80e2a9ac94fa54ca49f

| |

substr ( bytes bytea, start integer \[, count integer ] ) → bytea

Extracts the substring of bytes starting at the start'th byte, and extending for count bytes if that is specified. (Same as substring(bytes from start for count).)

substr('\x1234567890'::bytea, 3, 2) → \x5678

|
Functions `get_byte` and `set_byte` number the first byte of a binary string as byte 0. Functions `get_bit` and `set_bit` number bits from the right within each byte; for example bit 0 is the least significant bit of the first byte, and bit 15 is the most significant bit of the second byte. For historical reasons, the function `md5` returns a hex-encoded value of type `text` whereas the SHA-2 functions return type `bytea`. Use the functions [`encode`](#FUNCTION-ENCODE) and [`decode`](#FUNCTION-DECODE) to convert between the two. For example write `encode(sha256('abc'), 'hex')` to get a hex-encoded text representation, or `decode(md5('abc'), 'hex')` to get a `bytea` value. Functions for converting strings between different character sets (encodings), and for representing arbitrary binary data in textual form, are shown in [Table 9.13](#FUNCTIONS-BINARYSTRING-CONVERSIONS). For these functions, an argument or result of type `text` is expressed in the database's default encoding, while arguments or results of type `bytea` are in an encoding named by another argument. **Table 9.13. Text/Binary String Conversion Functions** |

Function

Description

Example(s)

convert ( bytes bytea, src\_encoding name, dest\_encoding name ) → bytea

Converts a binary string representing text in encoding src\_encoding to a binary string in encoding dest\_encoding (see Section 23.3.4 for available conversions).

convert('text\_in\_utf8', 'UTF8', 'LATIN1') → \x746578745f696e5f75746638

| |

convert\_from ( bytes bytea, src\_encoding name ) → text

Converts a binary string representing text in encoding src\_encoding to text in the database encoding (see Section 23.3.4 for available conversions).

convert\_from('text\_in\_utf8', 'UTF8') → text\_in\_utf8

| |

convert\_to ( string text, dest\_encoding name ) → bytea

Converts a text string (in the database encoding) to a binary string encoded in encoding dest\_encoding (see Section 23.3.4 for available conversions).

convert\_to('some\_text', 'UTF8') → \x736f6d655f74657874

| |

encode ( bytes bytea, format text ) → text

Encodes binary data into a textual representation; supported format values are: base64, escape, hex.

encode('123\000\001', 'base64') → MTIzAAE=

| |

decode ( string text, format text ) → bytea

Decodes binary data from a textual representation; supported format values are the same as for encode.

decode('MTIzAAE=', 'base64') → \x3132330001

|
The `encode` and `decode` functions support the following textual formats: base64[#](#ENCODE-FORMAT-BASE64) : The `base64` format is that of [RFC 2045 Section 6.8](https://datatracker.ietf.org/doc/html/rfc2045#section-6.8). As per the RFC, encoded lines are broken at 76 characters. However instead of the MIME CRLF end-of-line marker, only a newline is used for end-of-line. The `decode` function ignores carriage-return, newline, space, and tab characters. Otherwise, an error is raised when `decode` is supplied invalid base64 data — including when trailing padding is incorrect. escape[#](#ENCODE-FORMAT-ESCAPE) : The `escape` format converts zero bytes and bytes with the high bit set into octal escape sequences (`\`*`nnn`*), and it doubles backslashes. Other byte values are represented literally. The `decode` function will raise an error if a backslash is not followed by either a second backslash or three octal digits; it accepts other byte values unchanged. hex[#](#ENCODE-FORMAT-HEX) : The `hex` format represents each 4 bits of data as one hexadecimal digit, `0` through `f`, writing the higher-order digit of each byte first. The `encode` function outputs the `a`-`f` hex digits in lower case. Because the smallest unit of data is 8 bits, there are always an even number of characters returned by `encode`. The `decode` function accepts the `a`-`f` characters in either upper or lower case. An error is raised when `decode` is given invalid hex data — including when given an odd number of characters. In addition, it is possible to cast integral values to and from type `bytea`. Casting an integer to `bytea` produces 2, 4, or 8 bytes, depending on the width of the integer type. The result is the two's complement representation of the integer, with the most significant byte first. Some examples: ``` 1234::smallint::bytea \x04d2 cast(1234 as bytea) \x000004d2 cast(-1234 as bytea) \xfffffb2e '\x8000'::bytea::smallint -32768 '\x8000'::bytea::integer 32768 ``` Casting a `bytea` to an integer will raise an error if the length of the `bytea` exceeds the width of the integer type. See also the aggregate function `string_agg` in [Section 9.21](/the-sql-language/functions/functions-aggregate.md) and the large object functions in [Section 33.4](/client-interfaces/largeobjects/lo-funcs.md). *** 原文：[PostgreSQL 18.6 Documentation](https://www.postgresql.org/docs/18/functions-binarystring.html)（英文原文，待翻譯） --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.postgresql.tw/the-sql-language/functions/functions-binarystring.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.