Sqlite Custom Datatypes

SQLITE Custom DataTypes?

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.

How custom data type works in SQLite

Yes; STRFTIME has exactly the same affinity as FLUFFY BUNNIES, i.e., numeric.

Sqlite3 user defined datatype

Any column with the exception of INTEGER PRIMARY KEY can hold values of any type. The specified datatype is just a hint and it is called type affinity.

Determining type affinity:

• If the declared type contains the string "INT" then it is assigned INTEGER affinity.

• If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

• If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE.

• If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.

• Otherwise, the affinity is NUMERIC.

Therefore your BLABLA column gets the NUMERIC affinity.

Further reading: https://www.sqlite.org/datatype3.html

Declaring data types in SQLite

I would recommend not using self-defined types. I have observed in version 3.5.6 that types not already defined could sometimes cause an INSERT command to be refused. Maybe 1 out of 1000. I don't know if this was addressed since.

In any case, there is no sizing advantage in typing a column TINYINT or SMALLINT. The only advantage would be outside SQLite, for either parsing your column types with another program or to satisfy your personal need for tidiness. So I strongly recommend using the base types defined by SQLite and sticking to those.

Sqlite store string value in numeric or real datatypes

SQLite uses dynamic typing i.e. the datatype of a value is associated with the value itself, not with its container. It does not enforce data type constraints. You can store data of any type in any column. Read more about this here and Datatypes in SQLite here.

Excerpt from sqlite.org

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

In your case, SQLite tried to convert the value you passed i.e. asasassas into double / floating value, but since it failed to do that, it has inserted the value as a string. This feature of SQLite is called type affinity.

Sequelize: Custom Date Format in SQLite Database

Sequelize's DATE datatype defaults to that date + time + offset format. If you have the date format that you want available to you in the database software (Either out of the box or custom-created), you can extend Sequelize's datatypes. It's relatively painless code to implement.

---

Related Topics