Sqlite pitfall

i wanted to store a set of prime numbers together with other data. So i turned to my database of choice, sqlite3. Since sqlite has a tendency to automatically convert stuff into other stuff (flexible typing), i set the type for the prime explicitly to TEXT, because (quoted from flexible typing)

Note that an INTEGER or REAL value will never end up being stored in a TEXT column, since an INTEGER or REAL value can and always will be converted into its equivalent TEXT representation.

So:

CREATE TABLE IF NOT EXISTS
primes (
    id INTEGER PRIMARY KEY,
	prime TEXT UNIQUE ON CONFLICT IGNORE,
	T INTEGER,
	k FLOAT);

INSERT INTO PRIMES (prime, T, k) VALUES (
736967235538117954978726019337077632830443,
1234,
0.5);

SELECT * FROM primes;

But this unexpectedly returns

1|7.36967235538118e+41|1234|0.5

So the INSERT silently (and contrarily to the documentation) converts the prime into a boring, inexact float.

The fault lies partly with me, since it should have been

INSERT INTO PRIMES (prime, T, k) VALUES (
'736967235538117954978726019337077632830443',
1234,
0.5);

(notice the quotes around the prime). But i would have expected an error when inserting an unquoted series of digits into a TEXT column.

It is possible (but not well documented ) to have sqlite3 throw errors in this case, by adding a (non-SQL-standard) STRICT at the end of the CREATE TABLE statement (as in “I put types on this table, and this time I really mean it”).

Tue, 04 Nov 2025
[/osfail] permanent link