This section describes functions and operators for examining
and manipulating values of type bytea.

SQL defines some string
functions with a special syntax where certain key words rather
than commas are used to separate the arguments. Details are in
Table
9-9. Some functions are also implemented using the regular
syntax for function invocation. (See Table
9-10.)

Table 9-9. 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)

integer

Number of bytes in binary string

octet_length(
'jo\\000se'::bytea)

5

position(substring in string)

integer

Location of specified substring

position('\\000om'::bytea in
'Th\\000omas'::bytea)

3

substring(string [from
integer] [for integer])

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 the bytes
in bytes from the start and
end of string

trim('\\000'::bytea from
'\\000Tom\\000'::bytea)

Tom

get_byte(string, offset)

integer

Extract byte from string.

get_byte('Th\\000omas'::bytea,
4)

109

set_byte(string, offset, newvalue)

bytea

Set byte in string.

set_byte('Th\\000omas'::bytea, 4,
64)

Th\000o@as

get_bit(string, offset)

integer

Extract bit from string.

get_bit('Th\\000omas'::bytea,
45)

1

set_bit(string, offset, newvalue)

bytea

Set bit in string.

set_bit('Th\\000omas'::bytea, 45,
0)

Th\000omAs

Additional binary string manipulation functions are available
and are listed in Table
9-10. Some of them are used internally to implement the
SQL-standard string functions
listed in Table
9-9.

Table 9-10. Other Binary String Functions

Function

Return Type

Description

Example

Result

btrim(stringbyteabytesbytea)

bytea

Remove the longest string consisting only of bytes in
bytes from the start and end
of string.

btrim('\\000trim\\000'::bytea,
'\\000'::bytea)

trim

length(string)

integer

Length of binary string

length('jo\\000se'::bytea)

5

decode(stringtext,
typetext)

bytea

Decode binary string from string previously encoded with
encode. Parameter type is same
as in encode.