set time zone 'PRC';

--
--Add mutliple column 
CREATE TABLE cstore8_tmp (initial int4) with(orientation = column);
insert into cstore8_tmp values(1);
ALTER TABLE cstore8_tmp ADD(mychar char default 'a', mychar1 varchar(10) NOT NULL default 'ab', id integer NULL);
select * from cstore8_tmp;
insert into cstore8_tmp values(2,'b','abc',2);
select * from cstore8_tmp order by initial;
drop table cstore8_tmp;

-- Add column for patitioned table
CREATE TABLE cstore8_tmp(a int, b int) with ( orientation = column )  partition by range(b)
(
partition p1 values less than (3),
partition p2 values less than (6),
partition p3 values less than (10)
);
insert into cstore8_tmp values(1,1);
insert into cstore8_tmp values(2,4);
insert into cstore8_tmp values(3,9);
Alter table cstore8_tmp add column c int default 2;
select * from cstore8_tmp order by a;
insert into cstore8_tmp values(2,5,4);
select * from cstore8_tmp order by a, b;

drop table cstore8_tmp;
--
--Alter type
--

--char,char(n),varchar(n),integer
create table t1(id1 integer, mychar char, name char(5), name2 varchar(5)) with(orientation = column);
insert into t1 values(1,'1','12','1234');
insert into t1 values(2,'a','ab','abcd');
select * from t1 order by id1;
\d+ t1;

--can't alter distributed key
alter table t1 alter id1 type char;

-- char->int, failed
alter table t1 alter mychar type int;

-- char(n) extended
alter table t1 alter name type char(10);
\d+ t1;
 
--test altered column char extended
update t1 set name = 'abcdefg' where id1=2;
select * from t1 order by id1;

delete t1 where id1=2;

-- char->int, succeed
alter table t1 alter mychar type int;
\d+ t1;

--test altered column char->int, can't insert succeed
insert into t1 values(1,'b','abcdef','1234');

-- varchar(n) extended
alter table t1 alter name2 type varchar(20);
\d+ t1;

--test altered column varchar(n)
insert into t1 values(2,3,'abcdef','1234567');
select * from t1 order by id1;

-- varchar(n)->int
alter table t1 alter name2 type int;
\d+ t1;

-- int->char(n)
alter table t1 alter mychar type char(4);
\d+ t1;
insert into t1 values(3,'abc','abcde',12);
select * from t1 order by id1;

-- char(n)->char(m) cut shorter
alter table t1 alter name type char(5);
alter table t1 alter name type char(7);

-- char(n)->varchar(m)
alter table t1 alter name type varchar(4);
alter table t1 alter name type varchar(12);
\d+ t1;


-- int->smallint
alter table t1 alter name2 type smallint;
delete from t1 where id1=2;
alter table t1 alter name2 type smallint;
\d+ t1;

-- varchar(n)->text
alter table t1 alter name type text;
\d+ t1;

-- int->numeric
alter table t1 alter name2 type numeric(10,2);
\d+ t1;
select * from t1 order by id1;
insert into t1 values(4,'bc','ab',3.1415);
select * from t1 order by id1;

-- numeric->numeric
alter table t1 alter name2 type numeric(5,2);  --failed
alter table t1 alter name2 type numeric(7,3);  --succeed
select * from t1 order by id1;

-- numeric->char(n)
alter table t1 alter name2 type char(6);  --failed
alter table t1 alter name2 type char(10);  --succeed
\d+ t1;
insert into t1 values(5,'ab','123','12.34567890123');  --failed
insert into t1 values(5,'ab','123','12.3456789');  --succeed

-- char(n)->decimal
alter table t1 alter name2 type decimal;
select * from t1 order by id1;

-- decimal->float8
alter table t1 alter name2 type float;
select * from t1 order by id1;

-- float8->real(float4)
alter table t1 alter name2 type real;
select * from t1 order by id1;

-- real->numeric()
alter table t1 alter name2 type numeric(5,2);  --failed
alter table t1 alter name2 type numeric(5,1);  --succeed
select * from t1 order by id1;

-- numeric->int
alter table t1 alter name2 type int;
select * from t1 order by id1;

drop table t1;

--time type 
create table timetest(c1 timestamp, c2 timestamptz, c3 date) with(orientation = column);
insert into timetest values('2015-04-13 16:50:12', '2015-04-13 16:51:13', '2015-01-13');
insert into timetest values('2015/04/13 04:11:12 pm','04/13/2015 15:30:21', '02/04/2015');
select * from timetest order by c1;

-- timestamptz->time
alter table timetest alter c2 type time;
select * from timetest order by c1;

-- time->timetz
alter table timetest alter c2 type timetz;
select * from timetest order by c1;

-- timetz->timestamptz
alter table timetest alter c2 type timestamptz;

-- date->timestamptz
alter table timetest alter c3 type timestamptz;
select * from timetest order by c1;

-- timestamptz->date
alter table timetest alter c3 type date;
select * from timetest order by c1;

-- timetz->char(n)
alter table timetest alter c2 type char(15);
select * from timetest order by c1;

-- char(n)->timetz
alter table timetest alter c2 type timetz;  --failed
alter table timetest alter c2 type char(12); 
alter table timetest alter c2 type timetz;  --failed

--using
alter table timetest alter c2 type timetz using timetz(c2);  --succeed
select * from timetest order by c1;

alter table timetest alter c2 type date using c3;  --refer other column,failed

drop table timetest;

--
-- ALTER_TABLE MERGE PARTITIONS
-- Merge existing partitions for a partitioned table
--

-- Create table for testing

drop table if exists cstoremp_t;
create table cstoremp_t (a int, b text)
with (orientation = column)
partition by range (a)
(
partition cstoremp_t_p1 values less than (10),
partition cstoremp_t_p2 values less than (20),
partition cstoremp_t_p3 values less than (30),
partition cstoremp_t_p4 values less than (40)
)
;
insert into cstoremp_t values (1, 'A'), (11 ,'B'), (21 ,'C'), (31 ,'D');
select relname, boundaries from pg_partition where parentid in (select oid from pg_class where relname = 'cstoremp_t') order by 2;
create index cstoremp_t_idx on cstoremp_t(a) local;

-- Check partition indexing correctness
select relname from pg_partition where parentid = (select oid from pg_class where relname = 'cstoremp_t_idx') order by 1;
explain select * from cstoremp_t where a=1;

-- Merge two paritions, check name, range, indice of merged partition

alter table cstoremp_t merge partitions cstoremp_t_p1, cstoremp_t_p2 into partition cstoremp_t_px;
select relname, boundaries from pg_partition where parentid in (select oid from pg_class where relname = 'cstoremp_t') order by 2;

-- Check partition indexing correctness after merging
select relname from pg_partition where parentid = (select oid from pg_class where relname = 'cstoremp_t_idx') order by 1;
explain select * from cstoremp_t where a=1;

-- Merge paritions that in the wrong order / does not exist

alter table cstoremp_t merge partitions cstoremp_t_p3, cstoremp_t_px into partition cstoremp_t_px;
alter table cstoremp_t merge partitions cstoremp_t_p5, cstoremp_t_p6 into partition cstoremp_t_px;

-- End. Clean up