Math functions

Round and cut numbers, take powers, roots and logarithms, divide safely, put values in buckets, and make random numbers.

• Category: Functions — math
• Keywords: math, number, rounding, %, +, -, *, /, arithmetic, division by zero, truncate, cut decimals, cube root, log base 2, greatest common divisor, highest common factor, hcf, least common multiple, n!, combinations, histogram, bucket, bin, limit to a range, circuit limit, between bounds, divide by zero, ifnull division, zero if division by zero, ratio, percentage, banker's rounding, round half to even, NaN, not a number, infinity, inf, rand, random sample, shuffle, seed
• See also: Trigonometry and probability, Finance and price functions, Conversion functions, Aggregate functions

These functions work on numbers: INT, LONG, DOUBLE, DECIMAL and the other number types. Sine, cosine and the normal distribution are in trigonometry and probability; money functions (interest, loans, NPV) are in finance functions.

• A NULL argument gives NULL (except greatest and least, which skip NULLs).
• A result that is not a real number (like sqrt(-1) or ln(0)) is NULL, not an error.
• Text is not turned into a number by itself: round('5') is an error. Use CAST('5' AS DOUBLE).

Example: Price change of CITY, rounded to 2 decimals (sample data: sample)

SELECT time, close,
       round(close - lag(close), 2) AS change,
       round((close - lag(close)) / lag(close) * 100, 2) AS change_pct
FROM ochl_day
WHERE symbol = 'CITY'
LIMIT 5;

Result:

(5 rows)

abs

Syntax:

abs(x)

The number without its minus sign: abs(-5) is 5.

• Returns: the same type as x (INT stays INT, DECIMAL stays DECIMAL).
• NULL: abs(NULL) is NULL.

Example

SELECT abs(-5) AS a, abs(-2.75) AS b, abs(CAST(-3.5 AS DECIMAL(2))) AS c, abs(NULL) AS d;

Result:

(1 row)

Example: How far the close is from the open, for each day (sample data: sample)

SELECT time, symbol, open, close, abs(close - open) AS move
FROM ochl_day
WHERE symbol = 'COAST'
ORDER BY move DESC
LIMIT 3;

Result:

(3 rows)

round

Syntax:

round(x)
round(x, digits)

Rounds x to digits places after the decimal point. Halves go away from zero: round(2.5) is 3 and round(-2.5) is -3.

• Returns: DOUBLE. An INT or LONG with digits 0 or more comes back unchanged. A DECIMAL stays an exact DECIMAL with digits places: round(2.345::NUMERIC(10,3), 2) is 2.35.
• NULL: NULL in, NULL out.
• Notes: a DOUBLE can not hold every decimal exactly. round goes by the decimal a DOUBLE is written as, so round(1.005, 2) is 1.01 and round(2.675, 2) is 2.68, as in PostgreSQL. For money that must be exact, use a DECIMAL(2) column.
• Notes: to store the same numbers as a program that rounds (for example to 6 places), round in the procedure too: matching another system's rounding.

Example

SELECT round(512.456, 2) AS two_places, round(512.5) AS whole, round(-2.5) AS negative, round(1234.5, -2) AS hundreds, round(7) AS int_value, round(1.005, 2) AS half_cent;

Result:

(1 row)

Example: Average close per symbol, rounded for a report (sample data: sample)

SELECT symbol, round(avg(close), 2) AS avg_close, count(*) AS days
FROM ochl_day
GROUP BY symbol
ORDER BY symbol;

Result:

(3 rows)

Example: Text is not a number (shows an error)

SELECT round('5');

Result:

Error: line 1, column 8: round: argument 1 must be a number, got 5

round_half_even

Syntax:

round_half_even(x)
round_half_even(x, digits)

Rounds like round, but a value exactly halfway goes to the even neighbour: round_half_even(2.5) is 2, round_half_even(3.5) is 4 (banker's rounding, QuestDB's round_half_even). Adding many rounded amounts then does not drift upwards.

• Returns: the same kinds as round: a DECIMAL stays an exact DECIMAL with digits places, a whole number with digits 0 or more is unchanged, anything else is a DOUBLE.
• NULL: NULL in, NULL out.
• Notes: a DOUBLE is judged by the decimal it is written as, as round does: round_half_even(0.125::float8, 2) is 0.12, and round_half_even(0.1251::float8, 2) is 0.13 because it is past the half. round itself still rounds halves away from zero.

