Declaring data types in SQLite
- by dan04
I'm familiar with how type affinity works in SQLite: You can declare column types as anything you want, and all that matters is whether the type name contains "INT", "CHAR", "FLOA", etc. But is there a commonly-used convention on what type names to use?
For example, if you have an integer column, is it better to distinguish between TINYINT, SMALLINT, MEDIUMINT, and BIGINT, or just declare everything as INTEGER?
So far, I've been using the following:
INTEGER
REAL
CHAR(n) -- for strings with a known fixed with
VARCHAR(n) -- for strings with a known maximum width
TEXT -- for all other strings
BLOB
BOOLEAN
DATE -- string in "YYYY-MM-DD" format
TIME -- string in "HH:MM:SS" format
TIMESTAMP -- string in "YYYY-MM-DD HH:MM:SS" format
(Note that the last three are contrary to the type affinity.)
