Store boolean value in SQLite
SqliteSqlite Problem Overview
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?
Sqlite Solutions
Solution 1 - Sqlite
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).
Solution 2 - Sqlite
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
Solution 3 - Sqlite
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.
Solution 4 - Sqlite
using the Integer data type with values 0 and 1 is the fastest.
Solution 5 - Sqlite
Further to ericwa's answer. CHECK constraints can enable a pseudo boolean column by enforcing a TEXT datatype and only allowing TRUE or FALSE case specific values e.g.
CREATE TABLE IF NOT EXISTS "boolean_test"
(
"id" INTEGER PRIMARY KEY AUTOINCREMENT
, "boolean" TEXT NOT NULL
CHECK( typeof("boolean") = "text" AND
"boolean" IN ("TRUE","FALSE")
)
);
INSERT INTO "boolean_test" ("boolean") VALUES ("TRUE");
INSERT INTO "boolean_test" ("boolean") VALUES ("FALSE");
INSERT INTO "boolean_test" ("boolean") VALUES ("TEST");
Error: CHECK constraint failed: boolean_test
INSERT INTO "boolean_test" ("boolean") VALUES ("true");
Error: CHECK constraint failed: boolean_test
INSERT INTO "boolean_test" ("boolean") VALUES ("false");
Error: CHECK constraint failed: boolean_test
INSERT INTO "boolean_test" ("boolean") VALUES (1);
Error: CHECK constraint failed: boolean_test
select * from boolean_test;
id boolean
1 TRUE
2 FALSE
Solution 6 - Sqlite
But,if you want to store a bunch of them you could bit-shift them and store them all as one int, a little like unix file permissions/modes.
For mode 755 for instance, each digit refers to a different class of users: owner, group, public. Within each digit 4 is read, 2 is write, 1 is execute so 7 is all of them like binary 111. 5 is read and execute so 101. Make up your own encoding scheme.
I'm just writing something for storing TV schedule data from Schedules Direct and I have the binary or yes/no fields: stereo, hdtv, new, ei, close captioned, dolby, sap in Spanish, season premiere. So 7 bits, or an integer with a maximum of 127. One character really.
A C example from what I'm working on now. has() is a function that returns 1 if the 2nd string is in the first one. inp is the input string to this function. misc is an unsigned char initialized to 0.
if (has(inp,"sap='Spanish'") > 0)
misc += 1;
if (has(inp,"stereo='true'") > 0)
misc +=2;
if (has(inp,"ei='true'") > 0)
misc +=4;
if (has(inp,"closeCaptioned='true'") > 0)
misc += 8;
if (has(inp,"dolby=") > 0)
misc += 16;
if (has(inp,"new='true'") > 0)
misc += 32;
if (has(inp,"premier_finale='") > 0)
misc += 64;
if (has(inp,"hdtv='true'") > 0)
misc += 128;
So I'm storing 7 booleans in one integer with room for more.
Solution 7 - Sqlite
You could simplify the above equations using the following:
boolean flag = sqlInt != 0;
If the int representation (sqlInt) of the boolean is 0 (false), the boolean (flag) will be false, otherwise it will be true.
Concise code is always nicer to work with :)
Solution 8 - Sqlite
Flask with SQLAlchemy has boolean.
Solution 9 - Sqlite
Another way to do it is a TEXT column. And then convert the boolean value between Boolean and String before/after saving/reading the value from the database.
Ex. You have "boolValue = true;
"
To String:
//convert to the string "TRUE"
string StringValue = boolValue.ToString;
And back to boolean:
//convert the string back to boolean
bool Boolvalue = Convert.ToBoolean(StringValue);