3.6 Boolean Data Type

3.6.1 BOOLEAN

Data Type Declaration Format

BOOLEAN

The SQL:2008 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 exactly 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, from version 3.0.1 the strings 'true' and 'false' (case-insensitive) will be implicitly cast to BOOLEAN in value expressions, e.g.

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

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

Other Notes

• The type is represented in the API with the FB_BOOLEAN type and FB_TRUE and FB_FALSE constants.

• The value TRUE is greater than the value FALSE.