SQLITE Custom DataTypes?

Question

I am fairly new to SQLITE and I noticed that there are only a 4 datatypes but I see examples online where people are putting in their own datatypes. I don't really understand this and was wondering if someone could explain that to me. For example I see a column that will hold a date and the datatype that was given was timestamp which does not exist. What does it default to? Does it default to a text when you make your own?

Answer 1

sqlite3 uses a dynamic type system. There are only five storage classes: NULL, integer, real, text and blob. (Source: Datatypes In SQLite Version 3.)

And, to quote that page:

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

Apart from the integer primary key exception, SQLite doesn't enforce types at all. Which means that the type name you put in your create table is purely informative.

create table mytab (a apples, b bananas);

is a valid create table statement. You can insert timestamps, text, blobs into both columns (not saying that you should, but you can).

Look at the linked reference documentation for the type system for more information.

sqlite> create table mytab (a apples, b bananas);
sqlite> insert into mytab values (CURRENT_TIME, NULL);
sqlite> insert into mytab values ('hello', 3.14159);
sqlite> select * from mytab;
14:59:18|
hello|3.14159

To answer your question directly: there is no default. A storage type is associated with each value stored in the database, not to columns of a table.