分区表

range范围分区

• 建表语句

create table demorange(
    int serial8,
    val int,
    crt timestamp
) partition by range (crt);

• 创建过去30天的分区

do language plpgsql
$$
    begin
        for i in 0..30 loop
            execute format($date$ create table demorange_%s partition of demorange for values from ( %L ) to ( %L ) $date$,
                to_char(current_date-i-1, 'mmdd'), current_date - i - 1, current_date - i );
            end loop;
    end;
    $$;

• 查看分区

select  a.relname part,c.relname parent
from pg_class a
join pg_inherits b on a.oid = b.inhrelid
join pg_class c on c.oid = b.inhparent
where c.relname = 'demorange';

• 插入数据

insert into demorange(val, crt) select random(), current_date - (random()*30 + 1 )::int 
from generate_series(1,100000);
-- 或者
insert into demorange(val, crt) select random(), current_date + ((-(random()*30+1)*24)::int || 'hour')::interval 
from generate_series(1,100000);

• 检查数据

select * from demorange where crt < current_date-3 and crt > current_date-4;
select * from demorange_0821;

list分区

• 建表语句

create table demolist(
    int serial8,
    type_code varchar,
    val int
) partition by list (type_code);

• 建立分区

do language plpgsql
$$
    begin
        for i in 0..30 loop
            execute format($list$ create table demolist_%s partition of demolist for values in (%s) $list$,
                i, i);
            end loop;
    end;
    $$;

• 插入数据

  insert into demolist(type_code, val)  select (random()*30)::int, random()
  from generate_series(1, 100000);

hash分区

• 建表语句

create table demohash(
    int serial8,
    val int,
    crt timestamp
) partition by hash (val);

• 建立分区

do language plpgsql
$$
    begin
        for i in 0..30 loop
            execute format($list$ create table demohash_%s partition of demohash for values with (modulus 31, remainder %s) $list$,
                i, i);
            end loop;
    end;
    $$;

• 插入数据

insert into demohash(val, crt)  select (random()*10000)::int, current_date - ((random()*1000)::int||'hour')::interval
from generate_series(1, 100000);

-- 查询分区表
select * from demohash;
select * from demohash_1;