Menu
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?
915
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)?
Boolean values are not actually stored in Boolean variables as the words “true” or “false”. Instead, they are stored as integers: true becomes the integer 1, and false becomes the integer 0. Similarly, when Boolean values are evaluated, they don't actually evaluate to “true” or “false”.
SQL Server BooleanThere is no boolean data type in SQL Server. However, a common option is to use the BIT data type. A BIT data type is used to store bit values from 1 to 64. So, a BIT field can be used for booleans, providing 1 for TRUE and 0 for FALSE.
Boolean data types can be used to store the values true and false in a database. Booleans are most commonly used in databases to represent yes/no, on/off, or other related states.
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).
115
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
