What is the difference between related SQLite data-types like INT, INTEGER, SMALLINT and TINYINT?

Question

When creating a table in SQLite3, I get confused when confronted with all the possible datatypes which imply similar contents, so could anyone tell me the difference between the following data-types?

INT, INTEGER, SMALLINT, TINYINT  DEC, DECIMAL  LONGCHAR, LONGVARCHAR  DATETIME, SMALLDATETIME 

Is there some documentation somewhere which lists the min./max. capacities of the various data-types? For example, I guess smallint holds a larger maximum value than tinyint, but a smaller value than integer, but I have no idea of what these capacities are.

Answer 1

SQLite, technically, has no data types, there are storage classes in a manifest typing system, and yeah, it's confusing if you're used to traditional RDBMSes. Everything, internally, is stored as text. Data types are coerced/converted into various storage locations based on affinities (ala data types assigned to columns).

The best thing that I'd recommend you do is to :

1. Temporarily forget everything you used to know about standalone database datatypes

2. Read the above link from the SQLite site.

3. Take the types based off of your old schema, and see what they'd map to in SQLite

4. Migrate all the data to the SQLite database.

Note: The datatype limitations can be cumbersome, especially if you add time durations, or dates, or things of that nature in SQL. SQLite has very few built-in functions for that sort of thing. However, SQLite does provide an easy way for you to make your own built-in functions for adding time durations and things of that nature, through the sqlite3_create_function library function. You would use that facility in place of traditional stored procedures.