Capabilities of different table types

Key columns and sort key

	Primary Key table	Duplicate Key table	Aggregate table	Unique 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	Insert, delete, and update operations are performed based on the primary key. If the primary key value of a new data row is 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 same as that of an existing data row in the table, both the new and old data rows are retained in the table.	Insert, delete, and update operations are performed based on the aggregate key. So, if the aggregate Key value of a new data row is 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.	Insert, delete, and update operations are performed based on the unique key. If the unique key value of a new data row is 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 key	Since v3.0, the key columns and sort key are decoupled.	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 table	Duplicate Key table	Aggregate table	Unique Key table
BITMAP	Supported	Not supported	Supported. The aggregate functions must be bitmap_union, replace, or replace_if_not_null.	Supported
HLL	Supported	Not supported	Supported. The aggregate functions must be hll_union, replace, or replace_if_not_null.	Supported
PERCENTILE	Supported	Not supported	Supported. The aggregate functions must be percentile_union, replace, or replace_if_not_null.	Supported
semi-structured data types：JSON/ARRAY/MAP/STRUCT	Supported	Supported	Supported. The aggregate functions must be replace or replace_if_not_null.	Supported

Data change

	Primary Key Table	Duplicate Key table	Aggregate table	Unique 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.	Supported	Supported (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 INSERT	Supported
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 table	Duplicate Key table	Aggregate table	Unique Key table
Bitmap index/bloom filter index	Build indexes on key columns	Supported
	Build indexes on value columns	Supported	Supported	Not supported	Not supported
Partitioning/bucketing	Expression partitioning/list partitioning	Supported
	Random bucketing	Not supported	Supported from v3.1 onwards	Not supported	Not supported
Materialized views	Asynchronous materialized views	Supported
	Synchronous materialized views	Not supported	Supported	Supported	Supported
Other features	CTAS	Supported	Supported	Not supported	Not supported
	Backup & restore	Supported from v2.5 onwards	Supported