timescaledb

pig build pkg timescaledb;		# build rpm / deb with pig

pig repo add pgdg pigsty -u   # add both repo and update cache

pig install timescaledb;		# install via package name, for the active PG version

pig install timescaledb -v 18;   # install for PG 18
pig install timescaledb -v 17;   # install for PG 17
pig install timescaledb -v 16;   # install for PG 16
pig install timescaledb -v 15;   # install for PG 15

shared_preload_libraries = 'timescaledb';

CREATE EXTENSION timescaledb;

DROP TABLE IF EXISTS ts_test;
CREATE TABLE ts_test
(
    id BIGINT PRIMARY KEY,
    ts TIMESTAMPTZ NOT NULL,
    v  INTEGER -- payload
);
SELECT create_hypertable('ts_test', by_range('id'));

INSERT INTO ts_test 
    SELECT i, now() + (i || ' seconds')::INTERVAL, i % 100 
    FROM generate_series(1, 1000000) i;


ALTER TABLE ts_test SET (timescaledb.compress_chunk_time_interval = '24 hours');

CREATE MATERIALIZED VIEW continuous_aggregate_daily( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
  SELECT count(*) FROM ts_test;


SELECT add_job('SELECT 1','1h', initial_start => '2024-07-09 18:52:00+00'::timestamptz);