Example

SELECT round_half_even(2.5) AS two, round_half_even(3.5) AS four, round_half_even(-2.5) AS minus_two,
       round_half_even(2.345, 2) AS even_cent, round_half_even(2.355, 2) AS up_cent, round_half_even(1250, -2) AS hundreds;

Result:

(1 row)

floor

Syntax:

floor(x)

Rounds down to the whole number at or below x: floor(5.7) is 5, floor(-5.2) is -6.

• Returns: DOUBLE for DOUBLE input; a DECIMAL gives an exact DECIMAL with no places; an INT or LONG comes back unchanged.
• NULL: NULL in, NULL out.

Example

SELECT floor(5.7) AS a, floor(-5.2) AS b, floor(9) AS c;

Result:

(1 row)

Example: Price bands of 10 dollars (sample data: sample)

SELECT floor(close / 10) * 10 AS band, count(*) AS days
FROM ochl_day
WHERE symbol = 'CITY'
GROUP BY floor(close / 10) * 10
ORDER BY band;

Result:

(5 rows)

ceil, ceiling

Syntax:

ceil(x)
ceiling(x)

Rounds up to the whole number at or above x: ceil(5.2) is 6, ceil(-5.7) is -5. ceiling is the same function.

• Returns: DOUBLE for DOUBLE input; a DECIMAL gives an exact DECIMAL with no places; an INT or LONG comes back unchanged.
• NULL: NULL in, NULL out.

Example

SELECT ceil(5.2) AS a, ceiling(-5.7) AS b, ceil(4) AS c;

Result:

(1 row)

Example: How many pages for 956 rows, 100 rows per page (sample data: sample)

SELECT count(*) AS rows, ceil(count(*) / 100.0) AS pages FROM ochl_minutes;

Result:

(1 row)

trunc, truncate

Syntax:

trunc(x)
trunc(x, digits)
truncate(x, digits)

Cuts off the digits after digits places, without rounding: trunc(512.789, 2) is 512.78, while round gives 512.79. It moves toward zero: trunc(-5.7) is -5 (but floor(-5.7) is -6). truncate is the same function.

• Returns: DOUBLE. An INT or LONG with digits 0 or more comes back unchanged (as a LONG).
• NULL: NULL when x or digits is NULL.
• Notes: digits must be a whole number: trunc(5.99, 1.5) is an error.

Example

SELECT trunc(512.789, 2) AS two_places, round(512.789, 2) AS rounded, trunc(-5.7) AS negative, floor(-5.7) AS floor, truncate(1234.5, -2) AS hundreds, trunc(7) AS int_value;

Result:

(1 row)

Example: Average trade rate: cut and rounded to 1 decimal (sample data: sample)

SELECT symbol, avg(rate) AS avg_rate, trunc(avg(rate), 1) AS cut, round(avg(rate), 1) AS rounded
FROM trade_log
GROUP BY symbol
ORDER BY symbol;

Result:

(4 rows)

Example: digits must be a whole number (shows an error)

SELECT trunc(5.99, 1.5);

Result:

Error: line 1, column 8: trunc: argument 2 must be a whole number, got '1.5'

sqrt

Syntax:

sqrt(x)

Square root: sqrt(16) is 4.

• Returns: DOUBLE.
• NULL: NULL in, NULL out. A negative x also gives NULL.

Example

SELECT sqrt(16) AS a, sqrt(2) AS b, sqrt(-1) AS negative;

Result:

(1 row)

Example: Daily volatility from the variance of the close (sample data: sample)

SELECT symbol, round(sqrt(var_samp(close)), 3) AS stddev_close
FROM ochl_day
GROUP BY symbol
ORDER BY symbol;

Result:

(3 rows)

cbrt

Syntax:

cbrt(x)

The cube root: the number that, multiplied by itself three times, gives x. cbrt(27) is 3, because 3 × 3 × 3 = 27. Unlike sqrt, it works for negative numbers: cbrt(-8) is -2.

