Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16

Computed columns

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Computed columns, sometimes also called "virtual" columns, are columns that are generated from an expression based on other columns of the same row directly in the database. They cannot be written to, but may be used in projections, filters, and even indexes, as a complement or replacement of function based indexes.

Like any other data type modifying flag, the generator expression can be passed to the data type in jOOQ when creating such a table with computed columns:

Dialect support

This example using jOOQ:

createTable(name("x"))
   .column(name("interest"), DOUBLE)
   .column(name("interest_percent"), VARCHAR.generatedAlwaysAs(field(name("interest"), DOUBLE).times(100.0).concat(" %")))

Translates to the following dialect specific expressions:

Aurora Postgres

CREATE TABLE x (
  interest double precision,
  interest_percent varchar GENERATED ALWAYS AS ((CAST((interest * 1E2) AS varchar) || ' %'))
)

CockroachDB

CREATE TABLE x (
  interest double precision,
  interest_percent string GENERATED ALWAYS AS ((CAST((interest * CAST(1E2 AS double precision)) AS string) || ' %')) STORED
)

DB2, HSQLDB

CREATE TABLE x (
  interest double,
  interest_percent varchar(32672) GENERATED ALWAYS AS ((CAST((interest * 1E2) AS varchar(32672)) || ' %'))
)

Derby

CREATE TABLE x (
  interest double,
  interest_percent varchar(32672) GENERATED ALWAYS AS ((TRIM(CAST(CAST((interest * 1E2) AS char(38)) AS varchar(32672))) || ' %'))
)

DuckDB, Hana

CREATE TABLE x (
  interest double,
  interest_percent varchar GENERATED ALWAYS AS ((CAST((interest * 1E2) AS varchar) || ' %'))
)

Firebird

CREATE TABLE x (
  interest double precision,
  interest_percent varchar(4000) GENERATED ALWAYS AS ((CAST((interest * 1E2) AS varchar(4000)) || ' %'))
)

H2

CREATE TABLE x (
  interest double,
  interest_percent varchar AS ((CAST((interest * CAST(1E2 AS double)) AS varchar) || ' %'))
)

MariaDB, MySQL

CREATE TABLE x (
  interest double,
  interest_percent text GENERATED ALWAYS AS (concat(
    CAST((interest * 1E2) AS char),
    ' %'
  ))
)

Oracle

CREATE TABLE x (
  interest float,
  interest_percent varchar2(4000) GENERATED ALWAYS AS ((CAST((interest * 1E2) AS varchar2(4000)) || ' %'))
)

Postgres

CREATE TABLE x (
  interest double precision,
  interest_percent varchar GENERATED ALWAYS AS ((CAST((interest * 1E2) AS varchar) || ' %')) STORED
)

SQLServer

CREATE TABLE x (
  interest float,
  interest_percent AS (CAST((interest * 1E2) AS varchar(max)) + ' %')
)

ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Exasol, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo