PostgreSQL Length + 12 Examples

As a database developer, I frequently utilize the LENGTH() function. This powerful yet simple function is essential for string manipulation and data validation in PostgreSQL databases. In this PostgreSQL tutorial, we will explore the use of PostgreSQL length functions, which will enable us to understand the application of all length-related functions.

PostgreSQL Length

The length() function in PostgreSQL is used to determine the length of a string, which refers to the number of characters or bytes in the given string. The following explains the syntax of the length function.

LENGTH(string);

The length function will accept a string as a parameter. A string can be any of the data types, such as character(char), character varying(varchar), or text. The length function returns the number of characters in the string. Let’s check some of the PostgreSQL length function examples.

SELECT LENGTH ('SQL Server Guides');

SELECT LENGTH ('UNITED STATES'); 

Let’s check the implementation of the above query.

Also, check: PostgreSQL Update

Example 1: Length of a string

In the previous example, we saw a basic example of how to find the length of a string in PostgreSQL. Now, we will check three queries. The first is whether a string can hold an empty string, which is not a null value.

In the second query, it returns zero because a string holds a space character. In the third query, if we pass a NULL value to the length function, it will return a NULL value. Let’s check the queries.

SELECT LENGTH ('');

SELECT LENGTH (' ');

SELECT LENGTH (NULL);

Let’s check the output for the above queries.

In PostgreSQL, we typically use the length function in conjunction with other string functions, such as replace and substring, to handle strings more efficiently. The below query gets the user name and domain from the email address using substring, strpos, and lengthfunctions. Let’s check the query.

SELECT
 SUBSTRING (
'info@sqlserverguides.com',
1,
strpos(
'info@sqlserverguides.com',
'@'
) - 1
) AS user_name,
SUBSTRING (
'info@sqlserverguides.com',
strpos(
'info@sqlserverguides.com',
'@'
) + 1,
LENGTH (
'info@sqlserverguides.com'
)
) AS domain_name;

Let’s check the output for the above query.

Read: PostgreSQL if else

Example 2: Length in bytes

Now we will measure strings in bytes and bits. In PostgreSQL, to determine the number of bytes in a string, we use the octet_length function as shown below.

OCTET_LENGTH(string);

In the first query below it will return 1 byte. However, in the second query with the length function, it will also return just 1. Let’s see the example below.

SELECT OCTET_LENGTH ('Zoo');

SELECT OCTET_LENGTH ('UNITED STATES');

SELECT LENGTH ('Zoo');

SELECT LENGTH ('UNITED STATES');

Let’s examine the output of the query above.

Moreover, to measure the number of bits in a string, we will use the bit_length function. Let’s check the syntax below.

BIT_LENGTH(string);

Let’s understand some examples now.

SELECT BIT_LENGTH ('Z'); 

SELECT BIT_LENGTH ('Zoo'); 

SELECT BIT_LENGTH ('US');

SELECT BIT_LENGTH ('UNITED STATES');

Let’s check the output of the above queries.

Read: PostgreSQL Loop

Example 3: Length of numeric

The NUMERIC type in PostgreSQL stores numbers with a lot of digits. Generally, we use the NUMERIC type for numbers that require precision, such as monetary amounts or quantities. The statement below describes the syntax of the NUMERIC type.

NUMERIC(precision, scale)

We should always keep in mind that the precision is the total number of digits in the above syntax, and the scale is the exact number of digits in the fraction part.

Let’s suppose, in the below example, the number 111.1234567890 has a precision of 13 and a scale of 10. The NUMERIC type generally maintains a value up to 131,072 digits before the decimal point, and 16,383 digits after the decimal point. Let’s check the example below.

select scale(111.1234567890) as decimal_places;

Let’s examine the output of the query above.

Read: PostgreSQL Sum

Example 4: Length of array

This function in PostgreSQL returns the length of the requested array dimension. The array_length() is a system function that returns the length of the requested array dimension. Also, NULL will return if the array is zero-length, or a non-existent dimension is provided. Let’s check the syntax for returning the length of an array.

array_length(anyarray, int);

In the first query, it is a basic usage example for array_length(). Another query is using array_length() with a multi-dimensional array. Let’s look at the queries now.

SELECT array_length(array[1,2,3,4,5,6,7,8,9,0], 1);

SELECT array_length(ARRAY[ [1,2,3,4,5],[6,7,8,9,NULL] ], 2);

Let’s check the output for the above queries.

Read: PostgreSQL REGEXP_REPLACE

Example 5: Length of an integer

In PostgreSQL, we can also measure the length of a number rather than a string. In this case, we will use type casting to convert the number into a string and use the length function as shown in the example below.

SELECT LENGTH (CAST(12345678901234567890 AS TEXT));

In the first query, we used the length function to obtain the integers, but it is showing an error. We have to use the cast function with integers. Let’s check the output now.

Read: PostgreSQL while loop

Example 6: Length of column

We will utilize the LENGTH() or CHAR LENGTH() functions in PostgreSQL to get the string length of the column. Both of these functions will calculate the length of the string in PostgreSQL. Let’s see how to get the length of a string or character in PostgreSQL.

Syntax of LENGTH() is:

length()

Syntax of CHAR_LENGTH() is:

char_length (string)

The string length of the column is extracted using the LENGTH() function, which is the first method for determining the string length of a column in PostgreSQL.

select *, LENGTH(city) as Length from weather;

In the above query, the city is the column name and the weather is the table name. Let’s review the output for the query above.

Another method is to extract the string length of the column in PostgreSQL using the CHAR_LENGTH() function. Let’s check the query for it.

select *, CHAR_LENGTH(city) as Length from weather;

