Postgresql if else – How to use

In this Postgresql tutorial, we will learn about “Postgresql if-else” and cover the following topics.

• Postgresql if else condition
• Postgresql if else select
• Postgresql if else function
• Postgresql if else do nothing
• Postgresql if else update

Postgresql if else condtion

In PostgreSQL, we can use execute the different statements if some condition is true or false, for this we will use the if-else statement that is a conditional statement.

Let’s understand through a simple example that checks which number is greater or less and equal to another number.

DO $$
DECLARE
  x integer := 50;
  y integer := 90;
BEGIN 
  IF x > y THEN
	RAISE NOTICE 'x is greater than y';
  END IF;

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

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

In the above code, within DECLARE section two variables are declared named x and y with values of 50 and 90 respectively.

In the BEGIN and END sections, we have used the IF, THEN, and END IF for creating a conditional statement.

The output of the above code is given below.

Now, perform the same query with different data using the below code.

DO $$
DECLARE
  x varchar := 'Toronto';
  z varchar := 'Hamilton';
BEGIN 
  IF x = 'Toronto' THEN
	RAISE NOTICE 'city of canada';
  END IF;

  IF z = 'Hamilton' THEN
	RAISE NOTICE 'city of new zealand';
  END IF;
END $$;

The above code will output Canada if x is equal to ‘Toronto’ and New Zealand when z is equal to ‘Hamilton’.

Also, Check: PostgreSQL list databases

Postgresql if else select

In Postgresql, we can use if-else in the select statement but in the place of IF, here we will use the CASE and WHEN that is similar to the if-else statement. Because, in a SELECT statement, we cannot use the IF directly.

The table we are going to use in this section is employe which contains the information of employees like birth_date, first_name, last_name, hired_date, etc.

The description of the table is given below.

Let’s run the below code to show the first name of the employee if gender is M or Male otherwise, it will show the “Gender is not M or Male”.

SELECT gender,
  CASE
    WHEN gender ='M' THEN first_name
    ELSE 'Gender is not M or Male'
  END 
  AS gen
FROM employee

The output of the above code is given below.

Read: PostgreSQL Data Types

Postgresql if else function

In Postgresql, we can use the if-else statement in the function, so in this section, we will create a function that will return a number that is greater between two numbers.

Let’s create a function named greater_num that will return the greater number of two numbers.

CREATE FUNCTION greater_num(x int,y int)
RETURNS int AS
$$
BEGIN
  IF x > y THEN
  RETURN x;
  ELSE 
  RETURN y;
  END IF;
END;
$$ LANGUAGE plpgsql;

Let’s call the functions with two numbers 3 and 2.

SELECT greater_num(3,2);

The output of the above codes is given below.

In the above output, the greater number is 3 between 3 and 2.

Read: PostgreSQL Date Add

Postgresql if else do nothing

In Postgresql, we can return nothing or null when using the if-else statement, which means when the expression will true, then it returns the value otherwise returns nothing.

Let’s create a function name max_num to know the maximum number between two numbers.

CREATE FUNCTION max_num(x int,y int)
RETURNS int AS
$$
BEGIN
  IF x > y THEN
  RETURN x;
  ELSE
  RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;

In the above code, we will provide two different values to function max_num and if x is greater than y, then it will return the max value, otherwise, return a null value or nothing.

Let’s call the function with values 3 and 2.

SELECT max_num(3,2)

The output of the above code is given below.

In the above output, we have provided two value 3 and 2 and max_num function return the value 3.

Now we will call the function again with values 2 and 3.

The output in the above code is null when 2 and 3 are supplied to the max_num function.

Read: How to create a view in PostgreSQL

Postgresql if else update

In Postgresql, we can update the data using an if-else statement, we will use the employee table which contains a column named gender.

Let’s check the gender column before updating.

In the above output, the gender column has two types of values M and F.

So run the below code to update the M to Male or F to Female of column gender.

UPDATE employee
  SET gender = 
    CASE 
        WHEN gender = 'M' then 'Male'
        ELSE
		'Female'
    END
	
SELECT * FROM employee;

The output of the above code is given below.

Also, take a look at some more PostgreSQL tutorials.

• Postgresql create user with password
• PostgreSQL Length + 12 Examples
• How to set user password in PostgreSQL
• Postgresql ilike case insensitive
• PostgreSQL Min With Examples
• PostgreSQL CREATE INDEX
• Postgresql import SQL file
• Postgresql generate_series

So, in this tutorial, we have learned about “Postgresql if else” and covered the following topics.

• Postgresql if else condition
• Postgresql if else select
• Postgresql if else function
• Postgresql if else do nothing
• Postgresql if else update
• Postgresql if else in where clause