• Returns: DOUBLE.
• NULL: NULL in, NULL out.
• Notes: cbrt(x) is the same as pow(x, 1.0 / 3) for positive x, but pow gives NULL for a negative x.

Example

SELECT cbrt(27) AS a, cbrt(-8) AS b, cbrt(2) AS c, pow(-8, 1.0 / 3) AS pow_negative;

Result:

(1 row)

Example: Growth per year: 1,00,000 became 1,33,100 in 3 years

SELECT round((cbrt(133100.0 / 100000) - 1) * 100, 2) AS growth_pct_per_year;

Result:

(1 row)

pow, power

Syntax:

pow(x, y)
power(x, y)

x to the power y: pow(2, 10) is 1024. power is the same function.

• Returns: DOUBLE.
• NULL: NULL when an argument is NULL, or when the result is not a real number (too big, or a negative base with a fraction power).

Example

SELECT pow(2, 10) AS a, power(2, 0.5) AS b, pow(10, -2) AS c, pow(-8, 1.0 / 3) AS not_real;

Result:

(1 row)

Example: Money after 5 years at 8% a year

SELECT round(100000 * pow(1 + 0.08, 5), 2) AS amount;

Result:

(1 row)

exp

Syntax:

exp(x)

The number e (2.718...) to the power x. It undoes ln.

• Returns: DOUBLE. NULL when the result is too big for a DOUBLE.
• NULL: NULL in, NULL out.

Example

SELECT exp(0) AS a, exp(1) AS e, exp(ln(5)) AS back_to_5, exp(1000) AS too_big;

Result:

(1 row)

ln, log

Syntax:

ln(x)
log(x)

ln(x) is the natural logarithm (base e). log(x) is base 10, as in PostgreSQL: log(100) is 2, the same as log10(100). log(b, x) is base b.

• Returns: DOUBLE.
• NULL: NULL in, NULL out. x of 0 or less gives NULL.
• Notes: before 2026-09-25 log(x) was the natural logarithm. A query that meant ln should now say ln.

Example

SELECT ln(1) AS a, ln(exp(2)) AS b, log(100) AS base_10, log(2, 8) AS base_2, ln(0) AS zero;

Result:

(1 row)

Example: Log returns of CITY (sample data: sample)

SELECT time, close, round(ln(close / lag(close)), 5) AS log_return
FROM ochl_day
WHERE symbol = 'CITY'
LIMIT 4;

Result:

(4 rows)

log10

Syntax:

log10(x)

Logarithm base 10: log10(1000) is 3.

• Returns: DOUBLE.
• NULL: NULL in, NULL out. x of 0 or less gives NULL.

Example

SELECT log10(1000) AS a, log10(2) AS b, log10(-1) AS negative;

Result:

(1 row)

log2

Syntax:

log2(x)

Logarithm base 2: how many times you double 1 to get x. log2(8) is 3, because 1 → 2 → 4 → 8. For a price, log2(new / old) tells how many times the price doubled.

• Returns: DOUBLE.
• NULL: NULL in, NULL out. x of 0 or less gives NULL.

Example

SELECT log2(8) AS a, log2(1024) AS b, log2(1000) AS c, log2(0) AS zero;

Result:

(1 row)

Example: A share went from 100 to 800 dollars: how many doublings?

SELECT log2(800.0 / 100) AS doublings, pow(2, log2(800.0 / 100)) * 100 AS back_to_price;

Result:

(1 row)

sign

Syntax:

sign(x)

-1 for a negative number, 0 for zero, 1 for a positive number.

• Returns: DOUBLE.
• NULL: NULL in, NULL out.

Example

SELECT sign(-7) AS a, sign(0) AS b, sign(3.5) AS c;

Result:

(1 row)

Example: Up, down or flat days (sample data: sample)

SELECT sign(close - open) AS direction, count(*) AS days
FROM ochl_day
GROUP BY sign(close - open)
ORDER BY direction;

Result:

(2 rows)

mod

Syntax:

mod(x, y)
x % y

The remainder after dividing x by y: mod(10, 3) is 1. The % operator does the same.

• Returns: LONG when both are whole numbers, else DOUBLE. The sign follows x.
• NULL: NULL when an argument is NULL. A y of 0 is an error, "division by zero", as in PostgreSQL.

Example

