--- title: "Understanding PostgreSQL Conditional Functions" description: "Master PostgreSQL's conditional functions with CASE expressions, COALESCE, NULLIF, GREATEST, and LEAST to implement complex business logic in your queries." section: "Postgres basics" --- > **TimescaleDB is now Tiger Data.** PostgreSQL's conditional functions provide powerful tools for making decisions within your queries. You can use these functions in PostgreSQL or TimescaleDB to implement complex business logic directly in your SQL statements, making your queries more flexible and maintainable. In this article, we'll explore the essential conditional functions and show you how to use them effectively. ## CASE Expressions The CASE expression is PostgreSQL's primary conditional construct, similar to if-else statements in other programming languages. There are two forms: simple CASE and searched CASE. ### Simple CASE expression `CREATE TABLE orders ( id SERIAL PRIMARY KEY, status TEXT, amount DECIMAL(10,2), created_at TIMESTAMP );` `INSERT INTO orders (status, amount, created_at) VALUES ('pending', 100.00, '2025-01-01'), ('completed', 250.00, '2025-01-02'), ('cancelled', 75.00, '2025-01-03'), ('processing', 180.00, '2025-01-04');` `-- Simple CASE example SELECT id, amount, CASE status WHEN 'pending' THEN 'Awaiting Processing' WHEN 'processing' THEN 'In Progress' WHEN 'completed' THEN 'Finished' WHEN 'cancelled' THEN 'Terminated' ELSE 'Unknown Status' END AS status_description FROM orders; ` `` ### Searched CASE expression `-- Searched CASE example for order categorization SELECT id, amount, CASE WHEN amount >= 200 THEN 'High Value' WHEN amount >= 100 THEN 'Medium Value' ELSE 'Low Value' END AS order_category, CASE WHEN created_at >= CURRENT_DATE - INTERVAL '7 days' THEN 'Recent' WHEN created_at >= CURRENT_DATE - INTERVAL '30 days' THEN 'This Month' ELSE 'Older' END AS order_age FROM orders; ` ## COALESCE and NULLIF Functions These functions help handle `NULL` values in your queries effectively. ### COALESCE `COALESCE` returns the first non-null value in a list. It's particularly useful for providing default values: `CREATE TABLE customers ( id SERIAL PRIMARY KEY, first_name TEXT, last_name TEXT, preferred_name TEXT, phone TEXT, backup_phone TEXT );` `INSERT INTO customers (first_name, last_name, preferred_name, phone, backup_phone) VALUES ('John', 'Doe', NULL, '555-0123', NULL), ('Jane', 'Smith', 'Janey', NULL, '555-4567'), ('Bob', 'Johnson', NULL, NULL, NULL);` `-- Using COALESCE for names and contact information SELECT id, COALESCE(preferred_name, first_name) AS display_name, COALESCE(phone, backup_phone, 'No contact available') AS primary_contact FROM customers; ` ### NULLIF `NULLIF` returns `NULL` if two expressions are equal, and otherwise returns the first expression. This is useful for avoiding division by zero or handling special values: `CREATE TABLE sales_data ( id SERIAL PRIMARY KEY, total_value DECIMAL(10,2), items_sold INTEGER );` `INSERT INTO sales_data (total_value, items_sold) VALUES (1000.00, 10), (500.00, 5), (0.00, 0), (750.00, 3);` `-- Using NULLIF to avoid division by zero SELECT id, total_value, items_sold, total_value / NULLIF(items_sold, 0) AS average_item_value FROM sales_data; ` ## GREATEST and LEAST Functions These functions return the largest or smallest value from a list of expressions. ### GREATEST `CREATE TABLE product_prices ( id SERIAL PRIMARY KEY, product_name TEXT, supplier1_price DECIMAL(10,2), supplier2_price DECIMAL(10,2), supplier3_price DECIMAL(10,2) );` `INSERT INTO product_prices (product_name, supplier1_price, supplier2_price, supplier3_price) VALUES ('Widget A', 10.00, 12.00, 9.50), ('Widget B', 15.00, 14.00, NULL), ('Widget C', 20.00, NULL, NULL);` `-- Find the highest price among suppliers SELECT product_name, GREATEST( COALESCE(supplier1_price, 0), COALESCE(supplier2_price, 0), COALESCE(supplier3_price, 0) ) AS highest_price FROM product_prices; ` ### LEAST `-- Find the lowest price among suppliers SELECT product_name, LEAST( COALESCE(supplier1_price, 999999), COALESCE(supplier2_price, 999999), COALESCE(supplier3_price, 999999) ) AS lowest_price FROM product_prices WHERE supplier1_price IS NOT NULL OR supplier2_price IS NOT NULL OR supplier3_price IS NOT NULL; ` `` ## Combining Conditional Functions These functions can be combined to create more complex logic: `-- Complex example combining multiple conditional functions CREATE TABLE inventory ( id SERIAL PRIMARY KEY, product_name TEXT, current_stock INTEGER, minimum_stock INTEGER, maximum_stock INTEGER, last_count_date DATE );` `INSERT INTO inventory VALUES (1, 'Item A', 50, 20, 100, '2025-01-15'), (2, 'Item B', 10, 15, 75, '2025-01-10'), (3, 'Item C', 90, 25, 80, NULL);` `SELECT product_name, CASE WHEN current_stock <= LEAST(minimum_stock, 10) THEN 'Critical Reorder' WHEN current_stock <= minimum_stock THEN 'Reorder' WHEN current_stock >= maximum_stock THEN 'Overstocked' ELSE 'Normal' END AS stock_status, COALESCE( CASE WHEN last_count_date < CURRENT_DATE - INTERVAL '30 days' THEN 'Count Required' ELSE 'Count Up to Date' END, 'Never Counted' ) AS inventory_count_status FROM inventory; ` `` ## Performance Considerations When using conditional functions, keep these performance tips in mind: 1. `CASE` expressions are evaluated in order, so put the most likely conditions first. 2. Complex `CASE` expressions can impact query performance, especially in [WHERE clauses](https://www.timescale.com/learn/understanding-where-in-postgresql-with-examples). Consider indexing commonly used columns. 3. `COALESCE` stops evaluating after finding the first non-null value, so order arguments by likelihood of being non-null. 4. For `GREATEST` and `LEAST` with many arguments, consider using [array_agg with min/max for better performance](https://www.timescale.com/learn/understanding-postgresql-array-functions). ## Conclusion PostgreSQL's conditional functions provide a robust toolkit for implementing business logic directly in your queries. They will help you do the following: - Handle NULL values gracefully - Implement complex business rules - Transform data based on multiple conditions - Provide sensible defaults and fallback values Remember to consider performance implications when working with these functions, especially in larger datasets or complex queries. For more advanced usage and detailed information about PostgreSQL conditional functions, refer to the official [PostgreSQL documentation](https://www.postgresql.org/docs/current/functions-conditional.html).