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,)]