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

• Compatible with PostgreSQL version 9.3 and later.

Visual 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.

Code:

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

Sample 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.

Code:

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

Sample 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:

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:

Code:

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

Sample Output:

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

PostgreSQL OCTET_LENGTH(): Example using with TEXT Type

Code:

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

Sample Output:

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

PostgreSQL OCTET_LENGTH(): Example using with CHAR Type

Code:

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

Sample Output:

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

PostgreSQL OCTET_LENGTH(): Example using with VARCHAR Type

Code:

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

Sample Output:

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

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

Code:

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

Sample Output:

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

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

Code:

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

Sample Output:

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

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

Code:

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

Sample Output:

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

Previous: LOWER function
Next: OVERLAY function