This article is half-done without your Comment! *** Please share your thoughts via Comment ***
PostgreSQL 10 introduced the native table partitioning which is better than old/manual table partitioning.
Before, we had to manage table partitioning by specifying Trigger and Check constraint.
Now, in the new version, it handles automatically and reduces the work of DBA.
Check the below example of Native Table Partitioning:
Create a table and define the Partition column:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_employee ( empid int GENERATED BY DEFAULT AS IDENTITY, empname character varying, joiningdate timestamp without time zone ) PARTITION BY RANGE (joiningdate); |
Create three sample partitions on joiningdate:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE tbl_employee_history_201701 PARTITION OF tbl_employee FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2017-01-31 23:59:59'); CREATE TABLE tbl_employee_history_201702 PARTITION OF tbl_employee FOR VALUES FROM ('2017-02-01 00:00:00') TO ('2017-02-28 23:59:59'); CREATE TABLE tbl_employee_history_201703 PARTITION OF tbl_employee FOR VALUES FROM ('2017-03-01 00:00:00') TO ('2017-03-31 23:59:59'); |
Insert few sample records:
|
1 2 3 |
INSERT INTO tbl_employee VALUES (1,'Anvesh','2017-01-08'), (2,'Roy','2017-02-10'),(3,'Jenny','2017-03-12'); |
Check the main table, you can get all records:
|
1 |
select *From tbl_employee; |
Check individual table partition for specific range data:
|
1 2 3 |
select *From tbl_employee_history_201701; select *From tbl_employee_history_201702; select *From tbl_employee_history_201703; |
