defining a "VARIANT" column type in SQLite?

Question

Is there a way to define a column type in SQLite that holds any datatype that SQLite supports?

I have unsigned chars, signedchars, unsigned ints, signed ints, UTF-8 chars and blobs that I want to populate in a single column.

Reference to any literature is also appreciated.

Answer 1

Just don't put a type in the column declaration, so it has NONE affinity

create table my_table(my_col);

SQLite has a unique dynamic typing system. It has per-value typing, but if you specify a column type, SQLite will determine a type affinity (TEXT, NUMERIC, INTEGER, REAL, NONE). It then attempts to coerce each value to that affinity.

The actual supported types are NULL, INTEGER, REAL, TEXT, BLOB. See Datatypes in SQLite Version 3 for more information.

Answer 2

Variant is the default in sqlite, the reverse is harder. It is difficult to define a column that can only tolerate numbers and will throw an exception when you want to insert "this is text".

You can assert that a column can only store integers with a check constraint:

CHECK(typeof(x)='integer')