Available in versions: Dev (3.22) | Latest (3.21) | 3.20 | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
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.
System table columns: ROWID
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Certain RDBMS offer a standard way to access a system specific row locator on arbitrary tables, including tables that do not have a PRIMARY KEY. This system ID can be used to access such a row and make updates to it. Certain restrictions may apply, e.g. related to row movement (the ID may not be permanent over time). Please consult your RDBMS manual for details.
jOOQ offers this via the Table.rowid() API.
SELECT BOOK.ROWID FROM BOOK
create.select(BOOK.rowid()).from(BOOK).fetch();
The data type of such a column is org.jooq.RowId (see ROWID data type), which allows for abstracting over the various implementation specific representations.
Dialect support
This example using jOOQ:
BOOK.rowid()
Translates to the following dialect specific expressions:
DB2, Exasol, Informix, Oracle, SQLite
BOOK.rowid
H2
BOOK._rowid_
Postgres
BOOK.ctid
SQLServer
BOOK.%%physloc%%
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, DuckDB, Firebird, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, Snowflake, Spanner, Sybase, Teradata, Trino, Vertica, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.22. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!