timescale-commands.sql

CREATE TABLE IF NOT EXISTS public.test_cagg (
    "time" timestamp WITH time zone NOT NULL,
    col1 text NOT NULL,
    col2 text NOT NULL,
    col3 text NOT NULL,
    col4 text NOT NULL,
    col5 text NOT NULL,
    col6 text NOT NULL,
    col7 text NOT NULL,
    col8 text NOT NULL,
    col9 text NOT NULL,
    col10 text NOT NULL,
    col11 text NOT NULL,
    col12 text NOT NULL,
    col13 text NOT NULL,
    col14 bigint,
    col15 bigint,
    col16 bigint,
    col17 bigint,
    col18 bigint,
    col19 text,
    col20 double precision,
    col21 double precision,
    col22 double precision,
    col23 double precision,
    col24 double precision,
    col25 double precision,
    col26 double precision,
    col27 double precision,
    col28 double precision,
    col29 double precision,
    col30 double precision,
    col31 double precision,
    col32 double precision,
    col33 bigint,
    col34 text,
    col35 text NOT NULL DEFAULT '-' ::text,
    col36 text NOT NULL DEFAULT '-' ::text,
    col37 text NOT NULL DEFAULT '-' ::text,
    col38 text NOT NULL DEFAULT '-' ::text,
    col39 text NOT NULL DEFAULT '-' ::text,
    col40 text NOT NULL DEFAULT '-' ::text,
    col41 text,
    PRIMARY KEY (col1, col2, col3, col4, col5, col35, col36, col37, col6, col7, col8, col9, col10, col11, col38, col39, col40, col12, col19, time)
);

SELECT create_hypertable('test_cagg', 'time', chunk_time_interval => interval '6 hours', if_not_exists => TRUE);

CREATE MATERIALIZED VIEW test_cagg_1min WITH (timescaledb.continuous) AS
SELECT
    time_bucket (INTERVAL '1 minute', time) AS time,
    MAX(col21) AS col21,
    MAX(col22) AS col22,
    MAX(col23) AS col23,
    MAX(col24) AS col24,
    MAX(col25) AS col25,
    MAX(col26) AS col26,
    MAX(col27) AS col27,
    MAX(col28) AS col28,
    MAX(col29) AS col29,
    MAX(col30) AS col30,
    MAX(col31) AS col31,
    MAX(col32) AS col32,
    MAX(col20) AS col20,
    MAX(col33) AS col33,
    MAX(col14) AS col14,
    MAX(col15) AS col15,
    MAX(col16) AS col16,
    MAX(col17) AS col17,
    MAX(col18) AS col18,
    col1,
    col2,
    col13,
    col3,
    col4,
    col5,
    col35,
    col36,
    col37,
    col6,
    col8,
    col7,
    col9,
    col10,
    col38,
    col11,
    col39,
    col40,
    col12,
    col19,
    col34,
    col41
FROM
    test_cagg
GROUP BY
    col41,
    col1,
    col2,
    col13,
    col3,
    col4,
    col5,
    col35,
    col36,
    col37,
    col38,
    col39,
    col40,
    col6,
    col8,
    col7,
    col9,
    col10,
    col11,
    col12,
    col19,
    col34,
    time_bucket (INTERVAL '1 minute', time);

SELECT
    add_continuous_aggregate_policy ('test_cagg_1min', start_offset := INTERVAL '1 day', end_offset := INTERVAL '6 hours', schedule_interval := INTERVAL '6 hours');