3.6. Boolean Data Type

3.6.1. BOOLEAN

Data Type Declaration Format 

  |BOOLEAN

The SQL-compliant BOOLEAN data type (8 bits) comprises the distinct truth values TRUE and FALSE. Unless prohibited by a NOT NULL constraint, the BOOLEAN data type also supports the truth value UNKNOWN as the null value. The specification does not make a distinction between the NULL value of this data type, and the truth value UNKNOWN that is the result of an SQL predicate, search condition, or Boolean value expression: they may be used interchangeably to mean the same thing.

As with many programming languages, the SQL BOOLEAN values can be tested with implicit truth values. For example, field1 OR field2 and NOT field1 are valid expressions.

3.6.1.1. The IS Operator

Predicates can use the operator Boolean IS [NOT] for matching. For example, field1 IS FALSE, or field1 IS NOT TRUE.

ⓘ
Note

  • Equivalence operators (=, !=, <> and so on) are valid in all comparisons.

3.6.1.2. BOOLEAN Examples

  1. Inserting and selecting

       |CREATE TABLE TBOOL (ID INT, BVAL BOOLEAN);
   |COMMIT;
   | 
   |INSERT INTO TBOOL VALUES (1, TRUE);
   |INSERT INTO TBOOL VALUES (2, 2 = 4);
   |INSERT INTO TBOOL VALUES (3, NULL = 1);
   |COMMIT;
   | 
   |SELECT * FROM TBOOL;
   |          ID    BVAL
   |============ =======
   |           1 <true>
   |           2 <false>
   |           3 <null>

  2. Test for TRUE value

      |SELECT * FROM TBOOL WHERE BVAL;
  |          ID    BVAL
  |============ =======
  |           1 <true>

  3. Test for FALSE value

      |SELECT * FROM TBOOL WHERE BVAL IS FALSE;
  |          ID    BVAL
  |============ =======
  |           2 <false>

  4. Test for UNKNOWN value

      |SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN;
  |          ID    BVAL
  |============ =======
  |           3 <null>

  5. Boolean values in SELECT list

      |SELECT ID, BVAL, BVAL AND ID < 2
  |  FROM TBOOL;
  |          ID    BVAL
  |============ ======= =======
  |           1 <true>  <true>
  |           2 <false> <false>
  |           3 <null>  <false>

  6. PSQL declaration with start value

      |DECLARE VARIABLE VAR1 BOOLEAN = TRUE;

  7. Valid syntax, but as with a comparison with NULL, will never return any record

  |SELECT * FROM TBOOL WHERE BVAL = UNKNOWN;
  |SELECT * FROM TBOOL WHERE BVAL <> UNKNOWN;
3.6.1.2.1. Use of Boolean Against Other Data Types

Although BOOLEAN is not inherently convertible to any other data type, the strings 'true' and 'false' (case-insensitive) will be implicitly cast to BOOLEAN in value expressions. For example:

  |if (true > 'false') then ...

The value 'false' is converted to BOOLEAN. Any attempt to use the Boolean operators AND, NOT, OR and IS will fail. NOT 'False', for example, is invalid.

A BOOLEAN can be explicitly converted to and from string with CAST. UNKNOWN is not available for any form of casting.

For ordering and comparison, the value TRUE is greater than the value FALSE.