--创建父表 drop table num_master; create table num_master (id int not null primary key); --创建生成分区表的函数 create or replace function create_partition_table () returns void as $$ declare i int; declare cnt int; declare stmt text; begin i:= 0; cnt:=4; <<lable1>> while i < cnt loop stmt := 'create table num_slave'||i+1||'(check(id >='||i*100||' and id <'||(i+1)*100||')) inherits(num_master)'; execute stmt; i:=i + 1; end loop lable1; return; end; $$ language plpgsql; --执行分区表的创建 select create_partition_table (); --创建触发器函数 create or replace function num_insert_trigger() returns trigger as $$ begin if (new.id >=0 and new.id <100) then insert into num_slave1 values (new.*); elsif (new.id >=100 and new.id <200) then insert into num_slave2 values(new.*); elsif (new.id >=200 and new.id <300) then insert into num_slave3 values (new.*); elsif (new.id >=300 and new.id <400) then insert into num_slave4 values (new.*); else raise exception 'Column id out of range.'; end if; return null; end; $$ language plpgsql; --创建触发器 CREATE TRIGGER insert_num_master_trigger BEFORE INSERT ON num_master FOR EACH ROW EXECUTE PROCEDURE num_insert_trigger(); test=# \d num_master Table "public.num_master" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "num_master_pkey" PRIMARY KEY,btree (id) Number of child tables: 4 (Use \d+ to list them.) test=# \d+ num_master Table "public.num_master" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- id | integer | not null | plain | | Indexes: "num_master_pkey" PRIMARY KEY,btree (id) Triggers: insert_num_master_trigger BEFORE INSERT ON num_master FOR EACH ROW EXECUTE PROCEDURE num_insert_trigger() Child tables: num_slave1,num_slave2,num_slave3,num_slave4 Has OIDs: no --插入测试数据 \timing on insert into num_master select id from (select generate_series(1,399))ids(id); --查看测试的数据 test=# select count(*) from num_master; count ------- 399 (1 row) Time: 0.577 ms test=# select count(*) from num_slave1; count ------- 99 (1 row) Time: 0.327 ms test=# select count(*) from num_slave2; count ------- 100 (1 row) Time: 0.304 ms test=# select count(*) from num_slave3; count ------- 100 (1 row) Time: 0.424 ms test=# select count(*) from num_slave4; count ------- 100 (1 row) --查看查询下优化器的选择 test=# explain select * from num_master where id > 30 and id < 120; QUERY PLAN ----------------------------------------------------------------- Append (cost=0.00..4.98 rows=91 width=4) -> Seq Scan on num_master (cost=0.00..0.00 rows=1 width=4) Filter: ((id > 30) AND (id < 120)) -> Seq Scan on num_slave1 (cost=0.00..2.48 rows=70 width=4) Filter: ((id > 30) AND (id < 120)) -> Seq Scan on num_slave2 (cost=0.00..2.50 rows=20 width=4) Filter: ((id > 30) AND (id < 120)) (7 rows) 可以看到，选择了分区表进行查询 原文链接：https://www.f2er.com/postgresql/194646.html