Store boolean value in SQLite

Question

What is the type for a BOOL value in SQLite? I want to store in my table TRUE/FALSE values.

I could create a column of INTEGER and store in it values 0 or 1, but it won't be the best way to implement BOOL type.

Is there a way?

Answer 1

There is no native boolean data type for SQLite. Per the Datatypes doc:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Answer 2

In SQLite the best you can do is use the integers 0 and 1 to represent false and true. You could declare the column type like this:

CREATE TABLE foo(mycolumn BOOLEAN NOT NULL CHECK (mycolumn IN (0, 1))); 

Omit the NOT NULL if you want to allow NULL in addition to 0 and 1.

The use of the type name BOOLEAN here is for readability, to SQLite it's just a type with NUMERIC affinity.

Note that CHECK constraints have been supported since SQLite 3.3.0 (2006).

Here are some example INSERTs that will work: (note how strings and floating point numbers are parsed as integers)

sqlite> INSERT INTO foo VALUES(0); sqlite> INSERT INTO foo VALUES(1); sqlite> INSERT INTO foo VALUES(0.0); sqlite> INSERT INTO foo VALUES(1.0); sqlite> INSERT INTO foo VALUES("0.0"); sqlite> INSERT INTO foo VALUES("1.0"); sqlite> select mycolumn, typeof(mycolumn) from foo; 0|integer 1|integer 0|integer 1|integer 0|integer 1|integer 

and some that will fail:

sqlite> INSERT INTO foo VALUES("-1"); Error: constraint failed sqlite> INSERT INTO foo VALUES(0.24); Error: constraint failed sqlite> INSERT INTO foo VALUES(100); Error: constraint failed sqlite> INSERT INTO foo VALUES(NULL); Error: foo.mycolumn may not be NULL sqlite> INSERT INTO foo VALUES("true"); Error: constraint failed sqlite> INSERT INTO foo VALUES("false"); Error: constraint failed