Kenhuang Academy
  • English

      • PostgreSQL Operators

      Overview

      Operators are symbols or keywords used to perform operations. PostgreSQL supports various types of operators including arithmetic, comparison, logical, string, and other special operators.

      Arithmetic Operators

      Basic Arithmetic Operations

      -- Addition
SELECT 10 + 5 AS sum;  -- 15

-- Subtraction
SELECT 10 - 5 AS difference;  -- 5

-- Multiplication
SELECT 10 * 5 AS product;  -- 50

-- Division
SELECT 10 / 5 AS quotient;  -- 2
SELECT 10.0 / 3.0 AS decimal_division;  -- 3.333...

-- Modulo (remainder)
SELECT 10 % 3 AS remainder;  -- 1

-- Exponentiation
SELECT 2 ^ 3 AS power;  -- 8

-- Square root
SELECT |/ 25 AS square_root;  -- 5

-- Cube root
SELECT ||/ 27 AS cube_root;  -- 3

-- Factorial
SELECT 5! AS factorial;  -- 120

      Practical Examples

      -- Calculate order totals
SELECT 
    order_id,
    price * quantity AS subtotal,
    (price * quantity) * 0.08 AS tax,
    (price * quantity) * 1.08 AS total
FROM order_items;

-- Calculate discounted prices
SELECT 
    product_name,
    price AS original_price,
    price * 0.8 AS discounted_price,
    price - (price * 0.8) AS savings
FROM products;

-- Calculate percentages
SELECT 
    category,
    COUNT(*) AS count,
    COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products) AS percentage
FROM products
GROUP BY category;

      Comparison Operators

      Basic Comparisons

      -- Equal
SELECT * FROM products WHERE price = 100;

-- Not equal
SELECT * FROM products WHERE price <> 50;
SELECT * FROM products WHERE price != 50;  -- Same as above

-- Greater than
SELECT * FROM products WHERE price > 50;

-- Greater than or equal
SELECT * FROM products WHERE price >= 50;

-- Less than
SELECT * FROM products WHERE price < 100;

-- Less than or equal
SELECT * FROM products WHERE price <= 100;

      Comparison Examples

      -- Find products in specific price range
SELECT * FROM products 
WHERE price >= 50 AND price <= 100;

-- Find orders not in specific status
SELECT * FROM orders 
WHERE status <> 'cancelled';

-- Find highly rated products
SELECT * FROM products 
WHERE rating >= 4.5;

      Logical Operators

      AND Operator

      -- All conditions must be true
SELECT * FROM users 
WHERE status = 'active' 
    AND country = 'USA' 
    AND age >= 18;

-- Multiple AND conditions
SELECT * FROM products 
WHERE category = 'Electronics' 
    AND price > 100 
    AND stock > 0 
    AND rating >= 4.0;

      OR Operator

      -- At least one condition must be true
SELECT * FROM products 
WHERE category = 'Electronics' 
    OR category = 'Books';

-- Multiple OR conditions
SELECT * FROM users 
WHERE country = 'USA' 
    OR country = 'UK' 
    OR country = 'Canada';

      NOT Operator

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

-- Equivalent to
SELECT * FROM users 
WHERE status <> 'banned';

-- NOT with other operators
SELECT * FROM products 
WHERE NOT (price > 1000 OR discontinued = true);

      Combining Logical Operators

      -- Use parentheses to control precedence
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Computers') 
    AND price < 500 
    AND stock > 0;

-- Complex logic
SELECT * FROM orders 
WHERE (status = 'pending' OR status = 'processing') 
    AND (priority = 'high' OR total_amount > 1000) 
    AND NOT payment_method = 'cash';

      String Operators

      Concatenation Operator

      -- Use || to concatenate strings
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- Concatenate multiple strings
SELECT 'Order #' || order_id || ' - ' || status AS order_info FROM orders;

-- Use CONCAT function
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT CONCAT('Total: $', price) AS price_label FROM products;

      Pattern Matching Operators

      LIKE Operator

      -- % matches any characters
SELECT * FROM users WHERE name LIKE 'J%';  -- Starts with J
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- Ends with @gmail.com
SELECT * FROM users WHERE name LIKE '%john%';  -- Contains john

-- _ matches single character
SELECT * FROM users WHERE name LIKE '_ohn';  -- Matches John, Kohn, etc.

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@spam.com';

      ILIKE Operator (Case-Insensitive)

      -- Case-insensitive matching
