In DuckDB, ISFINITE() is a function for checking whether values are finite. DuckDB supports infinite values, and so we can use this function to check whether a value is infinite or not. This can be useful when working with floating-point data that might contain special values like NaN (Not a Number) or infinity. We can also use it on date and timestamp values.

Syntax

The following shows how we can use this function:

isfinite(FLOAT) -> BOOLEAN
isfinite(DOUBLE) -> BOOLEAN
isfinite(DATE) -> BOOLEAN
isfinite(TIMESTAMP) -> BOOLEAN
isfinite(TIMESTAMP WITH TIME ZONE) -> BOOLEAN

So it accepts various numeric and date/timestamp values, and it returns a Boolean indicating whether it’s true or false (i.e. finite or not).

Examples with Numeric Values

The ISFINITE() function returns true if a value is a regular number (not infinity, negative infinity, or NaN), and false otherwise.

Example:

SELECT ISFINITE(42);

Output:

+--------------+
| isfinite(42) |
+--------------+
| true         |
+--------------+

Here it is with some other values:

.nullvalue 'null'
SELECT 
    ISFINITE(42) AS '42',
    ISFINITE(3.14) AS '3.14',
    ISFINITE('infinity'::float) AS 'infinity',
    ISFINITE('-infinity'::float) AS '-infinity',
    ISFINITE('NaN'::float) AS 'NaN',
    ISFINITE(NULL) AS 'NULL';

Output:

+------+------+----------+-----------+-------+------+
|  42  | 3.14 | infinity | -infinity |  NaN  | NULL |
+------+------+----------+-----------+-------+------+
| true | true | false    | false     | false | null |
+------+------+----------+-----------+-------+------+

Examples with Date/Timestamp Values

Here’s an example that shows ISFINITE() being used with various date and timestamp values:

SELECT 
    ISFINITE(date '2001-01-01') AS '2001-01-01',
    ISFINITE(timestamp '2001-01-01 12:54:12') AS '2001-01-01 12:54:12',
    ISFINITE(timestamp with time zone '2001-01-01 12:54:12+01:30') AS '2001-01-01 12:54:12+01:30',
    ISFINITE(date 'infinity') AS 'infinity',
    ISFINITE(date '-infinity') AS '-infinity';

Output:

+------------+---------------------+---------------------------+----------+-----------+
| 2001-01-01 | 2001-01-01 12:54:12 | 2001-01-01 12:54:12+01:30 | infinity | -infinity |
+------------+---------------------+---------------------------+----------+-----------+
| true       | true                | true                      | false    | false     |
+------------+---------------------+---------------------------+----------+-----------+

Using the Wrong Data Type

Passing the wrong data type to the INFINITE() function results in an error:

SELECT ISFINITE('Bruce');

Output:

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.
	Candidate functions:
	isfinite(DOUBLE) -> BOOLEAN
	isfinite(DATE) -> BOOLEAN
	isfinite(TIMESTAMP) -> BOOLEAN
	isfinite(TIMESTAMP WITH TIME ZONE) -> BOOLEAN
	isfinite(FLOAT) -> BOOLEAN

LINE 1: SELECT ISFINITE('Bruce');
               ^

The error message provides us with a hint as to which data types are allowed.

Checking for Infinite Values: ISINF()

DuckDB also provides us with an ISINF() function that allows us to check for infinite values. It’s essentially the opposite of ISFINITE() in that it returns true if the value is infinite and false otherwise. Other than that, its syntax is the same as with ISFINITE().