Table of contents
1.
Introduction
2.
Conditionals
2.1.
CASE
2.2.
COALESCE
2.3.
NULLIF()
2.4.
CAST
3.
Control Flow
3.1.
 IF Statement
3.2.
Case Statement
3.3.
Loop Statement
3.4.
For Loops
3.5.
While Loops
3.6.
Exit
3.7.
Continue
4.
Frequently Asked Questions
4.1.
How to check multiple conditions in PostgreSQL?
4.2.
What is $$ in PostgreSQL?
4.3.
What is the use of % in SQL?
5.
Conclusion
Last Updated: Mar 27, 2024
Easy

Conditionals and Control Flow in PostgreSQL

Author Nagendra
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Hi Ninjas!! Welcome to another blog on PostgreSQL. Today we will learn about Conditionals and Control Flow in PostgreSQL. PostgreSQL is a powerful, open-source object-relational database management system (ORDBMS). It is used to store data securely.

Conditionals and Control Flow in PostgreSQL

Conditionals

The conditional statements are used to make decisions based on specific conditions in a query.

Let's look at the conditionals in PostgreSQL.

Conditionals Chart

Let's look into details of each of them.

CASE

CASE is used to form conditional queries. It is similar to the if-else statement.

Syntax:

CASE 
      WHEN condition_1 THEN result_1
      WHEN condition_2 THEN result_2
      [WHEN ...]
      [ELSE result_n]
END


Example:

Consider the table:

Table image
Select SUM( CASE When Department="CSE" THEN 1 ELSE 0 END) as CSE from student;

The query above is to find the sum of students from CSE department.
Output:

Output Image

COALESCE

The COALESCE function is used to handle the Null Values in a table. This function allows assigning specific values to Null Values during Database query. COALESCE conditional function can accepts number of arguments at a time ,If all the arguments are NULL in that case it returns the fist argument as not null.

Syntax:

COALESCE (argument_1, argument_2, …);

Example:

Consider the Following table:

table
Select Name, COALESCE(Marks, 0) as Marks from student;

In this example row 11 contains a null value. Using COALESCE on column marks the null value is replaced with 0.

Output:

Output Image

Example:

Consider table Placement_cell :

Here St_id 1011 contains a NULL value in the company column.

Placement_cell table
select company,coalesce(company,'NoINFO') from Placement_cell;

This query replaces the NULL value in company column to ‘NoINFO’.

OUTPUT:

output of coalesce query

NULLIF()

The NULLIF() is a type of conditional in PostgreSQL. It compares two arguments from a particular table record and returns NULL only when the values of the arguments are equal; else, it returns the value of the first argument.

Syntax:

NULLIF(argument1,argument2);


Example:

Consider the table:

Table image
Select Name, NULLIF(Marks, Roll) from student;


Output:

Output Image

EXAMPLE:

Let us consider a table Placement_cell with St_id ,name, status,Incampus, and company as columns. NULLIF conditional is applied to check the status and Incampus placement details of students. 

Placement_cell table
NULLIF(status,Incampus);

OUTPUT:

output of NULLIF query
NULLIF(argument1,argument2)

NULLIF compares the values of both the arguments ,and returns NULL if the values of the arguments are equal else returns the value of argument1.

CAST

The CAST operator is used to modify the data type of the values.

Syntax:

CAST ( expression AS DataType );


Example:

SELECT CAST(525.25 AS int);


Output:

525

Control Flow

The control flow is the sequence in which a computer executes statements.

Let's look at the Control Flow statements in PostgreSQL.

Control Flow Chart

Let's look into details of each of them.

 IF Statement

The IF statement executes statements based on conditions. If the condition is true, the specified statement is executed. If the condition is false, the control is passed to the next statement.

Example:

DO $$
DECLARE
  x integer := 100;
  y integer := 200;
BEGIN 
  IF x > y THEN
    RAISE NOTICE 'first integer is greater than second integer';
  END IF;

  IF x < y THEN
    RAISE NOTICE 'first integer is less than second integer';
  END IF;

  IF x = y THEN
    RAISE NOTICE 'first integer is equal to second integer';
  END IF;
END $$;

 

Output:

NOTICE: first integer is less than second integer

Case Statement

The CASE statement executes a block of code conditionally.

Syntax:

CASE expression
   WHEN exp_1 [, exp_2, ...] THEN
      when-statements
  [ ... ]
  [ELSE
      else-statements ]
END CASE;


Example:

do $$
declare
  grade char(3) := 'Mon';   
