Published at Feb 18, 2025
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.
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.
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 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;
These functions help handle NULL values in your queries effectively.
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 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;
These functions return the largest or smallest value from a list of expressions.
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;
-- 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;
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;
When using conditional functions, keep these performance tips in mind:
CASE expressions are evaluated in order, so put the most likely conditions first.
Complex CASE expressions can impact query performance, especially in WHERE clauses. Consider indexing commonly used columns.
COALESCE stops evaluating after finding the first non-null value, so order arguments by likelihood of being non-null.
For GREATEST and LEAST with many arguments, consider using array_agg with min/max for better performance.
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.