SELECT * FROM users WHERE name ILIKE 'john%';  -- Matches John, JOHN, john

-- Case-insensitive search
SELECT * FROM products WHERE name ILIKE '%laptop%';

      SIMILAR TO Operator

      -- SQL standard regex
SELECT * FROM users WHERE name SIMILAR TO '(John|Jane)%';
SELECT * FROM products WHERE sku SIMILAR TO '[A-Z]{3}[0-9]{4}';

      Regular Expression Operators

      -- ~ case-sensitive regex match
SELECT * FROM users WHERE name ~ '^J';
SELECT * FROM users WHERE email ~ '[0-9]+';

-- ~* case-insensitive regex match
SELECT * FROM users WHERE name ~* '^john';

-- !~ does not match (case-sensitive)
SELECT * FROM users WHERE email !~ '@spam\.com$';

-- !~* does not match (case-insensitive)
SELECT * FROM users WHERE name !~* '^test';

      Range Operators

      BETWEEN Operator

      -- Numeric range
SELECT * FROM products WHERE price BETWEEN 10 AND 100;

-- Equivalent to
SELECT * FROM products WHERE price >= 10 AND price <= 100;

-- Date range
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 100;

      IN Operator

      -- List of values
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

-- Numeric list
SELECT * FROM products WHERE category_id IN (1, 2, 3, 5, 8);

-- Subquery
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE active = true);

-- NOT IN
SELECT * FROM users WHERE country NOT IN ('Spam', 'Test');

      NULL Operators

      IS NULL and IS NOT NULL

      -- Check for NULL values
SELECT * FROM users WHERE phone IS NULL;

-- Check for non-NULL values
SELECT * FROM users WHERE phone IS NOT NULL;

-- Multiple NULL checks
SELECT * FROM users 
WHERE phone IS NULL 
    AND address IS NULL;

      IS DISTINCT FROM

      -- NULL-safe comparison
SELECT * FROM users WHERE email IS DISTINCT FROM NULL;

-- Equivalent to
SELECT * FROM users WHERE email IS NOT NULL;

-- Compare two potentially NULL values
SELECT * FROM products 
WHERE old_price IS DISTINCT FROM new_price;

      Array Operators

      -- Array contains
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'sale'];

-- Array is contained by
SELECT * FROM products WHERE ARRAY['electronics'] <@ tags;

-- Array overlap
SELECT * FROM products WHERE tags && ARRAY['new', 'featured'];

-- Array concatenation
SELECT ARRAY[1,2,3] || ARRAY[4,5,6];  -- {1,2,3,4,5,6}

-- Array element access
SELECT tags[1] FROM products;  -- First element

      JSON Operators

      -- Get JSON field
SELECT data->>'name' AS name FROM users;
SELECT data->'address'->>'city' AS city FROM users;

-- JSON path
SELECT data#>'{address,city}' AS city FROM users;

-- JSON contains
SELECT * FROM users WHERE data @> '{"status": "active"}';

-- JSON key exists
SELECT * FROM users WHERE data ? 'email';

      Bitwise Operators

      -- Bitwise AND
SELECT 5 & 3;  -- 1

-- Bitwise OR
SELECT 5 | 3;  -- 7

-- Bitwise XOR
SELECT 5 # 3;  -- 6

-- Bitwise NOT
SELECT ~5;  -- -6

-- Left shift
SELECT 5 << 2;  -- 20

-- Right shift
SELECT 20 >> 2;  -- 5

      Type Cast Operators

      -- :: operator
SELECT '100'::INTEGER;
SELECT '2024-01-15'::DATE;
SELECT NOW()::DATE;
SELECT 10::NUMERIC(10, 2);

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

      Subquery Operators

      EXISTS Operator

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

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

      ANY/SOME Operator

      -- Greater than any value
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Books');

-- Equal to any value
SELECT * FROM users
WHERE country = ANY (ARRAY['USA', 'UK', 'Canada']);

      ALL Operator

      -- Greater than all values
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

-- Not equal to all values
SELECT * FROM users
WHERE country <> ALL (ARRAY['Spam', 'Test']);

      Operator Precedence

      From highest to lowest:

      1. ::(type cast)
      2. [] (array element)
      3. . (table/column name separator)
      4. - (unary minus)
      5. ^ (exponentiation)
      6. *, /, % (multiply, divide, modulo)
      7. +, - (add, subtract)
      8. || (string concatenation)
      9. BETWEEN, IN, LIKE, ILIKE, SIMILAR TO
      10. <, >, =, <=, >=, <> (comparison)
      11. IS NULL, IS NOT NULL, IS DISTINCT FROM
      12. NOT (logical NOT)
      13. AND (logical AND)
      14. OR (logical OR)

      Use Parentheses to Control Precedence

      -- Without parentheses
