GaussDB(DWS)如何交换分区

基本语法：

ALTER TABLE Tab1 EXCHANGE PARTITION PartitionName WITH Tab2;

问题场景：

--创建分区表
drop table if exists tab_partition;
CREATE TABLE tab_partition (
	prod_key int,
	prod_code character varying(50)
)
WITH (orientation=column, compression=middle)
DISTRIBUTE BY HASH(prod_key)
PARTITION BY RANGE (prod_key)
( 
	 PARTITION p201901_r01 VALUES LESS THAN (100) TABLESPACE pg_default,
	 PARTITION p201901_r02 VALUES LESS THAN (200) TABLESPACE pg_default,
	 PARTITION p201901_r03 VALUES LESS THAN (300) TABLESPACE pg_default
);

--创建普通表
drop table if exists tab_tmp;
CREATE TABLE tab_tmp (
	prod_key int,
	prod_code character varying(50)
)
WITH (orientation=column, compression=middle)
DISTRIBUTE BY HASH(prod_key);

alter table tab_partition add column xxxxx character varying(200);
alter table tab_partition drop column xxxxx;
alter table tab_partition exchange partition (p201901_r02) with table tab_tmp; --报错
--ERROR:  tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns

问题根因：

交换分区基本原理是将两个表的物理文件直接进行交换，要求两个表的列能一一对应。

目标表的列经过增减后，两个表的表定义（由于删除列）无法一一对应，因此表的物理文件无法直接交换。

解决办法：

建普通表时，使用create table like语句，且带上dropcolums

drop table if exists tab_tmp2;
create table tab_tmp2(like tab_partition INCLUDING DROPCOLUMNS INCLUDING DISTRIBUTION INCLUDING STORAGE INCLUDING RELOPTIONS); --具体需要INCLUDING哪些参数需要结合业务实际需要
alter table tab_partition exchange partition (p201901_r02) with table tab_tmp2; --交换成功