If-then-else statements in postgresql

Nullif and coalesce, the dynamic duo!

Ever wished there was a way to render bad values useless or have a safe fallback? Good news: you have nullif and coalesce. They're like Batman and Robin for your database.

The nullif function returns null if its two arguments are identical, thereby eliminating bad values. On the other hand, coalesce is the fallback guy, returning the first non-null value from its list.

SELECT field1, field2, 
       CASE WHEN field1 > 0 THEN field2/field1 
            ELSE coalesce(nullif(field1, 0), 1) 
       END AS field3 
FROM your_table;


With coalesce and nullif in your corner, no need to fear zero values! By coalescing your worries away, zeros become ones.

Juggling multiple conditions

Sometimes, we have to handle more conditions than an airplane traffic controller. Using coalesce and nullif within CASE can help you manage these conditions like a pro.

Here's a scenario: field1 changes, and we want field3 to reflect these changes.

SELECT field1, 
       field2, 
       CASE WHEN field1 > 100 THEN field2*2
            WHEN field1 BETWEEN 50 AND 100 THEN field2 + 50 
            ELSE coalesce(nullif(field2, 0), field1) 
       END AS field3 
FROM your_table;


Cover your bases and ensure that every possible value of field1 gives the correct output. It's like having a well-trained dog; it does exactly what you expect.

Case-controlled complexity

For handling greater data wrestling, let us look at some of the ways you can bend CASE, nullif, and coalesce to your bidding.

Zero pity

Here's how nullif prevents us from blowing up the universe by dividing by zero:

SELECT field1, 
       field2, 
       CASE WHEN nullif(field1, 0) IS NOT NULL THEN field2/field1 
            ELSE 'Exploding is bad!' -- No more universe-annihilating division by zero!
       END AS field3 
FROM your_table;


Math within reach

Bring in some dynamic 'mathemagical' operations within CASE :

SELECT field1, field2, 
       CASE WHEN field1 > 0 THEN sqrt(field2)/field1 
            ELSE 'Square root of negative? You must be imaginary!'  -- Maths jokes, anyone?
       END AS field3
FROM your_table;


Merging conditions

The power of CASE with UNION ALL can help you navigate any data scenario:

SELECT 'PositiveRatio' AS Type, field2/field1 AS Value
FROM your_table
WHERE field1 > 0
UNION ALL
SELECT 'DefaultZero', 'Boring zero again' -- this zero can't surprise us anymore!
FROM your_table
WHERE field1 <= 0


Trigger happy scenarios

CASE can help you negotiate the landmines of unexpected data. Let's plot those field conditions and forge a path to victory!

Layered case expressions

Nested CASE statements are like onions, each revealing a new layer of enlightenment.

SELECT CASE WHEN field1 > 100 THEN field2*2
            WHEN field1 BETWEEN 50 AND 100 THEN field2 + 50 
            ELSE CASE WHEN field1 <= 0 THEN 'Whoops!
                      ELSE field2 
                 END
       END AS field3 
FROM your_table;


Sequential logic

Sometimes, conditions follow a natural order. Here's a simple ranking system:

SELECT field1, 
       CASE WHEN field1 > 10 THEN 'Large'
            WHEN field1 > 5 THEN 'Medium'
            ELSE 'Small' 
       END AS Size
FROM your_table;


This pattern gives 'Small', 'Medium', or 'Large' based on the value of field1. It's like clothing sizes, but for your data!

Null busters

An effective strategy against those pesky nulls - the ghosts of the SQL world!

SELECT field1, 
       coalesce(field2/NULLIF(field1, 0), 'Cannot Divide by Ghost!') AS field3   
FROM your_table;