SELECT * FROM products WHERE price > 50 OR category = 'Books' AND stock > 0;
-- Equivalent to: WHERE price > 50 OR (category = 'Books' AND stock > 0)

-- With parentheses to clarify intent
SELECT * FROM products WHERE (price > 50 OR category = 'Books') AND stock > 0;

      Practical Examples

      Example 1: E-commerce Product Filtering

      SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price BETWEEN 50 AND 200 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_range,
    stock > 0 AS in_stock
FROM products
WHERE (category IN ('Electronics', 'Computers') OR on_sale = true)
    AND price <= 1000
    AND rating >= 4.0
    AND NOT discontinued = true;

      Example 2: User Data Analysis

      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 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users
WHERE status = 'active'
    AND email LIKE '%@company.com'
    AND created_at >= CURRENT_DATE - INTERVAL '1 year';

      Example 3: Order Statistics

      SELECT 
    order_id,
    customer_id,
    total_amount,
    total_amount * 0.08 AS tax,
    total_amount * 1.08 AS total_with_tax,
    CASE 
        WHEN total_amount > 1000 THEN 'VIP'
        WHEN total_amount > 500 THEN 'Premium'
        ELSE 'Standard'
    END AS order_tier
FROM orders
WHERE status IN ('completed', 'shipped')
    AND order_date BETWEEN '2024-01-01' AND '2024-12-31'
    AND NOT (payment_method = 'cash' AND total_amount > 5000);

      Performance Optimization Tips

      -- 1. Create indexes on frequently used operator columns
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_users_status ON users(status);

-- 2. Avoid functions on indexed columns
-- Bad
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';

-- Good
SELECT * FROM users WHERE email = 'john@example.com';

-- 3. Use appropriate operators
-- Use IN instead of multiple OR
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

-- 4. Use EXPLAIN to analyze queries
EXPLAIN ANALYZE
SELECT * FROM products WHERE price > 100 AND category = 'Electronics';

      Best Practices

      1. Use parentheses for clarity: Even when not required, use parentheses to make logic clearer
      2. Choose the right operator: Use the most appropriate operator for the task
      3. Handle NULL properly: Use IS NULL instead of = NULL
      4. Consider performance: Create indexes on frequently used columns
      5. Match types: Ensure data types match in comparisons
      6. Use standard SQL: Prefer standard SQL operators for better portability

      Summary

      PostgreSQL operators are fundamental to building queries:

      • Arithmetic operators: +, -, *, /, %, ^
      • Comparison operators: =, <>, <, >, <=, >=
      • Logical operators: AND, OR, NOT
      • String operators: ||, LIKE, ILIKE, ~
      • Range operators: BETWEEN, IN
      • NULL operators: IS NULL, IS NOT NULL
      • Special operators: EXISTS, ANY, ALL

      Understanding and correctly using operators is crucial for writing efficient SQL queries.

      PostgreSQL Expressions

      Expression Overview

      Expressions are combinations of values and operators in PostgreSQL that compute results. Expressions can be used in SELECT, WHERE, HAVING, and other clauses.

      Conditional Expressions

      CASE Expression

      Simple CASE Expression

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

-- Practical application
SELECT 
    product_name,
    category,
    CASE category
        WHEN 'Electronics' THEN 'Electronics'
        WHEN 'Books' THEN 'Books'
        WHEN 'Clothing' THEN 'Apparel'
        ELSE 'Other'
    END AS category_label
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 Expression

      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;

      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;

      Expression Best Practices

      1. Use parentheses for readability: 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 values: Use COALESCE or NULLIF to handle NULL values
      5. Consider performance: Complex expressions may impact query performance
      6. Type matching: Ensure data types are compatible in expressions

      Expression Summary

      PostgreSQL expressions provide powerful data processing capabilities:

      • CASE expressions: Conditional logic
      • COALESCE: NULL value handling
      • NULLIF: Conditional NULL return
      • GREATEST/LEAST: Multi-value comparison
      • Window expressions: Advanced analytics

      Mastering expressions enables writing more flexible and powerful SQL queries.