CASE Statement in PostgreSQL

In PostgreSQL, the CASE statement is used for conditional logic within SQL queries, similar to other SQL databases. Below is the syntax of the CASE statement in PostgreSQL, along with an example:

SELECT

    order_id,

    order_date,

    CASE

        WHEN CURRENT_DATE - order_date <= INTERVAL '30 days' THEN 'Recent'

        WHEN CURRENT_DATE - order_date <= INTERVAL '90 days' THEN 'Within 3 Months'

        ELSE 'More than 3 Months Ago'

    END AS order_age

FROM

    orders;

In this example, let's assume there's a table named orders with columns order_id and order_date. We want to categorize orders based on their age:

- If the order was placed within the last 30 days, it's categorized as "Recent".

- If the order was placed within the last 90 days but more than 30 days ago, it's categorized as "Within 3 Months".

- If the order was placed more than 90 days ago, it's categorized as "More than 3 Months Ago".

The CASE statement evaluates each condition sequentially and returns the result associated with the first condition that evaluates to true. If none of the conditions are met, the ELSE clause provides a default result.

In PostgreSQL, you can perform date calculations directly by subtracting one date from another. Here, CURRENT_DATE represents the current date, and INTERVAL '30 days' and INTERVAL '90 days' represent durations.

This example demonstrates how to use the CASE statement in PostgreSQL for incorporating conditional logic within SQL queries. You can customize the conditions and result expressions based on your specific requirements.