SELECT mod(10, 3) AS a, 10 % 3 AS b, mod(-10, 3) AS c, mod(10.5, 3) AS d, mod(5, NULLIF(0, 0)) AS by_zero;

Result:

(1 row)

Example: Every 5th transaction of the trade log (sample data: sample)

SELECT transaction_id, symbol, quantity
FROM trade_log
WHERE transaction_id % 5 = 0
ORDER BY transaction_id
LIMIT 4;

Result:

(4 rows)

greatest

Syntax:

greatest(value, value, ...)

The biggest of the values. NULL values are skipped. Works for numbers, text and times.

• Returns: the biggest value (its own type).
• NULL: NULL only when every value is NULL.
• Notes: values that cannot be compared (a number and a text) give an error. At most 64 values.

Example

SELECT greatest(3, 9, 4) AS a, greatest(3, NULL, 9) AS b, greatest('CITY', 'COAST') AS c, greatest(NULL, NULL) AS d;

Result:

(1 row)

Example: The bigger of two prices (sample data: sample)

SELECT time, open, close, greatest(open, close) AS body_top
FROM ochl_day
WHERE symbol = 'VOLT'
LIMIT 3;

Result:

(3 rows)

least

Syntax:

least(value, value, ...)

The smallest of the values. NULL values are skipped. Works for numbers, text and times.

• Returns: the smallest value (its own type).
• NULL: NULL only when every value is NULL.
• Notes: values that cannot be compared give an error. At most 64 values.

Example

SELECT least(3, 9, 4) AS a, least(3, NULL, 9) AS b, least('2026-09-10', '2026-01-01') AS c;

Result:

(1 row)

Example: A number and a text cannot be compared (shows an error)

SELECT least(1, 'a');

Result:

Error: line 1, column 8: least: values cannot be compared

gcd

Syntax:

gcd(a, b)

The greatest common divisor: the biggest whole number that divides both a and b with no remainder. gcd(12, 18) is 6. Use it to make a ratio as small as possible, like a bonus share ratio of 20:100 that is really 1:5.

• Returns: LONG, 0 or more (the sign is ignored).
• NULL: NULL when an argument is NULL.
• Notes: gcd(0, 5) is 5. A number with a fraction (12.5) is an error; a DOUBLE like 12.0 is fine.

Example

SELECT gcd(12, 18) AS a, gcd(-12, 18) AS b, gcd(0, 5) AS c, gcd(7, 5) AS no_common;

Result:

(1 row)

Example: Write a bonus share ratio in its smallest form

SELECT bonus, held,
       bonus / gcd(bonus, held) AS bonus_small,
       held / gcd(bonus, held) AS held_small
FROM (SELECT 20 AS bonus, 100 AS held UNION ALL SELECT 15, 45 UNION ALL SELECT 7, 10) t;

Result:

(3 rows)

Example: Only whole numbers (shows an error)

SELECT gcd(12.5, 5);

Result:

Error: line 1, column 8: gcd: argument 1 must be a whole number, got '12.5'

lcm

Syntax:

lcm(a, b)

The least common multiple: the smallest whole number that both a and b divide. lcm(4, 6) is 12. If one thing happens every 4 days and another every 6 days, they happen on the same day every 12 days.

• Returns: LONG, 0 or more.
• NULL: NULL when an argument is NULL.
• Notes: lcm(0, x) is 0. A result too big for a LONG is an error.

Example

SELECT lcm(4, 6) AS a, lcm(-4, 6) AS b, lcm(0, 5) AS c, lcm(7, 5) AS d;

Result:

(1 row)

Example: The smallest order that fits a lot size of 10 and a lot size of 25

SELECT lcm(10, 25) AS smallest_order;

Result:

(1 row)

factorial

Syntax:

factorial(n)

n!: the product 1 × 2 × 3 × ... × n. factorial(5) is 120. It counts the ways to put n things in a row, and it is used to count combinations (how many ways to pick k things from n).

• Returns: LONG for n up to 20. From 21, the result is too big for a LONG and is a DOUBLE (not exact). From 171 it is too big even for a DOUBLE: NULL.
• NULL: NULL in, NULL out.
• Notes: factorial(0) is 1. A negative n or a fraction is an error.

Example

