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

Is there a boolean literal in SQLite?

As stated in Justin Ethier's answer, SQLite does not have specific data type for boolean. But starting from SQLite 3.23.0 it supports true/false literals:

2. Recognize TRUE and FALSE as constants. (For compatibility, if there exist columns named "true" or "false", then the identifiers refer to the columns rather than Boolean constants.)

3. Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE.

SELECT true AS t, false AS f;

SELECT 'condition is true'
WHERE 1 IS NOT FALSE;

CREATE TABLE a (id INT, b BOOLEAN DEFAULT(TRUE));
INSERT INTO a(id) VALUES(100);
SELECT * FROM a;
-- id  b
-- 100 1

dbfiddle.com demo

Answer 2

The question is explicitly not about the column type (i.e storage-wise) but the use of TRUE and FALSE literals (i.e. parser-wise), which are SQL-compliant as per the PostgreSQL keywords documentation (which happens to also include SQL-92, SQL:2008 and SQL:2011 columns in the reference table).

The SQLite documentation lists all supported keywords, and this list contains neither TRUE nor FALSE, hence SQLite sadly is non-compliant in that regard.

You can also test it easily and see how the parser barfs as it wants the token to be a column name:

$ sqlite3 :memory:
SQLite version 3.14.0 2016-07-26 15:17:14
sqlite> CREATE TABLE foo (booleanish INT);
sqlite> INSERT INTO foo (booleanish) VALUES (TRUE);
Error: no such column: TRUE

Answer 3

I noticed in sqlite for android, I can declare a Boolean column type with no error and its seems to work fine. I also tried defining the column as 'int' and storing java boolean values. I downloaded the db and confirmed I'm writing "true" in the column. I think it just works.