5

I'm a database novice looking at an SQLite database which appears to be storing text in an integer column. Here's an example session at the sqlite3 command line:

sqlite> .schema mytable
CREATE TABLE mytable (
    id              integer primary key,   /* 0 */
    mycol           integer not null,      /* 1 */
);

sqlite> SELECT mycol FROM mytable;
here is some text
here is more text
[...]
it's text all the way down

I'm confused. What gives?

9

This is a well known "quirk" of SQLite.

SQLite uses what it calls a dynamic typing system, which ultimately means that you can store text in integer fields - in Oracle, SQL Server and all the other big hitters in the database world, attempts to do this will fail - not with SQLite.

Take a look here:

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.

The advantages of this system are outlined here.

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,

I think the point of SQLite is a bit like the quote about the C programming language -

"C allows you to do very stupid things because it also allows you to do very clever ones."

Same goes for SQLite.

Check out the entire StackOverflow thread referenced above.

The datatype in SQLite is more of a "hint" than a command.

| improve this answer | |
  • 1
    Note that if you need strict typing, you can always add a constraint like CHECK(typeof(mycol) = 'integer'). – dan04 Jul 14 '15 at 23:08
  • The magic happens when you sort on an sqlite field that has been declared as integer yet has integers and text stored in it ...it sorts properly! sorting the numbers first in numerical order and then the Alpha in alpha order....superb!!! – Steve Day Mar 25 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.