Math Functions

PostgreSQL provides the usual set of mathematical functions and operators for rounding, powers, roots, remainders, and randomness — plus GENERATE_SERIES(), a distinctive and very useful function for producing sequences of numbers or dates on demand.

Rounding and basic arithmetic

Function	Purpose
`ROUND(n, d)`	Rounds `n` to `d` decimal places (default 0)
`CEIL(n)`	Rounds up to the nearest integer
`FLOOR(n)`	Rounds down to the nearest integer
`ABS(n)`	Absolute value
`POWER(a, b)`	`a` raised to the power `b`
`SQRT(n)`	Square root
`MOD(a, b)` / `a % b`	Remainder of `a` divided by `b`

SQL

SELECT
  ROUND(19.6789, 2)  AS rounded,     -- 19.68
  CEIL(4.1)          AS ceiling,     -- 5
  FLOOR(4.9)         AS floor_val,   -- 4
  ABS(-42)           AS absolute,    -- 42
  POWER(2, 10)       AS power_val,   -- 1024
  SQRT(144)          AS square_root, -- 12
  MOD(17, 5)         AS remainder,   -- 2
  17 % 5             AS remainder_op; -- 2

RANDOM()

RANDOM() returns a random double-precision value between 0 (inclusive) and 1 (exclusive).

SQL

SELECT RANDOM();  -- e.g. 0.7183465912...

Warning

PostgreSQL's RANDOM() is not cryptographically secure. Don't use it to generate tokens, passwords, or anything security-sensitive — use the pgcrypto extension's gen_random_bytes() (or gen_random_uuid() for UUIDs) instead.

Generating a random integer in a range

RANDOM() only gives a fraction between 0 and 1, so scaling and flooring it produces a random integer within any range you want:

A random integer between 1 and 100 (inclusive)

SQL

SELECT FLOOR(RANDOM() * 100 + 1)::int AS random_int;

RANDOM() * 100 scales the fraction to the range [0, 100), adding 1 shifts it to [1, 101), and FLOOR() truncates it down to a whole number, giving an integer uniformly distributed between 1 and 100.

GENERATE_SERIES() — a genuinely powerful function

GENERATE_SERIES(start, stop, step) produces a sequence of rows — numbers, or dates/timestamps with an interval step — without needing a source table at all. It's indispensable for building reports that need every value in a range represented, even ones with no matching data.

Every number from 1 to 5

SQL

SELECT GENERATE_SERIES(1, 5);

generate_series
---------------
              1
              2
              3
              4
              5

Every date in January 2024 — handy for a day-by-day report

SQL

SELECT GENERATE_SERIES(
  '2024-01-01'::date,
  '2024-01-31'::date,
  INTERVAL '1 day'
) AS report_date;

report_date
------------
2024-01-01
2024-01-02
...
2024-01-31

Tip

A common reporting pattern: LEFT JOIN a GENERATE_SERIES() of dates against your actual data, so days with zero activity still show up in the result as a row with 0 instead of being missing entirely. See the Date & Time Functions page for more on working with dates.

Note

GENERATE_SERIES() can be used directly in a FROM clause, just like a table — it's a set-returning function, not a scalar one.

ROUND, CEIL, FLOOR, ABS, POWER, SQRT, and MOD/% cover the everyday numeric operations.
RANDOM() returns a double between 0 and 1 and is not cryptographically secure.
Scale and floor RANDOM() to generate a random integer within any range.
GENERATE_SERIES() produces a sequence of numbers or dates on demand — invaluable for filling gaps in reports.