The jOOQ User Manual SQL building Data types Built-in data types BLOB (byte[])

previous : next

BLOB (byte[])

Supported by ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The BLOB (Binary Large OBject) data type represents a variable length binary type, or byte[] in Java, or Types.BLOB in JDBC.

Some RDBMS support different variable size binary types, where the BLOB type allows for very big objects to be stored in separate storage. In those RDBMS, BLOB may behave subtly differently from VARBINARY (byte[]).

DDL support

Dialect support

This example using jOOQ:

createTable("t").column("c", BLOB)

Translates to the following dialect specific expressions:

Access, SQLDataWarehouse

CREATE TABLE t (
  c binary
)

ASE, Sybase

CREATE TABLE t (
  c binary NULL
)

Aurora MySQL, DB2, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, SQLite, Teradata

CREATE TABLE t (
  c blob
)

Aurora Postgres, Postgres, YugabyteDB

CREATE TABLE t (
  c bytea
)

BigQuery, CockroachDB

CREATE TABLE t (
  c bytes
)

ClickHouse

CREATE TABLE t (
  c Nullable(varbinary)
)
ENGINE Log()

Databricks

CREATE TABLE t (
  c binary
)
TBLPROPERTIES(
  'delta.columnMapping.mode' = 'name',
  'delta.feature.allowColumnDefaults' = 'supported'
)

Redshift

CREATE TABLE t (
  c varbyte
)

Snowflake, Trino

CREATE TABLE t (
  c varbinary
)

SQLServer

CREATE TABLE t (
  c varbinary(max)
)

Vertica

CREATE TABLE t (
  c long varbinary
)

Cast support

Dialect support

This example using jOOQ:

cast(field("c"), BLOB)

Translates to the following dialect specific expressions:

Access

cstr(c)

ASE, Aurora MySQL, Databricks, MariaDB, MemSQL, MySQL, SQLDataWarehouse, Sybase

CAST(c AS binary)

Aurora Postgres, Postgres, YugabyteDB

CAST(c AS bytea)

BigQuery, CockroachDB

CAST(c AS bytes)

ClickHouse

CAST(c AS Nullable(varbinary))

DB2, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, SQLite, Teradata

CAST(c AS blob)

Oracle

to_blob(c)

Redshift

CAST(c AS varbyte)

Snowflake, Trino

CAST(c AS varbinary)

SQLServer

CAST(c AS varbinary(max))

Vertica

CAST(c AS long varbinary)

The jOOQ User Manual SQL building Data types Built-in data types BLOB (byte[])

previous : next