PostgreSQL PARTITION Partition Table

PostgreSQL partition table, the operability is quite convenient.

However, you can only decide whether to partition the table at creation time, and decide on the partition criteria field, after the normal table is created, you cannot modify the partition table.

Note: Other methods can also be converted to a partitioned table.

As with other databases, partitioned tables serve as a DB feature, with the advantages of not having to say, physically dispersed, and logically unified.

One drawback that must be noted is that the partition table does not allow other tables to be referenced as foreign keys . Only in the real scene business logic as a foreign key, design times talk scene and then scrutiny.

At the same time, only each partition table can be indexed separately before PG 11, and no primary key, index, etc. can be established on the master table.

Pg 11 can be indexed later for the logical partition table (master table), and the partition sub-table is created automatically.

Simple sql:

1 --DROP TABLE dbo.table01;2 3 CREATE TABLEdbo.table01 (4ID bigserial not NULL,5Cre_timetimestampwithout time zone,6Notevarchar( -)7) PARTITION byRANGE (cre_time)8    with (9OIDS=FALSETen   ); One  A CREATE TABLEdbo.table01_2018 -PARTITION ofdbo.table01 -    for VALUES  from('2018-01-01 00:00:00') to('2019-01-01 00:00:00'); the  - CREATE TABLEdbo.table01_2017 -PARTITION ofdbo.table01 -    for VALUES  from('2017-01-01 00:00:00') to('2018-01-01 00:00:00'); +  - ALTER TABLEdbo.table01 +OWNER toPostgres

PostgreSQL PARTITION Partition Table