create schema partition_test2;
set current_schema to 'partition_test2';
set dolphin.b_compatibility_mode to off;
CREATE TABLE IF NOT EXISTS test_part1
(
a int,
b int
) 
PARTITION BY RANGE(a)
(
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p2 VALUES LESS THAN (300)
);

CREATE TABLE IF NOT EXISTS test_subpart
(
a int,
b int
) 
PARTITION BY RANGE(a) SUBPARTITION BY RANGE(b)
(
		PARTITION p0 VALUES LESS THAN (100)
		(
			SUBPARTITION p0_0 VALUES LESS THAN (1000),
			SUBPARTITION p0_1 VALUES LESS THAN (2000),
			SUBPARTITION p0_2 VALUES LESS THAN (10000)
		),
		PARTITION p1 VALUES LESS THAN (200)
		(
			SUBPARTITION p1_0 VALUES LESS THAN (1000),
			SUBPARTITION p1_1 VALUES LESS THAN (2000),
			SUBPARTITION p1_2 VALUES LESS THAN (10000)
		),
		PARTITION p2 VALUES LESS THAN (300)
		(
			SUBPARTITION p2_0 VALUES LESS THAN (1000),
			SUBPARTITION p2_1 VALUES LESS THAN (2000),
			SUBPARTITION p2_2 VALUES LESS THAN (10000)
		)
);

--alter partition table
drop table if exists tab1;
create table tab1 (a datetime ,b varchar(100))
partition by range (to_days(a))
(partition p1 values less than (to_days('20240330')),
partition p2 values less than (to_days('20240930'))
);

--插入数据
insert into tab1 values('20240130','one');
insert into tab1 values('20240330','one');
insert into tab1 values('20240412','two');
insert into tab1 values('20240430','two');--这个值，在分裂分区的时候作为了键值
insert into tab1 values('20240512','two');
insert into tab1 values('20240630','three');

--切割分区，指定切割点
alter table tab1 split partition p2 at (to_days('20240430')) into (partition p3 ,partition pdefault);

select * from tab1 partition (p1);
select * from tab1 partition (p3);
select * from tab1 partition (pdefault);
drop table if exists tab1;
create table tab1 (a datetime ,b varchar(100))
partition by range (to_days(a))
(partition p1 values less than (to_days('20240330')),
partition p2 values less than (to_days('20240930'))
);

--插入数据
insert into tab1 values('20240130','one');
insert into tab1 values('20240330','one');
insert into tab1 values('20240412','two');
insert into tab1 values('20240430','two');--这个值，在分裂分区的时候作为了键值
insert into tab1 values('20240512','two');
insert into tab1 values('20240630','three');

--切割分区，不指定切割点
alter table tab1 split partition p2 into (
    partition p3 values less than (to_days('20240430')),
     partition pdefault values less than (to_days('20240930'))
);

