FirebirdSQL logo

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.

BLOB Subtypes

The optional SUB_TYPE parameter specifies the nature of data written to the column.Firebird provides two pre-defined subtypes for storing user data:

Subtype 0: BINARY

If a subtype is not specified, the specification is assumed to be for untyped data and the default SUB_TYPE BINARY (or SUB_TYPE 0) is applied.This is the subtype to specify when the data are any form of binary file or stream: images, audio, word-processor files, PDFs and so on.

Subtype 1: TEXT

Subtype 1 has an alias, TEXT, which can be used in declarations and definitions.For instance, BLOB SUB_TYPE TEXT (or BLOB SUB_TYPE 1).It is a specialized subtype used to store plain text data that is too large to fit into a string type.A CHARACTER SET may be specified, if the field is to store text with a different encoding to that specified for the database.A COLLATE clause is also supported.

Specifying CHARACTER SET without specifying a SUB_TYPE implies SUB_TYPE TEXT.

Custom Subtypes

It is also possible to add custom data subtypes, for which the range of enumeration from -1 to -32,768 is reserved.Custom subtypes enumerated with positive numbers are not allowed, as the Firebird engine uses the numbers from 2-upward for some internal subtypes in metadata.Custom subtype aliases can be inserted into the RDB$TYPES table by users with the system privilege CREATE_USER_TYPES.