PostgreSQL Expressions

Overview

Expressions are combinations of values, operators, and functions that PostgreSQL evaluates to compute a result. Expressions can be used in SELECT, WHERE, HAVING, and other clauses.

Expression Types

1. Constant Expressions

-- Numeric constants
SELECT 42;
SELECT 3.14159;
SELECT 1.5e10;

-- String constants
SELECT 'Hello, World!';
SELECT 'It''s a nice day';  -- Escape single quote

-- Boolean constants
SELECT TRUE;
SELECT FALSE;
SELECT NULL;

-- Date/Time constants
SELECT DATE '2024-01-15';
SELECT TIME '14:30:00';
SELECT TIMESTAMP '2024-01-15 14:30:00';
SELECT INTERVAL '1 day';

2. Column Reference Expressions

-- Simple column reference
SELECT name FROM users;
SELECT price FROM products;

-- Column reference with table name
SELECT users.name FROM users;
SELECT products.price FROM products;

-- Column reference with table alias
SELECT u.name, o.order_number
FROM users u
JOIN orders o ON u.id = o.user_id;

3. Arithmetic Expressions

-- Basic arithmetic
SELECT 10 + 5 AS addition;
SELECT 10 - 5 AS subtraction;
SELECT 10 * 5 AS multiplication;
SELECT 10 / 5 AS division;
SELECT 10 % 3 AS modulo;

-- Complex arithmetic
SELECT price * quantity AS subtotal FROM order_items;
SELECT (price * quantity) * (1 + tax_rate) AS total FROM orders;
SELECT ROUND(price * 0.9, 2) AS discounted_price FROM products;

-- Use parentheses to control precedence
SELECT (10 + 5) * 2 AS result;  -- 30
SELECT 10 + (5 * 2) AS result;  -- 20

4. String Expressions

-- String concatenation
SELECT 'Hello' || ' ' || 'World' AS greeting;
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- Using CONCAT function
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT CONCAT('Order #', order_id, ': ', status) AS order_info FROM orders;

-- String functions
SELECT UPPER(name) AS uppercase_name FROM users;
SELECT LOWER(email) AS lowercase_email FROM users;
SELECT LENGTH(name) AS name_length FROM users;
SELECT SUBSTRING(name, 1, 5) AS short_name FROM users;
SELECT TRIM('  Hello  ') AS trimmed;
SELECT REPLACE(description, 'old', 'new') AS replaced FROM products;

5. Comparison Expressions

-- Simple comparisons
SELECT * FROM products WHERE price > 100;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM orders WHERE status = 'completed';

-- Comparison results as columns
SELECT 
    name,
    price,
    price > 100 AS is_expensive,
    stock = 0 AS out_of_stock
FROM products;

-- Complex comparisons
SELECT * FROM products 
WHERE price BETWEEN 50 AND 200
    AND category IN ('Electronics', 'Computers');

6. Logical Expressions

-- AND expression
SELECT * FROM users 
WHERE status = 'active' AND country = 'USA';

-- OR expression
SELECT * FROM products 
WHERE category = 'Electronics' OR category = 'Books';

-- NOT expression
SELECT * FROM users WHERE NOT status = 'banned';

-- Combined logical expressions
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Computers')
    AND price < 500
    AND NOT discontinued = true;

Conditional Expressions

CASE Expressions

Simple CASE Expression

-- Basic syntax
SELECT 
    name,
    status,
    CASE status
        WHEN 'active' THEN 'Active User'
        WHEN 'pending' THEN 'Pending Approval'
        WHEN 'suspended' THEN 'Suspended'
        ELSE 'Unknown'
    END AS status_description
FROM users;

-- Practical application
SELECT 
    product_name,
    category,
    CASE category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Books' THEN 'Literature'
        WHEN 'Clothing' THEN 'Fashion'
        ELSE 'Other'
    END AS category_group
FROM products;

Searched CASE Expression

-- Basic syntax
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age >= 18 AND age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

-- Price tiers
SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price >= 50 AND price < 200 THEN 'Mid-range'
        WHEN price >= 200 AND price < 1000 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_tier
FROM products;

-- Order priority
SELECT 
    order_id,
    total_amount,
    customer_type,
    CASE 
        WHEN customer_type = 'VIP' THEN 1
        WHEN total_amount > 1000 THEN 2
        WHEN total_amount > 500 THEN 3
        ELSE 4
    END AS priority
