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

User · Answer

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

User · Answer

using the Integer data type with values 0 and 1 is the fastest

User · Answer

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

User · Answer

Flask with SQLAlchemy has boolean  https   overiq com flask-101 database-modelling-in-flask

User · Answer

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     gt  0    misc    1  if  has inp  stereo  true     gt  0    misc   2  if  has inp  ei  true     gt  0    misc   4  if  has inp  closeCaptioned  true     gt  0    misc    8  if  has inp  dolby     gt  0    misc    16  if  has inp  new  true     gt  0    misc    32  if  has inp  premier finale      gt  0    misc    64  if  has inp  hdtv  true     gt  0    misc    128    So I m storing 7 booleans in one integer with room for more

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sqlite] Store boolean value in SQLite

Examples related to sqlite