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

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

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))) || ' %'))
)

-- 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) || ' %'))
)

-- HANA
CREATE TABLE x (
  interest double,
  interest_percent varchar GENERATED ALWAYS AS ((CAST((interest * 1E2) 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)) + ' %')
)

-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, EXASOL, INFORMIX, MEMSQL, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SYBASE, 
-- TERADATA, VERTICA, YUGABYTEDB
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.18, 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