Is there a boolean literal in SQLite?

sqlite boolean
sqlite datetime
sqlite default value
sqlite storing boolean
sqlite cast
sqlite collate
sqlite like
sqlite escape

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.)

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.

Quirks, Caveats, and Gotchas In SQLite, The syntax for integer and floating point literals (collectively "numeric literals") is the result treated as a boolean, starting with the leftmost and continuing to the  There are no boolean literals in SQLite, instead, boolean literals are stored as numeric values. Here are some examples of valid boolean literals: Example. Explanation. 1. Equivalent to TRUE (stored as a number) 0. Equivalent to FALSE (stored as a number) NEXT: Analyze.

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:

  1. 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.)

  2. 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

SELECT * FROM a WHERE true;
-- id  b
-- 100 1

dbfiddle.com demo

SQL Language Expressions, Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQLite query that compare  In SQLite, a literal is the same as a constant. We’ll cover several types of literals – string literals, number literals, date and time literals and boolean literals.

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

Datatypes In SQLite Version 3, Boolean Literals and Tests; Window Functions; Filter Clause; Insert … on conflict (“Upsert”); Rename Column; Coming  Beginning with SQLite 3.23.0 (2018-04-02), SQLite recognizes the identifiers "TRUE" and "FALSE" as boolean literals, if and only if those identifiers are not already used for some other meaning. If there already exists columns or tables or other objects named TRUE or FALSE, then for the sake of backwards compatibility, the TRUE and FALSE identifiers refer to those other objects, not to the boolean values.

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

SQLite Boolean operator, This SQLite tutorial explains how to use literals (string, number, date, time, and boolean literals) in SQLite with examples. In SQLite, a literal is  The two syntaxes yield identical results. In many pragmas, the argument is a boolean. The boolean can be one of: 1 yes true on 0 no false off. Keyword arguments can optionally appear in quotes. (Example: 'yes' [FALSE].) Some pragmas takes a string literal as their argument.

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.

SQLite in 2018: A state of the art SQL dialect, Notice that SQLite does not provide Boolean data type therefore 1 means SQLite compares the values stored in the AlbumId column with a literal value 1 to​  SQLite only has four primitive data types: INTEGER, REAL, TEXT, and BLOB. APIs that return database values as an object will only ever return one of these four types. Additional .NET types are supported by Microsoft.Data.Sqlite, but values are ultimately coerced between these types and one of the four primitive types.

SQLite: Literals, Each column, variable and expression has related data type in SQLite. In SQLite, the datatype of a value is associated with the value itself, not with its container. Instead, Boolean values are stored as integers 0 (false) and 1 (true). Boolean Datatype SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). 2.2.

SQLite WHERE - Filter Rows in a Result Set, In this tutorial, you learn- SQLite storage classes. integer, real, null, blob, text, However, in dynamic types like in SQLite, the type of the column is However, the BOOLEAN values are stored as INTEGERS with values 0 (as  sqlite> select (15 + 6) as addition addition = 21 There are several built-in functions such as avg(), sum(), count(), etc., to perform what is known as aggregate data calculations against a table or a specific table column.

SQLite - Data Type, Here we will learn SQLite data types with examples and how to use data types (null, integer, text, real, blob, boolean, date and time) in sqlite databases with examples. SQLite Data Types. In SQLite, each table in a database has a data type and name. The data type defines what type of data can be stored in a column of a table.

Comments
  • See also stackoverflow.com/questions/843780/…
  • I came here after reading that exact bit of the documentation, which, IMHO, is extremely ambiguous about whether 0 and 1 are somehow aliased with the identifiers false and true, respectively, by SQLite's SQL parser.
  • Sadly this answer doesn't respond to the question, which is not about the storage class/*column type* but the use of TRUE and FALSE literals, which are SQL-compliant as per the Postgresql doc.
  • Sorry, I meant ANSI SQL-compliant, but couldn't find a definitive reference quickly enough so fell back to a note from the PostgreSQL doc. My comment started to turn like an answer so I cut short and transitioned into writing one that has the full run down and effectively gets to answer the actual question.
  • @Lloeki - SQLite is not ANSI SQL compliant with respect to booleans, though - their documentation is the definitive source. Along those lines, your link to the SQLite list of keywords is very useful to make it clear TRUE and FALSE are not supported.
  • @Lloeki SQLite now supports literals :) Please check stackoverflow.com/a/54223589/5070879
  • I came here after reading that exact bit of the documentation, which, IMHO, is extremely ambiguous about whether 0 and 1 are somehow aliased with the identifiers false and true, respectively, by SQLite's SQL parser.
  • Yes, I figured so, based on the answers to this question, but the cited portion of the docs is extremely unclear about that IMO. It says boolean values are stored as the numbers 0 and 1, respectively - and then indicates that those numbers match false and true. What are false and true there? Hints in natural language to readers, or constants/aliases understood by SQLite's SQL parser? As I said, it's very ambiguous the way it is written.
  • Doesn't say so. It could just as well be a false value understood by SQLite's SQL parser, it's just not clear. That's why reading that portion of the docs left me totally clueless and looking for some additional confirmation that there are no such constants false and true in SQLite, which is how I got here.
  • Some C compilers may introduce pseudo-bool constants, just as some SQL parsers may introduce pseudo-bool constants.
  • @Andrey C may not have an explicit boolean type, but it has booleans (i.e. whatever you put in an if condition, be it a comparison, integer, or result of a function call, is treated as a boolean because it can only have one of two effects), so it makes sense to have aliases to true and false. By the same token, SQLite doesn't have a boolean type, but it also has booleans, so it would make sense for a language designer to define aliases there, also. "Defining constant on your own is different thing from having it within language" Yes, but there are standard library defines.
  • This is the correct answer. Sqlite client libraries sometimes suppress this error (WTF), but as you've shown, if you run it in the command-line sqlite3 app, it doesn't like "true" or "false" literals.
  • On the flip side, SQLite supports datetimes, despite not being one of those five types, so this answer is inconclusive. sqlite.org/datatype3.html#datetime