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 in some statements and expressions, but contrary to scalar character types, it’s not part of the data type declaration syntax.

Specifying CHARACTER SET without specifying a SUB_TYPE implies SUB_TYPE TEXT.

Internal subtypes

The Firebird engine uses other positive subtype numbers for internal subtypes in metadata.

Custom subtypes

Negative subtype numbers can be used for user-defined subtypes. Positive subtype numbers are reserved for use by Firebird.

Optionally, you can add a name for such custom subtypes. Custom subtype names can be inserted into the RDB$TYPES table by users with the system privilege CREATE_USER_TYPES.

Define a subtype GZIP with subtype number -21

insert into SYSTEM.RDB$TYPES
  (RDB$FIELD_SUB_TYPE, RDB$TYPE, RDB$TYPE_NAME,
   RDB$SYSTEM_FLAG, RDB$DESCRIPTION)
values
  ('RDB$FIELD_SUB_TYPE', -21, 'GZIP', 0, 'GZIP compressed data');

Both the subtype name and the subtype number can be used: BLOB SUB_TYPE GZIP or BLOB SUB_TYPE -21.

SizeThe maximum size of a BLOB field depends on the page size of the database, whether the blob value is created as a stream blob or a segmented blob, and if segmented, the actual segment sizes used when populating the blob. For most built-in functions, the maximum size of a BLOB field is 4 GB, or data beyond the 4 GB limit is not addressable.

Operations and ExpressionsText BLOBs of any length and any character set can be operands for practically any statement or internal functions. The following operators are fully supported:

As an efficient alternative to concatenation, you can also use BLOB_APPEND() or the functions and procedures of system package RDB$BLOB_UTIL.

Partial support:

• An error occurs with these if the search argument is larger than or equal to 32 KB:

  STARTING [WITH],

  LIKE,

  CONTAINING

• Aggregation clauses work not on the contents of the field itself, but on the BLOB ID. Aside from that, there are some quirks:

  SELECT DISTINCT	returns several NULL values by mistake if they are present
  ORDER BY	—
  GROUP BY	concatenates the same strings if they are adjacent to each other, but does not do it if they are remote from each other

See alsoFILTER, DECLARE FILTER, BLOB_APPEND(), RDB$BLOB_UTIL