--- title: "/ (division)" slug: "dev-database-reference-sql-functions-operators-division" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-operators-division" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # / (division) *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Operators and predicates](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates) › Arithmetic operators.* `a / b` divides `a` by `b` and returns a `DOUBLE`, also when both are integers: `7 / 2` is 3.5. Dividing by zero doesn't raise an error; it returns infinity or `NaN`. ## Syntax ``` a / b ``` | Argument | Type | Description | | --- | --- | --- | | `a` | any integer type, `DECIMAL`, `FLOAT` or `DOUBLE` | The dividend. | | `b` | any integer type, `DECIMAL`, `FLOAT` or `DOUBLE` | The divisor. | **Returns:** `DOUBLE`, for integers and `DECIMAL` values too. Two `FLOAT` values give a `FLOAT`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_operators_division`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of three taxi trips: the distance in kilometres and the duration in minutes (`INTEGER`), the fare (`DECIMAL(10,2)`) and the fuel used in litres (`DOUBLE`). Trip 2 has a duration of 0, trip 3 is all zeros, and the last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_operators_division"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.trips" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, km INTEGER, minutes INTEGER, fare DECIMAL(10,2), litres DOUBLE)") run(f"INSERT INTO {TABLE} VALUES " "(1, 42, 35, 61.50, 3.5), " "(2, 7, 0, 12.00, 0.6), " "(3, 0, 0, 0.00, 0.0), " "(4, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `a / b` is the quotient as a binary floating-point number. Integers are converted to `DOUBLE` first (the plan in [How it runs in VAST](/develop/docs/dev-database-reference-sql-functions-operators-division#how-it-runs-in-vast) shows the casts), so the fraction is kept: `7 / 2` is 3.5, and `-7 / 2` is -3.5. To drop the fraction, use [// (integer division)](/develop/docs/dev-database-reference-sql-functions-operators-integer-division) or [divide](/develop/docs/dev-database-reference-sql-functions-numeric-divide). **`DECIMAL` values are divided as `DOUBLE` too,** so the result isn't exact: `2.45 / 0.7` is 3.5000000000000004. **Large integers lose precision.** A `DOUBLE` holds integers exactly only up to 9007199254740992 (2 to the power 53): `9007199254740993 / 1` is 9007199254740992.0. **Dividing by zero returns a value, not an error:** | Division | Result | | --- | --- | | A positive number by 0 | `inf` | | A negative number by 0 | `-inf` | | 0 by 0 | `nan` | | A positive number by `-0.0::DOUBLE` | `-inf` | `inf` and `nan` are valid `DOUBLE` values: they don't stop the query, and they carry on into later arithmetic: infinity divided by infinity is `nan`. Test for them with [isnan](/develop/docs/dev-database-reference-sql-functions-numeric-isnan) and [isinf](/develop/docs/dev-database-reference-sql-functions-numeric-isinf), or keep a zero divisor out of the division (see [Limitations and errors](/develop/docs/dev-database-reference-sql-functions-operators-division#limitations-and-errors)). ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL / 1`, `1 / NULL` | `NULL` | | `7 / 2`, `typeof(7 / 2)` | `3.5`, `DOUBLE` | | `-7 / 2` | `-3.5` | | `7::FLOAT / 2::FLOAT` | `3.5`, a `FLOAT` | | `1 / 0`, `-1 / 0`, `0 / 0` | `inf`, `-inf`, `nan` | | `isnan(0 / 0)` | `True` | | `1 / -0.0::DOUBLE` | `-inf` | | `2.45 / 0.7` | `3.5000000000000004`, a `DOUBLE` | | `9007199254740993 / 1` | `9007199254740992.0` | | A string such as `'12' / km` | Error: `Could not convert string '12' to INTERVAL` | | A boolean, date, `BLOB` or list | Error: `No function matches the given name and argument types` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-operators-division#set-up-the-examples): ``` run("SELECT NULL / 1, 1 / NULL, 7 / 2, typeof(7 / 2), -7 / 2, 7::FLOAT / 2::FLOAT, typeof(7::FLOAT / 2::FLOAT)") run("SELECT 1 / 0, -1 / 0, 0 / 0, isnan(0 / 0), 1 / -0.0::DOUBLE") run("SELECT 2.45 / 0.7, typeof(2.45 / 0.7), 9007199254740993 / 1") run(f"SELECT '12' / km FROM {TABLE} WHERE id = 1") run("SELECT true / 2") ``` ``` [(None, None, 3.5, 'DOUBLE', -3.5, 3.5, 'FLOAT')] [(inf, -inf, nan, True, -inf)] [(3.5000000000000004, 'DOUBLE', 9007199254740992.0)] OSError: Could not convert string '12' to INTERVAL InternalError: Binder Error: No function matches the given name and argument types '/(BOOLEAN, INTEGER_LITERAL)'. You might need to add explicit type casts. ``` ## Examples The average speed in km/h, the fare per kilometre and the fuel per 100 km. Trip 2 has a duration of 0, so its speed is `inf`; trip 3 divides 0 by 0, so its values are `nan`: ``` run(f"SELECT id, km / minutes * 60, fare / km, litres / km * 100 FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 72.0, 1.4642857142857142, 8.333333333333332), (2, inf, 1.7142857142857142, 8.571428571428571), (3, nan, nan, nan), (4, None, None, None)] ``` Keep only the trips faster than 1 km a minute. Trips 2 and 3 are kept as well: `7 / 0` is `inf` and `0 / 0` is `nan`, and both compare as greater than 1. Add `minutes > 0` to leave them out: ``` run(f"SELECT id, km, minutes FROM {TABLE} WHERE km / minutes > 1 ORDER BY id LIMIT 10") ``` ``` [(1, 42, 35), (2, 7, 0), (3, 0, 0)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not a quotient of two columns, so `km / minutes` is computed by the Query Engine: - **In the `SELECT` list,** the scan reads the columns and the Query Engine computes the quotient for each row it returns. - **In `WHERE`,** the scan sends both columns for every row, and a `FILTER` step in the Query Engine computes the quotient and keeps the rows that match. No rows are skipped in storage. The plan of the `WHERE` example shows the `FILTER` step above the scan, and the conversion of both columns to `DOUBLE`: ``` plan(f"SELECT id FROM {TABLE} WHERE km / minutes > 1") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #2 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ ((CAST(km AS DOUBLE) / │ │ CAST(minutes AS DOUBLE)) │ │ > 1.0) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ km │ │ minutes │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Multiply the constant instead of dividing the column.** `litres / 2 > 1` runs in a `FILTER` step; `litres > 1 * 2` compares the raw column with a constant, which is computed once and pushed down to VAST DataBase. Keep the direction of the comparison in mind when the divisor is negative. ``` plan(f"SELECT id FROM {TABLE} WHERE litres / 2 > 1") plan(f"SELECT id FROM {TABLE} WHERE litres > 1 * 2") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ ((litres / 2.0) > 1.0) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ litres │ │ id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Store a ratio you filter on often.** A quotient of two columns always runs in the Query Engine. If you often filter on the speed, compute it when you write the rows and keep it in its own column. - **Combine it with a pushed-down condition.** In `minutes > 0 AND km / minutes > 1`, `minutes > 0` is evaluated in VAST DataBase, so the division runs only on the rows that pass it. ## Limitations and errors - **Division by zero isn't an error.** It returns `inf`, `-inf` or `nan`. To get NULL instead, divide by `nullif(b, 0)`, or use [divide](/develop/docs/dev-database-reference-sql-functions-numeric-divide), which returns NULL for a zero divisor. To skip those rows, add `b <> 0` to `WHERE`. - **The result is always floating point.** Even `DECIMAL` values are divided as `DOUBLE`, so the result is rounded. For integer division, use [//](/develop/docs/dev-database-reference-sql-functions-operators-integer-division); for a quotient rounded down, [fdiv](/develop/docs/dev-database-reference-sql-functions-numeric-fdiv). - **Strings aren't converted.** `'12' / km` fails with `Could not convert string '12' to INTERVAL`, because the string is read as an interval. Cast it to a number first: `CAST('12' AS DOUBLE) / km`. - **Booleans, dates, `BLOB` values and lists** fail with `No function matches the given name and argument types`. ``` run(f"SELECT id, km / nullif(minutes, 0) * 60, divide(km::DOUBLE, minutes) * 60 FROM {TABLE} ORDER BY id LIMIT 10") run(f"SELECT id, km / minutes * 60 FROM {TABLE} WHERE minutes <> 0 ORDER BY id LIMIT 10") run(f"SELECT CAST('12' AS DOUBLE) / km FROM {TABLE} WHERE id = 1") ``` ``` [(1, 72.0, 72.0), (2, None, None), (3, None, None), (4, None, None)] [(1, 72.0)] [(0.2857142857142857,)] ``` ## Similar functions | Function | Result type for integers | `7` by `2` | `-7` by `2` | `1` by `0` | | --- | --- | --- | --- | --- | | `a / b` | `DOUBLE` | `3.5` | `-3.5` | `inf` | | [// (integer division)](/develop/docs/dev-database-reference-sql-functions-operators-integer-division) | the integer type; the fraction dropped toward zero | `3` | `-3` | `NULL` | | [divide](/develop/docs/dev-database-reference-sql-functions-numeric-divide) | the integer type, like `//` | `3` | `-3` | `NULL` | | [fdiv](/develop/docs/dev-database-reference-sql-functions-numeric-fdiv) | `DOUBLE`, rounded down | `3.0` | `-4.0` | `inf` | | [% (modulo)](/develop/docs/dev-database-reference-sql-functions-operators-modulo) | the remainder of `//` | `1` | `-1` | `NULL` | ``` run("SELECT 7 / 2, 7 // 2, divide(7, 2), fdiv(7, 2), 7 % 2") run("SELECT -7 / 2, -7 // 2, divide(-7, 2), fdiv(-7, 2), -7 % 2") run("SELECT 1 / 0, 1 // 0, divide(1, 0), fdiv(1, 0), 1 % 0") ``` ``` [(3.5, 3, 3, 3.0, 1)] [(-3.5, -3, -3, -4.0, -1)] [(inf, None, None, inf, None)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Operators and predicates](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates#arithmetic): all the arithmetic operators. - [// (integer division)](/develop/docs/dev-database-reference-sql-functions-operators-integer-division), [% (modulo)](/develop/docs/dev-database-reference-sql-functions-operators-modulo), [divide](/develop/docs/dev-database-reference-sql-functions-numeric-divide) and [fdiv](/develop/docs/dev-database-reference-sql-functions-numeric-fdiv). - [isnan](/develop/docs/dev-database-reference-sql-functions-numeric-isnan) and [isinf](/develop/docs/dev-database-reference-sql-functions-numeric-isinf): test for `nan` and `inf`. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.