CODEWITHSUNDEEP
typessqlite
Vishnu Pedireddi
Vishnu Pedireddi

Reputation: 2192

defining a "VARIANT" column type in SQLite?

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.

Upvotes: 1

Views: 2265

Answers (2)

TTT
TTT

Reputation: 2375

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')

Upvotes: 3

Matthew Flaschen
Matthew Flaschen

Reputation: 284796

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.

Upvotes: 8

Related Questions