分区表

以 his_car_position 车辆历史轨迹表为例。

1. 创建自增序列

create sequence "public"."seq_his_car_position_id"
increment 1
minvalue  1
maxvalue 9223372036854775807
start 1
cache 1;

2. 创建表

CREATE TABLE "public"."his_car_position" (
	"id" int8 NOT NULL DEFAULT nextval( 'seq_his_car_position_id' :: regclass ),
	"sn" varchar ( 30 ) COLLATE "pg_catalog"."default",
	"cn" varchar ( 20 ) COLLATE "pg_catalog"."default",
	"la" float8,
	"lo" float8,
	"te" timestamp ( 6 ) NOT NULL,
	CONSTRAINT "pk_his_car_position_id" PRIMARY KEY ("id", "te")
) PARTITION BY RANGE ( "te" "pg_catalog"."timestamp_ops" );

-- 给表字段添加注释
COMMENT ON COLUMN "public"."his_car_position"."id" IS 'id';
COMMENT ON COLUMN "public"."his_car_position"."sn" IS '设备';
COMMENT ON COLUMN "public"."his_car_position"."cn" IS '车号';
COMMENT ON COLUMN "public"."his_car_position"."la" IS '纬度';
COMMENT ON COLUMN "public"."his_car_position"."lo" IS '经度';
COMMENT ON COLUMN "public"."his_car_position"."te" IS '更新时间';
COMMENT ON TABLE "public"."his_car_position" IS '车辆历史信息表';

-- 给表设置权限
alter table "public"."his_car_position" owner TO "admin";

-- 给表添加索引
CREATE INDEX "his_car_position_id" ON "public"."his_car_position" USING hash ("id");
CREATE INDEX "his_car_position_te" ON "public"."his_car_position" USING hash ("te");

3. 创建默认分区子表

create table his_car_position_default partition of his_car_position default;

4. 创建当月分区子表

CREATE OR REPLACE FUNCTION "public"."generate_current_month_car_position_partition_tables"()
  RETURNS "pg_catalog"."bool" AS $BODY$
DECLARE
    start_date DATE := DATE_TRUNC('MONTH', CURRENT_DATE)::DATE;
    end_date DATE := start_date + INTERVAL '1 month' - INTERVAL '1 day';
    cur_date DATE := start_date;
    tab_name VARCHAR;
BEGIN
    -- Loop through each day in the month
    WHILE cur_date <= end_date LOOP
        -- Generate table name with the date
        tab_name := 'his_car_position_' || TO_CHAR(cur_date, 'YYYYMMDD');
        -- Create the table (modify this part according to your table structure)
        EXECUTE 'CREATE TABLE IF NOT EXISTS ' || tab_name || ' PARTITION OF his_car_position FOR VALUES FROM('''||TO_CHAR(cur_date, 'YYYY-MM-DD 00:00:00')||''') TO('''||TO_CHAR(cur_date+INTERVAL '1 day', 'YYYY-MM-DD 00:00:00')||''');';
        -- Move to the next day
    cur_date := cur_date + INTERVAL '1 day';

    END LOOP;
    -- Return success
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        -- Return failure and log the error
        RAISE NOTICE 'Error: %', SQLERRM;
        RETURN FALSE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

-- 创建当月分区子表函数后执行，返回 t，表示当月分区表创建成功
SELECT generate_current_month_car_position_partition_tables();

5. 创建下月分区子表

CREATE OR REPLACE FUNCTION "public"."generate_next_month_car_position_partition_tables"()
  RETURNS "pg_catalog"."bool" AS $BODY$
DECLARE
    start_date DATE := (DATE_TRUNC('MONTH', CURRENT_DATE)+ INTERVAL '1 month')::DATE;
    end_date DATE := start_date + INTERVAL '1 month' - INTERVAL '1 day';
    cur_date DATE := start_date;
    tab_name VARCHAR;
BEGIN
    -- Loop through each day in the month
    WHILE cur_date <= end_date LOOP
        -- Generate table name with the date
        tab_name := 'his_car_position_' || TO_CHAR(cur_date, 'YYYYMMDD');
        -- Create the table (modify this part according to your table structure)
        EXECUTE 'CREATE TABLE IF NOT EXISTS ' || tab_name || ' PARTITION OF his_car_position FOR VALUES FROM('''||TO_CHAR(cur_date, 'YYYY-MM-DD 00:00:00')||''') TO('''||TO_CHAR(cur_date+INTERVAL '1 day', 'YYYY-MM-DD 00:00:00')||''');';
        -- Move to the next day
    cur_date := cur_date + INTERVAL '1 day';

    END LOOP;
    -- Return success
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        -- Return failure and log the error
        RAISE NOTICE 'Error: %', SQLERRM;
        RETURN FALSE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

创建下月分区子表后，在当前地址下的 postgres库 中执行下面的语句，生成定时任务，自动创建下月的分区表：

-- 每月 26号10点 或 27号10点调用函数，创建下个月分区表
SELECT cron.schedule_in_database (
  'generate_next_month_car_position_partition_tables', 
  '0 10 26,27 * *', 
  'select generate_next_month_car_position_partition_tables();', 
  'atgv_db_his', 
  'admin' 
);

6. 导入旧表数据

insert into his_car_position (id, sn, cn, la, lo, te)
(
	SELECT
		id, sn, cn, la, lo, te
	from
		his_car_position_test
	where te >= '2023-12-12 00:00:00' and te < '2024-12-13 00:00:00'
)