How to insert old data to AggregatingMergeTree for AggregateFunction(count) column type in Clickhouse?

Question

I have old table:

CREATE TABLE old_stats
(
    id String,
    results_count UInt64
)
ENGINE = AggregatingMergeTree
PRIMARY KEY (id);

New aggregating table:

CREATE TABLE new_stats
(
    id String,
    results_count AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PRIMARY KEY (id);

How directly insert into new_stats from old_stats

INSERT INTO new_stats
SELECT
id,
result_count
FROM old_stats;

Not working solutions:

countState(result_count) as result_count makes exception Conversion from AggregateFunction(count, UInt8) to AggregateFunction(count) is not supported

arrayReduce('countState', range(1, results_count)) makes exception Conversion from AggregateFunction(count, UInt8) to AggregateFunction(count) is not supported

Answer 1

I just realized the answer as I was working on a different project. Your column in the MV is of type AggregateFunction(count), so you need to use the countState function to insert values into it.

Try the following query (it worked in a simple test I just ran):

INSERT INTO new_stats SELECT
    id,
    countState(results_count) AS results_count
FROM old_stats
GROUP BY id