PostgreSQL IF PostgreSQL · Conditional · IF

PL/pgSQL IF Statement

Master PL/pgSQL conditional logic with the IF statement — IF THEN, IF THEN ELSE, and IF THEN ELSIF forms. Critical fix: original tutorial showed broken syntax with ELSE condition_2 THEN; the actual keyword is ELSIF (no second E). Covers anti-patterns to avoid (clumsy IF, redundant = TRUE comparisons) and NULL semantics in conditions.

Conditional Control in PL/pgSQL

PL/pgSQL gives you the standard tools for conditional logic: IF for two-way and multi-way branching, and CASE for value/expression matching. Both let you execute different blocks of code based on whether some condition holds.

This page covers the three forms of IF:

Form	Use when
`IF ... THEN ... END IF;`	You only want to do something when a condition is true; do nothing otherwise.
`IF ... THEN ... ELSE ... END IF;`	You want one path when the condition is true and another when it's false.
`IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF;`	Multi-way branching against several mutually-exclusive conditions.

The IF THEN Form

IF condition THEN statements; END IF;

The condition is a Boolean expression — it must evaluate to TRUE, FALSE, or NULL. The statements between THEN and END IF run only when the condition is TRUE.

📌 NULL counts as "not true." If the condition evaluates to NULL (which happens easily — any comparison involving NULL produces NULL), the statements are skipped, just as if the condition were FALSE. Worth keeping in mind whenever your variables might be NULL.

Example 1 — Basic IF THEN

Print a message only when sales exceed a threshold:

PL/pgSQL — basic IF

DO $$
DECLARE
    n_sales INTEGER := 2000000;
BEGIN
    IF n_sales > 100000 THEN
        RAISE NOTICE 'Sales target exceeded: %', n_sales;
    END IF;
END
$$;

OUTPUT

psql — basic IF

postgres=#DO $$ DECLARE n_sales INTEGER := 2000000; BEGIN IF n_sales > 100000 THEN ... END IF; END $$;

NOTICE: Sales target exceeded: 2000000

Two Anti-Patterns to Avoid

Before moving to IF THEN ELSE, two patterns that beginners reach for and that more experienced PL/pgSQL writers avoid:

Anti-pattern 1: The clumsy IF

Using IF to assign a Boolean variable based on a Boolean expression is verbose. The expression is already a Boolean — just assign it directly.

PL/pgSQL — clumsy

-- Clumsy: 4 lines for a 1-line job
IF n_sales > n_costs THEN
    b_profitable := TRUE;
ELSE
    b_profitable := FALSE;
END IF;

-- Better: assign the Boolean expression directly
b_profitable := (n_sales > n_costs);

Anti-pattern 2: Comparing Booleans to TRUE/FALSE

A Boolean variable is already a Boolean — testing b = TRUE is redundant. IF b THEN is shorter and clearer.

PL/pgSQL — redundant comparison

-- Redundant
IF b_profitable = TRUE THEN
    RAISE NOTICE 'Profitable!';
END IF;

-- Cleaner
IF b_profitable THEN
    RAISE NOTICE 'Profitable!';
END IF;

-- For the negative case
IF NOT b_profitable THEN
    RAISE NOTICE 'Not profitable.';
END IF;

⚠️ b = TRUE and b behave the same when b is TRUE or FALSE — but differ for NULL. If b is NULL, b = TRUE evaluates to NULL (treated as not-true). The bare IF b THEN also treats NULL as not-true. They're equivalent in three-valued logic — but the bare form is shorter and the convention.

The IF THEN ELSE Form

IF condition THEN statements; ELSE else_statements; END IF;

If the condition is TRUE, the first block runs. If it's FALSE or NULL, the ELSE block runs.

Example 2 — Sales Commission Tier

10% commission for high-revenue deals, 5% otherwise:

PL/pgSQL — IF/ELSE

DO $$
DECLARE
    n_sales      NUMERIC := 250000;
    commission   NUMERIC;
BEGIN
    IF n_sales > 200000 THEN
        commission := n_sales * 0.10;
    ELSE
        commission := n_sales * 0.05;
    END IF;

    RAISE NOTICE 'Commission on % is %', n_sales, commission;
END
$$;

OUTPUT

psql — commission

postgres=#DO $$ DECLARE n_sales NUMERIC := 250000; commission NUMERIC; BEGIN IF n_sales > 200000 THEN ... END $$;

NOTICE: Commission on 250000 is 25000.00

