SQLite is typeless?

Question

If sqlite is typeless, so why we declare the datatype of the column ? what's the use of the declare datatype?

Why android SQLite can store double value(java 8 bytes) into float column

Answer 1

The declared column data type is used as a hint (http://sqlite.org/faq.html#q3):

So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string. This feature is called type affinity.

By the way, SQLite is not typeless; it uses dynamic typing. This means that any value that you insert maintains its own type information, instead of inferring the type from the table declaration. http://sqlite.org/datatype3.html

Answer 2

It's used to determine the "affinity" of the column.

CREATE TABLE Example (
    IntegerAffinity    INTEGER,
    RealAffinitity     REAL,
    NumericAffinity    NUMERIC,
    TextAffinity       TEXT,
    NoAffinity
);

Inserting some values into this table (with Python's sqlite3 module) gives):

>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [1234] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", ['1234'] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [math.pi] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [str(math.pi)] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", ['ABC'] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [b'5678'] * 5)
>>> db.execute("INSERT INTO Example VALUES(?, ?, ?, ?, ?)", [b'\xAB\xCD\xEF'] * 5)
>>> list(db.execute("SELECT * FROM Example"))
[(1234, 1234.0, 1234, '1234', 1234),
 (1234, 1234.0, 1234, '1234', '1234'),
 (3.141592653589793, 3.141592653589793, 3.141592653589793, '3.14159265358979', 3.141592653589793),
 (3.141592653589793, 3.141592653589793, 3.141592653589793, '3.141592653589793', '3.141592653589793'),
 ('ABC', 'ABC', 'ABC', 'ABC', 'ABC'),
 (b'5678', b'5678', b'5678', b'5678', b'5678'),
 (b'\xab\xcd\xef', b'\xab\xcd\xef', b'\xab\xcd\xef', b'\xab\xcd\xef', b'\xab\xcd\xef')]

• In text-affinity columns, numbers get converted to strings.
• In integer-, real-, and numeric-affinity columns, strings that contain numbers get converted to numbers.
• Strings that do not contain numbers remain strings, even in numeric columns.
• Blobs remain blobs, regardless of the column affinity.
• In columns with NONE affinity, values are inserted as-is.