IF Statement in PostgreSQL

The IF statement in PostgreSQL is a fundamental control structure used to implement conditional logic in database programs. It allows developers to execute specific blocks of code based on conditions, making database operations more dynamic, intelligent, and efficient.

This article provides a comprehensive, beginner-to-intermediate level explanation of the PostgreSQL IF statement, including syntax, real-world use cases, examples, best practices, and comparisons with alternative conditional constructs.

What Is an IF Statement in PostgreSQL?

An IF statement is a conditional control structure that evaluates a Boolean expression and executes SQL statements when the condition evaluates to TRUE.

In PostgreSQL, the IF statement is primarily used inside:

• PL/pgSQL functions
• Stored procedures
• DO blocks
• Triggers

Unlike some programming languages, PostgreSQL does not allow IF statements directly in plain SQL queries.

Why Use IF Statement in PostgreSQL?

Using the IF statement in PostgreSQL helps in implementing intelligent database logic such as:

• Validating data before processing
• Applying business rules at the database level
• Handling conditional updates and inserts
• Improving performance by avoiding unnecessary operations

Basic Syntax of IF Statement in PostgreSQL

Simple IF Statement Syntax

📄

IF condition THEN
    statements;
END IF;

The code inside the IF block runs only if the specified condition evaluates to TRUE.

IF...ELSE Statement Syntax

📄

IF condition THEN
    statements;
ELSE
    alternative_statements;
END IF;

IF...ELSIF...ELSE Syntax

📄

IF condition1 THEN
    statements1;
ELSIF condition2 THEN
    statements2;
ELSE
    statements3;
END IF;

Important Rules for Using IF Statement in PostgreSQL

Real-World Example: Employee Bonus Calculation

Consider a scenario where employee bonuses depend on their salary.

📄

DO $$
DECLARE
    salary NUMERIC := 75000;
    bonus NUMERIC;
BEGIN
    IF salary > 70000 THEN
        bonus := salary * 0.10;
    ELSE
        bonus := salary * 0.05;
    END IF;

    RAISE NOTICE 'Bonus Amount: %', bonus;
END $$;

This example shows how PostgreSQL IF ELSE logic can be used to calculate dynamic values.

Using IF Statement Inside a Function

Example: Check User Status

📄

CREATE OR REPLACE FUNCTION check_user_status(age INT)
RETURNS TEXT AS $$
BEGIN
    IF age < 18 THEN
        RETURN 'Minor';
    ELSIF age BETWEEN 18 AND 60 THEN
        RETURN 'Adult';
    ELSE
        RETURN 'Senior Citizen';
    END IF;
END;
$$ LANGUAGE plpgsql;

This function demonstrates conditional branching using IF, ELSIF, and ELSE in PostgreSQL.

IF Statement in PostgreSQL Triggers

Triggers frequently use IF conditions to control behavior during INSERT, UPDATE, or DELETE operations.

Example: Prevent Negative Salary

📄

CREATE OR REPLACE FUNCTION validate_salary()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

IF Statement vs CASE Expression in PostgreSQL

Common Use Cases of IF Statement in PostgreSQL

• Data validation before insert or update
• Role-based access logic
• Dynamic calculations
• Conditional logging
• Error handling

Common Mistakes to Avoid

• Using IF in plain SQL queries
• Forgetting END IF
• Not handling NULL values
• Overusing IF instead of CASE

The IF statement in PostgreSQL is a powerful control structure that enables conditional logic within PL/pgSQL blocks. By mastering IF, ELSE, and ELSIF constructs, developers can create robust functions, triggers, and procedures that handle real-world business logic efficiently.

Understanding when to use IF statements versus CASE expressions is essential for writing clean, maintainable, and high-performance PostgreSQL code.

Frequently Asked Questions (FAQs)

1. Can I use IF statement directly in a SELECT query?

No, PostgreSQL does not support IF in plain SQL queries. Use the CASE expression instead.

2. Where can I use IF statements in PostgreSQL?

IF statements can be used in PL/pgSQL functions, stored procedures, triggers, and DO blocks.

3. What is the difference between IF and CASE in PostgreSQL?

IF is used for procedural logic, while CASE is used within SQL queries for conditional expressions.

4. Can IF statements handle NULL values?

Yes, but NULL comparisons require explicit handling using IS NULL or COALESCE.

5. Is IF statement slower than CASE?

Performance depends on context. IF is ideal for procedural logic, while CASE is optimized for query execution.