The IF THEN ELSIF Form

For multi-way branching, use ELSIF (one word, no E). Each ELSIF condition is checked only if all previous conditions were false:

IF condition_1 THEN statements_1; ELSIF condition_2 THEN statements_2; ELSIF condition_3 THEN statements_3; ... ELSE else_statements; END IF;

⚠️ The original tutorial showed the syntax with ELSE condition_2 THEN — that's broken. The keyword for multi-way branching is ELSIF (no second E). The actual examples below the broken syntax box used ELSIF correctly, but the syntax reference itself was wrong. Other Postgres-family dialects spell it ELSEIF; PL/pgSQL specifically uses ELSIF.

Example 3 — Multi-Tier Bonus Logic

Different bonus amounts based on performance rating, falling through to zero for unrated employees:

PL/pgSQL — ELSIF chain

DO $$
DECLARE
    employee_rating INTEGER := 4;
    bonus           NUMERIC;
BEGIN
    IF employee_rating = 5 THEN
        bonus := 1000;
    ELSIF employee_rating = 4 THEN
        bonus := 750;
    ELSIF employee_rating = 3 THEN
        bonus := 500;
    ELSE
        bonus := 0;
    END IF;

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

OUTPUT

psql — bonus tiers

postgres=#DO $$ DECLARE employee_rating INTEGER := 4; bonus NUMERIC; BEGIN IF employee_rating = 5 THEN ... ELSIF ... END $$;

NOTICE: Bonus: $750

Conditions are evaluated top-to-bottom. As soon as one matches, its block runs and the rest are skipped. If none match and there's an ELSE, that runs; if there's no ELSE, the whole IF does nothing.

💡 For matching one expression against several values, prefer CASE. The example above pattern-matches employee_rating against 5, 4, 3 — that's a job for CASE employee_rating WHEN .... ELSIF earns its keep when each branch tests a different condition (different variables, ranges, complex predicates). See the CASE Statement page.

Nested IF Statements

An IF can appear inside another IF — nesting works as you'd expect:

IF condition_1 THEN IF condition_2 THEN nested_statements; END IF; ELSE else_statements; END IF;

Example 4 — Nested IF for Discount Eligibility

PL/pgSQL — nested IF

DO $$
DECLARE
    is_member    BOOLEAN := TRUE;
    order_total  NUMERIC := 150;
    discount     NUMERIC := 0;
BEGIN
    IF is_member THEN
        IF order_total >= 100 THEN
            discount := order_total * 0.15;
        ELSE
            discount := order_total * 0.05;
        END IF;
    END IF;

    RAISE NOTICE 'Discount: %', discount;
END
$$;

OUTPUT

psql — nested IF

postgres=#DO $$ DECLARE is_member BOOLEAN := TRUE; order_total NUMERIC := 150; ... IF is_member THEN IF order_total >= 100 THEN ... END $$;

NOTICE: Discount: 22.50

⚠️ Don't nest more than two levels deep. Each level of nesting makes the code harder to read. If you're tempted to nest three IFs, ask whether you can flatten it: combined conditions with AND / OR, an early-return pattern, or a CASE expression are usually clearer.

Best Practices

Don't write the clumsy IF. If you're assigning TRUE in one branch and FALSE in the other, just assign the Boolean expression directly: b := (x > y);
Don't compare Booleans to TRUE / FALSE. IF b THEN beats IF b = TRUE THEN. IF NOT b THEN beats IF b = FALSE THEN.
Spell it ELSIF in PL/pgSQL — one word, no second E. ELSEIF is a different language's spelling.
Use CASE for matching one expression against many values. ELSIF shines when each branch tests a different condition.
Keep nesting shallow. If you find yourself three levels deep, refactor: combine conditions, return early, or extract a helper function.
Remember NULL. A condition that evaluates to NULL is treated as not-true. If your variables might be NULL, use IS NULL / IS NOT NULL for explicit checks.

Conclusion

PL/pgSQL has three IF forms: IF ... THEN ... END IF, IF ... THEN ... ELSE ... END IF, and IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF.
The condition must be a Boolean expression. NULL is treated as not-true.
Use ELSIF (not ELSEIF, not ELSE ... THEN) for multi-way branching.
Conditions are evaluated top-to-bottom; the first true one wins. The ELSE branch is optional.
Avoid the clumsy IF — assign Boolean expressions directly. Avoid = TRUE / = FALSE comparisons.
For matching one expression against several values, CASE is usually clearer than ELSIF.