PARTITIONID pseudocolumn

The PARTITIONID pseudocolumn allows you to retrieve the partition ID for each row of data. The value of the PARTITIONID pseudocolumn is populated directly by the tablescan operator when reading raw data and is not physically stored. This article mainly explains how to use the PARTITIONID pseudocolumn.

Based on different partition levels and functions, the following six pseudocolumns are introduced:

| Column name | Type | Description | |--|--|--| | __ob_partition_id | int | partition_id: The ID of the partition corresponding to this row of data. | | __ob_partition_index | int | partition_index: The index number of the partition for this row of data. | | __ob_partition_name | varchar | partition_name: The name of the partition for this row of data. | | __ob_sub_partition_id | int | sub_partition_id: The ID of the sub-partition for this row of data. | | __ob_sub_partition_index | int | sub_partition_index: The index number of the sub-partition for this row. | | __ob_sub_partition_name | varchar | sub_partition_name: The name of the sub-partition for this row of data. |

Basic syntax

SELECT operations

Here is an example:

select c1,__ob_partition_id from t1;

UPDATE operations

The partitionid pseudocolumn cannot be modified and can appear in the WHERE condition.

Here is an example:

UPDATE t1 SET c1=c1 WHERE __ob_partition_index=2;

INSERT operations

It cannot appear in the column list of an INSERT statement.

Limitations

Limitations on column name conflicts with pseudocolumns

• Query statement: If a table already contains one of the pseudocolumn names listed above before upgrading to the version that supports partitionid, queries will still return the actual data from that column. If the table does not contain such a column, the corresponding pseudocolumn value will be returned.
• The statement for creating a table: You cannot create a table with a column name that conflicts with any of the pseudocolumn names listed above. Otherwise, an OB_ERR_COLUMN_DUPLICATE error will be returned.
  • This includes statements like create table tt1 as select c1,__ob_partition_id from t1; in MySQL-compatible mode.
• Drop column: If a table already contains a column with one of the pseudocolumn names, you can drop it as usual. Otherwise, an OB_ERR_BAD_FIELD_ERROR will be returned.
• Add column: You cannot directly add a column with a conflicting partitionid pseudocolumn name. Attempting to do so will result in an OB_ERR_COLUMN_DUPLICATE error.
• Rename column: You may only rename an existing column with a conflicting pseudocolumn name to another name. Otherwise, an OB_ERR_COLUMN_DUPLICATE error will be returned.

Limitations on table types

These pseudocolumns are only supported for user tables and user-defined views that include pseudocolumn names in their definitions.

Feature switches

The PARTITIONID pseudocolumn 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. This prevents users from creating tables that include these column names.

To address this issue, you can disable the PARTITIONID pseudocolumn feature by setting the parameter _enable_pseudo_partition_id to false. If your business needs to use these column names when creating tables, you can choose to turn off this feature.

Here is an example:

ALTER SYSTEM SET _enable_pseudo_partition_id = false;