Creating a blob column

A blob (Binary Large OBject) data type represents a constant hexadecimal number defined as 0[xX](hex)+ where hex is a hexadecimal character, such as [0-9a-fA-F]. For example, 0xcafe. The maximum theoretical size for a blob is 2 GB. The practical limit on blob size, however, is less than 1 MB. A blob type is suitable for storing a small image or short string.

Blob conversion functions

Conversely, the blobAsType function takes a 64-bit blob argument and converts it to a value of the specified data type, if possible.

The following example shows the use of a blob column and the bigintAsBlob function:

CREATE TABLE IF NOT EXISTS cycling.lastname_bio (
  lastname varchar PRIMARY KEY,
  bio blob
);

INSERT INTO cycling.lastname_bio (
  lastname, bio
) VALUES (
  'TSATEVICH', bigintAsBlob(3)
);

SELECT *
FROM cycling.lastname_bio;

Output:

 lastname  | bio
-----------+--------------------
 TSATEVICH | 0x0000000000000003

(1 rows)

The following example shows the use of blobAsBigint function:

ALTER TABLE cycling.lastname_bio
ADD id bigint;

INSERT INTO cycling.lastname_bio (
  lastname, id
) VALUES (
  'DUVAL', blobAsBigint(0x0000000000000003)
);

SELECT *
FROM cycling.lastname_bio;

Output:

 lastname  | bio                | id
-----------+--------------------+------
     DUVAL |               null |    3
 TSATEVICH | 0x0000000000000003 | null

(2 rows)