Error while creating continuous aggregate (materialized view) when source and new time columns have the same name (PSQL/TSDB)

Question

I'm facing a problem when creating a continuous aggregate on timescaledb (based on materialized views) when the source time column and the new time column have the same name like below:

CREATE MATERIALIZED VIEW IF NOT EXISTS minute_table
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 minute', my_time_col) AS my_time_col,
    ...
FROM source_table
GROUP BY my_time_col
WITH NO DATA;

I get the following error:

ERROR:  continuous aggregate view must include a valid time bucket function

But if I change the new time column name like in the below example just works:

CREATE MATERIALIZED VIEW IF NOT EXISTS minute_table
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 minute', my_time_col) AS my_time_col_asdf,
    ...
FROM source_table
GROUP BY my_time_col_asdf
WITH NO DATA;

How can I work around this? I need the source and new time columns to have the same name...

Answer 1

I found out.

There are two ways if you want to keep same name in both columns:

1. GROUP BY time_bucket(INTERVAL '1 minute', my_time_col)
2. GROUP BY 1

The first one more robust the second one shorter.