PostgreSQL OCTET_LENGTH() function

OCTET_LENGTH() function

The PostgreSQL octet_length function is used to count the number of bytes in a specified string.

This function is essential for understanding the storage size of string data, as it returns the length in bytes rather than characters. This is particularly useful when dealing with multibyte character encodings, where the byte length may differ from the character length.

Uses of OCTET_LENGTH() Function

• Storage Calculation: Determine the storage size of string data in bytes.


• Data Validation: Ensure data fits within byte limits for specific fields.


• Performance Optimization: Optimize queries and indexes based on byte length.


• Multibyte Character Support: Handle strings with multibyte characters more accurately.


• Memory Management: Manage and allocate memory based on byte size.


• Networking: Measure the byte size of strings for network transmission.

Syntax:

octet_length(string)

Parameters:

The above function is a synonym for LENGTH().

PostgreSQL Version: 9.3

• Compatible with PostgreSQL version 9.3 and later.

Pictorial Presentation of PostgreSQL OCTET_LENGTH() function

Example: PostgreSQL OCTET_LENGTH() function

The following PostgreSQL statement will return the length of the string w3resource. The return value is 10.

SQL Code:

SELECT octet_length('w3resource') AS "octet_length";

Output:

 octet_length
--------------
           10
(1 row)

PostgreSQL OCTET_LENGTH(): Example with Multibyte Characters

The OCTET_LENGTH() function is particularly useful when dealing with strings that contain multibyte characters. Consider the following example with a multibyte character string.

SQL Code:

SELECT octet_length('こんにちは') AS "octet_length";

Output:

octet_length|
------------+
          15|
(1 row)

In this example, each Japanese character is represented by 3 bytes.

PostgreSQL OCTET_LENGTH(): Example with Text Content

Consider a table documents with the following structure:

SQL Code:

CREATE TABLE documents (
    document_id serial PRIMARY KEY,
    content TEXT
);

Here is the data set for the table:
insert into documents values(1,'content1');
insert into documents values(2,'Another content');
insert into documents values(3,'A newr content');

If we want to display the document ID and the byte length of the content for all documents, the following SQL can be executed:

SQL Code:

SELECT document_id, octet_length(content) AS "content_byte_length"
FROM documents;

Output:

document_id|content_byte_length|
-----------+-------------------+
          1|                  8|
          2|                 15|
          3|                 14|

PostgreSQL OCTET_LENGTH(): Example using with TEXT Type

SQL Code:

SELECT octet_length(CAST('HELLO' AS TEXT)) AS "Byte Length Text";

Output:

Byte Length Text|
----------------+
               5|

PostgreSQL OCTET_LENGTH(): Example using with CHAR Type

SQL Code:

SELECT octet_length(CAST('HELLO' AS CHAR(10))) AS "Byte Length Char";

Output:

Byte Length Char|
----------------+
              10|

PostgreSQL OCTET_LENGTH(): Example using with VARCHAR Type

SQL Code:

SELECT octet_length(CAST('HELLO' AS VARCHAR(10))) AS "Byte Length Varchar";

Output:

Byte Length Varchar|
-------------------+
                  5|

PostgreSQL OCTET_LENGTH(): Example using with CONCAT() Function

SQL Code:

SELECT octet_length(CONCAT('Hello', 'World')) AS "Byte Length String";

Output:

Byte Length String|
------------------+
                10|

PostgreSQL OCTET_LENGTH(): Example using with SUBSTRING() Function

SQL Code:

SELECT octet_length(SUBSTRING('HELLO WORLD' FROM 1 FOR 5)) AS "Byte Length Substring";

Output:

Byte Length Substring|
---------------------+
                    5|

PostgreSQL OCTET_LENGTH(): Example using with TRIM() Function

SQL Code:

SELECT octet_length(TRIM('  HELLO  ')) AS "Byte Length Trimmed";

Output:

Byte Length Trimmed|
-------------------+
                  5|

Previous: LOWER function
Next: OVERLAY function