Skip to main content
Version: Latest-3.2

Capabilities of different table types

Key columns and sort key

Primary Key tableDuplicate Key tableAggregate tableUnique Key table
Key columns and UNIQUE constraints The primary key has the UNIQUE constraint and NOT NULL constraint. The duplicate key does not have the UNIQUE constraint. The aggregate key has the UNIQUE constraint. The unique key has the UNIQUE constraint.
Logical relationships between key columns and data changes If the primary key value of a new data row is the same as that of an existing data row in the table, unique constraint violation occurs. Then the new data row will replace the existing data row.
Compared to the Unique Key table, the Primary Key table has an enhanced underlying storage engine and therefore can replace the Unique Key table.
The duplicate key does not have the UNIQUE constraint. So, if the duplicate Key value of a new data row is the same as that of an existing data row in the table, both the new and old data rows are retained in the table. If the aggregate Key value of a new data row is the same as that of an existing data row in the table, the new and old data rows will be aggregated based on the aggregate key and the aggregate functions of value columns. If the unique key value of a new data row is the same as that of an existing data row in the table, the new data row will replace the existing data row.
The Unique Key table can be seen as an Aggregate table whose aggregate function is replace.
Relationship between key columns and sort keySince v3.0.0, the sort key is decoupled from the primary key in the Primary Key table. The key columns and sort key are coupled.
Data types supported by key columns and sort key Numeric (including integers and BOOLEAN), string, and date (DATE and DATETIME). Numeric (including integers, BOOLEAN, and DECIMAL), string, and date (DATE and DATETIME).
Relationship between key columns and partitioning/bucketing columns Partitioning columns and bucketing columns must be in the primary key. None Partitioning columns and bucketing columns must be in the aggregate key. Partitioning columns and bucketing columns must be in the unique key.

Data types of key and value columns

The key columns support the following data types: numeric (including integers, BOOLEAN, and DECIMAL), string, and date (DATE and DATETIME).

note

The key columns of a Primary Key table do not support the DECIMAL data type.

On the other hand, the value columns support basic data types, including numeric, string, and date (DATE and DATETIME). The support for BITMAP, HLL, and semi-structured types varies for value columns of different types of tables, as detailed below:

Primary Key tableDuplicate Key tableAggregate tableUnique Key table
BITMAPSupportedNot supported Supported. The aggregate functions must be bitmap_union, replace, or replace_if_not_null. Supported
HLLSupported Not supported Supported. The aggregate functions must be hll_union, replace, or replace_if_not_null. Supported
PERCENTILESupported Not supported Supported. The aggregate functions must be percentile_union, replace, or replace_if_not_null. Supported
semi-structured data types:JSON/ARRAY/MAP/STRUCTSupported SupportedSupported. The aggregate functions must be replace or replace_if_not_null. Supported

Data change

Primary Key TableDuplicate Key tableAggregate tableUnique Key table
INSERT through data loading Supported. Configure __op=0 in the load job to achieve INSERT.
In the internal implementation, StarRocks considers both INSERT and UPDATE operations as UPSERT operations.
SupportedSupported (Data rows with the same aggregate Key value will be aggregated.)Supported (Data rows with the same unique key value will be updated.)
UPDATE through data loading Not supported Supported (This can be achieved by using replace as the aggregate function.) Supported (The Unique Key table itself can be considered as an Aggregate table using the replace aggregate function.)
DELETE through data loading Supported. Configure __op=1 in the load job to achieve DELETE. Not supported
Integrity of data column values to be loaded By default, all column values need to be loaded. However, if partial column update (partial_update) is enabled, or a column has a default value, not all column values need to be loaded.By default, all column values need to be loaded. However, if a column has a default value, not all column values need to be loaded. By default, all column values need to be loaded. However, Aggregate tables can achieve partial column update by specifying the aggregate functions for value columns as REPLACE_IF_NOT_NULL. For details, see aggr_type. Also, if a column has a default value, not all column values need to be loaded.By default, all column values need to be loaded. However, if a column has a default value, not all column values need to be loaded.
DML INSERTSupported
DML UPDATE
  • Key columns as filter conditions: Supported
  • Value columns as filter conditions: Supported
Not supported
DML DELETE
  • Key columns as filter conditions: Supported
  • Value columns as filter conditions: Supported
  • Key columns as filter conditions: Supported
  • Value columns as filter conditions: Supported
Note that only simple filter conditions based on key or value columns themselves are supported, such as =, <, >. Complex filter conditions, such as functions or subqueries, are not supported.
  • Key columns as filter conditions: Supported. Note that only simple filter conditions based on key columns themselves are supported, such as =, <, >. Complex filter conditions, such as functions or subqueries, are not supported.
  • Value columns as filter conditions: Not supported.

Compatibility with other features

Primary Key tableDuplicate Key tableAggregate tableUnique Key table
Bitmap index/bloom filter indexBuild indexes on key columnsSupported
Build indexes on value columnsSupportedSupportedNot supportedNot supported
Partitioning/bucketingExpression partitioning/list partitioningSupported
Random bucketingNot supportedSupported from v3.1 onwardsNot supportedNot supported
Materialized viewsAsynchronous materialized viewsSupported
Synchronous materialized viewsNot supportedSupportedSupportedSupported
Other featuresCTASSupportedSupportedNot supported Not supported
Backup & restoreSupported from v2.5 onwardsSupported