Hi everyone, i've got a question about partitioning. Trying to create a partitioned table based on column 'CREATION_TIME' If i try the following statement i get errormessage: "Getting analyzing error. Detail message: The partition column could not be aggregated column and unique table's partition column must be key column."

CREATE TABLE `recomp` (
    `IDENT` BIGINT NOT NULL,
    `DB_ID` BIGINT NOT NULL,
    `TSTAMP` DATETIME,
    `CREATION_TIME` DATETIME,
    `STATES_ID` BIGINT NOT NULL,
    `STATES_ID_CTR` BIGINT NOT NULL,
    `STATUSCODES_ID` BIGINT NOT NULL,
    INDEX idx_bitmap_statuscodes_id (`STATUSCODES_ID`) USING BITMAP,
    INDEX idx_bitmap_processes_id (`PROCESSES_ID`) USING BITMAP
)
PRIMARY KEY (`IDENT`)
PARTITION BY date_trunc('day', `CREATION_TIME`)
DISTRIBUTED BY HASH(`IDENT`)
;

When i define an addidtional column "PARTITION_DATE" based on "CREATION_TIME" everything works fine (partitions are created based on partition_date).

CREATE TABLE `recomp` (
    `IDENT` BIGINT NOT NULL,
    `DB_ID` BIGINT NOT NULL,
    `TSTAMP` DATETIME,
    `CREATION_TIME` DATETIME,
    `STATES_ID` BIGINT NOT NULL,
    `STATES_ID_CTR` BIGINT NOT NULL,
    `STATUSCODES_ID` BIGINT NOT NULL,
    `DESCRIPTION` VARCHAR(255),
	, PARTITION_DATE DATE AS (date(CREATION_TIME)) 
    , INDEX idx_bitmap_statuscodes_id (`STATUSCODES_ID`) USING BITMAP
    , INDEX idx_bitmap_processes_id (`PROCESSES_ID`) USING BITMAP

)
PRIMARY KEY (IDENT)
PARTITION BY PARTITION_DATE
DISTRIBUTED BY HASH(IDENT) 
);

Why does this work and the first one not? I'm puzzled... Best regards Johann

Don't worry about this - It's a bug. For primary key table, partition column must be included as a part of primary key

Hi Robert, thanks for the info. I've now added the partitioning field to the primary key (and changed the order of the fields). This works. But i'm not sure how this will affect the upsert behavior, however, since the source system only uses IDENT as the primary key. This shouldn't be a problem with this table, since the creation time is only set during an INSERT. Theoretically, however, it's possible that this field is also updated. This would duplicate the record. I think I'll stick with the second option with a separate partitioning field. Best regards, Johann