postgresql根据表创建对应的分区表
来源:互联网 发布:js动态获取input的值 编辑:程序博客网 时间:2024/06/03 18:44
pg的分区表基于表继承
原orders表
CREATE TABLE public.orders
(
orderid integer NOT NULL DEFAULT nextval('orders_orderid_seq'::regclass),
orderdate date NOT NULL,
customerid integer,
netamount numeric(12,2) NOT NULL,
tax numeric(12,2) NOT NULL,
totalamount numeric(12,2) NOT NULL,
CONSTRAINT orders_pkey PRIMARY KEY (orderid),
CONSTRAINT fk_customerid FOREIGN KEY (customerid)
REFERENCES public.customers (customerid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL
)
create table orders_2004_01(check(orderdate>=date '2004-01-01' and orderdate<date '2004-02-01')) inherits(orders);
create table orders_2004_02(check(orderdate>=date '2004-02-01' and orderdate<date '2004-03-01')) inherits(orders);
create table orders_2004_03(check(orderdate>=date '2004-03-01' and orderdate<date '2004-04-01')) inherits(orders);
create table orders_2004_04(check(orderdate>=date '2004-04-01' and orderdate<date '2004-05-01')) inherits(orders);
create table orders_2004_05(check(orderdate>=date '2004-05-01' and orderdate<date '2004-06-01')) inherits(orders);
create table orders_2004_06(check(orderdate>=date '2004-06-01' and orderdate<date '2004-07-01')) inherits(orders);
create table orders_2004_07(check(orderdate>=date '2004-07-01' and orderdate<date '2004-08-01')) inherits(orders);
create table orders_2004_08(check(orderdate>=date '2004-08-01' and orderdate<date '2004-09-01')) inherits(orders);
create table orders_2004_09(check(orderdate>=date '2004-09-01' and orderdate<date '2004-10-01')) inherits(orders);
create table orders_2004_10(check(orderdate>=date '2004-10-01' and orderdate<date '2004-11-01')) inherits(orders);
create table orders_2004_11(check(orderdate>=date '2004-11-01' and orderdate<date '2004-12-01')) inherits(orders);
create table orders_2004_12(check(orderdate>=date '2004-12-01' and orderdate<date '2005-01-01')) inherits(orders);
分区表添加主键
alter table only orders_2004_01 add constraint orders_2004_01_pkey primary key(orderid);
alter table only orders_2004_02 add constraint orders_2004_02_pkey primary key(orderid);
alter table only orders_2004_03 add constraint orders_2004_03_pkey primary key(orderid);
alter table only orders_2004_04 add constraint orders_2004_04_pkey primary key(orderid);
alter table only orders_2004_05 add constraint orders_2004_05_pkey primary key(orderid);
alter table only orders_2004_06 add constraint orders_2004_06_pkey primary key(orderid);
alter table only orders_2004_07 add constraint orders_2004_07_pkey primary key(orderid);
alter table only orders_2004_08 add constraint orders_2004_08_pkey primary key(orderid);
alter table only orders_2004_09 add constraint orders_2004_09_pkey primary key(orderid);
alter table only orders_2004_10 add constraint orders_2004_10_pkey primary key(orderid);
alter table only orders_2004_11 add constraint orders_2004_11_pkey primary key(orderid);
alter table only orders_2004_12 add constraint orders_2004_12_pkey primary key(orderid);
创建本地索引
create index ix_orders_2004_01_custid on orders_2004_01 using btree(customerid);
create index ix_orders_2004_02_custid on orders_2004_02 using btree(customerid);
create index ix_orders_2004_03_custid on orders_2004_03 using btree(customerid);
create index ix_orders_2004_04_custid on orders_2004_04 using btree(customerid);
create index ix_orders_2004_05_custid on orders_2004_05 using btree(customerid);
create index ix_orders_2004_06_custid on orders_2004_06 using btree(customerid);
create index ix_orders_2004_07_custid on orders_2004_07 using btree(customerid);
create index ix_orders_2004_08_custid on orders_2004_08 using btree(customerid);
create index ix_orders_2004_09_custid on orders_2004_09 using btree(customerid);
create index ix_orders_2004_10_custid on orders_2004_10 using btree(customerid);
create index ix_orders_2004_11_custid on orders_2004_11 using btree(customerid);
create index ix_orders_2004_12_custid on orders_2004_12 using btree(customerid);
添加约束
alter table only orders_2004_01 add constraint fk_2004_01_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_02 add constraint fk_2004_02_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_03 add constraint fk_2004_03_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_04 add constraint fk_2004_04_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_05 add constraint fk_2004_05_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_06 add constraint fk_2004_06_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_07 add constraint fk_2004_07_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_08 add constraint fk_2004_08_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_09 add constraint fk_2004_09_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_10 add constraint fk_2004_10_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_11 add constraint fk_2004_11_customerid foreign key(customerid) references customers(customerid) on delete set null;
alter table only orders_2004_12 add constraint fk_2004_12_customerid foreign key(customerid) references customers(customerid) on delete set null;
迁移数据到分区表
create or replace function orders_update_trigger()
returns trigger as $$
begin
delete from orders where old.orderid=orderid;
insert into orders values(new.*);
return null;
end;
$$
language plpgsql;
create trigger update_orders before update on orders for each row execute procedure orders_update_trigger();
begin;
select * from orders;
select count(*) from orders_2004_01;
select count(*) from orders_2004_02;
update orders set orderid=orderid;
select count(*) from orders_2004_12;
select count(*) from orders_2004_01;
commit
- postgresql根据表创建对应的分区表
- postgresql 分区表 创建函数
- postgresql分区表创建
- 转载:PostgreSQL的分区表
- postgresql分区表的使用
- postgresql 中分区表的创建及使用样例
- PostgreSQL 创建分区表,SQL优化之PostgreSQL Table Partitioning
- postgresql分区表
- 根据存储过程名创建对应的命令过程
- oracle,查询分区表和非分区表,索引对应的表空间sql
- 对大表创建分区表的案例
- mysql 分区表 的创建
- oralce,创建分区,子分区表,分区索引,查询分区对应的数据sql
- 关于Postgresql 分区表的那些事
- PostgreSQL分区表的性能损耗验证
- ORACLE 分区表种类以及分区表的创建
- 创建分区表和查看分区表的Metadata
- Hive---外部分区表的创建
- 正确设置与使用SQL Server的字符集(Collation,即排序规则)
- Android Theme中的一些属性
- gerrit+http+2
- Android Bluetooth笔记
- HTTP协议详解
- postgresql根据表创建对应的分区表
- 纯css 实现下拉菜单
- 去哪儿网前端架构师司徒正美:如何挑选适合的前端框架?
- Android GridView实战:仿微信、支付宝钱包宫格界面
- 走在Java道路上的心得体会:2015.10——至今(持续更新)
- 有return的情况下try catch finally的执行顺序
- 获取一个字符串在另一个字符串中出现的次数
- IP Header
- 明锐 卡罗拉