创建父表
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);
}
在保存记录前执行
