How to Use IF ELSE in PL/pgSQL: Syntax and Examples

In PL/pgSQL, use IF ... THEN ... ELSE ... END IF; to run different code based on conditions. The IF checks a condition, THEN runs code if true, and ELSE runs code if false.

Syntax

The IF statement in PL/pgSQL lets you execute code conditionally. It starts with IF condition THEN, followed by the code to run if true. You can add ELSIF for more conditions and ELSE for the default case. Always end with END IF;.

• IF condition THEN: Checks if the condition is true.
• ELSIF condition THEN: Checks another condition if the first is false.
• ELSE: Runs if all conditions are false.
• END IF;: Marks the end of the IF block.

sql

IF condition THEN
    -- statements if condition is true
ELSIF another_condition THEN
    -- statements if another_condition is true
ELSE
    -- statements if none of the above conditions are true
END IF;

Example

This example shows a PL/pgSQL function that returns a message based on the input number using IF ELSE. It returns 'Positive', 'Negative', or 'Zero'.

sql

CREATE OR REPLACE FUNCTION check_number(num integer) RETURNS text AS $$
BEGIN
    IF num > 0 THEN
        RETURN 'Positive';
    ELSIF num < 0 THEN
        RETURN 'Negative';
    ELSE
        RETURN 'Zero';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Example calls:
SELECT check_number(10);  -- returns 'Positive'
SELECT check_number(-5);  -- returns 'Negative'
SELECT check_number(0);   -- returns 'Zero';

Output

 check_number 
--------------
 Positive
(1 row)

 check_number 
--------------
 Negative
(1 row)

 check_number 
--------------
 Zero
(1 row)

Common Pitfalls

Common mistakes when using IF ELSE in PL/pgSQL include:

• Forgetting END IF; to close the block.
• Using ELSE IF instead of ELSIF (PL/pgSQL requires ELSIF).
• Not using THEN after IF or ELSIF.
• Placing statements outside the BEGIN ... END block in functions.

Example of wrong and right usage:

sql

-- Wrong:
IF x > 0 THEN
    RAISE NOTICE 'Positive';
ELSE IF x < 0 THEN  -- Incorrect: should be ELSIF
    RAISE NOTICE 'Negative';
END IF;

-- Right:
IF x > 0 THEN
    RAISE NOTICE 'Positive';
ELSIF x < 0 THEN
    RAISE NOTICE 'Negative';
ELSE
    RAISE NOTICE 'Zero';
END IF;

Quick Reference

Keyword	Description
IF condition THEN	Start conditional block, runs if condition is true
ELSIF condition THEN	Additional condition if previous IF/ELSIF is false
ELSE	Runs if all previous conditions are false
END IF;	Ends the IF block

Key Takeaways

Use IF ... THEN ... ELSIF ... ELSE ... END IF; to control flow in PL/pgSQL.

Always close your IF blocks with END IF; to avoid syntax errors.

Use ELSIF (not ELSE IF) for multiple conditions in PL/pgSQL.

Place IF statements inside BEGIN ... END blocks in functions or procedures.

Test your conditions carefully to ensure correct logic flow.