In PostgreSQL, the octet_length() function returns the number of bytes in a given string. We pass the string as an argument and the function returns the number of bytes in that string.

Example

Here’s an example to demonstrate:

SELECT octet_length('a');

Result:

1

Here’s another example with various string lengths:

SELECT 
    octet_length('Cat') AS "Cat",
    octet_length('Cafe') AS "Cafe",
    octet_length('Café') AS "Café",
    octet_length('คาเฟ่') AS "คาเฟ่";

Result:

 Cat | Cafe | Café | คาเฟ่ 
-----+------+------+------
   3 |    4 |    5 |   15

Character Data

Passing a string of type character can result in a larger value being returned than we might have predicted. That’s because the character type adds white space to the end of the string if there are no other characters. In other words, it pads the string with white space in order to get it to the specified length.

Here’s an example that uses various types:

\x
SELECT
    octet_length('Cat '::character) AS "character",
    octet_length('Cat '::character(10)) AS "character(10)",
    octet_length('Cat '::bpchar) AS "bpchar",
    octet_length('Cat '::bpchar(10)) AS "bpchar(10)",
    octet_length('Cat '::varchar) AS "varchar",
    octet_length('Cat '::varchar(10)) AS "varchar(10)",
    octet_length('Cat '::text) AS "text";

Result:

character     | 1
character(10) | 10
bpchar        | 4
bpchar(10)    | 10
varchar       | 4
varchar(10)   | 4
text          | 4

In this example I used \x to switch to expanded display in order to make the results easier to read.

octet_length() vs bit_length()

The octet_length() function returns the number of bytes in the string, whereas bit_length() returns the number of bits. Therefore, the result of the bit_length() function is eight times the length returned by the octet_length() function.

Here they are compared:

SELECT 
    'octet_length()' AS "Function",
    octet_length('Cat') AS "Cat",
    octet_length('Cafe') AS "Cafe",
    octet_length('Café') AS "Café",
    octet_length('คาเฟ่') AS "คาเฟ่"
UNION
SELECT 
    'bit_length()',
    bit_length('Cat') AS "Cat",
    bit_length('Cafe') AS "Cafe",
    bit_length('Café') AS "Café",
    bit_length('คาเฟ่') AS "คาเฟ่";

Result:

    Function    | Cat | Cafe | Café | คาเฟ่ 
----------------+-----+------+------+------
 bit_length()   |  24 |   32 |   40 |  120
 octet_length() |   3 |    4 |    5 |   15

Passing a Null Argument

Passing a null argument results in null:

SELECT octet_length(null);

Result:

 octet_length 
--------------
         null