timescaledb

pig build get timescaledb; # get timescaledb source code
pig build dep timescaledb; # install build dependencies
pig build pkg timescaledb; # build extension rpm or deb
pig build ext timescaledb; # build extension rpms

pig repo add pgsql -u   # add PGDG + Pigsty repo and update cache (leave existing repos)

pig ext install timescaledb; # install by extension name, for the current active PG version
pig ext install timescaledb; # install via package alias, for the active PG version
pig ext install timescaledb -v 18;   # install for PG 18
pig ext install timescaledb -v 17;   # install for PG 17
pig ext install timescaledb -v 16;   # install for PG 16
pig ext install timescaledb -v 15;   # install for PG 15

CREATE EXTENSION timescaledb CASCADE SCHEMA timescaledb_information;

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);