FROM orders;

Nested CASE Expressions

SELECT 
    product_name,
    category,
    price,
    stock,
    CASE 
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < 10 THEN 
            CASE 
                WHEN price > 500 THEN 'Low Stock - High Value'
                ELSE 'Low Stock - Regular'
            END
        ELSE 'In Stock'
    END AS stock_status
FROM products;

COALESCE Expression

-- Return first non-NULL value
SELECT COALESCE(phone, 'No phone') AS contact_phone FROM users;

-- Multiple fallback values
SELECT COALESCE(mobile, home_phone, work_phone, 'No contact') AS phone FROM users;

-- Practical application
SELECT 
    name,
    COALESCE(email, 'no-email@example.com') AS email,
    COALESCE(phone, 'N/A') AS phone,
    COALESCE(address, 'No address provided') AS address
FROM users;

-- Use in calculations
SELECT 
    product_name,
    price,
    COALESCE(discount_price, price) AS final_price,
    price - COALESCE(discount_price, price) AS savings
FROM products;

NULLIF Expression

-- Return NULL if two values are equal
SELECT NULLIF(price, 0) FROM products;
SELECT NULLIF(email, '') FROM users;

-- Avoid division by zero
SELECT 
    total_sales,
    total_orders,
    total_sales / NULLIF(total_orders, 0) AS average_order_value
FROM sales_summary;

-- Practical application
SELECT 
    product_name,
    old_price,
    new_price,
    NULLIF(old_price, new_price) AS price_changed
FROM products;

GREATEST and LEAST Expressions

-- GREATEST returns maximum value
SELECT GREATEST(10, 20, 15) AS max_value;  -- 20
SELECT GREATEST(price, cost, 0) AS highest FROM products;

-- LEAST returns minimum value
SELECT LEAST(10, 20, 15) AS min_value;  -- 10
SELECT LEAST(stock, reorder_level) AS min_stock FROM products;

-- Practical application
SELECT 
    product_name,
    price,
    competitor_price,
    LEAST(price, competitor_price) AS best_price,
    GREATEST(price, competitor_price) - LEAST(price, competitor_price) AS price_difference
FROM products;

Aggregate Expressions

-- Basic aggregates
SELECT COUNT(*) AS total_users FROM users;
SELECT SUM(amount) AS total_sales FROM orders;
SELECT AVG(price) AS average_price FROM products;
SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products;

-- Conditional aggregates
SELECT 
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    SUM(total_amount) AS total_revenue,
    SUM(total_amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

-- Grouped aggregates
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category;

-- String aggregation
SELECT 
    category,
    STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM products
GROUP BY category;

Type Cast Expressions

-- Using :: operator
SELECT '100'::INTEGER AS int_value;
SELECT '2024-01-15'::DATE AS date_value;
SELECT NOW()::DATE AS today;
SELECT 10::NUMERIC(10, 2) AS decimal_value;

-- Using CAST function
SELECT CAST('100' AS INTEGER) AS int_value;
SELECT CAST('2024-01-15' AS DATE) AS date_value;
SELECT CAST(price AS TEXT) AS price_text FROM products;

-- Practical application
SELECT 
    order_id,
    order_date::DATE AS date_only,
    total_amount::INTEGER AS rounded_amount,
    CAST(customer_id AS TEXT) AS customer_id_text
FROM orders;

Array Expressions

-- Array construction
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;
SELECT ARRAY['apple', 'banana', 'orange'] AS fruits;

-- Array operations
SELECT ARRAY[1,2,3] || ARRAY[4,5,6] AS combined;  -- {1,2,3,4,5,6}
SELECT ARRAY[1,2,3] || 4 AS appended;  -- {1,2,3,4}

-- Array access
SELECT tags[1] AS first_tag FROM products;
SELECT tags[1:3] AS first_three_tags FROM products;

-- Array functions
SELECT array_length(tags, 1) AS tag_count FROM products;
SELECT unnest(ARRAY[1,2,3]) AS value;

Subquery Expressions

Scalar Subqueries

-- Subquery returning single value
SELECT 
    name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,
    price - (SELECT AVG(price) FROM products) AS price_diff
FROM products;

-- Correlated subquery
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

EXISTS Subqueries

-- Check if exists
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

-- NOT EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi 
    WHERE oi.product_id = p.id
);

