The PARTITIONID pseudo-column provides the ability to obtain the partition ID of each row of data. The value of the PARTITIONID pseudo-column is directly populated by the tablescan operator when reading the original data and is not stored physically. This topic describes how to use the PARTITIONID pseudo-column.
Based on the partition level and functionality, the following six pseudo-columns are primarily introduced:
Column name |
Type |
Meaning |
|---|---|---|
| __ob_partition_id | int | partition_id, the data in this row corresponds to partition of id. |
| __ob_partition_index | int | partition_index, the data in this row corresponds to partition Number index. |
| __ob_partition_name | varchar | partition_name, the row data corresponds to partition Name of the table name. |
| __ob_sub_partition_id | int | sub_partition_id, the data in this row corresponds to sub_partition of the id. |
| __ob_sub_partition_index | int | sub_partition_index, the data in this row corresponds to sub_partition Number of Records index. |
| __ob_sub_partition_name | varchar | sub_partition_name, the data in this row corresponds to sub_partition Name of the table name. |
Basic syntax
SELECT operations
An example is as follows:
select c1,__ob_partition_id from t1;
UPDATE operations
The partitionid pseudo-column cannot be modified, but it can be used in WHERE clauses.
An example is as follows:
UPDATE t1 SET c1=c1 WHERE __ob_partition_index=2;
INSERT operations
Cannot be included in the column list of an INSERT statement.
Limit Summary
Column name and pseudo-column field conflict limitation
- Query statements: If a table already had the aforementioned pseudo-column name before upgrading to a version that supports
partitionid, the actual data for that column will still be returned in query statements; the pseudo-column value is returned only if the column does not exist. - Table creation statement: When creating a table, you are not allowed to create column names that conflict with the pseudo-column names mentioned above. Otherwise, the error message
OB_ERR_COLUMN_DUPLICATEis returned.- Including
create table tt1 as select c1,__ob_partition_id from t1;.
- Including
- Drop column: When dropping a column from a table, if the pseudo-column name mentioned above already exists in the table, the drop operation can proceed normally. Otherwise, an error
OB_ERR_BAD_FIELD_ERRORis returned. - Add column: You cannot directly
adda conflictingpartitionidpseudo-column; otherwise, the error messageOB_ERR_COLUMN_DUPLICATEis returned. - Rename column: You can only rename an existing column name that conflicts with the preceding pseudo-column name to another one. Otherwise, an error
OB_ERR_COLUMN_DUPLICATEis returned.
Table type limitations
Only user tables and user views that contain table pseudocolumn names during definition support querying these pseudocolumns.
Feature switch
The PARTITIONID pseudo-column occupies six column names: __ob_partition_id, __ob_partition_index, __ob_partition_name, __ob_sub_partition_id, __ob_sub_partition_index, and __ob_sub_partition_name, which prevents users from creating tables containing these column names.
To address this issue, you can disable the PARTITIONID pseudo-column feature by configuring the _enable_pseudo_partition_id parameter. If your business requires using the aforementioned column name to create tables, you can choose to disable this feature.
An example is as follows:
Notice
After you disable the partitionid pseudo-column feature in the sys tenant by executing the following command, you must manually clear the plan cache in the current tenant by executing ALTER SYSTEM flush plan cache;.
ALTER SYSTEM SET _enable_pseudo_partition_id = false;