--創(chuàng)建范圍分區(qū)表(RANGE)

DROP TABLE orders_1;

CREATE TABLE orders_1 (

id INT PRIMARY KEY,

order_date CHAR(4) ,

customer_id INT,

product_name VARCHAR(255)

) PARTITION BY RANGE (order_date)

( PARTITION p1 VALUES LESS THAN (2021),

PARTITION p2 VALUES LESS THAN (2022),

PARTITION p3 VALUES LESS THAN (2023),

PARTITION P4 VALUES LESS THAN(MAXVALUE));

--查看創(chuàng)建的分區(qū)表信息

select relname,parttype,parentid,boundaries from pg_partition where parentid in (SELECT parentid FROM pg_partition where relname='orders_1');

--插入測(cè)試數(shù)據(jù)

INSERT INTO orders_1(id,order_date,customer_id,product_name)VALUES(1001,'2021',100,'商品A');

INSERT INTO orders_1(id,order_date,customer_id,product_name)VALUES(1002,'2022',200,'商品B');

INSERT INTO orders_1(id,order_date,customer_id,product_name)VALUES(1003,'2023',300,'商品C');

--訪問分區(qū)表：

-- 只查詢2020年的數(shù)據(jù)

SELECT * FROM orders_1 WHERE order_date = 2021;

-- 查詢2020年至2022年的數(shù)據(jù)

SELECT * FROM orders_1 WHERE order_date BETWEEN 2021 AND 2022;

-- 只查詢2020年之后的數(shù)據(jù)(超出范圍的數(shù)據(jù)將被過濾掉)

SELECT * FROM orders_1 WHERE order_date >= 2021;

--DROP

ALTER TABLE orders_1 DROP PARTITION P5;

--ADD

ALTER TABLE orders_1 ADD PARTITION P4 VALUES LESS THAN (2024);

ALTER TABLE orders_1 ADD PARTITION P6 VALUES LESS THAN (2025);

ALTER TABLE orders_1 ADD PARTITION P10 VALUES LESS THAN (MAXVALUE);

--RENAME

ALTER TABLE orders_1 RENAME PARTITION P10 TO P5;

--查看創(chuàng)建的分區(qū)表信息

select relname,parttype,parentid,boundaries from pg_partition where parentid in (SELECT parentid FROM pg_partition where relname='orders_1') ;

--創(chuàng)建哈希分區(qū)表（HASH）

drop TABLE orders_2;

CREATE TABLE orders_2 (

id INT PRIMARY KEY,

customer_id INT,

order_date DATE

) PARTITION BY HASH (customer_id)

( partition p1,

partition p2 );

--查看創(chuàng)建的分區(qū)表信息

select relname,parttype,parentid,boundaries from pg_partition where parentid in (SELECT parentid FROM pg_partition where relname='orders_2');

--插入測(cè)試數(shù)據(jù)

INSERT INTO orders_2(id,customer_id,order_date)VALUES(1001,100,date '20230613');

INSERT INTO orders_2(id,customer_id,order_date)VALUES(1002,200,date '20230614');

INSERT INTO orders_2(id,customer_id,order_date)VALUES(1003,300,date '20230615');

INSERT INTO orders_2(id,customer_id,order_date)VALUES(1004,400,date '20230612');

----訪問分區(qū)表:

--查詢customer_id 為100的訂單表信息

SELECT * FROM orders_2 WHERE customer_id =100;

--查詢customer_id 為100、200的訂單表信息

SELECT * FROM orders_2 WHERE customer_id IN (100,200);

--查詢customer_id 不是100、200的訂單表信息

SELECT * FROM orders_2 WHERE customer_id NOT IN (100,200);

--刪除分區(qū)表 drop table

DROP TABLE orders_2 ;

--刪除分區(qū)數(shù)據(jù) truncate分區(qū)

alter table orders_2 truncate partition p2;

--創(chuàng)建列表分區(qū)（LIST）

DROP TABLE orders_3;

CREATE TABLE orders_3 (

id INT PRIMARY KEY,

customer_id INT,

order_date DATE,

product_id INT,

quantity INT

) PARTITION BY LIST (customer_id) (

PARTITION p1 VALUES (100),

PARTITION p2 VALUES (200),

PARTITION p3 VALUES (300),

PARTITION p4 VALUES (400),

PARTITION p5 VALUES (500)

);

--查看創(chuàng)建的分區(qū)表信息

select relname,parttype,parentid,boundaries from pg_partition where parentid in (SELECT parentid FROM pg_partition where relname='orders_3');

--插入測(cè)試數(shù)據(jù)

INSERT INTO orders_3(id,customer_id,order_date,product_id,quantity)VALUES(1001,100,date '20230613',1,10);

INSERT INTO orders_3(id,customer_id,order_date,product_id,quantity)VALUES(1002,100,date '20230613',2,20);

INSERT INTO orders_3(id,customer_id,order_date,product_id,quantity)VALUES(1003,100,date '20230613',3,30);

INSERT INTO orders_3(id,customer_id,order_date,product_id,quantity)VALUES(1004,100,date '20230613',4,40);

--訪問分區(qū)表

--根據(jù)分區(qū)字段查詢

select * from orders_3 where customer_id = 100

--根據(jù)分區(qū)P1查詢

select * from orders_3 partition (p1);

--ADD，增加分區(qū)

ALTER TABLE orders_3 add partition p6 values (600);

--TRUNCATE,刪除分區(qū)數(shù)據(jù)

ALTER TABLE orders_3 truncate partition p6;

--DROP,刪除分區(qū)表

ALTER TABLE orders_3 drop partition p6;