postrgresql常用语句

/*创建索引序列，自增id,也可以直接声明id类型为serial

CREATE SEQUENCE "xu"."t_sys_log_main_id_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 99999999
 START 1
 CACHE 1;
ALTER TABLE "xu"."t_sys_log_main_id_seq" OWNER TO "postgres";
*/

/*创建父表
CREATE TABLE "xu"."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 "xu"."t_sys_log_main" IS '系统日志表';
COMMENT ON COLUMN "xu"."t_sys_log_main"."account_affiliation_code" IS '帐号所属机构代码';
COMMENT ON COLUMN "xu"."t_sys_log_main"."account_affiliation" IS '帐号所属机构';
COMMENT ON COLUMN "xu"."t_sys_log_main"."operation_time" IS '操作时间';
COMMENT ON COLUMN "xu"."t_sys_log_main"."operation_key" IS '操作类型(key)';
COMMENT ON COLUMN "xu"."t_sys_log_main"."operation_value" IS '操作类型(value)';
COMMENT ON COLUMN "xu"."t_sys_log_main"."operation_loginid" IS '操作帐号';
COMMENT ON COLUMN "xu"."t_sys_log_main"."operation_message" IS '操作信息';
COMMENT ON COLUMN "xu"."t_sys_log_main"."operation_ip" IS '登录地址';

ALTER TABLE "xu"."t_sys_log_main" ADD PRIMARY KEY ("id");
*/
/*创建分区表，分区表从父表继承，数据类型和字段数没有变化，主要以时间为分区键，也可以使用其他关键字分区。
create table xu.t_sys_log_y2016m09
(CHECK (operation_time >= DATE '2016-09-01' AND operation_time< DATE '2016-10-01'))
INHERITS (xu.t_sys_log_main);

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

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

create table xu.t_sys_log_y2016m12
(CHECK (operation_time >= DATE '2016-12-01' AND operation_time< DATE '2017-01-01'))
INHERITS (xu.t_sys_log_main);
*/
/*
根据分区键，为每张分区表建立索引。
create index t_sys_log_y2016m09_operation_time ON xu.t_sys_log_y2016m09(operation_time);
create index t_sys_log_y2016m10_operation_time ON xu.t_sys_log_y2016m10(operation_time);
create index t_sys_log_y2016m11_operation_time ON xu.t_sys_log_y2016m11(operation_time);
create index t_sys_log_y2016m12_operation_time ON xu.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 xu.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 xu.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 xu.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 xu.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 xu.t_sys_log_main
FOR EACH ROW
EXECUTE PROCEDURE sys_log_insert_trigger();
*/
/*

插入数据。
INSERT INTO xu.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 xu.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 xu.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 xu.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 xu.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 xu.t_sys_log_main VALUES 
(1,'200027', '西安高新第一中学初中校区', '2016-12-25 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
*/

select * from t_sys_log_main;

--查询分区表
select
    avg(expense)
from
    (select
        client_key,
        sum(expense) as expense
    from
        almart
    where
        date_key = date '2015-12-13'
    group by 1
    )；

--查询非分区表    
select
    avg(expense)
from
    (select
        client_key,
        sum(expense) as expense
    from
        almart_all
    where
        date_key = date '2015-12-13'
    group by 1
    ) foo；

--创建主表         
    CREATE TABLE tbl_partition
(
  id integer,
  name text,
  data numeric,
  gather_time timestamp
);
--创建自动分区
CREATE OR REPLACE FUNCTION auto_insert_into_tbl_partition()
  RETURNS trigger AS
$BODY$
DECLARE
    time_column_name    text ;          -- 父表中用于分区的时间字段的名称[必须首先初始化!!]
    curMM       varchar(6);     -- 'YYYYMM'字串,用做分区子表的后缀
    isExist         boolean;        -- 分区子表,是否已存在
    startTime       text;
    endTime     text;
    strSQL          text;

