---
title: "- (subtraction and negation)"
slug: "dev-database-reference-sql-functions-operators-subtraction"
updated: 2026-10-30T18:18:56Z
published: 2026-10-30T18:18:56Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-operators-subtraction"
---

> ## 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.

# - (subtraction and negation)

*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` subtracts `b` from `a`, and `-a` negates `a`. `subtract(a, b)` and `subtract(a)` are the same operations written as functions. On integers the result keeps the integer type, and a result that doesn't fit it stops the query.

## Syntax

```
a - b
-a
subtract(a, b)
subtract(a)
```

| Argument | Type | Description |
| --- | --- | --- |
| `a` | any integer type, `DECIMAL`, `FLOAT`, `DOUBLE`, or a date or time value | The value to subtract from, or the value to negate. |
| `b` | a number, or an `INTEGER` or `INTERVAL` when `a` is a date or time value | The value to subtract. |

**Returns:** for two numbers of the same type, that type: `INTEGER - INTEGER` is an `INTEGER`, `DOUBLE - DOUBLE` a `DOUBLE`. A `DECIMAL` result gets one more digit: `DECIMAL(10,2) - DECIMAL(10,2)` is a `DECIMAL(11,2)`. `-a` has the type of `a`. `DATE - INTEGER` is a `DATE`.

**Aliases:** `subtract(a, b)` for `a - b`, and `subtract(a)` for `-a`.

## 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_subtraction`. 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 stock counts for three products: the count at opening and at closing (`INTEGER`), the price and cost (`DECIMAL(10,2)`), the storage temperature (`DOUBLE`), the number of free bins (`UTINYINT`, an unsigned 8-bit integer) and the shipping date. 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_subtraction"'

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, "<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}.stock"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, opening INTEGER, closing INTEGER, price DECIMAL(10,2), "
    "cost DECIMAL(10,2), temp DOUBLE, bins UTINYINT, shipped DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 120, 95, 19.99, 12.50, 21.5, 5, DATE '2026-10-16'), "
    "(2, 40, 52, 4.25, 4.75, -3.25, 0, DATE '2026-10-01'), "
    "(3, 0, 0, 0.00, 0.00, 0.0, 200, DATE '2026-01-01'), "
    "(4, NULL, NULL, NULL, NULL, NULL, NULL, NULL)")
