PostgreSQL - IF Statement

The PostgreSQL IF statement is a conditional statement used in PL/pgSQL blocks, functions, procedures and triggers to execute different statements based on specified conditions. It helps control the flow of program execution by evaluating one or more conditions.

Syntax

IF condition THEN
    statements;
END IF;

IF...ELSE

IF condition THEN
    statements;
ELSE
    statements;
END IF;

IF...ELSIF...ELSE

IF condition_1 THEN
    statements;
ELSIF condition_2 THEN
    statements;
ELSE
    statements;
END IF;

Where:

• condition: A Boolean expression that evaluates to TRUE or FALSE.
• statements: The SQL or PL/pgSQL statements executed when the corresponding condition is satisfied.
• ELSIF: Specifies additional conditions to evaluate if the previous condition is FALSE.
• ELSE: Executes when none of the specified conditions are TRUE.

Working

Firstly, create a Library table and insert some records.

Example 1: Basic IF Statement

The following block checks whether copies of a book are available.

Query:

DO $$
DECLARE
    copies INT := 5;
BEGIN
    IF copies > 0 THEN
        RAISE NOTICE 'Book is available.';
    END IF;
END $$;

Output:

• The IF statement checks whether the number of available copies is greater than 0.
• Since the condition is TRUE, PostgreSQL executes the RAISE NOTICE statement.

Example 2: IF...ELSE Statement

The following block checks whether a book is available.

Query:

DO $$
DECLARE
    copies INT := 0;
BEGIN
    IF copies > 0 THEN
        RAISE NOTICE 'Book is available.';
    ELSE
        RAISE NOTICE 'Book is not available.';
    END IF;
END $$;

Output:

• Since the number of available copies is 0, the condition evaluates to FALSE.
• PostgreSQL executes the ELSE block.

Example 3: IF...ELSIF...ELSE Statement

The following block categorizes the availability of books based on the number of copies.

Query:

DO $$
DECLARE
    copies INT := 12;
BEGIN
    IF copies = 0 THEN
        RAISE NOTICE 'Out of Stock';
    ELSIF copies <= 5 THEN
        RAISE NOTICE 'Limited Stock';
    ELSE
        RAISE NOTICE 'Available';
    END IF;
END $$;

Output:

• PostgreSQL evaluates each condition sequentially.
• Since copies is greater than 5, the ELSE block is executed and displays "Available".