pg自动分区分表--按时间以年为分区时间分表

创建父表

CREATE TABLE public.meter_hour_value (
  meterid varchar(20) NOT NULL,
  meter_param_id varchar(20) NOT NULL,
  record_hour timestamp NOT NULL,
  value numeric(18, 4) NOT NULL,
  creator varchar(64) NULL,
  create_time timestamp NOT NULL,
  updater varchar(64) NULL,
  update_time timestamp NOT NULL,
  tenant_id int8 NOT null,
  CONSTRAINT meter_energy_item_hour_value_pkey PRIMARY KEY (meterid, meter_param_id, record_hour)
)PARTITION BY RANGE (record_hour);

创建分区表

@Resource
DataSource dataSource;

public void savePartitionIfNotExist(LocalDateTime time) {

        // 分区开始一年的第一天
        LocalDateTime localDateTimeStart = time.truncatedTo(ChronoUnit.DAYS).withDayOfYear(1);
        // 分区结束下一年的第一天
        LocalDateTime localDateTimeEnd = localDateTimeStart.plusYears(1);
        // 分区表开始时间
        String start =  localDateTimeStart.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        // 分区表结束时间
        String end = localDateTimeEnd.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        //分区表表名标分区标识
        String partitionDate = localDateTimeStart.format(DateTimeFormatter.ofPattern("yyyy"));
        String sql =   "CREATE TABLE IF NOT EXISTS meter_hour_value_" + partitionDate +
                "        PARTITION OF meter_hour_value " +
                "        FOR VALUES FROM ('" +start  +"') TO ('"+ end+"')";

        new JdbcTemplate(dataSource).execute(sql);
    }

在保存记录前执行