isfinite

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Floating-point values. Returns true if x is an ordinary number, and false if it's inf, -inf or NaN. It also takes a date or timestamp, and is false for the special values 'infinity' and '-infinity'.

Syntax

isfinite(x)

Returns: BOOLEAN.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_numeric_isfinite. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), 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 seven measurements: ordinary values, both infinities, NaN, negative zero and, in row 6, no value:

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_numeric_isfinite"'

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}.measurements"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, x DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2.5), (2, 'inf'::DOUBLE), (3, '-inf'::DOUBLE), (4, 'nan'::DOUBLE), "
    "(5, '-0.0'::DOUBLE), (6, NULL), (7, 1.0)")

[]
[]
[(7,)]

What it does

isfinite(x) is true for every number you can do ordinary arithmetic with, negative zero included, and false for the three special floating-point values: inf, -inf and NaN. It's NOT isinf(x) AND NOT isnan(x) in one call. NULL gives NULL, so a WHERE isfinite(x) condition also drops the rows with no value.

Use it to keep only usable values before you aggregate: a sum of a column that holds inf and -inf is NaN, and max() of a column that holds NaN is NaN.

For a DATE or TIMESTAMP, isfinite() is false for 'infinity' and '-infinity' and true for an ordinary date. The date and time version has the details.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT isfinite(NULL), isfinite(NULL::FLOAT), isfinite(1.0::DOUBLE), isfinite(-0.0::DOUBLE), isfinite(1::FLOAT)")
run("SELECT isfinite(1), isfinite(2.45), isfinite(9223372036854775807), typeof(isfinite(1))")
run("SELECT isfinite('inf'::DOUBLE), isfinite('-inf'::DOUBLE), isfinite('nan'::DOUBLE), "
    "isfinite('nan'::FLOAT), isfinite('inf'::FLOAT)")
run("SELECT isfinite(0 / 0), isfinite(1 / 0), isfinite(-1 / 0), isfinite(1e308 * 10)")
run("SELECT isfinite(DATE '2026-10-16'), isfinite('infinity'::DATE), isfinite('infinity'::TIMESTAMP)")
run("SELECT isfinite('nan')")
run("SELECT isfinite(true)")

[(None, None, True, True, True)]
[(True, True, True, 'BOOLEAN')]
[(False, False, False, False, False)]
[(False, False, False, False)]
[(True, False, False)]
InternalError: Binder Error: Could not choose a best candidate function for the function call "isfinite(STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'isfinite(BOOLEAN)'. You might need to add explicit type casts.

Examples

Test each measurement. Row 6 has no value, so its result is NULL:

run(f"SELECT id, x, isfinite(x) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2.5, True), (2, inf, False), (3, -inf, False), (4, nan, False), (5, -0.0, True), (6, None, None), (7, 1.0, True)]

Aggregate all the values, then only the finite ones. The first sum and maximum are NaN; with WHERE isfinite(x), inf, -inf and NaN are left out:

run(f"SELECT min(x), max(x), sum(x), count(x) FROM {TABLE}")
run(f"SELECT min(x), max(x), sum(x), count(x) FROM {TABLE} WHERE isfinite(x)")

[(-inf, nan, nan, 6)]
[(-0.0, 2.5, 3.5, 3)]

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 function of a column, so isfinite() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes isfinite() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes isfinite() and keeps the rows that match.

The plan of the aggregate with WHERE isfinite(x) shows the FILTER step above the scan:

plan(f"SELECT sum(x) FROM {TABLE} WHERE isfinite(x)")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│     Aggregates: sum(x)    │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│        isfinite(x)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│       Projections: x      │
│                           │
└───────────────────────────┘

Make it faster

• Store NULL for a missing or undefined result, not inf or NaN. Aggregates skip NULLs (count(x) above doesn't count row 6), so you don't need an isfinite() filter at all.

Limitations and errors

• A string literal must be cast. isfinite('nan') fails with Could not choose a best candidate function for the function call "isfinite(STRING_LITERAL)", because the literal could be a number, a date or a timestamp. Write isfinite('nan'::DOUBLE).
• Numbers, dates and timestamps only. A BLOB, boolean, list or interval fails with No function matches the given name and argument types.
• is_finite() isn't available. It fails with Scalar Function with name is_finite does not exist!; use isfinite():

run("SELECT is_finite(1.0)")

InternalError: Catalog Error: Scalar Function with name is_finite does not exist!

Similar functions

run("SELECT x, isnan(x), isinf(x), isfinite(x), signbit(x) FROM (VALUES ('nan'::DOUBLE), ('inf'::DOUBLE), "
    "('-inf'::DOUBLE), (-0.0::DOUBLE), (1.0::DOUBLE), (NULL::DOUBLE)) v(x)")

[(nan, True, False, False, False), (inf, False, True, False, False), (-inf, False, True, False, True), (-0.0, False, False, True, True), (1.0, False, False, True, False), (None, None, None, None, None)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Numeric and math functions: isnan, isinf and isfinite.
• isfinite for dates and timestamps.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.