User-Defined Row IDs for Tables

You can expose the row IDs in VAST Database tables and control the allocation of row IDs for inserted data. This is helpful if you want to use only a specific range of rows when inserting or querying table data.

To expose the row ID in a table, create a column with these attributes:

Although the vastdb_rowid field has int64 type, the value of a user-defined row ID cannot be larger than 2^47.

To insert information into specific table rows, specify the row ID you want in the INSERT statement, as in this example:

INSERT INTO TABLE (vastdb_rowid=ROW, data1, data2, data3)

This will insert data1, data2 and data3 into the table at the row ROW.

Note

If you include the row ID for an INSERT, as in the example above, you must use row IDs in all subsequent INSERTS into the table. You cannot mix INSERTS with user-allocated rows, and internally allocated rows (by leaving out the row ID).

To query data from a specific range of table rows:

SELECT * FROM TABLE WHERE vastdb_rowid>ROW1 and vastdb_rowid<ROW2

This selects rows in the table between ROW1 and ROW2.

Note

You can mix queries on the table using the row IDs with queries that do not  use row IDs.