select * from tab1 partition (p1);
select * from tab1 partition (p3);
select * from tab1 partition (pdefault);
drop table if exists tab1;
drop table if exists abs_tab;
CREATE TABLE abs_tab
( prod_id NUMBER(6)
    , cust_id NUMBER
    , time_id double precision
    , channel_id CHAR(1)
    , promo_id NUMBER(6)
    , quantity_sold NUMBER(3)
    , amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (abs(time_id))
( PARTITION p0 VALUES LESS THAN (abs('-5')),
  PARTITION p1 VALUES LESS THAN (abs('-20'))
);

insert into abs_tab values(1, 1, '1', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '-2', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '-3', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '4', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '-9', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '11', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '-19', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '-18', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '-15', 'a', 1, 1, 1);
insert into abs_tab values(1, 1, '5', 'a', 1, 1, 1);

alter table abs_tab split partition p1 at (abs('-10')) into (partition p1_1, partition p1_2);
select * from abs_tab partition (p0);
select * from abs_tab partition (p1_1);
select * from abs_tab partition (p1_2);
drop table abs_tab;

create table test_no_part1(a int, b int);
insert into test_part1 values(99,1),(199,1),(299,1);
select * from test_part1;
insert into test_subpart values(99,199),(199,1999),(299,2999);
select * from test_subpart;
--test b database truncate partition syntax
ALTER TABLE test_part1 truncate PARTITION p0, p1;
select * from test_part1;
insert into test_part1 (with RECURSIVE t_r(i,j) as(values(0,1) union all select i+1,j+2 from t_r where i < 20) select * from t_r);
select * from test_part1;
ALTER TABLE test_part1 truncate PARTITION all;
select * from test_part1;
ALTER TABLE test_subpart truncate PARTITION p0, p1, p2_2, p2_1;
select * from test_subpart;
insert into test_subpart (with RECURSIVE t_r(i,j) as(values(0,1) union all select i+1,j+2 from t_r where i < 20) select * from t_r);
select * from test_subpart;
ALTER TABLE test_subpart truncate PARTITION all;
select * from test_subpart;
--test opengauss truncate partition syntax
insert into test_part1 values(99,1),(199,1);
select * from test_part1;
ALTER TABLE test_part1 truncate PARTITION p0, truncate PARTITION p1;
select * from test_part1;
insert into test_subpart values(99,1),(199,1);
select * from test_subpart;
ALTER TABLE test_subpart truncate PARTITION p0, truncate PARTITION p1;
select * from test_subpart;

--test b database exchange partition syntax
insert into test_part1 values(99,1),(199,1),(299,1);
alter table test_part1 exchange partition p2 with table test_no_part1 without validation;
select * from test_part1;
select * from test_no_part1;
alter table test_part1 exchange partition p2 with table test_no_part1 without validation;
select * from test_part1;
select * from test_no_part1;
alter table test_part1 exchange partition p2 with table test_no_part1 with validation;
select * from test_part1;
select * from test_no_part1;
alter table test_part1 exchange partition p2 with table test_no_part1 with validation;
select * from test_part1;
select * from test_no_part1;
--test opengauss exchange partition syntax
alter table test_part1 exchange partition (p2) with table test_no_part1 without validation;
select * from test_part1;
select * from test_no_part1;
alter table test_part1 exchange partition (p2) with table test_no_part1 without validation;
select * from test_part1;
select * from test_no_part1;
alter table test_part1 exchange partition (p2) with table test_no_part1 with validation;
select * from test_part1;
select * from test_no_part1;
alter table test_part1 exchange partition (p2) with table test_no_part1 with validation;
select * from test_part1;
select * from test_no_part1;
--test b database exchange partition syntax
insert into test_subpart values(99,199),(199,1999),(299,2999);
alter table test_subpart exchange partition p2 with table test_no_part1 without validation;
--test opengauss exchange partition syntax
alter table test_subpart exchange partition (p2) with table test_no_part1 without validation;


--test b database analyze partition syntax
alter table test_part1 analyze partition p0,p1;
alter table test_part1 analyze partition all;
alter table test_subpart analyze partition p0,p1;
alter table test_subpart analyze partition all;
alter table test_subpart analyze partition perror;
--test opengauss analyze partition syntax
analyze test_part1 partition (p1);
analyze test_subpart partition (p1);



-------test USTORE table
CREATE TABLE IF NOT EXISTS test_part_ustore
(
a int,
b int
) 
PARTITION BY RANGE(a)
(
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p2 VALUES LESS THAN (300)
) with(STORAGE_TYPE = USTORE);
create table test_no_part2(a int, b int) with(STORAGE_TYPE = USTORE);
insert into test_part_ustore (with RECURSIVE t_r(i,j) as(values(0,1) union all select i+1,j+2 from t_r where i < 20) select * from t_r);
select * from test_part_ustore;
ALTER TABLE test_part_ustore truncate PARTITION all;
select * from test_part_ustore;
insert into test_part_ustore values(99,1),(199,1),(299,1);
alter table test_part_ustore exchange partition p2 with table test_no_part2 without validation;
select * from test_part_ustore;
select * from test_no_part2;
alter table test_part_ustore analyze partition p0,p1;
alter table test_part_ustore analyze partition all;



-------test segment table
CREATE TABLE IF NOT EXISTS test_part_segment
(
a int,
b int
) 
PARTITION BY RANGE(a)
(
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p2 VALUES LESS THAN (300)
) with(segment = on);
insert into test_part_segment (with RECURSIVE t_r(i,j) as(values(0,1) union all select i+1,j+2 from t_r where i < 20) select * from t_r);
select * from test_part_segment;
ALTER TABLE test_part_segment truncate PARTITION all;
select * from test_part_segment;
insert into test_part_segment values(99,1),(199,1),(299,1);
alter table test_part_segment exchange partition p2 with table test_no_part1 without validation;
select * from test_part_segment;
select * from test_no_part1;
alter table test_part_segment analyze partition p0,p1;
alter table test_part_segment analyze partition all;


-------test list partition tables
CREATE TABLE test_part_list (a int, b int)
PARTITION BY list(a)
(
PARTITION p1 VALUES (2000,2001,2002,2003),
PARTITION p2 VALUES (3000,3001,3002,3003),
PARTITION p3 VALUES (4000,4001,4002,4003),
PARTITION p4 VALUES (5000,5001,5002,5003)
);
insert into test_part_list values(2000,1),(3000,2),(4000,3),(5000,4);
select * from test_part_list order by 1,2;
ALTER TABLE test_part_list truncate PARTITION all;
select * from test_part_list order by 1,2;
insert into test_part_list values(2000,1),(3000,2),(4000,3),(5000,4);
alter table test_part_list exchange partition p2 with table test_no_part1 without validation;
select * from test_part_list order by 1,2;
select * from test_no_part1;
alter table test_part_list analyze partition p1,p2;
alter table test_part_list analyze partition all;
-------test hash partition tables
CREATE TABLE IF NOT EXISTS test_part_hash
(
a int,
b int
) 
PARTITION BY HASH(a)
(
    PARTITION p0,
    PARTITION p1,
    PARTITION p2
);
insert into test_part_hash (with RECURSIVE t_r(i,j) as(values(0,1) union all select i+1,j+2 from t_r where i < 20) select * from t_r);
select * from test_part_hash order by 1,2;
ALTER TABLE test_part_hash truncate PARTITION all;
select * from test_part_hash order by 1,2;
insert into test_part_hash values(99,1),(199,1),(299,1);
alter table test_part_hash exchange partition p2 with table test_no_part1 without validation;
select * from test_part_hash order by 1,2;
select * from test_no_part1;
alter table test_part_hash analyze partition p0,p1;
alter table test_part_hash analyze partition all;

-- test partition key upper and lower case
DROP TABLE IF EXISTS sales_range;
CREATE TABLE sales_range (
    sale_date int,
    PRIMARY KEY (SALE_DATE)
)
PARTITION BY RANGE (SALE_DATE) (
    PARTITION p202401 VALUES LESS THAN (1000),
    PARTITION p202402 VALUES LESS THAN (2000),
    PARTITION p202403 VALUES LESS THAN (3000),
    PARTITION p_others VALUES LESS THAN (MAXVALUE)
);
\d sales_range

DROP TABLE IF EXISTS sales_range;
CREATE TABLE sales_range (
    sale_date DATE,
    PRIMARY KEY (sale_date)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION p202403 VALUES LESS THAN ('2024-04-01'),
    PARTITION p_others VALUES LESS THAN (MAXVALUE)
);
\d sales_range

DROP TABLE IF EXISTS sales_range;
drop schema partition_test2 cascade;
reset current_schema;