postgresql分区表

概述创建自动分区采用两种方式 采用视图分区方式 采用直接分区方式 创建表 CREATE TABLE IF NOT EXISTS public.sales( id bigserial primary key , store_id varchar(50) , business_date date, start_time time, end_time time,

创建自动分区采用两种方式@H_404_2@ 采用视图分区方式 采用直接分区方式

创建表

CREATE table IF NOT EXISTS public.sales(    ID bigserial primary key,store_ID varchar(50),business_date date,start_time time,end_time time,dine_in_tc int,delivery_tc int,takeout_tc int,dine_in_s decimal(20,4),delivery_s decimal(20,takeout_s decimal(20,voucher_overcharge decimal(20,freight decimal(20,currency varchar(16),created_at timestamp default Now(),updated_at timestamp default Now());

创建索引

CREATE INDEX sales_store_ID ON public.sales (store_ID);CREATE INDEX sales_business_date ON public.sales (business_date);ALTER table public.sales  ADD CONSTRAINT sales_storeID_businessdate_starttime_endtime UNIQUE(store_ID,business_date,start_time,end_time);

1.采用视图分区方式 建立视图

CREATE VIEW public.sales_vIEw AS SELECT * FROM public.sales;

定义分表function

CREATE OR REPLACE FUNCTION public.insert_sales() RETURNS TRIGGER AS".'$BODY'."$DECLARE    _start_dt text;    _end_dt text;    _table_name text;BEGIN     IF NEW.ID IS NulL THENNEW.ID := nextval('sales_ID_seq');    // 保证分区后的ID可以自增END IF;_table_name := 'sales_vIEw_' || to_char(NEW.business_date,'YYYY_MM');    // 按照时间进行分区PERFORM 1FROM   pg_catalog.pg_class cJOIN   pg_catalog.pg_namespace n ON n.oID = c.relnamespaceWHERE  c.relkind = 'r'AND    c.relname = _table_nameAND    n.nspname = public;IF NOT FOUND THEN    _start_dt := to_char(date_trunc('month',NEW.business_date),'YYYY-MM-DD');    _end_dt:=_start_dt::timestamp + INTERVAL '1 month';    EXECUTE     'CREATE table public.'     || quote_IDent(_table_name)     || ' (CHECK (business_date >= '     || quote_literal(_start_dt)     || 'AND business_date < '     || quote_literal(_end_dt)     || ')) inheritS (public.sales)';    EXECUTE 'CREATE INDEX ' || quote_IDent(_table_name||'_business_date'||_start_dt) || ' ON public.' || quote_IDent(_table_name) || ' (business_date)';    EXECUTE 'CREATE INDEX ' || quote_IDent(_table_name||'_store_ID'||_start_dt) || ' ON public.' || quote_IDent(_table_name) || ' (store_ID)';    EXECUTE 'ALTER table public.' || quote_IDent(_table_name) || ' ADD CONSTRAINT ' || quote_IDent(_table_name||'_storeID_businessdate_starttime_endtime'||_start_dt) || ' UNIQUE (store_ID,end_time)';    EXECUTE 'ALTER table public.' || quote_IDent(_table_name) || ' OWNER TO ' || quote_IDent(current_user);    EXECUTE 'GRANT ALL ON table public.' || quote_IDent(_table_name) || ' TO ' || quote_IDent(current_user);END IF;    EXECUTE 'INSERT INTO public.' || quote_IDent(_table_name) || ' VALUES (.*) RETURNING *' USING NEW;    RETURN NEW;END;".'$BODY'."$LANGUAGE plpgsql;";

分表触发器

CREATE TRIGGER insert_sales_trigger INSTEAD OF INSERT ON public.sales_vIEw FOR EACH ROW EXECUTE PROCEDURE insert_sales();

定义更新function

CREATE OR REPLACE FUNCTION update_sales()RETURNS TRIGGER AS $$BEGIN    DELETE FROM sales_vIEw WHERE ID = NEW.ID;    INSERT INTO sales_vIEw VALUES (NEW.*);    RETURN NEW;END;$$ LANGUAGE plpgsql;";

更新触发器

CREATE TRIGGER update_sales_trigger INSTEAD OF UPDATE ON sales_vIEw FOR EACH ROW EXECUTE PROCEDURE update_oc_sales();

2.直接分区方式

CREATE OR REPLACE FUNCTION insert_sales() RETURNS TRIGGER AS".'$BODY'."$DECLARE    _start_dt text;    _end_dt text;    _table_name text;BEGIN     IF NEW.ID IS NulL THEN    NEW.ID := nextval('".$this->tablename."_ID_seq');  END IF;_table_name := 'sales_' || to_char(NEW.business_date,'YYYY_MM');PERFORM 1FROM   pg_catalog.pg_class cJOIN   pg_catalog.pg_namespace n ON n.oID = c.relnamespaceWHERE  c.relkind = 'r'AND    c.relname = _table_nameAND    n.nspname = 'public';IF NOT FOUND THEN    _start_dt := to_char(date_trunc('month','YYYY-MM-DD');    _end_dt:=_start_dt::timestamp + INTERVAL '1 month';    EXECUTE     'CREATE table IF NOT EXISTS public.'     || quote_IDent(_table_name)     || ' (CHECK (business_date >= '     || quote_literal(_start_dt)     || 'AND business_date < '     || quote_literal(_end_dt)     || ')) inheritS (public.sales)';    EXECUTE 'CREATE INDEX IF NOT EXISTS' || quote_IDent(_table_name||'_business_date'||_start_dt) || ' ON public.' || quote_IDent(_table_name) || ' (business_date)';    EXECUTE 'CREATE INDEX IF NOT EXISTS' || quote_IDent(_table_name||'_store_ID'||_start_dt) || ' ON public.' || quote_IDent(_table_name) || ' (store_ID)';    EXECUTE 'CREATE UNIQUE INDEX IF NOT EXISTS' || quote_IDent(_table_name||'_storeID_businessdate_starttime_endtime'||_start_dt) || ' ON public.' || quote_IDent(_table_name) || ' (store_ID,end_time)';    EXECUTE 'ALTER table public.' || quote_IDent(_table_name) || ' OWNER TO ' || quote_IDent(current_user);    EXECUTE 'GRANT ALL ON table public.' || quote_IDent(_table_name) || ' TO ' || quote_IDent(current_user);END IF;    EXECUTE 'INSERT INTO public.' || quote_IDent(_table_name) || ' VALUES (.*) on conflict(store_ID,end_time) do nothing RETURNING *' USING NEW;    RETURN NulL;END;".'$BODY'."$LANGUAGE plpgsql;

分表触发器

CREATE TRIGGER insert_sales_trigger BEFORE INSERT ON public.salses FOR EACH ROW EXECUTE PROCEDURE insert_sales();

两种方式比较 视图分区所有 *** 作都是对视图的 *** 作，直接分区是对主表进行 *** 作； 视图分区触发器使用instead of,直接分区使用before,因为无法直接用触发器替代对主表的 *** 作，只能 *** 作视图； 视图分区用instead of，在function中可以RETURN NEW，对数据库 *** 作后有明确的返回，直接分区用before方式，在function中采用RETURN NulL,数据库 *** 作没有返回； 直接分区可以用on conflict对主表insert进行ignore *** 作，视图分区不能。 总结

以上是内存溢出为你收集整理的postgresql分区表全部内容，希望文章能够帮你解决postgresql分区表所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。