In PostgreSQL the length() function returns the number of characters in a given string.

We pass the string as an argument and the function returns the number of characters as an integer.

Example

Here’s a quick example to demonstrate:

SELECT length('Café');

Result:

4

So it simply returns the number of characters, regardless of how many bits or bytes are in the string.

Similar Functions

PostgreSQL has a couple of other functions that do exactly the same thing; char_length() and character_length().

There’s also the bit_length() function that returns the number of bits in the string, and the octet_length() function which returns the number of bytes in the string.

Here they all are side by side when using the same string:

\x
SELECT 
    length('Café'),
    char_length('Café'),
    character_length('Café'),
    bit_length('Café'),
    octet_length('Café');

Result (using vertical output):

length           | 4
char_length      | 4
character_length | 4
bit_length       | 40
octet_length     | 5

Trimming White Space

If the original string contains white space on the right or left this can affect the result. We can use the trim() function to remove white space from the string:

SELECT 
    length(' Café ') AS "Untrimmed",
    length(trim(' Café ')) AS "Trimmed";

Result:

 Untrimmed | Trimmed 
-----------+---------
         6 |       4

We can also use btrim(), rtrim() and ltrim() if required.

Passing a Null Value

If the argument is null, then null is returned:

\pset null 'null'
SELECT length(null);

Result:

null