BEGIN  
   CASE grade  
      when 'Mon' then raise notice 'Monday';  
      when 'Tue' then raise notice 'Tuesday';  
      when 'Wed' then raise notice 'Wednesday';  
      when 'Thu' then raise notice 'Thursday';  
      when 'Fri' then raise notice 'Friday';  
      when 'Sat' then raise notice 'Saturday'; 
      when 'Sun' then raise notice 'Sunday';  
      else raise notice 'Failed';  
   END CASE;   
end; $$;

 

Output:

Notice: Monday

Loop Statement

The loop statement is used to establish an unconditional loop. This loop constantly runs the statements until it is terminated. The loop is terminated using exit or return statement.

Syntax:

loop
  statements;
end loop;

 

Example:

do $$
declare
  n integer:= 6;
  cnt integer := 1 ;  
begin
loop  
 exit when cnt > 10 ;
 raise notice '%', n*cnt;  
 cnt := cnt + 1 ;  
end loop;  
end; $$;

 

Output:

NOTICE: 6
NOTICE: 12
NOTICE: 18
NOTICE: 24
NOTICE: 30
NOTICE: 36
NOTICE: 42
NOTICE: 48
NOTICE: 54
NOTICE: 60

 

For Loops

The for loops are used to iterate over a range of numbers.

Syntax:

[ <<label>> ]
for loop_cnt in [ reverse ] from.. to [ by step ] loop
    statements
end loop [ label ];

 

Example:

do $$
begin
   for cnt in 1..10 loop
    raise notice '%', cnt*6;
   end loop;
end; $$

 

Output:

NOTICE: 6
NOTICE: 12
NOTICE: 18
NOTICE: 24
NOTICE: 30
NOTICE: 36
NOTICE: 42
NOTICE: 48
NOTICE: 54
NOTICE: 60

While Loops

The while loop is used to execute statements until the condition is true.

Syntax:

WHILE <condition>   
 LOOP statements;   
END LOOP;  

 

Example:

do $$
declare
 cnt integer := 1 ;
begin
    WHILE cnt <= 10 loop
    raise notice '%', cnt*6;
    cnt:= cnt+1;
   end loop;
end; $$

 

Output:

NOTICE: 6
NOTICE: 12
NOTICE: 18
NOTICE: 24
NOTICE: 30
NOTICE: 36
NOTICE: 42
NOTICE: 48
NOTICE: 54
NOTICE: 60

Exit

The EXIt statement is used to terminate from the loop. It terminates the block of code specified between the begin..end keywords.

Syntax:

exit [label] [when condition]


Example:

do $$
declare
  n integer:= 6;
  cnt integer := 1 ;  
begin
loop  
 exit when cnt > 10 ;
 raise notice '%', n*cnt;  
 cnt := cnt + 1 ;  
end loop;  
end;
$$;


Output:

NOTICE: 6
NOTICE: 12
NOTICE: 18
NOTICE: 24
NOTICE: 30
NOTICE: 36
NOTICE: 42
NOTICE: 48
NOTICE: 54
NOTICE: 60

Continue

The continue statement is used to skip the current iteration and jump to the next iteration in a loop.

Syntax:

CONTINUE [ label ] [ WHEN boolean-expression ];


Example:

do
$$
declare
  cnt int = 0;
begin
 loop
    cnt = cnt + 1;
 exit when cnt > 5;
 continue when mod(cnt,2) = 0;
 raise notice '%', cnt;
 end loop;
end;
$$;


Output:

NOTICE: 1
NOTICE: 3
NOTICE: 5

Frequently Asked Questions

How to check multiple conditions in PostgreSQL?

You can check multiple conditions in PostgreSQL by using AND operator.

What is $$ in PostgreSQL?

The $$ is the specific substitute for using single quotes to avoid escaping of nested single quotes (recursively).

What is the use of % in SQL?

The percent sign (%) represents zero, one, or multiple characters.

Conclusion

In this article, we have discussed the details of Conditionals and Control Flow in PostgreSQL with their examples.

We hope that the blog has helped you enhance your knowledge regarding Conditionals and Control Flow in PostgreSQL. You can refer to our guided paths on the Coding Ninjas Studio platform to learn more about DSADBMSCompetitive ProgrammingPythonJavaJavaScript, etc. To practice and improve yourself in the interview, you can also check out Top 100 SQL problemsInterview experienceCoding interview questions, and the Ultimate guide path for interviews. Do upvote our blogs to help other ninjas grow. Happy Coding!!

Live masterclass