Binary String Functions and Operators

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 SQL Binary String Functions and Operators. PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see Other Binary String Functions).

Table: SQL Binary String Functions and Operators

Additional binary string manipulation functions are available and are listed in Other Binary String Functions. Some of them are used internally to implement the SQL-standard string functions listed in SQL Binary String Functions and Operators.

Table: Other Binary String Functions

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 and 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 Text/Binary String Conversion Functions. 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: Text/Binary String Conversion Functions

The encode and decode functions support the following textual formats:

base64 : The base64 format is that of RFC 2045 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.

base64url : The base64url format is that of RFC 4648 Section 5, a base64 variant safe to use in filenames and URLs. The base64url alphabet uses '-' instead of '+' and '_' instead of '/' and also omits the '=' padding character.

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

See also the aggregate function string_agg in Aggregate Functions and the large object functions in Server-Side Functions.