PgSql分区表

创建分区父表

create table tab_aken(
    uid integer  not null,--把int换成serial自增
    ubirthday timestamp not null,
    uname varcahr ,
    primary key(uid,ubirthday ))partition by range(ubirthday );--分区字段必须主键

创建年月分区

create table aken_2023_1 partition of tab_aken for values from('2023-01-01') to('2023-01-01'::timestamp+interval'1 month');
    create table aken_2023_2 partition of tab_aken for values from('2023-02-01') to('2023-02-01'::timestamp+interval'1 month');
    create table aken_2023_3 partition of tab_aken for values from('2023-03-01') to('2023-03-01'::timestamp+interval'1 month');
    create table aken_2023_4 partition of tab_aken for values from('2023-04-01') to('2023-04-01'::timestamp+interval'1 month');
    create table aken_2023_5 partition of tab_aken for values from('2023-05-01') to('2023-05-01'::timestamp+interval'1 month');
    create table aken_2023_6 partition of tab_aken for values from('2023-06-01') to('2023-06-01'::timestamp+interval'1 month');
    create table aken_2023_7 partition of tab_aken for values from('2023-07-01') to('2023-07-01'::timestamp+interval'1 month');
    create table aken_2023_8 partition of tab_aken for values from('2023-08-01') to('2023-08-01'::timestamp+interval'1 month');
    create table aken_2023_9 partition of tab_aken for values from('2023-09-01') to('2023-09-01'::timestamp+interval'1 month');
    create table aken_2023_10 partition of tab_aken for values from('2023-10-01') to('2023-10-01'::timestamp+interval'1 month');
    create table aken_2023_11 partition of tab_aken for values from('2023-11-01') to('2023-11-01'::timestamp+interval'1 month');
    create table aken_2023_12 partition of tab_aken for values from('2023-12-01') to('2023-12-01'::timestamp+interval'1 month'); 

添加索引

create index tab_aken_idx on tab_aken ; 

--create index tab_aken_idx on tab_aken using btree(ubirthday )

 插入数据

INSERT INTO public.tab_aken(ubirthday , uname)VALUES ('2000-01-01 11:11:11','张三'),('2000-01-01 12:11:11','李四');