PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - CASE

The CASE statement in PostgreSQL provides a way to perform conditional logic in SQL queries. It allows you to evaluate conditions and return a value when the first condition is met. You can think of it as an IF-THEN-ELSE structure commonly used in programming.

Syntax:

There are two main forms of the CASE statement in SQL:

Simple CASE:

CASE expression 
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ ELSE result_else ]
END

Searched CASE:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ ELSE result_else ]
END

Examples:

Simple CASE:

Imagine you have a table students with a column grade that holds numerical grades.

SELECT name, grade,
CASE grade
    WHEN 90 THEN 'A'
    WHEN 80 THEN 'B'
    WHEN 70 THEN 'C'
    ELSE 'D'
END AS letter_grade
FROM students;

This query assigns a letter grade based on the numerical grade.

Searched CASE:

Using the same students table:

SELECT name, grade,
CASE 
    WHEN grade >= 90 THEN 'A'
    WHEN grade >= 80 AND grade < 90 THEN 'B'
    WHEN grade >= 70 AND grade < 80 THEN 'C'
    ELSE 'D'
END AS letter_grade
FROM students;

This query also assigns a letter grade but uses range conditions.

Points to Note:

The CASE statement evaluates conditions from top to bottom. Once a condition is true, it will return the result and not evaluate the conditions below it.
The ELSE part is optional. If omitted and no condition matches, the result will be NULL.
CASE can be used in various parts of SQL queries, including the SELECT, WHERE, ORDER BY, and HAVING clauses, as well as inside functions and stored procedures.
CASE statements can be nested for more complex conditional logic.

In essence, the CASE statement in PostgreSQL offers a flexible way to introduce conditional logic directly into your SQL queries, enabling you to manipulate and format data on-the-fly during querying.

Using CASE for conditional expressions in PostgreSQL:
- Description: The CASE statement is used for conditional expressions in PostgreSQL. It allows you to perform different actions based on different conditions.
- Code:
```
SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END AS my_result;
```

CASE vs. IF-THEN-ELSE in PostgreSQL:

Description: CASE is used for conditional expressions within SQL statements, while IF-THEN-ELSE is used within PL/pgSQL blocks (procedural language).

Code (CASE):

SELECT
    CASE
        WHEN x > 0 THEN 'Positive'
        WHEN x < 0 THEN 'Negative'
        ELSE 'Zero'
    END AS sign;

Code (IF-THEN-ELSE in PL/pgSQL):

DO $$
DECLARE
    sign TEXT;
BEGIN
    IF x > 0 THEN
        sign := 'Positive';
    ELSIF x < 0 THEN
        sign := 'Negative';
    ELSE
        sign := 'Zero';
    END IF;
    RAISE NOTICE 'Sign: %', sign;
END $$ LANGUAGE plpgsql;

CASE with multiple conditions in PostgreSQL:

Description: You can have multiple conditions within a single CASE statement.

Code:

SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        WHEN condition3 THEN result3
        ELSE result4
    END AS my_result;

Using CASE with SELECT statements in PostgreSQL:

Description: The CASE statement is often used within SELECT statements to conditionally derive values.

Code:

SELECT
    column1,
    column2,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result3
    END AS computed_column
FROM my_table;

CASE expression for updating data in PostgreSQL:

Description: You can use the CASE statement in an UPDATE statement to conditionally modify data.

Code:

UPDATE my_table
SET column1 = CASE
                WHEN condition1 THEN value1
                WHEN condition2 THEN value2
                ELSE column1
              END;

Nested CASE statements in PostgreSQL:

Description: CASE statements can be nested to handle more complex conditions.

Code:

SELECT
    CASE
        WHEN condition1 THEN
            CASE
                WHEN nested_condition THEN nested_result
                ELSE nested_default_result
            END
        WHEN condition2 THEN result2
        ELSE result3
    END AS my_result;

Handling NULL values with CASE in PostgreSQL:
- Description: CASE can be used to handle NULL values by specifying additional conditions.
- Code:
```
SELECT
    column1,
    CASE
        WHEN column2 IS NULL THEN 'No value'
        ELSE column2
    END AS new_column
FROM my_table;
```