Detect Infinite Values in DuckDB with the ISINF() Function

In DuckDB, we can use the ISINF() function to check whether a value is finite. DuckDB supports infinite values, and so this function allows us to check for that. This can be useful when working with floating-point data that might contain special values like NaN (Not a Number) or infinity. The function also works on date and timestamp values, as they can be infinite too.

Syntax

The ISINF() function can be used in the following contexts:

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

So the value we pass can be a numeric or date/timestamp value. The function returns a Boolean value indicating whether it’s true or false (i.e. finite or not). This can help identify problematic values in our datasets that might affect calculations.

Examples with Numeric Values

The ISINF() function returns true if a value is infinity, negative infinity, or NaN. It returns false if it’s just a regular number.

Example:

SELECT ISINF(37);

Output:

+-----------+
| isinf(37) |
+-----------+
| false     |
+-----------+

In this case I passed a regular number, and so ISINF() returned false.

Here it is with some other values:

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

Output:

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

We can see that the columns that contain infinity, -infinity, and NaN all return true, which means that they are indeed infinite. The first two columns contain regular numbers and so they’re finite (i.e. they’re not infinite).

The NULL column returns null. In this example I used .nullvalue 'null' so that the string null is returned whenever a NULL value is returned.

Examples with Date/Timestamp Values

As mentioned, ISINF() can be used with various date and timestamp values too. Here are some examples:

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

Output:

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

Using the Wrong Data Type

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

SELECT ISINF('Bank Balance');

Output:

Binder Error: Could not choose a best candidate function for the function call "isinf(STRING_LITERAL)". In order to select one, please add explicit type casts.
	Candidate functions:
	isinf(DOUBLE) -> BOOLEAN
	isinf(DATE) -> BOOLEAN
	isinf(TIMESTAMP) -> BOOLEAN
	isinf(TIMESTAMP WITH TIME ZONE) -> BOOLEAN
	isinf(FLOAT) -> BOOLEAN

LINE 1: SELECT ISINF('Bank Balance');

The error message shows us which data types are allowed.

Checking for Finite Values: ISFINITE()

DuckDB also provides us with an ISFINITE() function that allows us to check for finite values. So basically, it’s the inverse of ISINF(); it returns true if the value is finite and false otherwise. Other than that, its syntax is the same as the ISINF() function’s syntax.