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) || ' %')) )
ClickHouse
CREATE TABLE x ( interest Nullable(double), interest_percent Nullable(String) MATERIALIZED (CAST((interest * 1E2) AS Nullable(String)) || ' %') ) ENGINE Log()
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, Exasol, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!