Data Partitioning - List partitioning

Giriş

Sütun FOR VALUES IN ile belirtilen listedeki değer sahipse ilgili tabloya yazılır.

Açıklaması şöyle

List partitioning is useful when you have a table that contains categorical data, and you want to partition the table based on the values in a specific column. For example, you can partition a table of customers by country, with each partition containing data for a specific country.

Şeklen şöyle

Örnek - CREATE TABLE + PARTITION BY LIST

Şöyle yaparız. Burada tablo yeni yaratılırken partition tabloları da yaratılıyor

-- Create Partition Table
CREATE TABLE sales (
    id SERIAL,
    product_name TEXT,
    sale_date DATE
) PARTITION BY LIST (product_name);

-- Creating individual partitions
CREATE TABLE sales_electronics PARTITION OF sales
    FOR VALUES IN ('Laptop', 'TV', 'Smartphone');

CREATE TABLE sales_clothing PARTITION OF sales
    FOR VALUES IN ('Shirt', 'Pants', 'Dress');

-- Adding additional partitions later
CREATE TABLE sales_furniture PARTITION OF sales
    FOR VALUES IN ('Chair', 'Table');

-- Insert Values in Parent Table
INSERT INTO sales (product_name, sale_date)
VALUES
    ('Laptop', '2023-06-01'),
    ('Shirt', '2023-06-02'),
    ('Chair', '2023-06-03');

-- Detach the partition Table
ALTER TABLE sales
DETACH PARTITION sales_furniture;

-- Attach the partition Tables
ALTER TABLE sales
ATTACH PARTITION sales_furniture
FOR VALUES IN ('Chair', 'Table');

Örnek - ALTER TABLE + ADD PARTITION

Şöyle yaparız. Burada ilk başta customer tablosunda partition yok. Daha sonra 

ALTER TABLE ... ADD PARTITION ile ekleniyor

-- create the customers table
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  country VARCHAR(255),
  email VARCHAR(255)
);

-- create the partition tables
CREATE TABLE customers_us (LIKE customers);
CREATE TABLE customers_eu (LIKE customers);
CREATE TABLE customers_ru (LIKE customers);
...

-- attach the partition tables to the customers table
ALTER TABLE customers ADD PARTITION customers_us FOR VALUES IN ('US');
ALTER TABLE customers ADD PARTITION customers_eu FOR VALUES IN 
  ('AT', 'BE', 'BG', 'HR', 'CY', 'CZ', 'DK', 'EE', 'FI', 'FR', 'DE', 'GR', 'HU', 
   'IE', 'IT', 'LV', 'LT', 'LU', 'MT', 'NL', 'PL', 'PT', 'RO', 'SK', 'SI', 'ES', 'SE');
ALTER TABLE customers ADD PARTITION customers_ru FOR VALUES IN ('RU');
...