Store boolean value in SQLite

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?

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).

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

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

You can convert boolean to int in this way:

int flag = (boolValue)? 1 : 0;

You can convert int back to boolean as follows:

 // Select COLUMN_NAME  values from db. 
 // This will be integer value, you can convert this int value back to Boolean as follows
Boolean flag2 = (intValue == 1)? true : false;

If you want to explore sqlite, here is a tutorial.
I have given one answer here. It is working for them.

Store boolean value in SQLite

Related

Recent Posts