filmov
tv
PostgreSQL : How to create Table partitions
Показать описание
CREATE TABLE students (
batch_date date NOT NULL,
name varchar(30) NOT NULL,
age varchar(20)
)
PARTITION BY RANGE (batch_date);
CREATE TABLE students_jan PARTITION OF students
FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE students_feb PARTITION OF students
FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE students_mar PARTITION OF students
FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
(batch_date, "name", age)
VALUES('2021-02-03', 'mark', '22');
(batch_date, "name", age)
VALUES('2021-04-03', 'mark', '22');
select * from students_jan ;
select * from students_feb ;
select * from students_mar ;
-----------
CREATE TABLE cities (
city_id bigserial NOT NULL,
name text NOT NULL,
population bigint
)
PARTITION BY LIST (left(lower(name), 1));
CREATE TABLE cities_ab PARTITION OF cities
FOR VALUES IN ('a', 'b');
CREATE TABLE cities_cd PARTITION OF cities
FOR VALUES IN ('c', 'd');
----------
CREATE TABLE orders (
order_id bigint NOT NULL,
cust_id bigint NOT NULL,
status text
)
PARTITION BY HASH (order_id);
CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
batch_date date NOT NULL,
name varchar(30) NOT NULL,
age varchar(20)
)
PARTITION BY RANGE (batch_date);
CREATE TABLE students_jan PARTITION OF students
FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE students_feb PARTITION OF students
FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE students_mar PARTITION OF students
FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
(batch_date, "name", age)
VALUES('2021-02-03', 'mark', '22');
(batch_date, "name", age)
VALUES('2021-04-03', 'mark', '22');
select * from students_jan ;
select * from students_feb ;
select * from students_mar ;
-----------
CREATE TABLE cities (
city_id bigserial NOT NULL,
name text NOT NULL,
population bigint
)
PARTITION BY LIST (left(lower(name), 1));
CREATE TABLE cities_ab PARTITION OF cities
FOR VALUES IN ('a', 'b');
CREATE TABLE cities_cd PARTITION OF cities
FOR VALUES IN ('c', 'd');
----------
CREATE TABLE orders (
order_id bigint NOT NULL,
cust_id bigint NOT NULL,
status text
)
PARTITION BY HASH (order_id);
CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);