SELECT factorial(0) AS a, factorial(5) AS b, factorial(20) AS c, factorial(21) AS d, factorial(171) AS e;

Result:

(1 row)

Example: How many ways to pick 3 shares from the 5 securities? (sample data: sample)

SELECT n, factorial(n) / (factorial(3) * factorial(n - 3)) AS ways_to_pick_3
FROM (SELECT count(*) AS n FROM securities) t;

Result:

(1 row)

Example: A negative number is an error (shows an error)

SELECT factorial(-1);

Result:

Error: line 1, column 8: factorial: needs 0 or more, got -1

pi

Syntax:

pi()

The number π (3.14159...): the length around a circle divided by its width. You need it with sin and cos and with radians.

• Returns: DOUBLE.
• NULL: never NULL.

Example

SELECT pi() AS pi, round(pi(), 2) AS short, 2 * pi() AS full_circle_in_radians;

Result:

(1 row)

degrees

Syntax:

degrees(x)

Changes an angle in radians to degrees. A full circle is 360 degrees or 2π radians, so degrees(pi()) is 180. The functions asin, acos, atan and atan2 give radians; use degrees to read them.

• Returns: DOUBLE.
• NULL: NULL in, NULL out.

Example

SELECT degrees(pi()) AS half_circle, degrees(pi() / 2) AS right_angle, degrees(1) AS one_radian, degrees(atan(1)) AS atan_1;

Result:

(1 row)

radians

Syntax:

radians(x)

Changes an angle in degrees to radians, the unit that sin, cos and tan need. radians(180) is π.

• Returns: DOUBLE.
• NULL: NULL in, NULL out.

Example

SELECT radians(180) AS pi, radians(90) AS right_angle, round(sin(radians(30)), 4) AS sin_30_degrees;

Result:

(1 row)

width_bucket

Syntax:

width_bucket(x, low, high, count)

Cuts the range from low to high into count buckets of the same width, and says which bucket x falls in: 1 to count. A value below low is bucket 0; a value at or above high is bucket count + 1. Use it to make a histogram: a count of values per bucket.

• Returns: LONG.
• NULL: NULL when an argument is NULL.
• Notes: each bucket includes its start and not its end. When low is bigger than high, the buckets count down. low and high must be different, else an error. Like PostgreSQL width_bucket.

Example

SELECT width_bucket(512, 400, 600, 4) AS a, width_bucket(399, 400, 600, 4) AS below, width_bucket(600, 400, 600, 4) AS at_high, width_bucket(450, 600, 400, 4) AS down;

Result:

(1 row)

Example: Histogram of CITY's one-minute closes on 10 September (sample data: sample)

SELECT width_bucket(close, 500, 510, 5) AS bucket,
       500 + (width_bucket(close, 500, 510, 5) - 1) * 2 AS from_price,
       count(*) AS minutes
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
GROUP BY width_bucket(close, 500, 510, 5)
ORDER BY bucket;

Result:

(4 rows)

Example: Daily returns in 1% buckets from -3% to +3% (sample data: sample)

SELECT width_bucket(chg, -3, 3, 6) AS bucket, count(*) AS days
FROM (SELECT pct_change(close) OVER (PARTITION BY symbol ORDER BY time) AS chg
      FROM ochl_day
      WHERE time >= '2026-08-23') t
WHERE chg IS NOT NULL
GROUP BY width_bucket(chg, -3, 3, 6)
ORDER BY bucket;

Result:

(7 rows)

clamp

Syntax:

clamp(x, low, high)

Keeps x inside the range from low to high: below low gives low, above high gives high, else x. It is the same as least(greatest(x, low), high).

• Returns: the value that was chosen, with its own type.
• NULL: NULL when any argument is NULL.
• Notes: low bigger than high is an error. Values that cannot be compared (a number and a text) are an error.

Example

SELECT clamp(15, 0, 10) AS too_big, clamp(-3, 0, 10) AS too_small, clamp(4.5, 0, 10) AS inside, clamp('m', 'a', 'k') AS text;

Result:

(1 row)

Example: Circuit limit: an order price must stay within 10% of yesterday's close

Setup:

