I have a SQLite database with a table named `measurements`

, which contains some experimental data measured by a power meter. They’re stored as data type `REAL`

in a field named `power_dbm`

. In some measurements, the readings can be off the scale, the meter cannot obtain a reading due to overrange or underrange, thus the values `+Inf`

and `-Inf`

are used to represent these values.

For example, here is some of the data.

sqlite> SELECT power_dbm, typeof(power_dbm) from measurements; -10.312|real -9.908|real -11.344|real -Inf|real

As you see, there’s a floating-point number of negative infinity. Unfortunate I don’t know the correct syntax to select these values from the database. I tried:

sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm=-Inf; Error: no such column: Inf sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm='-Inf' (nothing returned) sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm='-infinity'; (nothing returned) sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm=-1/0; (nothing returned) sqlite> SELECT freq_hz, power_dbm from measurements WHERE power_dbm is -1/0; (incorrect results returned, power_dbm is null instead of negative infinity)

What is the correct syntax to specify the literal value of positive or negative infinity in SQL/SQLite? Any additional references to relevant documentation will also greatly be appreciated.

## Advertisement

## Answer

### Solution 1: Use an impossibly-huge floating-point literal

I found writing an impossibly-huge floating-point literal such as `9e999`

is one way to construct `+Inf`

/`-Inf`

.

sqlite> SELECT 9e999; Inf sqlite> SELECT -9e999; -Inf sqlite> SELECT freq_hz, power_dbm, typeof(power_dbm) FROM measurements ...> WHERE power_dbm=-9e999; 370000000|-Inf|real 440000000|-Inf|real 510000000|-Inf|real 580000000|-Inf|real

### Solution 2: Use `ieee754()`

with hardcoded exponent/significand for Infinity

You can construct `+Inf`

and `-Inf`

manually from an exponent and significand in the IEEE 754 representation via function `ieee754()`

. This is the original solution I used before I found the `9e999`

trick…

sqlite> SELECT ieee754(4503599627370496, 972); Inf sqlite> SELECT ieee754(-4503599627370496, 972); -Inf sqlite> SELECT freq_hz, power_dbm, typeof(power_dbm) FROM measurements ...> WHERE power_dbm=ieee754(-4503599627370496, 972); 370000000|-Inf|real 440000000|-Inf|real 510000000|-Inf|real 580000000|-Inf|real

The detailed usage of `ieee754()`

and other related functions is documented in Floating Point Numbers.

### Solution 3: Use Parameterized SQL queries

Using `ieee754(-4503599627370496, 972)`

feels like a more rigorous construction than a random value like `9e999`

, but `9e999`

is adequate for typing commands in a console by hand when all you want is having a quick look of the data, so correctness is not a real concern.

In an actual program, one would be using parameterized SQL queries instead of handling strings anyway, simply passing the native `Infinity`

constant (e.g. Python’s `math.inf`

) in your programming language is sufficient.

$ python3 Python 3.9.9 (main, Nov 19 2021, 00:00:00) [GCC 10.3.1 20210422 (Red Hat 10.3.1-1)] on linux Type "help", "copyright", "credits" or "license" for more information. >>> import math >>> import sqlite3 >>> DB_FILE = "./data.db" >>> con = sqlite3.connect(DB_FILE) >>> cur = con.cursor() >>> cur.execute( ... "SELECT power_dbm from measurements " ... "WHERE power_dbm=?", (-math.inf,)).fetchall() [(-inf,), (-inf,), (-inf,), (-inf,)]