create-partitions.sql

DECLARE
    table_names text[] := ARRAY['history', 'history_uint', 'history_log', 'history_text', 'history_str', 'history_bin'];
    trends_tables text[] := ARRAY['trends', 'trends_uint'];
    table_name text;
    day_start bigint;
    day_end bigint;
    month_start bigint;
    month_end bigint;
    suffix text;
    i integer;
BEGIN
    -- Create daily partitions for history tables (next 30 days)
    FOREACH table_name IN ARRAY table_names LOOP
        FOR i IN 0..29 LOOP -- 30 days ahead
            day_start := extract(epoch from date_trunc('day', now() + (i || ' days')::interval));
            day_end := day_start + 86400; -- 1 day in seconds
            suffix := to_char(now() + (i || ' days')::interval, 'YYYYMMDD');
            
            EXECUTE format(
                'CREATE TABLE IF NOT EXISTS %I_%s PARTITION OF %I FOR VALUES FROM (%s) TO (%s)',
                table_name, suffix, table_name, day_start, day_end
            );
        END LOOP;
        
        RAISE NOTICE 'Created partitions for table: %', table_name;
    END LOOP;
    
    -- Create monthly partitions for trends tables (next 3 months)
    FOREACH table_name IN ARRAY trends_tables LOOP
        FOR i IN 0..2 LOOP -- 3 months ahead
            month_start := extract(epoch from date_trunc('month', now() + (i || ' months')::interval));
            month_end := extract(epoch from date_trunc('month', now() + ((i+1) || ' months')::interval));
            suffix := to_char(now() + (i || ' months')::interval, 'YYYYMM');
            
            EXECUTE format(
                'CREATE TABLE IF NOT EXISTS %I_%s PARTITION OF %I FOR VALUES FROM (%s) TO (%s)',
                table_name, suffix, table_name, month_start, month_end
            );
        END LOOP;
        
        RAISE NOTICE 'Created partitions for trends table: %', table_name;
    END LOOP;
    
    RAISE NOTICE 'Partition maintenance completed successfully!';
END;