SQLite data-types

Question

Hi there,

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.

Any help would be appreciated.

Alan Harris-Reid

Answer 1

SQLite, technically, has no data types, there are storage classes in a manifest typing system, and yeah, it's confusing if your'e 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.

Answer 2

Most of those are there for compatibility. You really only have integer, float, text, and blob. Dates can be stored as either a number (unix time is integer, microsoft time is float) or as text.

Answer 3

The difference is syntactic sugar. Only a few substrings of the type names matter as for as the type affinity is concerned.

• INT, INTEGER, SMALLINT, TINYINT → INTEGER affinity, because they all contain "INT".
• LONGCHAR, LONGVARCHAR → TEXT affinity, because they contain "CHAR".
• DEC, DECIMAL, DATETIME, SMALLDATETIME → NUMERIC, because they don't contain any of the substrings that matter.

The rules for determining affinity are listed at the SQLite site.

If you insist on strict typing, you can implement it with CHECK constraints:

CREATE TABLE T (
   N   INTEGER CHECK(TYPEOF(N) = 'integer'),
   Str TEXT CHECK(TYPEOF(Str) = 'text'),
   Dt  DATETIME CHECK(JULIANDAY(Dt) IS NOT NULL)
);

But I never bother with it.

As for the capacity of each type:

• INTEGER is always signed 64-bit. Note that SQLite optimizes the storage of small integers behind-the-scenes, so TINYINT wouldn't be useful anyway.
• REAL is always 64-bit (double).
• TEXT and BLOB have a maximum size determined by a preprocessor macro, which defaults to 1,000,000,000 bytes.