什么是高级？这就叫高级—openGauss（63）

openGauss #入门 #安装 #数据库 #开源

知识来源：docs-opengauss.osinfra.cn/zh/
修改分区表示例
示例11：
sql 代码解读复制代码--创建分区表employees_table。
openGauss=# CREATE TABLE employees_table
(
employee_id INTEGER NOT NULL,
employee_name CHAR(20) NOT NULL,
onboarding_date DATE NOT NULL,
position CHAR(20)
)
PARTITION BY RANGE(onboarding_date)
(
PARTITION founders VALUES LESS THAN('2000-01-01 00:00:00'),
PARTITION senate VALUES LESS THAN('2010-01-01 00:00:00'),
PARTITION seniors VALUES LESS THAN('2020-01-01 00:00:00'),
PARTITION newcomer VALUES LESS THAN(MAXVALUE)
);

-- 插入数据
openGauss=# INSERT INTO employees_table VALUES
(1, 'SMITH', '1997-01-10 00:00:00','Manager'),
(2, 'JONES', '2001-05-06 00:00:00', 'Supervisor'),
(3, 'WILLIAMS', '2011-09-17 00:00:00', 'Engineer'),
(4, 'TAYLOR', '2021-10-21 00:00:00', 'Clerk');

查看newcomer分区
openGauss=# SELECT * FROM employees_table PARTITION (newcomer);
employee_id | employee_name | onboarding_date | position
-------------+----------------------+---------------------+----------------------
4 | TAYLOR | 2021-10-21 00:00:00 | Clerk
(1 row)

--删除newcomer分区。
openGauss=# ALTER TABLE employees_table DROP PARTITION newcomer;
ALTER TABLE

-- 查看newcomer分区数据
openGauss=# select * from employees_table partition (newcomer);
ERROR: partition "newcomer" of relation "employees_table" does not exist

--增加fresh分区。
openGauss=# ALTER TABLE employees_table ADD PARTITION fresh VALUES LESS THAN ('2040-01-01 00:00:00');
ALTER TABLE

--以2030-01-01 00:00:00为分割点，分裂fresh分区为current、future两个分区
openGauss=# ALTER TABLE employees_table SPLIT PARTITION fresh AT ('2030-01-01 00:00:00') INTO (PARTITION current, PARTITION future);
ALTER TABLE

--将分区current改名为now
openGauss=# ALTER TABLE employees_table RENAME PARTITION current TO now;
ALTER TABLE

--将founders，senate合并为一个分区original。
openGauss=# ALTER TABLE employees_table MERGE PARTITIONS founders, senate INTO PARTITION original;

openGauss #入门 #安装 #数据库 #开源

知识来源：docs-opengauss.osinfra.cn/zh/