```

```
[]
[]
[(4,)]
```

## What it does

`a - b` is the difference of two numbers, and `-a` is `a` with its sign changed. The function forms do the same: `subtract(7, 2)` is 5, and `subtract(-5)` is 5.

**Integers stay integers.** Two `INTEGER` values give an `INTEGER`, two `BIGINT` values a `BIGINT`, and so on for each integer type. If the result doesn't fit the type, the query stops with an overflow error, such as `Overflow in subtraction of INT32 (&#x2026;)!`. This also applies to the unsigned types (`UTINYINT`, `USMALLINT`, `UINTEGER`, `UBIGINT`): they can't hold a negative number, so `0 - 1` on a `UTINYINT` is an error, `Overflow in subtraction of UINT8 (0 - 1)!`.

**Negating an unsigned value wraps around.** `-a` on an unsigned type doesn't fail: `-(1::UTINYINT)` is 255, the largest `UTINYINT`, and `-(0::UBIGINT)` is 0. Negating the smallest value of a signed type fails, because its positive counterpart doesn't fit: `-((-128)::TINYINT)` stops with `Overflow in negation of integer!`.

**`DECIMAL` is exact,** and the result gets one more digit before the decimal point: `DECIMAL(10,2) - DECIMAL(10,2)` is a `DECIMAL(11,2)`, so the smallest `DECIMAL(10,2)` minus 1 still fits. `FLOAT` and `DOUBLE` results don't raise overflow errors; `&apos;inf&apos;::DOUBLE - &apos;inf&apos;::DOUBLE` is `nan`.

**Dates:** `DATE - INTEGER` subtracts days and returns a `DATE`: `DATE &apos;2026-10-16&apos; - 3` is 2026-10-13. Date and time arithmetic is on the [date and time](/develop/docs/dev-database-reference-sql-functions-date-time) page.

**Two minus signs:** `- -5` (with a space) is 5, but `--5` starts a SQL comment, so `SELECT --5` is a syntax error. Write `-(-5)` or `- -5`.

**The unary minus binds before `**`:** `-2 ** 2` is `(-2) ** 2`, which is 4.0, not -4.0. Write `-(2 ** 2)` for -4.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `NULL - 1`, `1 - NULL` | `NULL` |
| `subtract(7, 2)`, `subtract(-5)` | `5`, `5` |
| `- -5` | `5` |
| `-2 ** 2` | `4.0` |
| `&apos;inf&apos;::DOUBLE - &apos;inf&apos;::DOUBLE` | `nan` |
| `signbit(-(0.0::DOUBLE))` | `True`: negating 0.0 gives -0.0 |
| `-(1::UTINYINT)` | `255`, a `UTINYINT` |
| A `UTINYINT` 0 minus 1 | Error: `Overflow in subtraction of UINT8 (0 - 1)!` |
| `-((-128)::TINYINT)` | Error: `Overflow in negation of integer!` |
| An `INTEGER` result below -2147483648 | Error: `Overflow in subtraction of INT32 (&#x2026;)!` |
| A string such as `&apos;12&apos; - opening` | Error: `Could not choose a best candidate function for the function call &quot;-(STRING_LITERAL, INTEGER)&quot;` |
| A boolean, `BLOB` or list | Error: `No function matches the given name and argument types` |
| `SELECT --5` | Error: a syntax error, because `--` starts a comment |

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-subtraction#set-up-the-examples):

```
run("SELECT NULL - 1, 1 - NULL, subtract(7, 2), subtract(-5), - -5, -2 ** 2")
run("SELECT 'inf'::DOUBLE - 'inf'::DOUBLE, signbit(-(0.0::DOUBLE)), -(1::UTINYINT), typeof(-(1::UTINYINT))")
run(f"SELECT bins - CAST(1 AS UTINYINT) FROM {TABLE} WHERE id = 2")
run("SELECT -((-128)::TINYINT)")
run(f"SELECT closing - 2147483647 - 2 FROM {TABLE} WHERE id = 3")
run(f"SELECT '12' - opening FROM {TABLE} WHERE id = 1")
run("SELECT true - 1")
run("SELECT --5")
```

```
[(None, None, 5, 5, 5, 4.0)]
[(nan, True, 255, 'UTINYINT')]
OSError: Overflow in subtraction of UINT8 (0 - 1)!
OSError: Overflow in negation of integer!
OSError: Overflow in subtraction of INT32 (-2147483647 - 2)!
InternalError: Binder Error: Could not choose a best candidate function for the function call "-(STRING_LITERAL, INTEGER)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '-(BOOLEAN, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Parse query: 'SELECT --5' failed with: Parser Error: SELECT clause without selection list
```

## Examples

The change in stock, the margin, the temperature negated, and the same change with `subtract()`:

```
run(f"SELECT id, closing - opening, price - cost, -temp, subtract(closing, opening) FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, -25, Decimal('7.49'), -21.5, -25), (2, 12, Decimal('-0.50'), 3.25, 12), (3, 0, Decimal('0.00'), -0.0, 0), (4, None, None, None, None)]
```

The types of the results, including a `BIGINT` minus an `INTEGER`:

```
run(f"SELECT typeof(closing - opening), typeof(price - cost), typeof(-temp), typeof(id - opening) FROM {TABLE} WHERE id = 1")
```

```
[('INTEGER', 'DECIMAL(11,2)', 'DOUBLE', 'BIGINT')]
```

Three days before each shipping date:

```
run(f"SELECT id, shipped - 3 FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, datetime.date(2026, 10, 13)), (2, datetime.date(2026, 9, 28)), (3, datetime.date(2025, 12, 29)), (4, None)]
```

Keep only the products whose stock went down:

```
run(f"SELECT id, opening, closing FROM {TABLE} WHERE closing - opening < 0 ORDER BY id LIMIT 10")
```

```
[(1, 120, 95)]
```

## 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 arithmetic on two columns, so `closing - opening` is computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the columns and the Query Engine computes the difference 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 difference 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:

```
plan(f"SELECT id FROM {TABLE} WHERE closing - opening < 0")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((closing - opening) < 0) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          closing          │
│          opening          │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Subtracting a constant from an integer column is pushed down.** `closing - 5 &gt; 50`, compared with a constant, is evaluated in VAST DataBase: its plan is only a scan, with no `FILTER` step:

```
plan(f"SELECT id FROM {TABLE} WHERE closing - 5 > 50")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

- **Don't negate the column.** `-closing &lt; -50` isn't rewritten: it runs in a `FILTER` step. Write `closing &gt; 50` instead:

```
plan(f"SELECT id FROM {TABLE} WHERE -closing < -50")
plan(f"SELECT id FROM {TABLE} WHERE closing > 50")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (-(closing) < -50)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          closing          │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

- **Store a difference you filter on often.** A difference of two columns always runs in the Query Engine. If you often filter on `closing - opening`, compute it when you write the rows and keep it in its own column.
- **Combine it with a pushed-down condition.** In `id &gt; 100 AND closing - opening &lt; 0`, `id &gt; 100` is still evaluated in VAST DataBase, so the subtraction runs only on the rows that pass it.

## Limitations and errors

- **Integer overflow stops the query.** A result outside the range of its integer type fails with `Overflow in subtraction of &#x2026;!` (or `Overflow in negation of integer!` for `-a`), and one such row cancels the whole query. Cast to a wider type first: `CAST(closing AS BIGINT) - &#x2026;`.
- **Unsigned types can't go below 0.** Subtraction on `UTINYINT`, `USMALLINT`, `UINTEGER` or `UBIGINT` fails when the result would be negative. Cast to a signed type first. Negation doesn't fail; it wraps around (see [What it does](/develop/docs/dev-database-reference-sql-functions-operators-subtraction#what-it-does)).
- **No strings.** `&apos;12&apos; - opening` fails with `Could not choose a best candidate function`. Cast the string first: `CAST(&apos;12&apos; AS INTEGER) - opening`.
- **Booleans, `BLOB` values and lists** fail with `No function matches the given name and argument types`.
- **`TIMESTAMP WITH TIME ZONE`** values can't be subtracted; see [date and time](/develop/docs/dev-database-reference-sql-functions-date-time).

```
run(f"SELECT CAST(closing AS BIGINT) - 2147483647 - 2 FROM {TABLE} WHERE id = 3")
run(f"SELECT CAST(bins AS INTEGER) - 1 FROM {TABLE} WHERE id = 2")
run(f"SELECT CAST('12' AS INTEGER) - opening FROM {TABLE} WHERE id = 1")
```

```
[(-2147483649,)]
[(-1,)]
[(-108,)]
```

## Similar functions

| Function | Does | Result for `7` and `2` |
| --- | --- | --- |
| `a - b`, `subtract(a, b)` | the difference | `5` |
| `-a`, `subtract(a)` | the sign changed | `-7` for `7` |
| [+ (addition)](/develop/docs/dev-database-reference-sql-functions-operators-addition) | the sum | `9` |
| [abs](/develop/docs/dev-database-reference-sql-functions-numeric-abs) | the distance between two values, as `abs(b - a)` | `5` for `abs(2 - 7)` |
| [Date and time arithmetic](/develop/docs/dev-database-reference-sql-functions-date-time) | `DATE - INTEGER`, `DATE - DATE`, `TIMESTAMP - INTERVAL` |  |

```
run("SELECT 7 - 2, subtract(7, 2), -7, subtract(7), 7 + 2, abs(2 - 7)")
```

```
[(5, 5, -7, -7, 9, 5)]
```

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.
- [+ (addition)](/develop/docs/dev-database-reference-sql-functions-operators-addition), [* (multiplication)](/develop/docs/dev-database-reference-sql-functions-operators-multiplication) and [abs](/develop/docs/dev-database-reference-sql-functions-numeric-abs).
- [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.