CASE Statement in PostgreSQL

In PostgreSQL, the CASE statement allows you to perform conditional logic within a SQL query. It evaluates a list of conditions and returns one result based on the first condition that is true, or a default value if no conditions are true. Here's the syntax:-

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE default_result

END

- condition1, condition2, etc.: Conditions to be evaluated.

- result1, result2, etc.: Results returned if the corresponding condition is true.

- default_result: Result returned if none of the conditions are true (optional).

Example with Data:-

Suppose we have a table named employees with columns employee_id, first_name, last_name, and salary. We want to create a new column called salary_range based on the salary of each employee.

CREATE TABLE employees (

    employee_id SERIAL PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    salary NUMERIC

);

INSERT INTO employees (first_name, last_name, salary)

VALUES

    ('John', 'Doe', 50000),

    ('Jane', 'Smith', 70000),

    ('Alice', 'Johnson', 90000),

    ('Bob', 'Brown', 60000);

Now, let's use a CASE statement to categorize employees into salary ranges:

SELECT

    first_name,

    last_name,

    salary,

    CASE

        WHEN salary < 60000 THEN 'Low'

        WHEN salary >= 60000 AND salary < 80000 THEN 'Medium'

        ELSE 'High'

    END AS salary_range

FROM employees;

Output:

| first_name | last_name | salary | salary_range |

|------------|-----------|--------|--------------|

| John       | Doe       | 50000  | Low          |

| Jane       | Smith     | 70000  | Medium       |

| Alice      | Johnson   | 90000  | High         |

| Bob        | Brown     | 60000  | Medium       |

In this example, the CASE statement categorizes employees into salary ranges ('Low', 'Medium', 'High') based on their salary.

In PostgreSQL, the CASE statement allows you to perform conditional logic within a SQL query. It evaluates a list of conditions and returns one result based on the first condition that is true, or a default value if no conditions are true. Here's the syntax:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE default_result

END

- condition1, condition2, etc.: Conditions to be evaluated.

- result1, result2, etc.: Results returned if the corresponding condition is true.

- default_result: Result returned if none of the conditions are true (optional).

Example with Data:

Suppose we have a table named employees with columns employee_id, first_name, last_name, and salary. We want to create a new column called salary_range based on the salary of each employee.

CREATE TABLE employees (

    employee_id SERIAL PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    salary NUMERIC

);

INSERT INTO employees (first_name, last_name, salary)

VALUES

    ('John', 'Doe', 50000),

    ('Jane', 'Smith', 70000),

    ('Alice', 'Johnson', 90000),

    ('Bob', 'Brown', 60000);

Now, let's use a `CASE` statement to categorize employees into salary ranges:

SELECT

    first_name,

    last_name,

    salary,

    CASE

        WHEN salary < 60000 THEN 'Low'

        WHEN salary >= 60000 AND salary < 80000 THEN 'Medium'

        ELSE 'High'

    END AS salary_range

FROM employees;

Output:

| first_name | last_name | salary | salary_range |

|------------|-----------|--------|--------------|

| John       | Doe       | 50000  | Low          |

| Jane       | Smith     | 70000  | Medium       |

| Alice      | Johnson   | 90000  | High         |

| Bob        | Brown     | 60000  | Medium       |

In this example, the CASE statement categorizes employees into salary ranges ('Low', 'Medium', 'High') based on their salary.

Here are five frequently asked questions about the `CASE` statement in PostgreSQL:

1. What is the CASE statement in PostgreSQL?

   - The CASE statement in PostgreSQL is a conditional expression that allows you to perform conditional logic within a SQL query. It evaluates multiple conditions and returns a result based on the first condition that is true, or a default value if none of the conditions are true.

2. How do you use the CASE statement in PostgreSQL?

   - You use the CASE statement by specifying one or more conditions with corresponding results. The syntax is:

   

     CASE

         WHEN condition1 THEN result1

         WHEN condition2 THEN result2

         ...

         ELSE default_result

     END

3. Can the CASE statement be nested in PostgreSQL?

   - Yes, the CASE statement can be nested within another CASE statement in PostgreSQL. This allows for more complex conditional logic to be implemented.

4. What data types can be used with the `CASE` statement in PostgreSQL?

   - The CASE statement in PostgreSQL can be used with any data type supported by the database, including numeric, string, date, and boolean types.

5. In which scenarios is the CASE statement commonly used in PostgreSQL?

   - The CASE statement is commonly used in PostgreSQL for various purposes, such as data transformation, conditional aggregation, and generating computed columns. It's particularly useful when you need to perform different actions based on different conditions within a single query.