FirebirdSQL logo

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.

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

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

Binary Data Types

BLOBs (Binary Large Objects) are complex structures used to store text and binary data of an undefined length, often very large.

Syntax
BLOB [SUB_TYPE <subtype>]
  [SEGMENT SIZE <segment size>]
  [CHARACTER SET <character set>]
  [COLLATE <collation name>]

If the SUB_TYPE and CHARACTER SET clauses are absent, then subtype BINARY (or 0) is used.If the SUB_TYPE clause is absent and the CHARACTER SET clause is present, then subtype TEXT (or 1) is used.

Shortened syntax
BLOB (<segment size>)
BLOB (<segment size>, <subtype>)
BLOB (, <subtype>)
Note

Formally, the COLLATE clause is not part of the data type declaration, and its position depends on the syntax of the statement.

Segment Size

Specifying the BLOB segment size is a throwback to times past, when applications for working with BLOB data were written in C (Embedded SQL) with the help of the gpre pre-compiler.Nowadays, it is effectively irrelevant.The segment size for BLOB data is determined by the client side and is usually larger than the data page size, in any case.