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 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.
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
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 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
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
ieee754(-4503599627370496, 972) feels like a more rigorous construction than a random value like
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,)]