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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.