Is there a boolean literal in SQLite?

Question

I know about the boolean column type, but is there a boolean literal in SQLite? In other languages, this might be true or false. Obviously, I can use 0 and 1, but I tend to avoid so-called "magic numbers" where possible.

From this list, it seems like it might exist in other SQL implementations, but not SQLite. (I'm using SQLite 3.6.10, for what it's worth.)

Answer 1

SQLite doesn't have Boolean type, you should use INTEGER with 0 is false and 1 is true

Answer 2

1.1 Boolean Datatype

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

Docs

Answer 3

There are only 5 datatypes supported in SQLite3.

From the Official SQLite3 doc. "Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

NULL. The value is a NULL value.

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value."

If you are going to store 1s and 0s, then SQLite wil use 1 byte if storage. Which is not bad. Official Doc link :- http://www.sqlite.org/datatype3.html

Answer 4

BOOLEAN -> NUMERIC (Affinity)

Column Affinity

SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities: Affinity Description

• TEXT This column stores all data using storage classes NULL, TEXT or BLOB.
• NUMERIC This column may contain values using all five storage classes.
• INTEGER Behaves the same as a column with NUMERIC affinity with an exception in a CAST expression.
• REAL Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation
• NONE A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

Boolean Datatype:

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

@moiing-duck "On the flip side, SQLite supports datetimes, despite not being one of those five types, so this answer is inconclusive"

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

Answer 5

From section 1.1 Boolean Datatype of the docs:

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

So it looks like you are stuck with 0 and 1.

Answer 6

There is no boolean data type. There are only 5 types, listed here. Integers can be stored with various widths on disk, the smallest being 1 byte. However, this is an implementation detail:

"But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer)."

Given that, it is not surprising there are no boolean literals.