分区处理

SELECT * FROM pg_class c,pg_namespace n where relnamespace = n.oid and c.relkind=’r’ and nspname=’public’ and relname=’customer_address’;

MOGDB/OPENGAUSSDB 分区处理

 select pp.* from pg_catalog.pg_partition pp,pg_catalog.pg_class  pc ,pg_catalog.pg_namespace  pn
where pp.parttype ='p' and pp.parentid  = pc.oid and pc.relname ='customer_address'
and pn.oid =pc.relnamespace and pn.nspname ='public';

select pp.* from pg_catalog.pg_partition pp,pg_catalog.pg_class  pc 
where pp.parttype ='p' and pp.parentid  = pc.oid and pc.relfilenode = 25142


 CREATE TABLE public.customer_address
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10),
    ca_street_name      character varying(60),
    ca_street_type      character(15),
    ca_suite_number     character(10),
    ca_city             character varying(60),
    ca_county           character varying(30),
    ca_state            character(2),
    ca_zip              character(10),
    ca_country           character varying(20),
    ca_gmt_offset       numeric(5,2),
    ca_location_type    character(20)
) 
PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE) 
)
ENABLE ROW MOVEMENT;


insert into customer_address (ca_address_sk,ca_address_id,ca_street_number,ca_street_name,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset,ca_location_type) 
values(1,'beijing','hebei','lakj','dbc','ddddd','abc','bbbb','c','ddddd','eeeeeeee',123,'ddddddddddd');

insert into customer_address 
select (select max(ca_address_sk) from customer_address)+ca_address_sk,
ca_address_id,ca_street_number,ca_street_name,ca_street_type,ca_suite_number,ca_city,ca_county,ca_state,ca_zip,ca_country,ca_gmt_offset,ca_location_type 
from customer_address limit 2 ;

POSTGRESQL 分区处理

select ps.relfilenode,ps.relname partitionname,pp.relname from pg_class pp,pg_inherits pi ,pg_class ps 
where pp.relfilenode =pi.inhparent and pi.inhrelid = ps.relfilenode  
and pp.relname ='t_sys_log_main';



CREATE SEQUENCE "public"."t_sys_log_main_id_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 99999999
 START 1
 CACHE 1;

ALTER TABLE "public"."t_sys_log_main_id_seq" OWNER TO "postgres";


CREATE TABLE "public"."t_sys_log_main" (
"id" int4 DEFAULT nextval('t_sys_log_main_id_seq'::regclass) NOT NULL,
"account_affiliation_code" varchar(100) COLLATE "default" NOT NULL,
"account_affiliation" varchar(50) COLLATE "default" NOT NULL,
"operation_time" timestamp(6) NOT NULL,
"operation_key" varchar(2) COLLATE "default" NOT NULL,
"operation_value" varchar(30) COLLATE "default" NOT NULL,
"operation_loginid" varchar(100) COLLATE "default" NOT NULL,
"operation_message" varchar(300) COLLATE "default" NOT NULL,
"operation_ip" varchar(30) COLLATE "default" NOT NULL
)
WITH (OIDS=FALSE)
;

COMMENT ON TABLE "public"."t_sys_log_main" IS '系统日志表';
COMMENT ON COLUMN "public"."t_sys_log_main"."account_affiliation_code" IS '帐号所属机构代码';
COMMENT ON COLUMN "public"."t_sys_log_main"."account_affiliation" IS '帐号所属机构';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_time" IS '操作时间';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_key" IS '操作类型(key)';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_value" IS '操作类型(value)';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_loginid" IS '操作帐号';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_message" IS '操作信息';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_ip" IS '登录地址';

ALTER TABLE "public"."t_sys_log_main" ADD PRIMARY KEY ("id");



create table t_sys_log_y2016m09
(CHECK (operation_time >= DATE '2016-09-01' AND operation_time< DATE '2016-10-01'))
INHERITS (t_sys_log_main);

create table t_sys_log_y2016m10
(CHECK (operation_time >= DATE '2016-10-01' AND operation_time< DATE '2016-11-01'))
INHERITS (t_sys_log_main);

create table t_sys_log_y2016m11
(CHECK (operation_time >= DATE '2016-11-01' AND operation_time< DATE '2016-12-01'))
INHERITS (t_sys_log_main);

create table t_sys_log_y2016m12
(CHECK (operation_time >= DATE '2016-12-01' AND operation_time< DATE '2017-01-01'))
INHERITS (t_sys_log_main);


create index t_sys_log_y2016m09_operation_time ON t_sys_log_y2016m09(operation_time);
create index t_sys_log_y2016m10_operation_time ON t_sys_log_y2016m10(operation_time);
create index t_sys_log_y2016m11_operation_time ON t_sys_log_y2016m11(operation_time);
create index t_sys_log_y2016m12_operation_time ON t_sys_log_y2016m12(operation_time);


CREATE
OR REPLACE FUNCTION sys_log_insert_trigger () RETURNS TRIGGER AS $$
BEGIN

IF (
    NEW .operation_time >= DATE '2016-09-01'
    AND NEW .operation_time < DATE '2016-10-01'
) THEN
    INSERT INTO t_sys_log_y2016m09
VALUES
    (NEW .*) ;
ELSEIF (
    NEW .operation_time >= DATE '2016-10-01'
    AND NEW .operation_time < DATE '2016-11-01'
) THEN
    INSERT INTO t_sys_log_y2016m10
VALUES
    (NEW .*) ;
ELSEIF (
    NEW .operation_time >= DATE '2016-11-01'
    AND NEW .operation_time < DATE '2016-12-01'
) THEN
    INSERT INTO t_sys_log_y2016m11
VALUES
    (NEW .*) ;
ELSEIF (
    NEW .operation_time >= DATE '2016-12-01'
    AND NEW .operation_time < DATE '2017-01-01'
) THEN
    INSERT INTO t_sys_log_y2016m12
VALUES
    (NEW .*) ;
ELSE
    RAISE EXCEPTION 'Date out of range!' ;
END
IF ; RETURN NULL ;
END ; $$ LANGUAGE plpgsql;



CREATE TRIGGER sys_log_insert_trigger BEFORE INSERT ON t_sys_log_main
FOR EACH ROW
EXECUTE PROCEDURE sys_log_insert_trigger();

INSERT INTO t_sys_log_main VALUES 
(1,'200022', '西安高新第一中学初中校区', '2016-9-8 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES 
(1,'200023', '西安高新第一中学初中校区', '2016-9-12 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES 
(1,'200024', '西安高新第一中学初中校区', '2016-10-8 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES 
(1,'200025', '西安高新第一中学初中校区', '2016-11-8 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES 
(1,'200026', '西安高新第一中学初中校区', '2016-12-8 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES 
(1,'200027', '西安高新第一中学初中校区', '2016-12-25 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');