How to add partition to a pre created table?

Aster
Enthusiast

How to add partition to a pre created table?

I know in Aster, we can add partitions to logically partitioned table. We can also remove partitions from a logically partition table.

My question is if you create a table and forget to partition it. How will you add partitioning?

Example:

Case (i) Adding Partitions 

--1. original table DDL

create table public.partition_test_1 

( i int, j char , d date ) 

distribute by hash  ( i  ) 

partition by range (d ) 

(

partition jan2014_jun2014 ( start '2014-01-01' :: date end '2014-06-30':: date ) ,

partition jul2014_dec2014 ( start '2014-07-01' :: date end '2014-12-31':: date ) 

) ;

--2. Create a temp tale with same DDl as source table (without partition DDL). The temp table is deleted once the partition is altered.

create table public.temp001 

( i int, j char , d date ) 

distribute by hash  ( i  ) 

--3. Adding a new partition and inserting values here 

ALTER TABLE public.partition_test_1  ATTACH PARTITION jan2015_jun2015 ( start '2015-01-01' :: date end '2015-06-30':: date ) 

from public.temp001;

insert into   public.partition_test_1 values ( 1, 'c','2015-06-29' );

create table public.temp001 

( i int, j char , d date ) 

distribute by hash  ( i  )

ALTER TABLE public.partition_test_1  ATTACH PARTITION jul2015_dec2015 ( start '2015-07-01' :: date end '2015-12-31':: date ) 

from public.temp001;

insert into   public.partition_test_1 values ( 1, 'c','2015-07-29' );

create table public.temp001 

( i int, j char , d date ) 

distribute by hash  ( i  )

ALTER TABLE public.partition_test_1  ATTACH PARTITION jan2016_jun2016 ( start '2016-01-01' :: date end '2016-06-30':: date ) 

from public.temp001;

 Case (ii) dropping off the oldest partition 

 create table public.drop_old_partition_test 

 ( order_id int, 

 order_desc text, 

 order_date date 

 )

 distribute by hash( order_id)

 partition by range (order_date)

 (

 partition pre_2014 ( start minvalue end '2014-01-01' ) 

, partition jan2014_mar2014 ( start '2014-01-01' :: date end '2014-03-31' :: date inclusive ) 

);

insert into public.drop_old_partition_test values ( 123, 'papaya', '2013-12-12');

insert into public.drop_old_partition_test values ( 234, 'staplers', '2013-03-31');

select * from public.drop_old_partition_test ;

 alter table public.drop_old_partition_test detach partition ( pre_2014 )  into public.temp_table_drop; /* A TEMP TABLE THAT IS CREATED ON THE FLY */

 The Temp table has data from the aprtitions that are dropped.

 The table now does not have date for the partition.

 case (iii) Add partiton to a table 

  create table public.add_a_new_partition 

 ( order_id int, 

 order_desc text, 

 order_date date 

 )

 distribute by hash(order_id);

 create table public.temp_partition 

 (

  order_id int, 

 order_desc text, 

 order_date date 

 )

 distribute by hash(order_id)

 partition by range (order_date)

 (

 partition pre_2014 ( start minvalue end '2014-01-01' ) 

, partition jan2014_mar2014 ( start '2014-01-01' :: date end '2014-03-31' :: date inclusive ) 

);

 ALTER TABLE public.add_a_new_partition  ATTACH PARTITION pre_2014 ( start minvalue end '2014-01-01' ) 

from public.temp_partition; <-- EEK error here

1 REPLY
Enthusiast

Re: How to add partition to a pre created table?

Figured it out.

YOU CANNOT ALTER A TABLE AND ADD PARTITION

In Aster, you can only add partitions or remove partitions to a logically partitioned table.

( unlike in teradata where you can alter table primary index and add partition. Here you cannot)