CREATE TABLE orders (id INT, symbol STRING, prev_close DOUBLE, price DOUBLE);
INSERT INTO orders VALUES (1, 'CITY', 504.9, 570.0), (2, 'COAST', 376.1, 390.0), (3, 'VOLT', 191.2, 160.0);

SELECT id, symbol, prev_close, price,
       clamp(price, round(prev_close * 0.9, 1), round(prev_close * 1.1, 1)) AS allowed_price
FROM orders
ORDER BY id;

Result:

(3 rows)

Example: The lower bound must not be bigger (shows an error)

SELECT clamp(5, 10, 0);

Result:

Error: line 1, column 8: clamp: the lower bound is bigger than the upper bound

safe_divide

Syntax:

safe_divide(x, y)

x / y, but NULL when y is 0. The / operator stops the query with "division by zero" instead, as PostgreSQL does; safe_divide works like SAFE_DIVIDE in BigQuery, so copied SQL runs.

• Returns: DOUBLE.
• NULL: NULL when an argument is NULL or y is 0.
• Notes: to get 0 instead of NULL, use div0.

Example

SELECT safe_divide(10, 4) AS a, safe_divide(10, 0) AS by_zero, 10 / NULLIF(0, 0) AS operator, safe_divide(NULL, 2) AS null_value;

Result:

(1 row)

Example: Average trade size, also on a day with no trades

Setup:

CREATE TABLE broker_day (broker INT, trades INT, shares INT);
INSERT INTO broker_day VALUES (1, 12, 3400), (34, 0, 0), (45, 5, 1250);

SELECT broker, trades, shares, safe_divide(shares, trades) AS avg_trade_size
FROM broker_day
ORDER BY broker;

Result:

(3 rows)

div0

Syntax:

div0(x, y)

x / y, but 0 when y is 0 (like DIV0 in Snowflake). Use it when a zero is the right answer, for example so that a total over many rows is not hurt by a NULL.

• Returns: DOUBLE.
• NULL: NULL when an argument is NULL. A y of 0 gives 0, not NULL.

Example

SELECT div0(10, 4) AS a, div0(10, 0) AS by_zero, div0(5, NULL) AS null_value;

Result:

(1 row)

Example: safe_divide and div0 side by side

Setup:

CREATE TABLE broker_day (broker INT, trades INT, shares INT);
INSERT INTO broker_day VALUES (1, 12, 3400), (34, 0, 0), (45, 5, 1250);

SELECT broker, safe_divide(shares, trades) AS safe_divide, div0(shares, trades) AS div0
FROM broker_day
ORDER BY broker;

Result:

(3 rows)

percent

Syntax:

percent(part, total)

part as a percent of total: part / total × 100. percent(25, 200) is 12.5.

• Returns: DOUBLE (12.5 means 12.5%).
• NULL: NULL when an argument is NULL or total is 0.

Example

SELECT percent(25, 200) AS a, percent(3, 4) AS b, percent(5, 0) AS zero_total;

Result:

(1 row)

Example: Share of paying (pro) users in each city (sample data: query-app)

SELECT city, count(*) AS users,
       sum(CASE WHEN plan = 'pro' THEN 1 ELSE 0 END) AS pro_users,
       round(percent(sum(CASE WHEN plan = 'pro' THEN 1 ELSE 0 END), count(*)), 1) AS pro_pct
FROM app_users
WHERE city IS NOT NULL
GROUP BY city
ORDER BY pro_pct DESC, city;

Result:

(4 rows)

is_nan, is_inf, is_finite

Syntax:

is_nan(x)
is_inf(x)
is_finite(x)

A DOUBLE can hold three special values that are not normal numbers: NaN ("not a number", for example from 0 × infinity), inf (infinity) and -inf. They can come from other programs or from a calculation that is too big. These functions test for them.

• Returns: BOOL.
• NULL: NULL in, NULL out.
• Notes: most KDB math functions give NULL instead of NaN or inf. The * operator does not: 1e308 * 10 is inf. Text is an error.

Example

SELECT is_nan(CAST('NaN' AS DOUBLE)) AS nan, is_inf(CAST('-inf' AS DOUBLE)) AS minus_inf,
       is_finite(1e308 * 10) AS too_big_is_finite, is_finite(512.5) AS normal, is_nan(NULL) AS null_value;

Result:

(1 row)

