Mathematical Functions

PostgreSQL provides a comprehensive set of mathematical functions that make working with numerical data efficient and powerful. Whether you're performing basic arithmetic, complex calculations, or data analysis, these functions are essential tools for any database professional working with quantitative data.

Sample Data Setup

Let's work with elevation data to demonstrate PostgreSQL's mathematical capabilities in a real-world context:

CREATE TABLE elevation_data (
  id SERIAL PRIMARY KEY,
  location TEXT,
  altitude NUMERIC,
  slope_angle NUMERIC
);

INSERT INTO elevation_data (location, altitude, slope_angle) VALUES
('Everest Base Camp', 5364.75, 27.5),
('Dead Sea', -430.0, 2.3),
('Denver', 1609.3, 5.0);

Basic Arithmetic Operations

PostgreSQL supports standard arithmetic operations directly in queries:

SELECT
  location,
  altitude,
  altitude + 100 AS adjusted_altitude,
  altitude * 0.3048 AS altitude_in_meters
FROM elevation_data;

Rounding and Precision Functions

Control numerical precision with these essential functions:

SELECT
  location,
  ABS(altitude) AS positive_altitude,
  CEIL(altitude) AS ceiling,
  FLOOR(altitude) AS floor,
  ROUND(altitude, 1) AS rounded_1_decimal
FROM elevation_data;

Power and Root Functions

Perform exponential and root calculations:

SELECT
  location,
  POWER(altitude, 2) AS altitude_squared,
  SQRT(ABS(altitude)) AS sqrt_altitude,
  MOD(CAST(altitude AS INT), 500) AS remainder_div_500
FROM elevation_data;

Trigonometric Functions

Essential for spatial analysis and geometric calculations:

SELECT
  location,
  slope_angle,
  SIN(RADIANS(slope_angle)) AS sin_slope,
  COS(RADIANS(slope_angle)) AS cos_slope,
  TAN(RADIANS(slope_angle)) AS tan_slope
FROM elevation_data;

Logarithmic and Exponential Functions

For advanced mathematical operations and data transformations:

SELECT
  location,
  LN(altitude + 1000) AS natural_log,
  LOG(10, altitude + 1000) AS log_base_10,
  EXP(1) AS exponential_e,
  PI() AS pi_constant
FROM elevation_data;

Aggregate Functions for Data Analysis

Let's explore aggregate functions with order data:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer TEXT,
  amount NUMERIC,
  order_date DATE
);

INSERT INTO orders (customer, amount, order_date) VALUES
('Alice', 100.00, '2023-01-01'),
('Bob', 150.00, '2023-01-02'),
('Alice', 200.00, '2023-01-03'),
('Charlie', NULL, '2023-01-04'),
('Bob', 300.00, '2023-01-05');

Basic Aggregate Functions

SELECT 
  COUNT(*) AS total_orders,
  COUNT(amount) AS orders_with_amount,
  SUM(amount) AS total_revenue,
  AVG(amount) AS average_order,
  MIN(amount) AS smallest_order,
  MAX(amount) AS largest_order
FROM orders;

Grouped Aggregations

SELECT 
  customer, 
  COUNT(*) AS order_count,
  SUM(amount) AS total_spent,
  AVG(amount) AS avg_order_value
FROM orders 
GROUP BY customer;

Advanced Aggregate Techniques

DISTINCT in Aggregates

SELECT COUNT(DISTINCT customer) AS unique_customers FROM orders;

Date-Based Aggregation

SELECT 
  DATE_TRUNC('month', order_date) AS month, 
  SUM(amount) AS monthly_revenue
FROM orders
GROUP BY month
ORDER BY month;

Nested Aggregation

SELECT MAX(daily_total) AS highest_daily_revenue
FROM (
  SELECT DATE(order_date), SUM(amount) AS daily_total
  FROM orders
  GROUP BY DATE(order_date)
) daily_totals;

Best Practices

1. Handle NULL values: Use COALESCE() or NULLIF() when necessary
2. Choose appropriate data types: Use NUMERIC for precise calculations
3. Consider performance: Index columns used in mathematical operations
4. Use meaningful aliases: Make calculated columns readable
5. Validate results: Check for division by zero and overflow conditions