Boolean columns in SQLite are columns that can only contain two values: true or false. In SQLite these values are represented by the integers 1 and 0, respectively.
👋 Check out our easy to use desktop GUI for SQL
Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
Boolean columns are often used to store data that has a binary nature, such as the gender of a person (male or female) or the status of a task (completed or not completed).
Creating a BOOLEAN Column
To create a Boolean column in SQLite, you would use the following syntax:
CREATE TABLE table_name ( column_name BOOLEAN );
Inserting into a BOOLEAN column
Once the table is created, you can insert data into the Boolean column using the INSERT INTO statement. Unlike with databases such as PostgreSQL or MySQL we must use
0 integer values. For example:
INSERT INTO table_name (column_name) VALUES (1);
This would insert the value
true (represented by the integer 1) into the Boolean column. You can also use the INSERT INTO statement to insert the value false (represented by the integer 0) into the column.
Filtering by True or False
To retrieve data from a Boolean column, you can use the SELECT statement. For example:
SELECT * FROM table_name WHERE column_name = 1;
This would return all rows from the table where the value in the Boolean column is true (represented by the integer 1). You can also use the SELECT statement to retrieve rows where the value in the Boolean column is false (represented by the integer 0).
Using Booleans To Manipulate Data
In addition to using the SELECT statement to retrieve data from a Boolean column, you can also use it to manipulate the data. For example, you can use the UPDATE statement to change the value in a Boolean column from true to false or vice versa. You can also use the DELETE statement to remove rows from the table where the value in the Boolean column is true or false.
SQLite Boolean Column Summary
In conclusion, Boolean columns in SQLite are useful for storing data that has a binary nature. They can be created using the CREATE TABLE statement, and data can be inserted, retrieved, and manipulated using a variety of SQLite statements, such as SELECT, INSERT INTO, UPDATE, and DELETE. However unlike in other databases, when you perform such actions you will need to use
0 rather than