What have you defined the column as - text?

What is your application putting the data in as? numerically or textually?

If you have a database with a table called 'table1' and add a field as text, and add '11' to it both as numbers and text, you can only pull back one record.

You get the other record if you search for 11.

So, it doesn't matter how they're indexed. The index takes care of the index. Search for 11 and it'll be in the index. Search for '11' and it'll be in the index. My point is, you can't search for 11 and get both records, so the records are independent.

As long as you only need to distinguish text from numbers, a single index will do:

sqlite> create table foo (fooid integer primary key, fooval any not null unique) strict;
sqlite> insert into foo (fooval) values('1.0');
sqlite> insert into foo (fooval) values(1.0);
sqlite> insert into foo (fooval) values('1');
sqlite> insert into foo (fooval) values(1);
Runtime error: UNIQUE constraint failed: foo.fooval (19)
sqlite> select * from foo where fooval='1.0';
+-------+--------+
| fooid | fooval |
+-------+--------+
| 1     | '1.0'  |
+-------+--------+
sqlite> select * from foo where fooval=1.0;
+-------+--------+
| fooid | fooval |
+-------+--------+
| 2     | 1.0    |
+-------+--------+
sqlite> 

My understanding is that I should probably define my data column as BLOB or NUMERIC. NUMERIC does type casting as opposed to BLOB, which mean that '11' is stored as integer. However, if I have a text literal containing a sufficiently long sequence of digits, it is converted to REAL, losing some of the information. For the BLOB column such conversion does not take place.

Now, from https://sqlite.org/datatype3.html#comparison_expressions and the following sections I understand that I can have a single index, which will properly work for both numeric and textual queries as if the column only had data type values relevant to the query.

I believe you can simply put your content in the database and then query for it and everything will work fine.

CREATE TABLE t1(a INTEGER PRIMARY KEY, data BLOB);
CREATE INDEX t1data ON t1(data);

You don't need special indexes. You don't need to do anything special in your queries. It will just work.

Don't overthink this.

Thank you.