PostgreSQL : How to create Table partitions

preview_player
Показать описание
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);
Рекомендации по теме