Let’s examine the output of the query above.

Read: PostgreSQL add primary key

Example 7: Length of text

In PostgreSQL, we should know that a text is also a data type used to keep characters of infinite length. Moreover, the text data type has a maximum string length of 65,535 bytes.

To put it another way, the PostgreSQL Text data type utilizes the character data type, denoted by the text keyword. The representation of VARCHAR without a size and TEXT is the same. We should never forget that CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

As we have already learned in the previous topic, a string can be any of the data types, such as character (char), character varying (varchar), or text. Therefore, we will determine the length of the text using the syntax below.

SELECT char_length('sql server guides') AS "Length of a String";

SELECT char_length('united states of america') AS "Length of a String";

Let’s check the output now.

We have observed that the output of the above query is 17 instead of 15. This is because the whitespace between sql and server, and guides is also counted.

Read: PostgreSQL Having Clause

Example 8: Length of json array

In PostgreSQL, the json_array_length() function returns the number of elements in the outermost JSON array. The return type for json_array_length(JSON) is int. Let’s review the example. Below is the query for it.

SELECT json_array_length('["sql","server","guides"]') AS length;

SELECT json_array_length('["united","states","of","america"]') AS length;

Let’s review the output of the query above.

Read: PostgreSQL group by

Example 9: Length of jsnob

There are parallel variants of these operators for both the JSON and jsonb types. The JSON data type is a blob (binary large object) that holds JSON data in raw format, including whitespace, object order, and even duplicate keys in objects.

It will offer limited querying capabilities, and it’s slow since it needs to load and parse the whole JSON blob each time. Additionally, JSONB will store JSON data in a custom format optimized for querying, and it will not reparse the JSON blob each time. Let’s check the query for it.

SELECT jsonb_array_length('["sql","server","guides"]') AS length;

SELECT jsnob_array_length('["united","states","of","america"]') AS length;

Let’s examine the output of the query above.

Read: PostgreSQL Joins

Example 10: Length of Boolean

PostgreSQL gives the standard SQL type boolean. The boolean type can have various states: true, false, and a third state, which is unknown and is represented as a null value. The boolean storage size is 1 byte only.

Although PostgreSQL is quite flexible when dealing with TRUE and FALSE values. PostgreSQL uses one byte to store a boolean value in the database. The BOOLEAN can be abbreviated as BOOL.

We must note that leading or trailing whitespace does not matter, and all constant values except for true and false must be enclosed in single quotes.

Let’s understand this concept with the help of an example. Suppose we have a table item_availability with a boolean value in its values. Now we will calculate its length. Firstly, we will understand the syntax.

select count(*), count(column name with boolean values) from table_name;

Now let’s understand the query. In the query below, ‘available’ is the column name with boolean values, and ‘item_availability’ is the table name.

select count(*), count(available) from item_availability;

Now we will check the output.

Read: PostgreSQL Add Foreign Key

Example 11: Length precision scale

The scale of a number is the exact count of decimal digits in the fractional section, to the right of the decimal point, in PostgreSQL. The precision of a number is the entire count of important digits in the whole number, which is the number of digits to the left and right of the decimal point.

Hence, the first number (precision) in the type definition is the total number of digits. Another is the number of decimal digits. Let’s check the syntax.

NUMERIC(precision, scale)

In the above syntax, the precision is the entire number of digits, and the scale is the number of digits in the fraction part. Like the number 45678.682 has a precision of 8 and a scale of 3.

The NUMERIC data type generally holds a value from 131,072 digits before the decimal point to 16,383 digits after the decimal point. The scale of the NUMERIC data type is zero or positive. The following shows the syntax of the NUMERIC type with a scale of zero.

NUMERIC(precision)

If we discard precision as well as scale, we can store any precision and scale up to the limit mentioned above.

NUMERIC

The NUMERIC and DECIMAL types are equal, and both are also part of the SQL standard. In PostgreSQL, we should avoid using the NUMERIC type in the precision scale, as calculations on NUMERIC values are generally slower than those on integers, floats, and double precision.

Let’s understand this with the help of an example. In the output below, 682 represents the decimal scale, and 45678682 represents the precision, which is the total number of digits. Let’s check its output now.

select precision(45678.682) as decimal_places; 

In the output below, 682 represents the decimal scale, and 45678682 represents the precision, which is the total number of digits. Let’s check its output now.

Read: PostgreSQL Subquery

Example 12: Length of bytea field

PostgreSQL length of bytea fields is a binary string, a sequence of octets (or bytes). Binary strings are differentiated from character strings in two ways. First, binary strings specifically allow storing octets of value zero and other “non-printable” octets (usually, octets outside the decimal range 32 to 126).

Character strings cancel zero octets, and also cancel any other octet values and sequences of octet values that are invalid as specified by the database’s selected character set encoding. Secondly, operations on binary strings process the actual bytes since the processing of character strings depends on locale settings.

Briefly, binary strings are suitable for storing data that the programmer thinks of as raw bytes, since character strings are appropriate for storing text. The octet_length function returns the length in bytes of a bytea field. Let’s check the syntax for it.

SELECT octet_length(the_data_field) FROM table_name;

We have already calculated the weather table, and now we will calculate the length of its bytea field. Let’s check the query for its implementation.

SELECT octet_length(city) FROM weather;

Let’s check its output now.

Conclusion

In this tutorial, we have studied the use of the PostgreSQL length function and gained an understanding of all the length functions through multiple specific examples for each case.

Also, take a look at some more PostgreSQL articles.

• PostgreSQL unique constraint
• PostgreSQL replace + Examples
• PostgreSQL now() function
• PostgreSQL Average + Examples