Conditional Logic in PostgreSQL: IF and CASE Explained

PostgreSQL IF Statement: Conditional Logic in SQL

Learn how to use conditional logic in PostgreSQL with the IF statement. Includes syntax, examples, and tips for efficient conditional database operations.

PostgreSQL IF Statement

PostgreSQL does not have a direct IF statement for use within SQL queries. However, conditional logic can be achieved using:

1. PL/pgSQL Blocks:

The IF statement is available in PL/pgSQL blocks for procedural logic.

2. CASE Expression:

For inline conditional logic in SQL queries.

Using IF in PL/pgSQL

The IF statement in PL/pgSQL allows developers to execute conditional operations within a procedural block.

Syntax:

DO $$
BEGIN
    IF condition THEN
        -- Statements to execute if condition is true
    ELSIF other_condition THEN
        -- Statements for the next condition
    ELSE
        -- Statements if no condition matches
    END IF;
END;
$$ LANGUAGE plpgsql;

Example:

Code:

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM users WHERE email = '[email protected]') THEN
        RAISE NOTICE 'User exists';
    ELSE
        RAISE NOTICE 'User does not exist';
    END IF;
END;
$$ LANGUAGE plpgsql;

Explanation:

• The DO $$ ... $$ block defines an anonymous PL/pgSQL function.
• IF EXISTS checks if a user exists in the users table.
• RAISE NOTICE prints a message depending on the condition's result

Using CASE in SQL Queries

The CASE expression allows for inline conditional evaluations within a query.

Syntax:

SELECT 
    CASE 
        WHEN condition THEN result
        ELSE default_result
    END
FROM table_name;

Example:

Code:

SELECT 
    user_id, 
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 60 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

Explanation:

• Evaluates age and assigns a category (Minor, Adult, or Senior).
• Returns the result as a new column (age_group).

Advanced Tips

• Nested IF Statements:
  PL/pgSQL supports nested IF statements for complex logic.
• Performance Consideration:
  Use CASE expressions in queries where possible, as they are optimized for SQL execution.
• Debugging:
  Use RAISE NOTICE in PL/pgSQL blocks to debug logic during development.

All PostgreSQL Questions, Answers, and Code Snippets Collection.