03.CreateTable

## ---- Partition 확인.
\d+ sales

SELECT partitiontablename, partitionrangestart, partitionrangeend
FROM pg_partitions
WHERE tablename = 'sales' AND schemaname = 'public';

## ---- 함수 생성
CREATE OR REPLACE FUNCTION generate_random_string_md5(length INT)
RETURNS TEXT AS $$
BEGIN
    RETURN SUBSTRING(MD5(RANDOM()::text || CLOCK_TIMESTAMP()::text), 1, length);
END;
$$ LANGUAGE plpgsql VOLATILE; 

SELECT generate_random_string_md5(10);

## 테이블 생성 예시1
CREATE TABLE test_table (
    col1 character varying(82),
    col2 character varying(10),
    col3 character varying(82),
    col4 character varying(50),
    start_datetime integer,
    gploaded_time timestamp without time zone
)
WITH (appendonly=true) DISTRIBUTED BY (col1);

INSERT INTO test_table values('00001','col2col2','col3col3', 'col4col4col4col4',2019384,now());
INSERT INTO test_table values('00002','col2col2','col3col3', 'col4col4col4col4',2019384,now());
INSERT INTO test_table values('00003','col2col2','col3col3', 'col4col4col4col4',2019384,now());
INSERT INTO test_table values('00004','col2col2','col3col3', 'col4col4col4col4',2019384,now());
INSERT INTO test_table values(generate_random_string_md5(10),'col2col2','col3col3', 'col4col4col4col4',2019384,now());

## 테이블 생성 예시2
CREATE TABLE rtitrans (
    imsi character varying(82),
    subscriber_mccmnc character varying(10),
    msisdn character varying(82),
    imei character varying(50),
    called_digits character varying(50),
    start_datetime integer,
    end_datetime integer,
    first_cell_lac integer,
    first_cell_idsac integer,
    current_cell_lac integer,
    current_cell_idsac integer,
    dr_type integer,
    status character varying(50),
    ingest_time bigint,
    processed_time bigint,
    export_time bigint,
    extra_col text,
    gploaded_time timestamp without time zone
)
WITH (appendonly=true) DISTRIBUTED BY (imsi);

CREATE TABLE sales (
    sale_id     bigint,
    sale_date   date,
    region      text,
    amount      numeric
)
DISTRIBUTED BY (sale_id)  -- 데이터 분산은 분산키로 처리
PARTITION BY RANGE (sale_date) -- 파티셔닝은 파티션키로 처리
(
    PARTITION sales_2025_q1 START ('2025-01-01'::date) INCLUSIVE
                          END ('2025-04-01'::date) EXCLUSIVE,
    PARTITION sales_2025_q2 START ('2025-04-01'::date) INCLUSIVE
                          END ('2025-07-01'::date) EXCLUSIVE,
    PARTITION sales_2025_q3 START ('2025-07-01'::date) INCLUSIVE
                          END ('2025-10-01'::date) EXCLUSIVE,
    PARTITION sales_2025_q4 START ('2025-10-01'::date) INCLUSIVE
                          END ('2026-01-01'::date) EXCLUSIVE,
    -- 정의된 범위 밖의 데이터가 들어갈 기본 파티션 (필수 권장)
    DEFAULT PARTITION others
);

## 결합 파티션 테이블 생성 (Range – List 이중 파티션)
CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text) 
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), 
  SUBPARTITION asia VALUES ('asia'), 
  SUBPARTITION europe VALUES ('europe'), 
  DEFAULT SUBPARTITION other_regions)
  (START (date '2011-01-01') INCLUSIVE
   END (date '2012-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month'), 
   DEFAULT PARTITION outlying_dates );

## 결합 파티션 테이블 확인
psql
\dt *.sales*

## Range Partition 생성
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int )
DISTRIBUTED BY (city_id)
PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2021m01 PARTITION OF measurement
  FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');

CREATE TABLE measurement_y2021m02 PARTITION OF measurement
  FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');

CREATE TABLE measurement_y2021m03 PARTITION OF measurement
  FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
...
CREATE TABLE measurement_y2021m11 PARTITION OF measurement
  FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');

## List Partition 생성
CREATE TABLE listpart (id int, rank int, year int, color char(1), count int) 
  DISTRIBUTED BY (id)
  PARTITION BY LIST (color);

CREATE TABLE listpart_red PARTITION OF listpart FOR VALUES IN ('r');
CREATE TABLE listpart_green PARTITION OF listpart FOR VALUES IN ('g');
CREATE TABLE listpart_blue PARTITION OF listpart FOR VALUES IN ('b');
CREATE TABLE listpart_other PARTITION OF listpart DEFAULT;

## Hash Partition 생성
CREATE TABLE hpt (a int, b int, c text) PARTITION BY HASH(c);

CREATE TABLE hpt_p1 PARTITION OF hpt FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE hpt_p2 PARTITION OF hpt FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE hpt_p3 PARTITION OF hpt FOR VALUES WITH (MODULUS 3, REMAINDER 2);

## Partition 확인
\dt+ hpt*

## 테이블 파티션 추가 
ALTER TABLE sales ADD PARTITION sales_2026_q1
    START ('2026-01-01'::date) INCLUSIVE
    END ('2026-02-01'::date) EXCLUSIVE;

## 테이블 파티션 삭제
ALTER TABLE sales DROP PARTITION sales_2025_q1;

## DEFAULT 파티션 관리
ALTER TABLE sales SPLIT DEFAULT PARTITION
    START ('2026-01-01'::date) INCLUSIVE
    END ('2026-02-01'::date) EXCLUSIVE
    INTO (PARTITION sales_2026_q1, DEFAULT PARTITION);

## 예시: DEFAULT 파티션에서 2024년 1월 분량을 쪼개서 새로운 파티션으로 생성
ALTER TABLE sales 
SPLIT DEFAULT PARTITION 
    START (date '2024-01-01') INCLUSIVE 
    END (date '2024-02-01') EXCLUSIVE
    INTO (PARTITION p202401, DEFAULT PARTITION);

## procudure/function으로 신규 파티션 추가 가능
## (개념적인 예시) 다음 달 파티션 추가 함수
CREATE OR REPLACE FUNCTION add_next_month_partition() RETURNS void AS $$
DECLARE
    next_month_start date;
    next_month_end date;
    partition_name text;
BEGIN
    -- 다음 달 1일 계산
    next_month_start := date_trunc('month', current_date + interval '1 month');
    -- 다다음 달 1일 계산
    next_month_end := date_trunc('month', current_date + interval '2 months');
    
    -- 동적 SQL 실행 (DEFAULT 파티션 없다는 가정)
    EXECUTE format('ALTER TABLE sales ADD PARTITION START (%L) INCLUSIVE END (%L) EXCLUSIVE', 
                   next_month_start, next_month_end);
END;
$$ LANGUAGE plpgsql;