下面整理记录一些时常会使用到的SQL,也方便以后查阅：
专注于为中小企业提供成都网站设计、做网站服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业正阳免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了数千家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。
描述 | SQL |
添加分区（在没有创建DEFAULT PARTITION的情况下） | alter tabletablename add partition partition_namestart (BIGINT '1471276800000') inclusive end (BIGINT '1471363200000') exclusive; |
添加分区（在创建DEFAULT PARTITION的情况下） | ALTER TABLE tablename SPLIT DEFAULT PARTITION START (1471363200000) INCLUSIVE END (1471449600000) EXCLUSIVE INTO (PARTITIONpartition_name, default partition) |
修改ID序列取值的方式，提高效率 | ALTER SEQUENCEtablename_seq_name CACHE 100 |
创建分区 | PARTITION BY RANGE (date) (PARTITION Jan16 START (BIGINT '1451577600') INCLUSIVE , PARTITION Feb16 START (BIGINT '1454256000') INCLUSIVE , PARTITION Mar16 START (BIGINT '1456761600') INCLUSIVE , PARTITION Apr16 START (BIGINT '1459440000') INCLUSIVE , PARTITION May16 START (BIGINT '1462032000') INCLUSIVE , PARTITION Jun16 START (BIGINT '1464710400') INCLUSIVE , PARTITION Jul16 START (BIGINT '1467302400') INCLUSIVE , PARTITION Aug16 START (BIGINT '1469980800') INCLUSIVE , PARTITION Sep16 START (BIGINT '1472659200') INCLUSIVE , PARTITION Oct16 START (BIGINT '1475251200') INCLUSIVE , PARTITION Nov16 START (BIGINT '1477929600') INCLUSIVE , PARTITION Dec16 START (BIGINT '1480521600') INCLUSIVE END (BIGINT '1483200000') EXCLUSIVE ); |
创建分区(会创建天粒度的N多个分区20160701到20170101，每间隔一天一个分区) | PARTITION BY RANGE (TIME)( PARTITION partition_nameSTART (BIGINT ‘1467302400000’) INCLUSIVE END(BIGINT ‘1483200000000’) EXCLUSIVE EVERY(BIGINT ‘86400000’) ) |
删除分区 | ALTER TABLEtablename DROP PARTITION partition_name; |
删除表分区数据 | ALTER TABLEtablename TRUNCATE PARTITION partition_name; |
删除列 | ALERT TABLEtablenameDROP COLUMNcolumn_name; |
删除序列 | DROP SEQUENCE serial_name |
添加列 | ALTER TABLEtablename ADD COLUMN column_name column_type |
修改列类型 | ALTER TABLEtablename ALTER COLUMNcolumn_name TYPEnew_column_type |
函数操作举例
例子一
删除函数： DROP FUNCTION function_name(varchar);
创建函数：
CREATE OR REPLACE FUNCTION convert_grade(grade varchar)
RETURNS integer AS
$BODY$
DECLARE
result int;
BEGIN
if grade = '优' then result = 1;
ELSIF grade ='良' then result = 2;
ELSIF grade = '中' then result = 3 ;
ELSIF grade = '差' then result = 4 ;
end if ;
RETURN result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
例子二
---------创建生成RADIUS表的函数,参数：省份的拼音------------------
CREATE OR REPLACE FUNCTION CREATE_RADIUS_TABLE(province varchar)
RETURNS BOOLEAN AS $$
DECLARE
passed BOOLEAN;
BEGIN
EXECUTE
'CREATE TABLE JK_'||province||'_RADIUS(
ID bigserial,
start_time BIGINT,
end_time BIGINT,
user_account VARCHAR,
src_ip VARCHAR,
bras VARCHAR
INSERT_DATE DATE DEFAULT NOW()
)DISTRIBUTED BY (ID)';
EXECUTE 'ALTER SEQUENCE JK_'||province||'_RADIUS_ID_seq CACHE 100' ;
RETURN passed;
END;
$$ LANGUAGE plpgsql ;
----------用于创建所有省的所需的表-------------
CREATE OR REPLACE FUNCTION CREATE_REPLACE_ALL_TABLE()
RETURNS BOOLEAN AS $$
DECLARE
passed BOOLEAN:=TRUE;
province varchar;
BEGIN
FOR province IN
SELECT OBJECT_EN_NAME FROM CM_PROVINCE
LOOP
EXECUTE 'DROP TABLE IF EXISTS JK_'||province||'_RADIUS'; --先删除
PERFORM CREATE_RADIUS_TABLE(province);
END LOOP;
RETURN passed;
END;
$$ LANGUAGE plpgsql ;
例子三
CREATE OR REPLACE FUNCTION CREATE_PARTITION(beginT timestamp with time zone,partitionN INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
passed BOOLEAN:=TRUE;
pname VARCHAR ;
smsc bigint ;
emsc bigint ;
beginTime timestamp with time zone;
partitionNum INTEGER ;
BEGIN
beginTime := to_TIMESTAMP(to_char(beginT,'yyyymmddhh34'),'yyyymmddhh34') ;
partitionNum := partitionN;
WHILE partitionNum > 0 LOOP
--得到分区名称
pname := to_char(beginTime,'yyyymmddhh34');
--开始毫秒数
smsc = extract(epoch from beginTime)::bigint * 1000;
--结束毫秒
beginTime := (beginTime + interval '1 hour');
emsc = extract(epoch from beginTime)::bigint * 1000;
--添加分区
EXECUTE 'ALTER TABLE'||'JK_COMPLEX_XDR'||' SPLIT DEFAULT PARTITION START ('||smsc||') INCLUSIVE
END ('||emsc||') EXCLUSIVE INTO (PARTITION p_day_'||pname||', default partition)';
partitionNum := partitionNum-1;
RAISE INFO 'current num: %,pname: %,stime:%,etime:%',partitionNum,pname,smsc,emsc;
END LOOP;
RETURN passed;
END;
$$ LANGUAGE plpgsql ;
例四：返回多条结果集
CREATE OR REPLACE FUNCTION get_record()
RETURNS SETOF RECORDAS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT OBJECT_NAME FROM CM_PROVINCE
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql ;
--执行函数
select * from get_record() t(name varchar);
添加两层分区举例
create table p_test(
ID integer,
name varchar ,
time bigint
)DISTRIBUTED BY (ID)
PARTITION BY LIST(name)
SUBPARTITION BY RANGE(time)
SUBPARTITION TEMPLATE
(
SUBPARTITION sp_time_111 START (BIGINT '111') INCLUSIVE END (BIGINT '222') EXCLUSIVE ,
default SUBPARTITION sp_time_other
)
(
PARTITION p_name_d VALUES('d'),
PARTITION p_name_s VALUES('s'),
PARTITION p_name_f VALUES('f')
);
--添加主分区
alter table p_test add partition p_name_x VALUES('x');
--修改子分区模版,子分区将按照新的模版产生。已经存在的分区不会被修改
ALTER TABLE p_test SET SUBPARTITION TEMPLATE
(
SUBPARTITION sp_time_222 START (BIGINT '222') INCLUSIVE END (BIGINT '333') EXCLUSIVE ,
DEFAULT SUBPARTITION other
)
--查看分区情况
SELECT partitionboundary, partitiontablename,
partitionname, partitionlevel, partitionrank
FROM pg_partitions WHERE tablename='p_test';
--查询指定的子分区
select * from p_test subpartition(sp_time_111);
