代码拉取完成，页面将自动刷新
同步操作将从 openGauss/openGauss-server 强制同步，此操作会覆盖自 Fork 仓库以来所做的任何修改，且无法恢复！！！
确定后同步将在后台操作，完成时将刷新页面，请耐心等待。
--
-- Test exchange operator for interval partitioned table
--
--
---- create interval partitioned table
--
CREATE TABLE interval_normal_exchange (logdate date not null)
PARTITION BY RANGE (logdate)
INTERVAL ('1 month')
(
PARTITION interval_normal_exchange_p1 VALUES LESS THAN ('2020-03-01'),
PARTITION interval_normal_exchange_p2 VALUES LESS THAN ('2020-04-01'),
PARTITION interval_normal_exchange_p3 VALUES LESS THAN ('2020-05-01')
);
-- see about the info of the partitioned table in pg_partition
select relname, parttype, partstrategy, boundaries from pg_partition
where parentid = (select oid from pg_class where relname = 'interval_normal_exchange')
order by relname;
relname | parttype | partstrategy | boundaries
-----------------------------+----------+--------------+--------------
interval_normal_exchange | r | i |
interval_normal_exchange_p1 | p | r | {2020-03-01}
interval_normal_exchange_p2 | p | r | {2020-04-01}
interval_normal_exchange_p3 | p | r | {2020-05-01}
(4 rows)
-- insert the record that is smaller than the lower boundary
insert into interval_normal_exchange values ('2020-02-21');
insert into interval_normal_exchange values ('2020-02-22');
insert into interval_normal_exchange values ('2020-02-23');
insert into interval_normal_exchange values ('2020-5-01');
insert into interval_normal_exchange values ('2020-5-02');
insert into interval_normal_exchange values ('2020-5-03');
-- see about the info of the partitioned table in pg_partition
select relname, parttype, partstrategy, boundaries from pg_partition
where parentid = (select oid from pg_class where relname = 'interval_normal_exchange')
order by relname;
relname | parttype | partstrategy | boundaries
-----------------------------+----------+--------------+------------------------------
interval_normal_exchange | r | i |
interval_normal_exchange_p1 | p | r | {2020-03-01}
interval_normal_exchange_p2 | p | r | {2020-04-01}
interval_normal_exchange_p3 | p | r | {2020-05-01}
sys_p1 | p | i | {"Mon Jun 01 00:00:00 2020"}
(5 rows)
--
---- create to be exchanged table and test range partition exchange
--
CREATE TABLE interval_exchange_test (logdate date not null);
insert into interval_exchange_test values ('2020-02-24');
insert into interval_exchange_test values ('2020-02-25');
insert into interval_exchange_test values ('2020-02-26');
-- do exchange partition interval_normal_exchange_p1 and interval_exchange_test
-- The data they have belongs to the same range.
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test;
select * from interval_normal_exchange partition (interval_normal_exchange_p1)order by logdate;
logdate
--------------------------
Mon Feb 24 00:00:00 2020
Tue Feb 25 00:00:00 2020
Wed Feb 26 00:00:00 2020
(3 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Fri Feb 21 00:00:00 2020
Sat Feb 22 00:00:00 2020
Sun Feb 23 00:00:00 2020
(3 rows)
-- exchange back
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test;
select * from interval_normal_exchange partition (interval_normal_exchange_p1)order by logdate;
logdate
--------------------------
Fri Feb 21 00:00:00 2020
Sat Feb 22 00:00:00 2020
Sun Feb 23 00:00:00 2020
(3 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Mon Feb 24 00:00:00 2020
Tue Feb 25 00:00:00 2020
Wed Feb 26 00:00:00 2020
(3 rows)
-- Insert a new record not belongs to interval_normal_exchange_p1
insert into interval_exchange_test values ('2020-3-05');
-- defaut is WITH VALIDATION, and the exchange will be failed
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test;
ERROR: some rows in table do not qualify for specified partition
-- WITHOUT VALIDATION and the exchange will be success, but some date will in the wrong range;
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test WITHOUT VALIDATION;
select * from interval_normal_exchange partition (interval_normal_exchange_p1)order by logdate;
logdate
--------------------------
Mon Feb 24 00:00:00 2020
Tue Feb 25 00:00:00 2020
Wed Feb 26 00:00:00 2020
Thu Mar 05 00:00:00 2020
(4 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Fri Feb 21 00:00:00 2020
Sat Feb 22 00:00:00 2020
Sun Feb 23 00:00:00 2020
(3 rows)
-- not include '2020-3-05'
select * from interval_normal_exchange where logdate > '2020-03-01' order by logdate;
logdate
--------------------------
Fri May 01 00:00:00 2020
Sat May 02 00:00:00 2020
Sun May 03 00:00:00 2020
(3 rows)
--
---- clean the data and test interval partition exchange
--
truncate table interval_exchange_test;
insert into interval_exchange_test values ('2020-5-04');
insert into interval_exchange_test values ('2020-5-05');
insert into interval_exchange_test values ('2020-5-06');
-- exchange table
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (sys_p1)
WITH TABLE interval_exchange_test;
select * from interval_normal_exchange partition (sys_p1)order by logdate;
logdate
--------------------------
Mon May 04 00:00:00 2020
Tue May 05 00:00:00 2020
Wed May 06 00:00:00 2020
(3 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Fri May 01 00:00:00 2020
Sat May 02 00:00:00 2020
Sun May 03 00:00:00 2020
(3 rows)
-- exchange back
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (sys_p1)
WITH TABLE interval_exchange_test;
select * from interval_normal_exchange partition (sys_p1)order by logdate;
logdate
--------------------------
Fri May 01 00:00:00 2020
Sat May 02 00:00:00 2020
Sun May 03 00:00:00 2020
(3 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Mon May 04 00:00:00 2020
Tue May 05 00:00:00 2020
Wed May 06 00:00:00 2020
(3 rows)
insert into interval_exchange_test values ('2020-6-05');
-- defaut is WITH VALIDATION, and the exchange will be failed
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test;
ERROR: some rows in table do not qualify for specified partition
-- WITHOUT VALIDATION and the exchange will be success, but some date will in the wrong range;
ALTER TABLE interval_normal_exchange EXCHANGE PARTITION (interval_normal_exchange_p1)
WITH TABLE interval_exchange_test WITHOUT VALIDATION;
select * from interval_normal_exchange partition (interval_normal_exchange_p1)order by logdate;
logdate
--------------------------
Mon May 04 00:00:00 2020
Tue May 05 00:00:00 2020
Wed May 06 00:00:00 2020
Fri Jun 05 00:00:00 2020
(4 rows)
select * from interval_exchange_test order by logdate;
logdate
--------------------------
Mon Feb 24 00:00:00 2020
Tue Feb 25 00:00:00 2020
Wed Feb 26 00:00:00 2020
Thu Mar 05 00:00:00 2020
(4 rows)
-- not include '2020-6-05'
select * from interval_normal_exchange order by logdate;
logdate
--------------------------
Fri May 01 00:00:00 2020
Sat May 02 00:00:00 2020
Sun May 03 00:00:00 2020
Mon May 04 00:00:00 2020
Tue May 05 00:00:00 2020
Wed May 06 00:00:00 2020
Fri Jun 05 00:00:00 2020
(7 rows)
select * from interval_normal_exchange where logdate > '2020-06-01' order by logdate;
logdate
---------
(0 rows)
drop table interval_normal_exchange;
drop table table_001;
ERROR: table "table_001" does not exist
create table table_001(
COL_1 smallint,
COL_2 char(5),
COL_3 int,
COL_4 date,
COL_5 boolean,
COL_6 nchar(5),
COL_7 float
);
drop table partition_table_001;
ERROR: table "partition_table_001" does not exist
create table partition_table_001(
COL_1 smallint,
COL_2 char(5),
COL_3 int,
COL_4 date,
COL_5 boolean,
COL_6 nchar(5),
COL_7 float
)
PARTITION BY RANGE (COL_4)
INTERVAL ('1 month')
(
PARTITION partition_table_001_p1 VALUES LESS THAN ('2020-03-01'),
PARTITION partition_table_001_p2 VALUES LESS THAN ('2020-04-01'),
PARTITION partition_table_001_p3 VALUES LESS THAN ('2020-05-01')
);
insert into partition_table_001 values (1,'aaa',1,'2020-02-23',true,'aaa',1.1);
insert into partition_table_001 values (2,'bbb',2,'2020-03-23',false,'bbb',2.2);
insert into partition_table_001 values (3,'ccc',3,'2020-04-23',true,'ccc',3.3);
insert into partition_table_001 values (4,'ddd',4,'2020-05-23',false,'ddd',4.4);
insert into partition_table_001 values (5,'eee',5,'2020-06-23',true,'eee',5.5);
insert into partition_table_001 values (6,'fff',6,'2020-07-23',false,'fff',6.6);
ALTER TABLE partition_table_001 EXCHANGE PARTITION (sys_p1) WITH TABLE table_001;
select * from table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
4 | ddd | 4 | Sat May 23 00:00:00 2020 | f | ddd | 4.4
(1 row)
select * from partition_table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
1 | aaa | 1 | Sun Feb 23 00:00:00 2020 | t | aaa | 1.1
2 | bbb | 2 | Mon Mar 23 00:00:00 2020 | f | bbb | 2.2
3 | ccc | 3 | Thu Apr 23 00:00:00 2020 | t | ccc | 3.3
5 | eee | 5 | Tue Jun 23 00:00:00 2020 | t | eee | 5.5
6 | fff | 6 | Thu Jul 23 00:00:00 2020 | f | fff | 6.6
(5 rows)
select relname, parttype, partstrategy, boundaries from pg_partition
where parentid = (select oid from pg_class where relname = 'partition_table_001')
order by relname;
relname | parttype | partstrategy | boundaries
------------------------+----------+--------------+------------------------------
partition_table_001 | r | i |
partition_table_001_p1 | p | r | {2020-03-01}
partition_table_001_p2 | p | r | {2020-04-01}
partition_table_001_p3 | p | r | {2020-05-01}
sys_p1 | p | i | {"Mon Jun 01 00:00:00 2020"}
sys_p2 | p | i | {"Wed Jul 01 00:00:00 2020"}
sys_p3 | p | i | {"Sat Aug 01 00:00:00 2020"}
(7 rows)
ALTER TABLE partition_table_001 EXCHANGE PARTITION (sys_p1) WITH TABLE table_001;
select * from table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+-------+-------+-------+-------
(0 rows)
select * from partition_table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
1 | aaa | 1 | Sun Feb 23 00:00:00 2020 | t | aaa | 1.1
2 | bbb | 2 | Mon Mar 23 00:00:00 2020 | f | bbb | 2.2
3 | ccc | 3 | Thu Apr 23 00:00:00 2020 | t | ccc | 3.3
4 | ddd | 4 | Sat May 23 00:00:00 2020 | f | ddd | 4.4
5 | eee | 5 | Tue Jun 23 00:00:00 2020 | t | eee | 5.5
6 | fff | 6 | Thu Jul 23 00:00:00 2020 | f | fff | 6.6
(6 rows)
insert into table_001 values (7,'eee',7,'2020-08-23',true,'eee',7.7);
ALTER TABLE partition_table_001 EXCHANGE PARTITION (sys_p1) WITH TABLE table_001 with validation verbose;
select * from table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
4 | ddd | 4 | Sat May 23 00:00:00 2020 | f | ddd | 4.4
(1 row)
select * from partition_table_001 order by 1;
col_1 | col_2 | col_3 | col_4 | col_5 | col_6 | col_7
-------+-------+-------+--------------------------+-------+-------+-------
1 | aaa | 1 | Sun Feb 23 00:00:00 2020 | t | aaa | 1.1
2 | bbb | 2 | Mon Mar 23 00:00:00 2020 | f | bbb | 2.2
3 | ccc | 3 | Thu Apr 23 00:00:00 2020 | t | ccc | 3.3
5 | eee | 5 | Tue Jun 23 00:00:00 2020 | t | eee | 5.5
6 | fff | 6 | Thu Jul 23 00:00:00 2020 | f | fff | 6.6
7 | eee | 7 | Sun Aug 23 00:00:00 2020 | t | eee | 7.7
(6 rows)
drop table table_001;
drop table partition_table_001;
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手