Using Case/When in PostgreSQL

The "default" choice for a database system back in the day when I first started my career in web hosting was MySQL. MySQL is a great option for storing data! And a. MySQL server can also perform replication with another MySQL server, so it's a great option for a live backup.

However, as time progressed, I became more and more familiar with PostgreSQL.

If you're coming from the MySQL world, then PostgreSQL certainly has some oddities. I was just reflecting on this the other day, where in MySQL I can use "SHOW CREATE TABLE table_name", but PostgreSQL doesn't provide that simple statement. Instead, you can use something like "\d table_name".

In any case, the CASE statement is a fun feature, and it's not unique to PostgreSQL alone.

The CASE statement allows a sort of IF/ELSE logic in your database query. This is helpful because sometimes you need to conditionally select different sets of data depending on a situation. Instead of putting that logic purely into some programming language, you can make the decision within the database query.

So how is this done?

Fortunately I manage hosting infrastructure, so I have easy access to all sorts of server programs. Although, using docker or some other container runtime, you too can easily access a PostgreSQL system.

First off, the typical syntax of using CASE goes like this:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

However, you should always consult the documentation, which gives this syntactical example:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

No matter, as the formatting itself doesn't matter much. Which can be detrimental in extreme cases.

Well, let's first start with a table that we can work with:

CREATE TABLE topping (
    id SERIAL PRIMARY KEY,
    name VARCHAR(20)
);

Then we'll insert a few values:

INSERT INTO topping (name) VALUES
    ('pepperoni'),
    ('cheese'),
    ('sardines');

Now we can see the data:

SELECT * FROM topping;
 id |   name    
----+-----------
  1 | pepperoni
  2 | cheese
  3 | sardines
(3 rows)

We can also conditionally select the data, using CASE

SELECT * FROM topping
    WHERE TRUE
    AND CASE
        WHEN topping.name LIKE '%roni%' THEN TRUE
        ELSE FALSE
    END;

This yields a single result:

 id |   name    
----+-----------
  1 | pepperoni
(1 row)

But, you can see the point hopefully. This one checks for the case of the topping name matching something. But, you can also pass some sort of value into the query, and check that value. So this is quite flexible.

What if we really wanted to mix things up? Well, the CASE statement can be used in the SELECT as well, not just the WHERE:

SELECT
    name,
    CASE
        WHEN name LIKE '%cheese%' THEN 'Dairy'
        WHEN name LIKE '%roni%' THEN 'Meat'
        ELSE 'Junk'
    END AS type
FROM topping;

This yields:

   name    | type  
-----------+-------
 pepperoni | Meat
 cheese    | Dairy
 sardines  | Junk
(3 rows)

You can even use CAN within the ORDER BY:

SELECT * FROM topping ORDER BY CASE WHEN some_input = 'Name' THEN name END;

So the great thing about SQL is that you can use your imagine to the fullest extent!

Share