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 Statement

The CASE statement in PostgreSQL allows for conditional operations in SQL, similar to IF-THEN-ELSE logic in traditional programming languages. It provides a means of performing different actions depending on certain conditions. Here's a deeper dive into the CASE statement.

Syntax:

The CASE statement can take two forms in SQL:

Simple CASE:

CASE input_expression 
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    ...
    [ ELSE result_default ]
END

Here, the input_expression is compared sequentially to the expression values. If a match is found, the corresponding result is returned.

Searched CASE:

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

In this form, conditions are evaluated in the order they appear. The result for the first true condition is returned.

Examples:

Simple CASE:

Imagine a table orders with a column status that holds values such as 'shipped', 'pending', etc.

SELECT order_id, status,
CASE status
    WHEN 'shipped' THEN 'Order has been shipped'
    WHEN 'pending' THEN 'Order is still pending'
    ELSE 'Status unknown'
END AS status_description
FROM orders;

This query provides a descriptive status based on the status value.

Searched CASE:

Using a table products with a price column:

SELECT product_name, price,
CASE 
    WHEN price > 1000 THEN 'Expensive'
    WHEN price BETWEEN 500 AND 1000 THEN 'Moderate'
    ELSE 'Affordable'
END AS price_category
FROM products;

This query categorizes products based on their price.

Points to Note:

CASE evaluations happen in order, top to bottom. Once a true condition is found, the corresponding result is returned, and subsequent conditions are not checked.
The ELSE clause is optional. If no conditions are true and there's no ELSE clause, the result is NULL.
CASE can be used in many parts of SQL statements, such as in the SELECT, WHERE, ORDER BY, and HAVING clauses, as well as inside aggregate functions and more.
Nested CASE statements are possible, providing even more complex conditional logic if needed.
Remember that performance can be a concern, especially with more complicated CASE structures or when operating on large datasets. Always monitor query performance and consider indexing or restructuring if necessary.

In summary, the CASE statement in PostgreSQL is a powerful tool for adding conditional logic to SQL statements, making it possible to perform more dynamic and context-aware operations directly within queries.

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

CASE statement with SELECT in PostgreSQL:

Description: CASE statements are often used within SELECT queries 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;

Multiple conditions with CASE statement in PostgreSQL:

Description: You can have multiple conditions within a single CASE statement for more complex logic.

Code:

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

CASE statement vs COALESCE in PostgreSQL:

Description: While CASE is used for conditional logic, COALESCE is used to return the first non-null expression in a list.

Code (CASE):

SELECT
    column1,
    CASE
        WHEN column2 IS NULL THEN 'No value'
        ELSE column2
    END AS new_column
FROM my_table;

Code (COALESCE):

SELECT
    column1,
    COALESCE(column2, 'No value') AS new_column
FROM my_table;

CASE statement in UPDATE queries in PostgreSQL:

Description: The CASE statement can be used in UPDATE queries 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: You can nest CASE statements 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;
```