PostgreSQL - Numeric Functions

PostgreSQL - Modern Numeric Functions

PostgreSQL provides a rich library of mathematical and numeric functions essential for data analysis, calculations, and manipulation. This tutorial organizes these functions into logical groups and demonstrates their use with practical, modern examples.

Let’s use a sample products table for our examples:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC(10, 2),
    stock_change INT,
    rating NUMERIC(3, 2)
);

INSERT INTO products (name, price, stock_change, rating) VALUES
('Laptop', 1200.55, -10, 4.58),
('Mouse', 25.00, 50, 4.20),
('Keyboard', 75.99, 25, 4.66),
('Webcam', 49.49, -5, 3.81);

Rounding and Truncation Functions

These functions are used to adjust the precision of numeric values.

• ROUND(n, d): Rounds a number n to d decimal places. If d is omitted, rounds to the nearest integer.
• CEIL(n) or CEILING(n): Rounds a number n UP to the nearest integer.
• FLOOR(n): Rounds a number n DOWN to the nearest integer.

SELECT
    name,
    price,
    ROUND(price) AS rounded_to_integer,
    ROUND(price, 1) AS rounded_to_one_decimal,
    CEIL(price) AS ceiling_price,
    FLOOR(price) AS floor_price
FROM products WHERE name = 'Laptop';

Result:

  name  |  price  | rounded_to_integer | rounded_to_one_decimal | ceiling_price | floor_price
--------+---------+--------------------+------------------------+---------------+-------------
 Laptop | 1200.55 |               1201 |                 1200.6 |          1201 |        1200

Core Arithmetic Functions

• ABS(n): Returns the absolute (non-negative) value of n.
• MOD(a, b): Returns the remainder of a divided by b (modulo operator).
• SQRT(n): Returns the square root of n.
• POW(base, exp) or POWER(base, exp): Returns base raised to the power of exp.

-- Get the magnitude of stock changes
SELECT name, stock_change, ABS(stock_change) AS absolute_change FROM products;

-- Example of POWER
SELECT POWER(2, 8); -- Result: 256

-- Example of SQRT
SELECT SQRT(64); -- Result: 8

Value Comparison Functions

These functions are useful for finding the extreme values from a list of arguments.

• GREATEST(val1, val2, ...): Returns the largest value from the input list.
• LEAST(val1, val2, ...): Returns the smallest value from the input list.

-- Imagine a scenario with a base price and a sale price.
SELECT
    name,
    price AS original_price,
    LEAST(price * 0.8, 1000.00) AS sale_price -- Sale is 20% off, capped at $1000
FROM products WHERE name = 'Laptop';

Result:

  name  | original_price | sale_price
--------+----------------+------------
 Laptop |        1200.55 |     960.44

Trigonometric and Logarithmic Functions

PostgreSQL provides a full suite of functions for specialized scientific and engineering calculations. Note that trigonometric functions operate on radians.

Trigonometric Functions

• SIN(x), COS(x), TAN(x)
• ASIN(x), ACOS(x), ATAN(x), ATAN2(y, x)
• COT(x)
• RADIANS(degrees): Converts degrees to radians.
• DEGREES(radians): Converts radians to degrees.

-- Calculate the sine of 90 degrees
SELECT SIN(RADIANS(90)); -- Result: 1

Logarithmic Functions

• LOG(n): Natural logarithm (base e).
• LOG(b, n): Logarithm of n with base b.
• EXP(n): Exponential function (e^n).

SELECT LOG(10, 100); -- Result: 2

Other Useful Functions

• PI(): Returns the constant value of Pi (π).
• SIGN(n): Returns -1 if n is negative, 0 if n is zero, and 1 if n is positive.

SELECT name, stock_change, SIGN(stock_change) AS direction FROM products;

-- Result:
--    name    | stock_change | direction
-- -----------+--------------+-----------
--  Laptop    |          -10 |        -1
--  Mouse     |           50 |         1
--  Keyboard  |           25 |         1
--  Webcam    |           -5 |        -1