Example: Which values are normal numbers?

SELECT name, is_nan(x) AS is_nan, is_inf(x) AS is_inf, is_finite(x) AS is_finite
FROM (SELECT 'price' AS name, 512.5 AS x
      UNION ALL SELECT 'too big', 1e308 * 10
      UNION ALL SELECT 'not a number', CAST('NaN' AS DOUBLE)) t;

Result:

(3 rows)

random, rand

Syntax:

random()
rand()

A random number from 0 up to (but not including) 1. Every call gives a new number, also on every row. rand is the same function.

• Returns: DOUBLE, at least 0 and less than 1.
• NULL: never NULL.
• Notes:
  • A random whole number from 1 to 6: floor(random() * 6) + 1.
  • ORDER BY random() LIMIT n picks n random rows.
  • Not safe for passwords or tokens: use random_token for those.
  • Results with random() are never taken from a cache.

Example: Every one-minute candle gets a random number, always from 0 to 1 (sample data: sample)

SELECT count(*) AS rows, min(r) >= 0 AS at_least_0, max(r) < 1 AS below_1
FROM (SELECT random() AS r FROM ochl_minutes) t;

Result:

(1 row)

Example: Pick 3 app users for a survey (setseed makes the pick repeatable) (sample data: query-app)

SELECT setseed(0.42);
SELECT username, city FROM app_users ORDER BY random() LIMIT 3;

Result:

SELECT setseed(0.42)

(1 row)

SELECT username, city FROM app_users ORDER BY random() LIMIT 3

(3 rows)

setseed

Syntax:

setseed(seed)

Starts the random numbers from a fixed point, the seed. After the same setseed, random() gives the same numbers again, in the same order. Use it for tests and for a random sample that you can make again.

• Returns: always NULL.
• NULL: setseed(NULL) gives NULL and changes nothing.
• Notes: the seed is shared by the whole kdb program. On a server, another user's random() calls move the numbers on, so the order is only repeatable when nobody else uses random() at the same time.

Example: The same seed gives the same numbers

SELECT setseed(0.5);
SELECT round(random(), 4) AS first, round(random(), 4) AS second;
SELECT setseed(0.5);
SELECT round(random(), 4) AS first_again, round(random(), 4) AS second_again;

Result:

SELECT setseed(0.5)

(1 row)

SELECT round(random(), 4) AS first, round(random(), 4) AS second

(1 row)

SELECT setseed(0.5)

(1 row)

SELECT round(random(), 4) AS first_again, round(random(), 4) AS second_again

(1 row)

Example: The seed must be from -1 to 1 (shows an error)

SELECT setseed(2);

Result:

Error: line 1, column 8: setseed: the seed must be between -1 and 1

Arithmetic operators

Syntax:

a + b    a - b    a * b    a / b    a % b    -a

• NULL: NULL on either side gives NULL.
• Division by zero: 5 / 0, 5.0 / 0 and 5 % 0 stop the query with "division by zero" (SQLSTATE 22012), as in PostgreSQL. For NULL instead, write x / NULLIF(y, 0) or safe_divide.
• Too big: a whole-number result that does not fit a LONG stops the query (SQLSTATE 22003). Cast one side to DOUBLE for an approximate answer.
• * and / are done before + and -. Use ( ) to change the order.

Example

SELECT 2 + 3 * 4 AS a, (2 + 3) * 4 AS b, 7 / 2 AS c, 7 % 2 AS d, 5 / NULLIF(0, 0) AS e, -2 * 3 AS f, NULL + 1 AS g;

Result:

(1 row)

Example: Division by zero is an error (shows an error)

SELECT 5 / 0;

Result:

Error: line 1, column 10: division by zero

Example: Too big for a LONG (shows an error)

SELECT 9223372036854775807 + 1;

Result:

Error: line 1, column 28: bigint out of range (numeric value out of range)

Example: Times: add one hour, and the minutes between two times

SELECT CAST('2026-09-10T05:15:00Z' AS TIMESTAMP) + 3600000 AS plus_one_hour,
       (CAST('2026-09-10T11:00:00Z' AS TIMESTAMP) - CAST('2026-09-10T05:15:00Z' AS TIMESTAMP)) / 60000 AS minutes;

Result:

(1 row)