The jOOQ User Manual SQL building Data types Built-in data types NUMERIC (BigDecimal)

previous : next

NUMERIC (BigDecimal)

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

The NUMERIC data type represents a decimal number, or java.math.BigDecimal in Java, or Types.NUMERIC in JDBC.

Given the variable precision characteristic, the NUMERIC type should usually be accompanied by explicit precision and scale flags. If absent, the default behaviour is dialect specific.

DDL support

Dialect support

This example using jOOQ:

createTable("t").column("c", NUMERIC(10, 5))

Translates to the following dialect specific expressions:

Access, Aurora Postgres, CockroachDB, Derby, DuckDB, Firebird, H2, HSQLDB, Hana, Informix, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Teradata, Vertica, YugabyteDB

CREATE TABLE t (
  c numeric(10, 5)
)

ASE, Sybase

CREATE TABLE t (
  c numeric(10, 5) NULL
)

Aurora MySQL, DB2, MariaDB, MemSQL, MySQL, Trino

CREATE TABLE t (
  c decimal(10, 5)
)

BigQuery

CREATE TABLE t (
  c decimal
)

ClickHouse

CREATE TABLE t (
  c Nullable(decimal(10, 5))
)
ENGINE Log()

Databricks

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

Exasol, Oracle, Snowflake

CREATE TABLE t (
  c number(10, 5)
)

Cast support

Dialect support

This example using jOOQ:

cast(field("c"), NUMERIC(10, 5))

Translates to the following dialect specific expressions:

Access

cdec(c)

ASE, Aurora Postgres, CockroachDB, Databricks, Derby, DuckDB, Firebird, H2, HSQLDB, Hana, Informix, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Sybase, Teradata, Vertica, YugabyteDB

CAST(c AS numeric(10, 5))

Aurora MySQL, DB2, MariaDB, MemSQL, MySQL, Trino

CAST(c AS decimal(10, 5))

BigQuery

CAST(c AS decimal)

ClickHouse

CAST(c AS Nullable(decimal(10, 5)))

Exasol, Oracle, Snowflake

CAST(c AS number(10, 5))

The jOOQ User Manual SQL building Data types Built-in data types NUMERIC (BigDecimal)

previous : next