Shulou(Shulou.com)06/01 Report--

The following is to sort out and record some SQL that you often use, which is also convenient for future reference:

Description

SQL

Add a partition (without creating a DEFAULT PARTITION)

Alter table tablename add partition partition_name start (BIGINT '1471276800000') inclusive end (BIGINT' 1471363200000') exclusive

Add a partition (in the case of creating a DEFAULT PARTITION)

ALTER TABLE tablename SPLIT DEFAULT PARTITION

START (1471363200000) INCLUSIVE END (1471449600000) EXCLUSIVE

INTO (PARTITION partition_name, default partition)

Modify the value of ID sequence to improve efficiency

ALTER SEQUENCE tablename_seq_name CACHE 100

Create Partition

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)

Create partitions (create N partitions with a day granularity of 20160701 to 20170101, one partition every other day)

PARTITION BY RANGE (TIME) (

PARTITION partition_name START (BIGINT '1467302400000') INCLUSIVE END (BIGINT' 1483200000000') EXCLUSIVE EVERY (BIGINT '86400000')

)

Delete partition

ALTER TABLE tablename DROP PARTITION partition_name

Delete table partition data

ALTER TABLE tablename TRUNCATE PARTITION partition_name

Delete column

ALERT TABLE tablename DROP COLUMN column_name

Delete sequence

DROP SEQUENCE serial_name

Add column

ALTER TABLE tablename ADD COLUMN column_name column_type

Modify column type

ALTER TABLE tablename ALTER COLUMN column_name

TYPE new_column_type

Examples of function operation

Example one

Delete function: DROP FUNCTION function_name (varchar)

Create a function:

CREATE OR REPLACE FUNCTION convert_grade (grade varchar)

RETURNS integer AS

$BODY$

DECLARE

Result int

BEGIN

If grade = 'excellent' then result = 1

ELSIF grade = 'good' then result = 2

ELSIF grade = 'medium' then result = 3

ELSIF grade = 'poor' then result = 4

End if

RETURN result

END

$BODY$

LANGUAGE plpgsql VOLATILE

Example two

-create a function to generate the RADIUS table. Parameter: Pinyin of the province-

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

-required tables for creating all provinces-

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';-delete first

PERFORM CREATE_RADIUS_TABLE (province)

END LOOP

RETURN passed

END

$$LANGUAGE plpgsql

Example three

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

-- get the partition name

Pname: = to_char (beginTime,'yyyymmddhh34')

-- start milliseconds

Smsc = extract (epoch from beginTime):: bigint * 1000

-- end millisecond

BeginTime: = (beginTime + interval'1 hour')

Emsc = extract (epoch from beginTime):: bigint * 1000

-- add a partition

EXECUTE 'ALTER TABLE' | |' JK_COMPLEX_XDR' | | 'SPLIT DEFAULT PARTITION START (' | | smsc | |') INCLUSIVE

END ('| | emsc | |') EXCLUSIVE INTO (PARTITION paired daylight'| | pname | |', default partition)'

PartitionNum: = partitionNum-1

RAISE INFO 'current num:%, pname:%, stime:%,etime:%',partitionNum,pname,smsc,emsc

END LOOP

RETURN passed

END

$$LANGUAGE plpgsql

Example 4: return multiple result sets

CREATE OR REPLACE FUNCTION get_record ()

RETURNS SETOF RECORD AS $$

DECLARE

Rec RECORD

BEGIN

FOR rec IN

SELECT OBJECT_NAME FROM CM_PROVINCE

LOOP

RETURN NEXT rec

END LOOP

RETURN

END

$$LANGUAGE plpgsql

-- execute function

Select * from get_record () t (name varchar)

Add a two-tier partition example

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')

);

-- add primary partition

Alter table p_test add partition p_name_x VALUES ('x')

Modify the sub-partition template, and the sub-partition will be generated according to the new template. Existing partitions will not be modified

ALTER TABLE p_test SET SUBPARTITION TEMPLATE

(

SUBPARTITION sp_time_222 START (BIGINT '222') INCLUSIVE END (BIGINT '333') EXCLUSIVE

DEFAULT SUBPARTITION other

)

-- check the partition

SELECT partitionboundary, partitiontablename

Partitionname, partitionlevel, partitionrank

FROM pg_partitions WHERE tablename='p_test'

-- query the specified subpartition

Select * from p_test subpartition (sp_time_111)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.