1. Parent child inheritance table
At present, MogDB does not support inherits inheritance feature.
omm=# CREATE TABLE tab_t2(age int) inherits(tab_t1); ERROR: CREATE TABLE ... INHERITS is not yet supported.
PostgreSQL supports inheritance. Partitioned tables before version 10 are implemented through inheritance. Each partition is actually an independent table. Data update can be realized by trigger or rule.
The following demonstrates inheritance features in PostgreSQL:
CREATE TABLE tab_t1(id int primary key,name varchar(20) not null); CREATE TABLE tab_t2(age int) inherits(tab_t1);
Add fields to parent table:
alter table tab_t1 add create_date date;
View table structure:
postgres=# \d tab_t1
Table "public.tab_t1"
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(20) | | not null |
create_date | date | | |
Indexes:
"tab_t1_pkey" PRIMARY KEY, btree (id)
Number of child tables: 1 (Use \d+ to list them.)
postgres=# \d tab_t2
Table "public.tab_t2"
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(20) | | not null |
age | integer | | |
create_date | date | | |
Inherits: tab_t1Instead of routing data through triggers or rules, we insert data directly:
INSERT INTO tab_t1 VALUES (1,'data 1 in tab_t1',now()); INSERT INTO tab_t1 VALUES (2,'data 2 in tab_t1',now()); INSERT INTO tab_t2 VALUES (3,'data 3 in tab_t2',18,now()); INSERT INTO tab_t2 VALUES (4,'data 4 in tab_t2',20,now());
Querying data from parent table will display all data of parent table and child table:
postgres=# SELECT * from tab_t1; id | name | create_date ----+------------------+------------- 1 | data 1 in tab_t1 | 2021-04-11 2 | data 2 in tab_t1 | 2021-04-11 3 | data 3 in tab_t2 | 2021-04-11 4 | data 4 in tab_t2 | 2021-04-11 (4 rows)
Query ONLY the parent table through the ONLY keyword:
postgres=# SELECT * from ONLY tab_t1; id | name | create_date ----+------------------+------------- 1 | data 1 in tab_t1 | 2021-04-11 2 | data 2 in tab_t1 | 2021-04-11 (2 rows)
Query from a sub table to display only the data in the sub table:
postgres=# select * from tab_t2; id | name | age | create_date ----+------------------+-----+------------- 3 | data 3 in tab_t2 | 18 | 2021-04-11 4 | data 4 in tab_t2 | 20 | 2021-04-11 (2 rows)
Notes for using inherited properties:
The child table cannot fully inherit all the attributes of the parent table, such as unique constraints, primary keys and foreign keys. Check constraints and non empty constraints can be inherited.
The structure of the parent table is modified, and the structure of the child table is modified at the same time.
When there is no data in the parent table, it is not recommended to create indexes and or unique constraints on the parent table, but should be created separately on each child table.
2. Declarative partition: range partition
Map the data to each partition based on the range, which is determined by the partition key specified when creating the partition table. This partition method is more commonly used, and the partition key often uses date.
PostgreSQL is supported from version 10. The range partition declarative syntax is divided into two steps:
1. Create the table as a partitioned table by specifying the PARTITION BY clause, including the partition method and the column list used as the partition key.
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate)2. Create partitions. The definition of each partition must specify the partition method and partition key boundary corresponding to the parent table.
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
...The MogDB range partition declarative syntax can be completed in one step. The range partition clause syntax has two formats:
VALUES LESS THAN syntax format (the partition key of range partition policy supports up to 4 columns)
START END syntax format (the partition key of range partition policy only supports 1 column)
Note that the syntax of the above two clauses cannot be mixed, and the syntax format of START END is gs_dump will be converted to VALUES LESS THAN syntax format.
Example of MogDB range partition:
CREATE TABLE tab_part (
id int not null,
create_date date not null
) PARTITION BY RANGE(create_date)
(
PARTITION p_20210401 VALUES LESS THAN(to_date('2021-04-01','yyyy-mm-dd')),
PARTITION p_20210402 VALUES LESS THAN(to_date('2021-04-02','yyyy-mm-dd')),
PARTITION p_max VALUES LESS THAN(MAXVALUE)
);Viewing the system table, you can see that the partition policy is "r" and the range partition.
omm=# select relname,partstrategy from pg_partition where relname='tab_part'; relname | partstrategy ----------+-------------- tab_part | r (1 row)
View zones and boundaries:
select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_part');
relname | parttype | parentid | boundaries
------------+----------+----------+-------------------------
tab_part | r | 16412 |
p_20210401 | p | 16412 | {"2021-04-01 00:00:00"}
p_20210402 | p | 16412 | {"2021-04-02 00:00:00"}
p_max | p | 16412 | {NULL}
(4 rows)Next, insert three pieces of data:
insert into tab_part values(1,'2021-03-31'); insert into tab_part values(2,'2021-04-01'); insert into tab_part values(3,'9999-12-31');
Query partition by partition name p_20210402, or by PARTITION FOR.
omm=# select * from tab_part PARTITION (p_20210402); id | create_date ----+--------------------- 2 | 2021-04-01 00:00:00 (1 row)
3. Declarative partition: list partition
Divide the table by explicitly listing the key values that appear in each partition.
Like the previous range partition, the PostgreSQL list partition declarative syntax is also a two-step process. It is supported from version 10, and MogDB only needs one step.
Example of MogDB list partition:
CREATE TABLE tab_list(
dept_no number,
part_no varchar2(20),
country varchar2(20),
dtime date,
amount number
)
PARTITION BY LIST(country)(
PARTITION europe VALUES('FRANCE', 'ITALY'),
PARTITION asia VALUES('INDIA', 'PAKISTAN'),
PARTITION americas VALUES('US', 'CANADA')
);Viewing the system table, you can see that the partition policy is "l" and the partition list is.
omm=# select relname,partstrategy from pg_partition where relname='tab_list'; relname | partstrategy ----------+-------------- tab_list | l (1 row)
View zones and boundaries:
select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_list');
relname | parttype | parentid | boundaries
----------+----------+----------+------------------
tab_list | r | 16389 |
americas | p | 16389 | {US,CANADA}
asia | p | 16389 | {INDIA,PAKISTAN}
europe | p | 16389 | {FRANCE,ITALY}
(4 rows)4. Declarative partition: hash partition
The data is mapped to each partition through hash, and records with the same hash value are stored in each partition.
PostgreSQL hash partition declarative syntax is also a two-step process. It is supported from version 11, and MogDB only needs one step.
Example of MogDB hash partition:
CREATE TABLE tab_hash(
dept_no number,
part_no varchar2(20),
country varchar2(20),
dtime date,
amount number
)PARTITION BY HASH(part_no)(
PARTITION p1,
PARTITION p2,
PARTITION p3
);Viewing the system table, you can see that the partition policy is "h" and hash partition.
omm=# select relname,partstrategy from pg_partition where relname='tab_hash'; relname | partstrategy ----------+-------------- tab_hash | h (1 row)
View zones and boundaries:
select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_hash');
relname | parttype | parentid | boundaries
----------+----------+----------+------------
tab_hash | r | 16405 |
p3 | p | 16405 | {2}
p2 | p | 16405 | {1}
p1 | p | 16405 | {0}
(4 rows)5. Automatic expansion interval partition based on range partition
Interval partition is a functional extension of Range type partition. For Range partitions with continuous data types, if the inserted new data value does not match the current partition, the interval partition feature can realize automatic partition creation. Partition field must be of time type (date or timestamp).
PostgreSQL does not support this syntax at present, and MogDB has supported it from the version.
Example of MogDB interval partition:
CREATE TABLE tab_range_interval (
id int not null,
create_date date not null
) PARTITION BY RANGE(create_date) INTERVAL('1 month')
(
PARTITION p1 VALUES LESS THAN(to_date('2021-01-29','yyyy-mm-dd'))
);Viewing the system table, you can see that the partition policy is "i" and the interval partition.
omm=# select relname,partstrategy,interval from pg_partition where relname='tab_range_interval';
relname | partstrategy | interval
----------+--------------+-----------
tab_part | i | {"1 month"}
(1 row)Next, insert three pieces of data:
insert into tab_range_interval values(1,'2021-01-29'); insert into tab_range_interval values(2,'2021-02-28'); insert into tab_range_interval values(3,'2022-03-29');
After inserting data, check whether the corresponding partition is automatically created:
omm=# select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_range_interval');
relname | parttype | parentid | boundaries
--------------------+----------+----------+--------------
tab_range_interval | r | 16572 |
p1 | p | 16572 | {2021-01-29}
sys_p1 | p | 16572 | {2021-02-28}
sys_p2 | p | 16572 | {2021-03-28}
sys_p3 | p | 16572 | {2022-04-28}
(5 rows)You can see sys_p1，sys_p2，sys_p3 is the partition automatically generated by the system, and the month end problem is automatically handled.
be careful:
1. Starting from version 2.0.0, the default character set of the template library is set by SQL_ASCII is changed to UTF8, and the database compatibility is changed from Oracle to PostgreSQL. The impact on this test is the date data type.
2. At present, only INTERVAL-RANGE is supported, and other methods are not supported.
3. The interval partition field must be of time type (date or timestamp).
6. Sub partition
MogDB currently does not support sub partitions, and PostgreSQL declarative partitions are supported.
PostgreSQL sub partition example:
Create partition table first:
CREATE TABLE sales(
id serial,
sales_count int,
sales_date date not null
) PARTITION BY RANGE(sales_date);Create a partition by year: sales_2018,sales_2019,sales_2020:
CREATE TABLE sales_2018 PARTITION OF sales
FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
CREATE TABLE sales_2019 PARTITION OF sales
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
CREATE TABLE sales_2020 PARTITION OF sales
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');When creating a new partition in 2021, you can create sub partitions by month:
create table sales_2021 partition of sales
for values from ('2021-01-01') to ('2022-01-01') partition by range(sales_date);
create table sales_2021_01 partition of sales_2021
for values from ('2021-01-01') to ('2021-02-01');
create table sales_2021_02 partition of sales_2021
for values from ('2021-02-01') to ('2021-03-01');
create table sales_2021_03 partition of sales_2021
for values from ('2021-03-01') to ('2021-04-01');
create table sales_2021_04 partition of sales_2021
for values from ('2021-04-01') to ('2021-05-01');
create table sales_2021_05 partition of sales_2021
for values from ('2021-05-01') to ('2021-06-01');
create table sales_2021_06 partition of sales_2021
for values from ('2021-06-01') to ('2021-07-01');
create table sales_2021_07 partition of sales_2021
for values from ('2021-07-01') to ('2021-08-01');
create table sales_2021_08 partition of sales_2021
for values from ('2021-08-01') to ('2021-09-01');
create table sales_2021_09 partition of sales_2021
for values from ('2021-09-01') to ('2021-10-01');
create table sales_2021_10 partition of sales_2021
for values from ('2021-10-01') to ('2021-11-01');
create table sales_2021_11 partition of sales_2021
for values from ('2021-11-01') to ('2021-12-01');
create table sales_2021_12 partition of sales_2021
for values from ('2021-12-01') to ('2022-01-01');Next, we can see the partition sales by viewing the sales table through \ d +_ 2021 has one more PARTITIONED attribute than other partitions, indicating that it is a sub partition.
Partitioned table "postgres.sales"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------+-----------+----------+-----------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('sales_id_seq'::regclass) | plain | |
sales_count | integer | | | | plain | |
sales_date | date | | not null | | plain | |
Partition key: RANGE (sales_date)
Partitions: sales_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
sales_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
sales_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
sales_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'), PARTITIONEDWe can view sales through \ d +_ 2021:
Partitioned table "postgres.sales_2021"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+---------+-----------+----------+-----------------------------------+---------+--------------+-------------
id | integer | | not null | nextval('sales_id_seq'::regclass) | plain | |
sales_count | integer | | | | plain | |
sales_date | date | | not null | | plain | |
Partition of: sales FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Partition constraint: ((sales_date IS NOT NULL) AND (sales_date >= '2021-01-01'::date) AND (sales_date < '2022-01-01'::date))
Partition key: RANGE (sales_date)
Partitions: sales_2021_01 FOR VALUES FROM ('2021-01-01') TO ('2021-02-01'),
sales_2021_02 FOR VALUES FROM ('2021-02-01') TO ('2021-03-01'),
sales_2021_03 FOR VALUES FROM ('2021-03-01') TO ('2021-04-01'),
sales_2021_04 FOR VALUES FROM ('2021-04-01') TO ('2021-05-01'),
sales_2021_05 FOR VALUES FROM ('2021-05-01') TO ('2021-06-01'),
sales_2021_06 FOR VALUES FROM ('2021-06-01') TO ('2021-07-01'),
sales_2021_07 FOR VALUES FROM ('2021-07-01') TO ('2021-08-01'),
sales_2021_08 FOR VALUES FROM ('2021-08-01') TO ('2021-09-01'),
sales_2021_09 FOR VALUES FROM ('2021-09-01') TO ('2021-10-01'),
sales_2021_10 FOR VALUES FROM ('2021-10-01') TO ('2021-11-01'),
sales_2021_11 FOR VALUES FROM ('2021-11-01') TO ('2021-12-01'),
sales_2021_12 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01')summary
1. At present, mogdb only supports declarative partition, range partition, list partition, hash partition and automatic expansion interval partition of INTERVAL-RANGE. PostgreSQL supports inheritance and declarative partitions, and does not support automatic expansion of interval partitions.
2. The partition field of automatic expansion interval partition currently only supports time type (date or timestamp).
3.MogDB does not support sub partitions at present, and PostgreSQL supports declarative sub partitions.
4. For a declarative partition, the partition must have exactly the same column set as the partition table, and the table structure must be strictly consistent. In table inheritance, the child table can have additional columns that have not appeared in the parent table, and table inheritance allows multiple inheritance.
Ink Sky Wheel original link: https://www.modb.pro/db/49865... (copy to the browser or click "read original" to view it immediately)
About the author
Peng Chong, PG technical consultant of Yunhe enmo, online name "Duomi Papi", PG community certification expert, China's first PostgreSQL ACE Partner, has been engaged in software research and development based on PostgreSQL database for many years, and is good at PL/PGSQL business migration and optimization, Oracle to PostgreSQL migration and upgrading, and heterogeneous database integration; As a columnist of PostgreSQL practice of Mo Tianlun, he is keen on sharing PostgreSQL practice technology, actively promotes PostgreSQL in his own post, and is committed to making more contributions to PG community.