Working with Conditional Expressions

SELECT id,
       CASE WHEN total_cents >= 10000 THEN 'large'
            WHEN total_cents >= 1000  THEN 'medium'
            ELSE 'small' END AS bucket
  FROM orders;

SELECT id,
       CASE status WHEN 'new' THEN 0
                   WHEN 'paid' THEN 1
                   WHEN 'shipped' THEN 2
                   ELSE -1 END AS status_code
  FROM orders;

SELECT count(*) FILTER (WHERE status='paid') AS paid_count,
       sum(CASE WHEN status='paid' THEN total_cents ELSE 0 END) AS paid_revenue
  FROM orders;

SELECT * FROM orders
WHERE CASE WHEN :role = 'admin' THEN true
           ELSE customer_id = :uid END;

SELECT * FROM orders
ORDER BY CASE status WHEN 'new' THEN 1 WHEN 'paid' THEN 2 ELSE 3 END,
         placed_at DESC;

SELECT id, COALESCE(nickname, full_name, 'Anonymous') AS display_name FROM users;

SELECT a / NULLIF(b, 0) FROM ratios;     -- avoids divide-by-zero

SELECT GREATEST(a, b, c) FROM nums;
-- NULL inputs are ignored

SELECT LEAST(price, list_price, sale_price) AS min_price FROM products;

SELECT * FROM flags WHERE active IS TRUE;        -- excludes NULL
SELECT * FROM flags WHERE active IS NOT TRUE;    -- false or NULL
SELECT * FROM flags WHERE complete IS UNKNOWN;   -- NULL only