This section describes functions and operators for examining and
manipulating values of type
bytea
.
SQL
defines some string functions that use
key words, rather than commas, to separate
arguments. Details are in
Table 9.12
.
PostgreSQL
also provides versions of these functions
that use the regular function invocation syntax
(see
Table 9.13
).
Note
The sample results shown on this page assume that the server parameter
bytea_output
is set
to
escape
(the traditional PostgreSQL format).
Table 9.12.
SQL
Binary String Functions and Operators
Function
Return Type
Description
Example
Result
string
||
string
bytea
String concatenation
'\\Post'::bytea || '\047gres\000'::bytea
\\Post'gres\000
octet_length(
string
)
int
Number of bytes in binary string
octet_length('jo\000se'::bytea)
5
overlay(
string
placing
string
from
int
[
for
int
])
bytea
Replace substring
overlay('Th\000omas'::bytea placing '\002\003'::bytea from 2 for 3)
T\\002\\003mas
position(
substring
in
string
)
int
Location of specified substring
position('\000om'::bytea in 'Th\000omas'::bytea)
3
substring(
string
[
from
int
] [
for
int
])
bytea
Extract substring
substring('Th\000omas'::bytea from 2 for 3)
h\000o
trim([
both
]
bytes
from
string
)
bytea
Remove the longest string containing only bytes appearing in
bytes
from the start
and end of
string
trim('\000\001'::bytea from '\000Tom\001'::bytea)
Tom
Additional binary string manipulation functions are available and
are listed in
Table 9.13
. Some
of them are used internally to implement the
SQL
-standard string functions listed in
Table 9.12
.
Table 9.13. Other Binary String Functions
Function
Return Type
Description
Example
Result
btrim(
string
bytea
,
bytes
bytea
)
bytea
Remove the longest string containing only bytes appearing in
bytes
from the start and end of
string
btrim('\000trim\001'::bytea, '\000\001'::bytea)
trim
decode(
string
text
,
format
text
)
bytea
Decode binary data from textual representation in
string
.
Options for
format
are same as in
encode
.
decode('123\000456', 'escape')
123\000456
encode(
data
bytea
,
format
text
)
text
Encode binary data into a textual representation. Supported
formats are:
base64
,
hex
,
escape
.
escape
converts zero bytes and high-bit-set bytes to
octal sequences (
\
nnn
) and
doubles backslashes.
encode('123\000456'::bytea, 'escape')
123\000456
get_bit(
string
,
offset
)
int
Extract bit from string
get_bit('Th\000omas'::bytea, 45)
1
get_byte(
string
,
offset
)
int
Extract byte from string
get_byte('Th\000omas'::bytea, 4)
109
length(
string
)
int
Length of binary string
length('jo\000se'::bytea)
5
md5(
string
)
text
Calculates the MD5 hash of
string
,
returning the result in hexadecimal
get_byte
and
set_byte
number the first byte
of a binary string as byte 0.
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.
Note that for historic 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
encode(sha256('abc'),
'hex')
to get a hex-encoded text representation.