16.1.6 ROWID

Available since Machbase 8.7.0

ROWID is a 64-bit identifier used to locate a row within a table. This page defines its SQL meaning, per-table predicates, INSERT results, and lifetime. For SDK access APIs and code, see SDK Feature Support and the relevant language page.

This feature is supported in Standard Edition. Update both the server and SDK to versions supporting ROWID. It is unavailable in Cluster Edition.

ROWID and Business Keys

ROWID identifies a stored row’s location in the current table. It is not a permanent business key such as an order number or device ID.

• It is not an ordinary column and is excluded from SELECT *. Select it explicitly when needed.
• Do not compare ROWIDs across tables or use one table’s ROWID to query another table.
• Do not decompose ROWID values or use them in arithmetic.
• Numeric order does not indicate ingestion order across the entire table.
• New tables cannot define a real column named ROWID.
• For legacy tables with a real ROWID column, that column takes precedence. Rename the existing column to use the ROWID pseudocolumn.

SELECT ROWID, name, time, value
  FROM sensor_tag
 WHERE name = 'TAG-01';

Support by Table Type

TRANSACTION, LOOKUP, and VOLATILE use a single LONG/INT64 PRIMARY KEY with a value of 0 or greater as ROWID, regardless of AUTO_INCREMENT. If the application supplies the PK, that value is returned. If an AUTO_INCREMENT PK is omitted or NULL, the server-generated value is returned. Negative PK values cannot be used as ROWIDs.

LOG and TAG ROWIDs range from 0..UINT64_MAX-1; the three PRIMARY KEY-based table types use 0..INT64_MAX. 0 is valid. UINT64_MAX cannot be used as ROWID.

LOG Queries

LOG ROWIDs support range queries and ordering. Because _ARRIVAL_TIME can be equal in multiple rows, use ROWID to locate a specific row again.

SELECT ROWID, message
  FROM app_log
 WHERE ROWID >= ?
   AND ROWID < ?
 ORDER BY ROWID;

ROWID IN (...) is not supported.

TAG Queries

TAG supports only single-ROWID equality lookup. You can combine tag name, time, and value predicates with AND; a row is returned only when every condition matches.

SELECT ROWID, name, time, value
  FROM sensor_tag
 WHERE ROWID = ?
   AND name = 'TAG-01'
   AND time >= TO_DATE('2026-08-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

The following conditions cannot be used with TAG ROWID.

Comparing TRANSACTION, LOOKUP, and VOLATILE

The following three tables support the same AUTO_INCREMENT declaration, but differ in restart behavior and ingestion features.

CREATE TRANSACTION TABLE orders (
    id   LONG PRIMARY KEY AUTO_INCREMENT,
    item VARCHAR(100)
);

CREATE LOOKUP TABLE lookup_orders (
    id   LONG PRIMARY KEY AUTO_INCREMENT,
    item VARCHAR(100)
);

CREATE VOLATILE TABLE volatile_orders (
    id   LONG PRIMARY KEY AUTO_INCREMENT,
    item VARCHAR(100)
);

LOOKUP PROPERTY(SEQUENCE) and NEXTVAL() are separate from AUTO_INCREMENT. Do not configure both mechanisms on the same column.

TRANSACTION table DDL cannot run during an explicit transaction. If CREATE TRANSACTION TABLE fails with ERR-02362, run COMMIT or ROLLBACK first, then retry.

JOIN, Aggregation, and Views

JOIN results have no ROWID representing the entire result. Select the ROWID of each required source table alias separately.

SELECT a.ROWID AS order_rowid,
       b.ROWID AS item_rowid,
       a.customer, b.item
  FROM orders a JOIN order_items b ON a.id = b.order_id;

Conditions for Receiving ROWID from INSERT

Do not use INSERT ... RETURNING ROWID. Supported SDKs return ROWID with the execution result of a successful single-row INSERT ... VALUES.

Generated ROWID is a per-statement result. There is no SQL function that retrieves the latest value for the entire connection.

Empty Results and Errors

A valid ROWID absent from the current table returns zero rows, not an error. This includes deleted rows and rows failing additional predicates. In contrast, NULL, negative PKs, UINT64_MAX, values that cannot convert to numbers, and unsupported TAG ranges/IN/OR/ordering are errors.

Lifetime and Retries

Do not use ROWID as a long-term business key.

An INSERT may succeed without the application receiving its ROWID if the network response is lost. Automatically repeating the INSERT can create duplicate rows. First verify whether it was applied using a business key or a separate idempotency policy.

Related Documentation

• SDK Feature Support
• AUTO_INCREMENT
• LOG Data Ingestion
• TAG Data Ingestion