Skip to content
Tutorials Center
My learning

Topics

SQL Reference · Constraints and Data Types

SQL BLOB and binary types

Reference for SQL binary types: BLOB, BYTEA, VARBINARY and RAW, hex literals, byte length versus character length, and why large files rarely belong in a table.

A binary type stores raw bytes rather than text: image thumbnails, hashes, encrypted values, compressed data or fixed-size identifiers. The standard name is BLOB (binary large object). Databases also offer fixed or limited variants such as BINARY(n) and VARBINARY(n).

Unlike text, a blob has no character set and no collation. Comparing two blobs compares their bytes one by one, and LENGTH() returns a number of bytes. SQLite writes blob literals as x'...' with pairs of hex digits.

SQL
CREATE TABLE attachments (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  content BLOB
);

Try it

SQL Blob literals, length and hex

Press Run (or Ctrl + Enter) to see the result here.

SQL Characters versus bytes

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
BLOB
BINARY(n)
VARBINARY(n)

x'48656C6C6F'     -- hexadecimal blob literal (SQLite, MySQL, standard SQL)
Type Meaning
BLOB Binary data of any length.
BINARY(n) Fixed n bytes, padded with zero bytes (not in SQLite).
VARBINARY(n) Up to n bytes (not enforced in SQLite).

Returns: bytes. Most clients show them as hexadecimal; use hex() (SQLite, MySQL, Oracle) or encode(x, 'hex') (PostgreSQL) to print them.

Dialect differences

Database Type name Literal Notes
SQLite BLOB x'4A5B' Any value can be stored as a blob. zeroblob(n) and randomblob(n) build them.
PostgreSQL BYTEA '\x4a5b' Hex format by default. Large objects are a separate feature.
MySQL BLOB, MEDIUMBLOB, LONGBLOB, BINARY, VARBINARY x'4A5B' BLOB types are limited to 64 KB, 16 MB and 4 GB in that order.
SQL Server BINARY(n), VARBINARY(n), VARBINARY(MAX) 0x4A5B IMAGE is deprecated.
Oracle BLOB, RAW(n) HEXTORAW('4A5B') RAW is for short values up to a few thousand bytes.
SQL
-- PostgreSQL
SELECT '\x48656c6c6f'::bytea, encode('\x48656c6c6f'::bytea, 'escape');

-- SQL Server
SELECT 0x48656C6C6F, CONVERT(VARCHAR(10), 0x48656C6C6F);

-- Oracle
SELECT RAWTOHEX(HEXTORAW('48656C6C6F')) FROM dual;

Usage notes

  • Bytes, not characters. length(CAST('café' AS BLOB)) counts UTF-8 bytes. Use length() on text if you want characters.
  • Blobs are not text. In SQLite, 'A' = x'41' is false because a text value and a blob value are different storage classes, even if their bytes match. Cast explicitly when you compare.
  • Large files: database rows get slow and backups large when they hold megabytes of file data. Store big files in object storage and keep a path or URL in the table. Small values (hashes, thumbnails under a few KB) are fine.
  • Hashes and fixed-size ids: a 16-byte BLOB is half the size of the 32-character hex text for the same value. See UUIDs.
  • Reading in application code returns a byte array (bytes, Buffer, byte[]), not a string. Use parameters instead of building hex literals by string concatenation.
  • No sensible sorting. Ordering by a blob column sorts by raw bytes, which is rarely what a user expects.
  • Indexes on blobs work but get large quickly. Index a hash of the content instead.