Populate Average String Length of the column in PostgreSQL

To populate the average string length of the column in a PostgreSQL table, you can use the AVG(),CHAR_LENGTH(), function along with  windows function over all_rows;  or a round about method of AVG()& CHAR_LENGTH() function in combination with an UPDATE statement. Let’s look at these approaches with examples

We will be using below “summerfruit” Table

Method 1: Populate the average string length of a column in a PostgreSQL with AVG(), CHAR_LENGTH() and Windows Function:

In the below example we will be populating average string length of a column throughout the column using AVG(), CHAR_LENGTH()  and windows function

 

select *, AVG(CHAR_LENGTH(name)) over all_rows as avg_name_length
from Summerfruits
window all_rows as ();

• AVG(CHAR_LENGTH(name)): This function that calculates the average string length of the name column.
• OVER all_rows: This specifies that the window function should use the window named all_rows.
• WINDOW all_rows AS (): This defines a window named all_rows. The parentheses () indicate that the window includes all rows in the result set, meaning no specific partitioning or ordering is applied, so it operates over the entire dataset.

 Output:

Method 2: Populate the average string length of a column in a PostgreSQL with AVG(), LENGTH() and Windows Function:

In the below example we will be populating average string length of a column throughout the column using AVG(), LENGTH()  and windows function

 

select *, AVG(LENGTH(name)) over all_rows as avg_name_length
from Summerfruits
window all_rows as ();

• AVG(LENGTH(name)): This function that calculates the average string length of the name column.
• OVER all_rows: This specifies that the window function should use the window named all_rows.
• WINDOW all_rows AS (): This defines a window named all_rows. The parentheses () indicate that the window includes all rows in the result set, meaning no specific partitioning or ordering is applied, so it operates over the entire dataset.

 Output:

Method 3: Populate the average string length of a column in a PostgreSQL with AVG(), CHAR_LENGTH() and update statement:

To populate the average string length of a column in a PostgreSQL table, you can use the AVG() & CHAR_LENGTH() function in combination with an UPDATE statement. This operation might be needed when you want to fill a column with the average value of another column

Step1 :  Add a column named avg_name_length

 

ALTER TABLE Summerfruits ADD COLUMN avg_name_length decimal(10,2);

Step2 :  Calculate the Average string length of the column, Update the table with the average string length value by using
SET keyword

 


UPDATE Summerfruits
SET avg_name_length = (SELECT AVG(CHAR_LENGTH(name)) FROM Summerfruits);

Now, the average string length value is updated to the avg_name_length column for all rows in the table

Step3 :  Verify the update

To ensure that the avg_name_length column has been populated correctly, select all columns from the table:

 

select * from Summerfruits

So, our final table will be

Output: