Partition

DROP TABLE IF EXISTS sales CASCADE;

-- Create parent table with partitioning
CREATE TABLE sales (
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    customer TEXT NOT NULL,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

-- create some indexes

-- Create default partition
Create table sales_default partition of sales default;

-- January 2024 partition
create table sales_2024_01 partition of sales for values from ('2024-01-01') to ('2024-02-01');
create table sales_2024_02 partition of sales for values from ('2024-02-01') to ('2024-03-01');
create table sales_2024_03 partition of sales for values from ('2024-03-01') to ('2024-04-01');
create table sales_2024_04 partition of sales for values from ('2024-04-01') to ('2024-05-01');
create table sales_2024_05 partition of sales for values from ('2024-05-01') to ('2024-06-01');
create table sales_2024_06 partition of sales for values from ('2024-06-01') to ('2024-07-01');
create table sales_2024_07 partition of sales for values from ('2024-07-01') to ('2024-08-01');
create table sales_2024_08 partition of sales for values from ('2024-08-01') to ('2024-09-01');
create table sales_2024_09 partition of sales for values from ('2024-09-01') to ('2024-10-01');
create table sales_2024_10 partition of sales for values from ('2024-10-01') to ('2024-11-01');
create table sales_2024_11 partition of sales for values from ('2024-11-01') to ('2024-12-01');
create table sales_2024_12 partition of sales for values from ('2024-12-01') to ('2025-01-01');

-- January 2023 partition
create table sales_2023_01 partition of sales for values from ('2023-01-01') to ('2023-02-01');
create table sales_2023_02 partition of sales for values from ('2023-02-01') to ('2023-03-01');
create table sales_2023_03 partition of sales for values from ('2023-03-01') to ('2023-04-01');
create table sales_2023_04 partition of sales for values from ('2023-04-01') to ('2023-05-01');
create table sales_2023_05 partition of sales for values from ('2023-05-01') to ('2023-06-01');
create table sales_2023_06 partition of sales for values from ('2023-06-01') to ('2023-07-01');
create table sales_2023_07 partition of sales for values from ('2023-07-01') to ('2023-08-01');
create table sales_2023_08 partition of sales for values from ('2023-08-01') to ('2023-09-01');
create table sales_2023_09 partition of sales for values from ('2023-09-01') to ('2023-10-01');
create table sales_2023_10 partition of sales for values from ('2023-10-01') to ('2023-11-01');
create table sales_2023_11 partition of sales for values from ('2023-11-01') to ('2023-12-01');
create table sales_2023_12 partition of sales for values from ('2023-12-01') to ('2024-01-01');

-- January 2025 partition
create table sales_2025_01 partition of sales for values from ('2025-01-01') to ('2025-02-01');
create table sales_2025_02 partition of sales for values from ('2025-02-01') to ('2025-03-01');
create table sales_2025_03 partition of sales for values from ('2025-03-01') to ('2025-04-01');
create table sales_2025_04 partition of sales for values from ('2025-04-01') to ('2025-05-01');
create table sales_2025_05 partition of sales for values from ('2025-05-01') to ('2025-06-01');
create table sales_2025_06 partition of sales for values from ('2025-06-01') to ('2025-07-01');
create table sales_2025_07 partition of sales for values from ('2025-07-01') to ('2025-08-01');
create table sales_2025_08 partition of sales for values from ('2025-08-01') to ('2025-09-01');
create table sales_2025_09 partition of sales for values from ('2025-09-01') to ('2025-10-01');
create table sales_2025_10 partition of sales for values from ('2025-10-01') to ('2025-11-01');
create table sales_2025_11 partition of sales for values from ('2025-11-01') to ('2025-12-01');
create table sales_2025_12 partition of sales for values from ('2025-12-01') to ('2026-01-01');

-- 2026 partitions
CREATE TABLE sales_2026_01 PARTITION OF sales FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE sales_2026_02 PARTITION OF sales FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE sales_2026_03 PARTITION OF sales FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE sales_2026_04 PARTITION OF sales FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE sales_2026_05 PARTITION OF sales FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE sales_2026_06 PARTITION OF sales FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE sales_2026_07 PARTITION OF sales FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE sales_2026_08 PARTITION OF sales FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE sales_2026_09 PARTITION OF sales FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE sales_2026_10 PARTITION OF sales FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE sales_2026_11 PARTITION OF sales FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE sales_2026_12 PARTITION OF sales FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- 2027 partitions
CREATE TABLE sales_2027_01 PARTITION OF sales FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');
CREATE TABLE sales_2027_02 PARTITION OF sales FOR VALUES FROM ('2027-02-01') TO ('2027-03-01');
CREATE TABLE sales_2027_03 PARTITION OF sales FOR VALUES FROM ('2027-03-01') TO ('2027-04-01');
CREATE TABLE sales_2027_04 PARTITION OF sales FOR VALUES FROM ('2027-04-01') TO ('2027-05-01');
CREATE TABLE sales_2027_05 PARTITION OF sales FOR VALUES FROM ('2027-05-01') TO ('2027-06-01');
CREATE TABLE sales_2027_06 PARTITION OF sales FOR VALUES FROM ('2027-06-01') TO ('2027-07-01');
CREATE TABLE sales_2027_07 PARTITION OF sales FOR VALUES FROM ('2027-07-01') TO ('2027-08-01');
CREATE TABLE sales_2027_08 PARTITION OF sales FOR VALUES FROM ('2027-08-01') TO ('2027-09-01');
CREATE TABLE sales_2027_09 PARTITION OF sales FOR VALUES FROM ('2027-09-01') TO ('2027-10-01');
CREATE TABLE sales_2027_10 PARTITION OF sales FOR VALUES FROM ('2027-10-01') TO ('2027-11-01');
CREATE TABLE sales_2027_11 PARTITION OF sales FOR VALUES FROM ('2027-11-01') TO ('2027-12-01');
CREATE TABLE sales_2027_12 PARTITION OF sales FOR VALUES FROM ('2027-12-01') TO ('2028-01-01');

insert into sales(sale_date, amount, customer)
select

        date '2023-01-01' + (random() * 364)::int
    ,
    round((2.99 + random() * (1870.00 - 2.99))::numeric, 2),
    (array[
        'Beauty','Clothing','Health','Industrial','Kids','Garden','Toys','Tools','Computers','Electronics',
        'Jewelry','Books','Movies','Grocery','Automotive','Outdoors','Music','Home','Sports','Games','Baby','Shoes'
    ])[floor(random()*22 + 1)]
from generate_series(1,50000);

CREATE TABLE sales_archive (LIKE sales INCLUDING ALL) 
PARTITION BY RANGE (sale_date);

ALTER TABLE sales DETACH PARTITION sales_2023_01;

ALTER TABLE sales_archive ATTACH PARTITION sales_2023_01 
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');