sqlitesqldatatypes

How can I store a very big integer as a string in SQLite3 to an existing column with INTEGER affinity?

The problem: loss of accuracy

Using sqlite3 :memory: (the command-line tool) to explain the issue.

There is an existing table with a column v that has INTEGER affinity.
Here I will create a dummy one for the question's purposes.

CREATE TABLE t(i INTEGER PRIMARY KEY, v INTEGER NOT NULL);

Then some attempts:

INSERT INTO t(v) VALUES (8712347686782346789123467821348);
INSERT INTO t(v) VALUES ('text is fine');
INSERT INTO t(v) VALUES ('8712347686782346789123467821348');

SELECT * FROM t;
1|8.71234768678235e+30
2|text is fine
3|8.71234768678235e+30

Store the value as a BLOB: success

There is a way to force a BLOB-typed value:

INSERT INTO t(v) VALUES (UNHEX(HEX('8712347686782346789123467821348')));

SELECT * FROM t ORDER BY i DESC LIMIT 2;
4|8712347686782346789123467821348
3|8.71234768678235e+30

This also works with:

INSERT INTO t(v) VALUES (CAST('8712347686782346789123467821348' AS BLOB));

Store the value as TEXT: failure

This does not work:

INSERT INTO t(v) VALUES (CAST('8712347686782346789123467821348' AS TEXT));

Is there a way to force a TEXT-typed value?

Solution

  • My question still stands if it's possible to store the digits as TEXT just like it can be done as BLOB.

    No, rather you have to force force/convert the value (prefix/suffix or even an intermediate value) to be considered as TEXT (String). This is because SQLite determines the value that is stored and tries to store it as efficiently as possible (at least storage space wise).

    e.g. INSERT INTO t(v) VALUES ('M8712347686782346789123467821348'); where the M prefix (in this case) forces that value to be treated as a TEXT (String) value.

    Unless using a STRICT table (relatively new) the affinity is more an indication of the type of data (see the link below for affinity handling).

    So any purely numeric value will be stored (if the column affinity is INTEGER, again see the link for more comprehensive affinity handling) as either an integer value (64 bit signed) or as a real value (in the case of 8712347686782346789123467821348 it is too large to be stored as an integer so is stored as a real/float) as this is more efficient storage wise BUT may(is) be inaccurate due to precision.

    The following links may be of interest as they are far more comprehensive than this answer:-

    1. Data Types in SQLite
    2. The Advantages of Flexible Types