7. CASE and changing types

Two everyday tools: CASE for if/then logic inside a query, and casting for converting one type to another.

CASE: if / then / else

CASE lets a column's value depend on a condition — like a spreadsheet IF. Here we label films by length:

SELECT title,
       length,
       CASE
         WHEN length < 60  THEN 'short'
         WHEN length < 120 THEN 'medium'
         ELSE 'long'
       END AS size
FROM film;

It checks each WHEN top to bottom and stops at the first match. If none match and there's no ELSE, the result is NULL.

Casting: converting types

Sometimes you need a value as a different type — a number as text, text as a date. Cast with CAST(value AS type), or the shorthand ::type:

SELECT title,
       rental_rate,
       CAST(rental_rate AS INTEGER) AS rounded,
       'Film #' || film_id::TEXT AS label
FROM film;

|| joins text together, so film_id is cast to text first. Casting a decimal to INTEGER truncates it toward zero (it doesn't round).

Try it

Add a column called tier that is 'premium' when rental_rate is at least 4.00, and 'standard' otherwise. Return title, rental_rate, and tier, sorted by title.

SELECT title, rental_rate
FROM film
-- add the tier column
ORDER BY title;

SELECT title,
       rental_rate,
       CASE WHEN rental_rate >= 4.00 THEN 'premium' ELSE 'standard' END AS tier
FROM film
ORDER BY title;