PostgreSQL 9.6分区表使用

1.创建主表

CREATE TABLE public.t_sensor_data (
	id varchar(32) NOT NULL,
	sensor_item_id varchar(32) NOT NULL,
	value numeric(19, 3) NULL,
	record_time timestamp(6) NOT NULL,
);

2.创建分区表

create table "t_sensor_data_202306"(check (record_time  >=  date '2023-06-01' and record_time <  date '2023-07-01') )
        inherits (t_sensor_data);
create table "t_sensor_data_202307"(check (record_time  >=  date '2023-07-01' and record_time <  date '2023-08-01') )
        inherits (t_sensor_data);
create table "t_sensor_data_202308"(check (record_time  >=  date '2023-08-01' and record_time <  date '2023-09-01') )
        inherits (t_sensor_data);

3. 创建触发器函数

create or replace function t_sensor_data_insert_trigger()
returns trigger as $$
begin
    EXECUTE format ('insert into %I values ($1.* )','t_sensor_data_' || to_char(NEW. record_time, 'yyyymm')) USING NEW;
  return null;
end;
$$
language plpgsql

4.创建触发器

create trigger t_sensor_data_detail_trigger
before insert on t_sensor_data
for each row execute procedure t_sensor_data_insert_trigger ()

5.结果

INSERT INTO "t_sensor_data" (id, sensor_item_id, value,record_time)
VALUES (6, 12, 3,'2023-07-13 13:26:35.729');   

select * from t_sensor_data_202306 tsd 

结果:

文章参考:https://blog.csdn.net/yztezhl/article/details/123522707