PostgreSQL Length + 12 Examples

In this PostgreSQL tutorial, we will study the use of Postgresql length that will allow us to understand the use of all the length functions. And we are going to discuss the following list of topics.

• Postgresql length
• Postgresql length of string
• Postgresql length of array
• Postgresql length of integer
• Postgresql length of numeric
• Postgresql length of column
• Postgresql length of text
• Postgresql length of json array
• Postgresql length of jsnob
• Postgresql length constraint
• Postgresql length of boolean
• Postgresql length of bytea field
• Postgresql length in bytes
• Postgresql length precision scale

Postgresql length

The length() is a function in Postgresql that is used to find the length of a string means the number of characters or the number of bytes in the given string. The below 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 basically 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

Postgresql length of a string

In the previous example, we have seen a basic example of how we can find the length of a string in Postgresql. Now, we will check three queries first is that 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 generally use the length function with another string function like replace, substring, etc. to handle string 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

Postgresql length in bytes

Now we will measure strings in bytes and bits. In Postgresql, getting the number of bytes in a string, we will use the octet_length function as under.

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 check the output of the above query.

Moreover, for measuring the number of bits of 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

Postgresql 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 accuracy like monetary amounts or quantities. The below statement 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 16,383 digits afterwards the decimal point. Let’s check the example below.

select scale(111.1234567890) as decimal_places;

Let’s check the output of the above query.

Read: Postgresql Sum

Postgresql 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 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

Postgresql length of a integer

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

SELECT LENGTH (CAST(12345678901234567890 AS TEXT));

In the first query, we have taken the integers with length function 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

Postgresql 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 the 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 is the first method of getting the string length of the 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 check the output now for the above query.

Another way is that the string length of the column in PostgreSQL is extracted using the CHAR_LENGTH() function. Let’s check the query for it.

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

Let’s check the output of the above query.

Read: PostgreSQL add primary key

Postgresql length of text

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

To put it another way, the PostgreSQL Text data type uses the character data type, which is denoted by text, and the representation of the varchar without Size n and Text are the same. We should always remember CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

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

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, server, and guides is also counted.

Read: Postgresql Having Clause

Postgresql length of json array

In Postgresql, the json_array_length(JSON) will return the number of elements in the outermost JSON array. The return type for json_array_length(JSON) is int. Let’s check the example for it. 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 check the output of the above query.

Read: PostgreSQL group by

Postgresql length constraint

In Postgresql, data types are methods to limit the kind of data that is stored in a table. The constraint they give is too rough in several applications. Like a column containing a student, the fees column should perhaps only accept positive values. But there is no basic data type that will accept only positive numbers.

Another issue can be supposed if we want to constrain column data concerning other columns or rows. Like if in a table containing student information, there should be only one row for each student id. We can describe constraints on columns as well as tables in Postgresql. Constraints give us as much control over the data in our tables as we want.

If a user attempts to store data in a column that would disrupt a constraint, an error occurs. This applies although the value came from the default value explanation. We have taken a table from our database and we will find the length of the constraints in the name column. Let’s check the query first.

SELECT name,length(name) 
AS "Length of the Name" 
FROM worker
WHERE length(name)>1;

Let’s check the output now.

Read: PostgreSQL Min

Postgresql 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. And, JSONB will store JSON data in a custom format that is optimized for querying and 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 check the output of the above query.

Read: Postgresql Joins

Postgresql 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 have to note that the leading or trailing whitespace does not matter and all the 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 its value in boolean in 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 below query, 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

Postgresql length precision scale

The scale of a numeric is the exact count of decimal digits in the fractional section, to the right of the decimal point in Postgresql. The precision of a numeric is the entire count of important digits in the whole number which is the number of digits to one as well as the other sides 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 below shows the syntax of NUMERIC type with a scale of zero.

NUMERIC(precision)

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

NUMERIC

The NUMERIC and DECIMAL types are equal and both of them are also a part of the SQL standard. In PostgreSQL, we should not use the NUMERIC type in the precision scale since calculations on NUMERIC values are generally slower than integers, floats, and double precisions.

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

select precision(45678.682) as decimal_places; 

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

Read: PostgreSQL Subquery

Postgresql length of bytea field

Postgresql length of bytea fields binary string is 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 size of a bytea field. Let’s check the syntax for it.

SELECT octet_length(the_data_field) FROM table_name;

We have already table weather 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.

Also, take a look at some more PostgreSQL articles.

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

In this tutorial, we have studied the use of the Postgresql length function and have understood the use of all the length functions. And we have discussed the following list of topics.

• Postgresql length
• Postgresql length of string
• Postgresql length of array
• Postgresql length of integer
• Postgresql length of numeric
• Postgresql length of column
• Postgresql length of text
• Postgresql length of json array
• Postgresql length of jsnob
• Postgresql length constraint
• Postgresql length of boolean
• Postgresql length of bytea field
• Postgresql length in bytes
• Postgresql length precision scale