Integer Division in SQL: DIV, MOD, and the Cast Trap

Integer division returns only the quotient and throws away the fractional part. Sounds trivial, yet this is where analytics breaks most often: one stray type cast and 7 / 2 flips from 3 to 3.5.

Dividing two integers in PostgreSQL

In PostgreSQL the / operator looks at the operand types. If both are integers, the result is an integer and gets truncated toward zero (truncation, not floor):

SELECT 7 / 2;     -- 3
SELECT -7 / 2;    -- -3, not -4
SELECT 100 / 30;  -- 3

This is handy for bucketing. For example, group users into buckets of 100 ids:

SELECT
  id / 100 AS bucket,
  count(*) AS users
FROM users
GROUP BY id / 100
ORDER BY bucket;

Gotcha: truncation goes toward zero, not downward. For positive numbers there is no difference, but -7 / 2 yields -3, whereas a mathematical floor would give -4. When you actually need floor behavior, call floor() explicitly.

The cast trap

The classic mistake is accidentally turning an operand into a floating-point number. A single numeric or ::float is enough to make the division fractional:

SELECT 7 / 2;            -- 3   (int / int)
SELECT 7 / 2.0;          -- 3.5 (int / numeric)
SELECT 7::float / 2;     -- 3.5
SELECT amount / 2 FROM orders;  -- amount NUMERIC -> fractional result

If amount is declared as NUMERIC, then amount / 2 is already fractional. To get the integer count of "whole halves", truncate the result explicitly with DIV or floor().

The DIV(a, b) function

PostgreSQL provides a div(a, b) function that always performs integer division and returns the quotient truncated toward zero, regardless of the operand types:

SELECT div(7, 2);        -- 3
SELECT div(7.9, 2.0);    -- 3   (works on numeric too)
SELECT div(-7, 2);       -- -3

This is safer than / because it does not depend on whether the operands are integers. Great for money columns declared as NUMERIC. Let us count how many whole hundreds fit into each order:

SELECT
  id,
  amount,
  div(amount, 100) AS full_hundreds
FROM orders;

MySQL's DIV operator

In MySQL / always returns a fractional result, even for two integers: SELECT 7 / 2 gives 3.5. For integer division there is a dedicated DIV operator:

-- MySQL
SELECT 7 DIV 2;     -- 3
SELECT 100 DIV 30;  -- 3
SELECT id DIV 100 AS bucket FROM users;

In ClickHouse both forms exist: the function intDiv(7, 2) and the familiar 7 / 2 for float division. So the very same query behaves differently across engines, which is worth keeping in mind when porting code.

DIV together with MOD: quotient and remainder

Integer division almost always travels with MOD (the remainder). Together they give the full picture: how many whole times the divisor fits, and what is left over.

SELECT
  amount,
  div(amount, 100)  AS whole_hundreds,
  mod(amount, 100)  AS remainder
FROM orders;

A classic trick is converting seconds into minutes and seconds in one query:

SELECT
  id,
  extract(epoch FROM (now() - created_at))::int AS age_seconds,
  div(extract(epoch FROM (now() - created_at))::int, 60) AS minutes,
  mod(extract(epoch FROM (now() - created_at))::int, 60) AS seconds
FROM orders;

Another example: distribute employees across shifts in round-robin fashion using the remainder:

SELECT
  name,
  dept,
  mod(id, 3) AS shift   -- 0, 1, 2
FROM employees
ORDER BY shift, name;

Gotcha: in PostgreSQL the sign of mod follows the dividend, so mod(-7, 3) returns -1, not 2. If you need an always-non-negative remainder (for example, for hash sharding), wrap it: mod(mod(x, n) + n, n).

Remember the essentials: in PostgreSQL / truncates only when both operands are integers, in MySQL you need the DIV operator for that, and the div(a, b) function is the most portable and predictable way to get an integer quotient in both engines.