IN Subqueries

-- Value in subquery results
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE active = true
);

-- NOT IN
SELECT * FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

ANY/ALL Subqueries

-- ANY: satisfies any one
SELECT * FROM products
WHERE price > ANY (
    SELECT price FROM products WHERE category = 'Books'
);

-- ALL: satisfies all
SELECT * FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE category = 'Books'
);

Window Expressions

-- ROW_NUMBER
SELECT 
    name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- RANK and DENSE_RANK
SELECT 
    name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

-- Aggregate window functions
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total,
    AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

Practical Examples

Example 1: E-commerce Product Display

SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 50 THEN '💰 Great Value'
        WHEN price < 200 THEN '💵 Good Deal'
        ELSE '💎 Premium'
    END AS price_badge,
    COALESCE(discount_price, price) AS final_price,
    ROUND((price - COALESCE(discount_price, price)) / NULLIF(price, 0) * 100, 0) AS discount_percent,
    stock > 0 AS in_stock,
    CASE 
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < 10 THEN 'Only ' || stock || ' left'
        ELSE 'In Stock'
    END AS stock_message
FROM products
WHERE NOT discontinued = true;

Example 2: User Statistics Report

SELECT 
    first_name || ' ' || last_name AS full_name,
    email,
    EXTRACT(YEAR FROM AGE(birth_date)) AS age,
    CASE 
        WHEN EXTRACT(YEAR FROM AGE(birth_date)) < 18 THEN 'Minor'
        WHEN EXTRACT(YEAR FROM AGE(birth_date)) BETWEEN 18 AND 35 THEN 'Young Adult'
        WHEN EXTRACT(YEAR FROM AGE(birth_date)) BETWEEN 36 AND 60 THEN 'Middle Age'
        ELSE 'Senior'
    END AS age_group,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS total_orders,
    COALESCE((SELECT SUM(total_amount) FROM orders WHERE user_id = users.id), 0) AS lifetime_value,
    CASE 
        WHEN (SELECT SUM(total_amount) FROM orders WHERE user_id = users.id) > 10000 THEN 'VIP'
        WHEN (SELECT SUM(total_amount) FROM orders WHERE user_id = users.id) > 5000 THEN 'Gold'
        WHEN (SELECT SUM(total_amount) FROM orders WHERE user_id = users.id) > 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_tier
FROM users
WHERE status = 'active';

Example 3: Order Analysis

SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    total_amount * 0.08 AS tax,
    total_amount * 1.08 AS total_with_tax,
    CASE 
        WHEN total_amount > 1000 THEN total_amount * 0.1
        WHEN total_amount > 500 THEN total_amount * 0.05
        ELSE 0
    END AS discount,
    total_amount * 1.08 - CASE 
        WHEN total_amount > 1000 THEN total_amount * 0.1
        WHEN total_amount > 500 THEN total_amount * 0.05
        ELSE 0
    END AS final_total,
    EXTRACT(DAY FROM CURRENT_DATE - order_date) AS days_since_order,
    CASE 
        WHEN EXTRACT(DAY FROM CURRENT_DATE - order_date) > 30 THEN 'Over 30 days'
        WHEN EXTRACT(DAY FROM CURRENT_DATE - order_date) > 7 THEN '7-30 days'
        ELSE 'Last 7 days'
    END AS order_age
FROM orders
WHERE status IN ('completed', 'shipped');

Best Practices

1. Use parentheses for clarity: Use parentheses in complex expressions to clarify precedence
2. Avoid excessive nesting: Deeply nested expressions are hard to maintain
3. Use meaningful aliases: Provide clear aliases for complex expressions
4. Handle NULL properly: Use COALESCE or NULLIF to handle NULL values
5. Consider performance: Complex expressions may impact query performance
6. Match types: Ensure data types are compatible in expressions

Summary

PostgreSQL expressions are fundamental to building powerful queries:

• Constants and column references: Basic building blocks
• Arithmetic and string expressions: Data calculation and transformation
• Conditional expressions: CASE, COALESCE, NULLIF
• Aggregate expressions: Data summarization
• Subquery expressions: Complex data retrieval
• Window expressions: Advanced analytics

Mastering expressions enables you to write more flexible and powerful SQL queries.