Note
This function is available starting with V4.6.2.1.
Purpose
This function returns the maximum value of the partitioning key for the partition containing data in a partitioned table.
Limitations and considerations
- The caller must have the SELECT privilege on the target table (performed at the table level). If the privilege is missing, the call will fail.
- Table names in parameters must be constants (string literals that can be determined during parsing). Variables or non-constant expressions are not supported as table name parameters.
- The table to be queried must be a partitioned table, and the partition type must be RANGE or LIST.
- The table to be queried must be an internal table. External tables and DBlinks are not supported.
- Only a single-column partitioning key is supported; a multi-column partitioning key is not supported.
- Expression partitioning is not supported. In this case, the partitioning key cannot be referenced directly as a single column; instead, an expression must be used in the partition definition. Therefore,
MAX_PTcannot be used. - For a subpartitioned table, only the values of the partitions are returned.
- If all partitions are empty, NULL is returned.
- If different partition values exist within the same partition, the result of
MAX_PTisundefined, and a value from any row in that partition will be returned.
Syntax
MAX_PT('[database_name.]table_name')
Parameters
database_name: Optional. The name of the database where the table is located. If not specified, the table is in the default database of the current session by default. You can also use the format'db_name.table_name'to specify both the database and table names in the first string parameter (separated by at most one.).table_name: The name of the table. Database and table names are resolved according to the current session's case-sensitivity policy and valid character rules, and must match the names stored in the data dictionary (including case).
Return value
Returns the maximum partition value (according to the max() comparison logic) from the data partitions with the same data type and partitioning key type.
Examples
Create a partitioned table named
test_tbl1.obclient> CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT) PARTITION BY LIST (col1) ( PARTITION P0 VALUES IN (1), PARTITION P1 VALUES IN (2), PARTITION P2 VALUES IN (3), PARTITION P3 VALUES IN (4) );Insert test data
obclient> INSERT INTO test_tbl1 VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4), (1,1,2), (2,2,2);The return result is as follows:
Query OK, 6 rows affected Records: 6 Duplicates: 0 Warnings: 0View the maximum value of the partitioning key for the partition containing data in the
test_tbl1table.obclient> SELECT MAX_PT('test_tbl1');The return result is as follows:
+---------------------+ | MAX_PT('test_tbl1') | +---------------------+ | 4 | +---------------------+ 1 row in set