BEGIN
    -- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]
    time_column_name := TG_ARGV[0];

    -- 判断对应分区表 是否已经存在?
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
    curMM := to_char( strSQL::timestamp , 'YYYYMM' );
    select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curMM);

    -- 若不存在, 则插入前需 先创建子分区
    IF ( isExist = false ) THEN  
        -- 创建子分区表
        startTime := curMM||'01 00:00:00.000';
        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curMM||
                  ' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
                             ||time_column_name||'< '''|| endTime ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL; 
        -- 创建索引
        strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||curMM||'_INDEX_'||time_column_name||' ON '
                  ||TG_RELNAME||'_'||curMM||' ('||time_column_name||');' ;
        EXECUTE strSQL;       
    END IF; 
    -- 插入数据到子分区!
    strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*' ;
    EXECUTE strSQL USING NEW;
    RETURN NULL; 
END
$BODY$
  LANGUAGE plpgsql;
 ---调度触发器
 CREATE TRIGGER insert_tbl_partition_trigger
  BEFORE INSERT
  ON tbl_partition
  FOR EACH ROW
  EXECUTE PROCEDURE auto_insert_into_tbl_partition('gather_time');
--插入数据
INSERT INTO public.tbl_partition(
    id, name, data, gather_time)
    VALUES (2, '大海都是水', 123,  '2018-08-12 14:23:15.012');

select pg_size_pretty(pg_database_size('postgres'));

/*
CREATE TABLE xu.almart
(
    date_key date,
    hour_key smallint,
    client_key integer,
    item_key integer,
    account integer,
    expense numeric
);
*/
/*
CREATE TABLE xu.almart_2015_12_10 () inherits (xu.almart);
CREATE TABLE xu.almart_2015_12_11 () inherits (xu.almart);
CREATE TABLE xu.almart_2015_12_12 () inherits (xu.almart);
CREATE TABLE xu.almart_2015_12_13 () inherits (xu.almart);
*/
/*
ALTER TABLE xu.almart_2015_12_10
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-10'::date);
ALTER TABLE xu.almart_2015_12_11
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-11'::date);
ALTER TABLE xu.almart_2015_12_12
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-12'::date);
ALTER TABLE xu.almart_2015_12_13
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-13'::date);
*/
/*
CREATE INDEX almart_date_key_2015_12_10
ON xu.almart_2015_12_10 (date_key);
CREATE INDEX almart_date_key_2015_12_11
ON xu.almart_2015_12_11 (date_key);
CREATE INDEX almart_date_key_2015_12_12
ON xu.almart_2015_12_12 (date_key);
CREATE INDEX almart_date_key_2015_12_13
ON xu.almart_2015_12_13 (date_key);
*/
--创建分区函数
/*
CREATE OR REPLACE FUNCTION almart_partition_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.date_key = DATE '2015-12-10'
    THEN
        INSERT INTO xu.almart_2015_12_10 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-11'
    THEN
        INSERT INTO xu.almart_2015_12_11 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-12'
    THEN
        INSERT INTO xu.almart_2015_12_12 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-13'
    THEN
        INSERT INTO xu.almart_2015_12_13 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-14'
    THEN
        INSERT INTO xu.almart_2015_12_14 VALUES (NEW.*);
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
*/
--挂载分区Trigger
/*
CREATE TRIGGER insert_almart_partition_trigger
BEFORE INSERT ON xu.almart
FOR EACH ROW EXECUTE PROCEDURE almart_partition_trigger();
*/
/*
CREATE TABLE xu.almart_all
(
    date_key date,
    hour_key smallint,
    client_key integer,
    item_key integer,
    account integer,
    expense numeric
);
*/
/*
INSERT INTO xu.almart_all
select
    (select
        array_agg(i::date)
     from generate_series('2015-12-01'::date, '2015-12-30'::date, '1 day'::interval) as t(i)
    )[floor(random()*4)+1] as date_key,
    floor(random()*24) as hour_key,
    floor(random()*1000000)+1 as client_key,
    floor(random()*100000)+1 as item_key,
    floor(random()*20)+1 as account,
    floor(random()*10000)+1 as expense
from
    generate_series(1,300000000,1);
*/   
     select count(*) from xu.almart


     INSERT INTO xu.almart SELECT * FROM xu.almart_all;