Postgresql while loop – Complete Guide

In this PostgreSQL tutorial, we will learn about “Postgresql while loop” and cover the following topics.

• Postgresql while loop in select
• Postgresql while loop insert
• Postgresql while loop update
• Postgresql while loop break
• Postgresql while loop date range
• Postgresql while loop execute
• Postgresql while loop array

Introduction to while loop

When we need to perform the same task again and again then we use the while loop or while loop is used where the task is repetitive. Suppose that we want the population of the city in the United Kindom.

To find the population we will need to search each city name according to the country to fetch the exact population and this kind of task are repetitive. The easy solution to this kind of problem is a while loop.

The while loop runs the block of code until the condition becomes false. The syntax of the while loop is given below.

WHILE condition LOOP
   statements;
END LOOP;

In the above syntax, first PostgreSQL checks or evaluates the condition before executing the statements.

If the condition becomes true, then it executes the statements. After each iteration, the while loop checks or evaluates the condition again.

Inside the block of the while loop, we need to change the values of some variables to make the condition false at some points. Otherwise, we will have an indefinite loop.

Postgresql while loop insert

In Postgresql, we can insert the data in the column or table using the WHILE LOOP.

Let’s create the new table named dummy_insert.

CREATE TABLE dummy_insert(id int);

Insert the data in the table dummy_insert using the while loop.

DO $$
DECLARE 
_id int :=0;
BEGIN
	WHILE _id < 6 LOOP
	INSERT INTO dummy_insert(id)VALUES(_ID);
	_id := _id+1;
	END LOOP;
END $$;

SELECT * FROM dummy_insert;

In the above code, we have declared the variable _id and initialized it to 0 in the DECLARE section.

The output of the above code is given.

Read: PostgreSQL Loop Examples

Postgresql while loop update

In Postgresql, with the help of a while loop, we can update the column or table value.

The table we will use in this section is a dummy_name and a description of the table is given below.

CREATE TABLE dummy_name(id SERIAL,name VARCHAR);

Insert the following records.

INSERT INTO dummy_name(name)VALUES('JHON'),('ADAM'),('Leon M'),
('Randy K'),('David C'),('Michael M');

Now run the below query to update the name in the upper case.

DO $$
DECLARE 
colum_no int :=1;
BEGIN
	WHILE colum_no < 5 LOOP
	UPDATE dummy_name
	SET name = UPPER(name)
	WHERE id = colum_no;
	colum_no := colum_no+1;
	END LOOP;
END $$;

The output of the above code is given below.

Read: Postgresql if else

Postgresql while loop break

In Postgresql, we can break the while loop after reaching a certain condition, but there is no BREAK in Postgresql to terminate the while loop. Instead, we can use EXIT to terminate the loop.

Let’s run the below code to exit or break the loop after printing the 5 values from 1 to 5.

DO $$
DECLARE 
counter int:=1;
BEGIN
	WHILE counter <=10 LOOP
	IF counter <= 5 THEN
	RAISE NOTICE '%',counter;
	ELSE
	EXIT;
	END IF;
	counter := counter + 1;
	END LOOP;
END $$;

The output of the above code is given below.

Read: Postgres date range

Postgresql while loop date range

In Postgresql, we can run a while loop over the date range, the date range is a range of dates.

Let’s run the below code to run while looping over the range of dates.

DO $$
DECLARE 
start_date date:= '2020-01-01';
end_date date := '2020-01-06';
BEGIN
	WHILE start_date <= end_date LOOP
	RAISE NOTICE '%',start_date;
	start_date := start_date + interval '1 day';
	END LOOP;
END $$;

The output of the above code is given below.

Read: Drop Database PSQL

Posgresql while loop array

In Postgresql, we can loop or iterate over an array, an array is a collection of the homogenous types of data types.

Let’s create an array named digit, that contains 5 integers from 1 to 5, then we will print each value of the array using the while loop.

DO $$
DECLARE 
digit int[] :=array[1,2,3,4,5];
array_idx int:=1;
BEGIN
	WHILE array_idx < 6 LOOP
	RAISE NOTICE '%',digit[array_idx];
	array_idx := array_idx+1;
	END LOOP;
END $$;

The output of the above code is given below.

Also, check the following PostgreSQL tutorials.

• Postgresql date comparison
• Postgresql date to string
• Postgresql Joins
• Postgresql Format
• PostgreSQL list users
• Postgresql now() function
• Postgresql Having Clause
• Postgresql Add Foreign Key
• PostgreSQL Date Difference
• PostgreSQL Min With Examples

So, in this tutorial, we have learned about “Postgresql while loop“. And we have also covered the following topics.

• Postgresql while loop in select
• Postgresql while loop insert
• Postgresql while loop update
• Postgresql while loop break
• Postgresql while loop date range
• Postgresql while loop execute
• Postgresql while loop array