Oracle Table Partitioning and PostgreSQL Partitions and Table Inheritance - Oracle to Aurora PostgreSQL Migration Playbook
Oracle UsagePostgreSQL UsageUsing The Partition MechanismSummary

Oracle Table Partitioning and PostgreSQL Partitions and Table Inheritance

Feature compatibility AWS SCT / AWS DMS automation level AWS SCT action code index Key differences

Three star feature compatibility

Three star automation level

N/A

Foreign keys referencing to/from partitioned tables are supported on the individual tables in PostgreSQL. Some partition types are not supported by PostgreSQL.

Oracle Usage

The purpose of database partitioning is to provide support for very large tables and indexes by splitting them into smaller pieces. Each partition has its own name and definitions. They can be managed separately or collectively as one object. From an application perspective, partitions are transparent. Partitioned tables behave the same as non-partitioned tables allowing your applications access using unmodified SQL statements. Table partitioning provides several benefits:

  • Performance Improvements — Table partitions help improve query performance by accessing a subset of a partition instead of scanning a larger set of data. Additional performance improvements can be achieved when using partitions and parallel query execution for DML and DDL operations.

  • Data Management — Table partitions facilitate easier data management operations (such as data migration), index management (creation, dropping, or rebuilding indexes), and backup/recovery. These operations are also referred to as Information Lifecycle Management (ILM) activities.

  • Maintenance Operations — Table partitions can significantly reduce downtime caused by table maintenance operations.

Oracle 18c introduces the following enhancements to partitioning.

  • Online Merging of Partitions and Subpartitions: now it is possible to merge table partitions concurrently with Updates/Deletes and Inserts on a partitioned table.

  • Oracle 18c also allows to modify partitioning strategy for the partitioned table: e.g. hash partitioning to range. This can be done both offline and online.

Oracle 19 introduces hybrid partitioned tables: partitions can now be both internal Oracle tables and external tables and sources. It is also possible to integrate both internal and external partitions together in a single partitioned table.

Hash Table Partitioning

When a partition key is specified (for example, a table column with a NUMBER data type), Oracle applies a hashing algorithm to evenly distribute the data (records) among all defined partitions. The partitions have approximately the same size.

The following example creates a hash partitioned table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMBER NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR2(500),
  ERROR_CODE VARCHAR2(10))
  PARTITION BY HASH (ERROR_CODE)
  PARTITIONS 3
  STORE IN (TB1, TB2, TB3);

List Table Partitioning

You can specify a list of discrete values for the table partitioning key in the description of each partition. This type of table partitioning enables control over partition organization using explicit values. For example, partition events by error code values.

The following example creates a list-partitioned table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMBER NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR2(500),
  ERROR_CODE VARCHAR2(10))
  PARTITION BY LIST (ERROR_CODE)
  (PARTITION warning VALUES ('err1', 'err2', 'err3') TABLESPACE TB1,
  PARTITION critical VALUES ('err4', 'err5', 'err6') TABLESPACE TB2);

Range Table Partitioning

Partition a table based on a range of values. The Oracle database assigns rows to table partitions based on column values falling within a given range. Range table partitioning is one of the most frequently used type of partitioning, primarily with date values. Range table partitioning can also be implemented with numeric ranges (1-10000, 10001- 20000…).

The following example creates a range-partitioned table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMBER NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR2(500))
  PARTITION BY RANGE (EVENT_DATE)
  (PARTITION EVENT_DATE VALUES
    LESS THAN (TO_DATE('01/01/2015',
    'DD/MM/YYYY')) TABLESPACE TB1,
  PARTITION EVENT_DATE VALUES
    LESS THAN (TO_DATE('01/01/2016',
    'DD/MM/YYYY')) TABLESPACE TB2,
  PARTITION EVENT_DATE VALUES
    LESS THAN (TO_DATE('01/01/2017',
    'DD/MM/YYYY')) TABLESPACE TB3);

Composite Table Partitioning

With composite partitioning, a table can be partitioned by one data distribution method, and then each partition can be further subdivided into sub-partitions using the same, or different, data distribution method(s). For example:

  • Composite list-range partitioning.

  • Composite list-list partitioning.

  • Composite range-hash partitioning.

Partitioning Extensions

Oracle provides additional partitioning strategies that enhance the capabilities of basic partitioning. These partitioning strategies include:

  • Manageability extensions.

    • Interval partitioning.

    • Partition advisor.

  • Partitioning key extensions.

    • Reference partitioning.

    • Virtual column-based partitioning.

Split Partitions

The SPLIT PARTITION statement can be used to redistribute the contents of one partition, or sub-partition, into multiple partitions or sub-partitions.

ALTER TABLE SPLIT PARTITION p0 INTO
  (PARTITION P01 VALUES LESS THAN (100), PARTITION p02);

Exchange Partitions

The EXCHANGE PARTITION statement is useful to exchange table partitions in or out of a partitioned table.

ALTER TABLE orders EXCHANGE
  PARTITION p_ord3 WITH TABLE orders_year_2016;

Subpartitioning Tables

You can create Subpartitions within partitions to further split the parent partition.

PARTITION BY RANGE(department_id)
  SUBPARTITION BY HASH(last_name)
  SUBPARTITION TEMPLATE
    (SUBPARTITION a TABLESPACE ts1,
    SUBPARTITION b TABLESPACE ts2,
    SUBPARTITION c TABLESPACE ts3,
    SUBPARTITION d TABLESPACE ts4)
  (PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)

For more information, see Partitioning Concepts in the Oracle documentation.

Automatic List Partitioning

Oracle 12c introduces automatic list partitioning. This enhancement enables automatic creation of new partitions for new values inserted into a list-partitioned table. An automatic list-partitioned table is created with only one partition. The database creates the additional table partitions automatically.

The following example creates an automatic list-partitioned table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMBER NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR2(500),
  ERROR_CODE VARCHAR2(10))
  PARTITION BY LIST (ERROR_CODE) AUTOMATIC
  (PARTITION warning VALUES ('err1', 'err2', 'err3'))

For more information, see Oracle Partitioning in the Oracle documentation.

PostgreSQL Usage

Starting from PostgreSQL 10, there is an equivalent option to Oracle Partitions when using RANGE or LIST partitions, as declarative partitions are being supported in PostgreSQL.

Prior to PostgreSQL 10, the table partitioning mechanism in PostgreSQL differed from Oracle. Partitioning in PostgreSQL was implemented using table inheritance. Each table partition was represented by a child table which was referenced to a single parent table. The parent table remained empty and was only used to represent the entire table data set (as a meta-data dictionary and as a query source).

In PostgreSQL 10, you still need to create the partition tables manually, but you do not need to create triggers or functions to redirect data to the right partition.

Some of the Partitioning management operations are performed directly on the sub-partitions (sub-tables). Querying can be performed directly on the partitioned table itself.

Starting with PostgreSQL 11 following features were added.

  • For partitioned tables, a default partition can now be created that will store data which can’t be redirected to any other explicit partitions.

  • In addition to partitioning by ranges and lists, tables can now be partitioned by a hashed key.

  • When UPDATE changes values in a column that’s used as partition key in partitioned table, data is moved to proper partitions.

  • An index can now be created on a partitioned table. Corresponding indexes will be automatically created on individual partitions.

  • Foreign keys can now be created on a partitioned table. Corresponding foreign key constraints will be propagated to individual partitions.

  • Triggers FOR EACH ROW can now be created on a partitioned table. Corresponding triggers will be automatically created on individual partitions as well.

  • When attaching or detaching new partition to a partitioned table with the foreign key, foreign key enforcement triggers are correctly propagated to a new partition.

For more information, see Table Partitioning in the PostgreSQL documentation.

Using The Partition Mechanism

List Partition

CREATE TABLE emps (
  emp_id SERIAL NOT NULL,
  emp_name VARCHAR(30) NOT NULL)
PARTITION BY LIST (left(lower(emp_name), 1));

CREATE TABLE emp_abc
  PARTITION OF emps (
    CONSTRAINT emp_id_nonzero CHECK (emp_id != 0)
  ) FOR VALUES IN ('a', 'b', 'c');

CREATE TABLE emp_def
  PARTITION OF emps (
    CONSTRAINT emp_id_nonzero CHECK (emp_id != 0)
  ) FOR VALUES IN ('d', 'e', 'f');

INSERT INTO emps VALUES (DEFAULT, 'Andrew');

row inserted.

INSERT INTO emps VALUES (DEFAULT, 'Chris');

row inserted.

INSERT INTO emps VALUES (DEFAULT, 'Frank');

row inserted.

INSERT INTO emps VALUES (DEFAULT, 'Pablo');

SQL Error [23514]: ERROR: no partition of relation "emps" found for row
Detail: Partition key of the failing row contains ("left"(lower(emp_name::text), 1)) = (p).

To prevent the preceding error, make sure that all partitions exist for all possible values in the column that partitions the table. The default partition feature was added in PostgreSQL 11.

Use the MAXVALUE and MINVALUE in your FROM/TO clause. This can help you get all values with RANGE partitions without the risk of creating new partitions.

Range Partition

CREATE TABLE sales (
  saledate DATE NOT NULL,
  item_id INT,
  price FLOAT
) PARTITION BY RANGE (saledate);

CREATE TABLE sales_2018q1
  PARTITION OF sales (
    price DEFAULT 0
  ) FOR VALUES FROM ('2018-01-01') TO ('2018-03-31');

CREATE TABLE sales_2018q2
  PARTITION OF sales (
    price DEFAULT 0
  ) FOR VALUES FROM ('2018-04-01') TO ('2018-06-30');

CREATE TABLE sales_2018q3
  PARTITION OF sales (
    price DEFAULT 0
  ) FOR VALUES FROM ('2018-07-01') TO ('2018-09-30');

INSERT INTO sales VALUES (('2018-01-08'),3121121, 100);

row inserted.

INSERT INTO sales VALUES (('2018-04-20'),4378623);

row inserted.

INSERT INTO sales VALUES (('2018-08-13'),3278621, 200);

row inserted.

When you create a table with the PARTITION OF clause, you can use the PARTITION BY clause with it. Using the PARTITION BY clause will create a sub-partition.

A sub-partition can be the same type as the parent partition table or it can be another partition type.

List Combined With Range Partition

The following example creates a LIST partition and sub-partitions by RANGE.

CREATE TABLE salers (
  emp_id serial not null,
  emp_name varchar(30) not null,
  sales_in_usd int not null,
  sale_date date not null
) PARTITION BY LIST (left(lower(emp_name), 1));

CREATE TABLE emp_abc
  PARTITION OF salers (
    CONSTRAINT emp_id_nonzero CHECK (emp_id != 0)
  ) FOR VALUES IN ('a', 'b', 'c') PARTITION BY RANGE (sale_date);

CREATE TABLE emp_def
  PARTITION OF salers (
    CONSTRAINT emp_id_nonzero CHECK (emp_id != 0)
  ) FOR VALUES IN ('d', 'e', 'f') PARTITION BY RANGE (sale_date);

CREATE TABLE sales_abc_2018q1
  PARTITION OF emp_abc (
    sales_in_usd DEFAULT 0
  ) FOR VALUES FROM ('2018-01-01') TO ('2018-03-31');

CREATE TABLE sales_abc_2018q2
  PARTITION OF emp_abc (
    sales_in_usd DEFAULT 0
  ) FOR VALUES FROM ('2018-04-01') TO ('2018-06-30');

CREATE TABLE sales_abc_2018q3
  PARTITION OF emp_abc (
    sales_in_usd DEFAULT 0
  ) FOR VALUES FROM ('2018-07-01') TO ('2018-09-30');

CREATE TABLE sales_def_2018q1
  PARTITION OF emp_def (
    sales_in_usd DEFAULT 0
  ) FOR VALUES FROM ('2018-01-01') TO ('2018-03-31');

CREATE TABLE sales_def_2018q2
  PARTITION OF emp_def (
    sales_in_usd DEFAULT 0
  ) FOR VALUES FROM ('2018-04-01') TO ('2018-06-30');

CREATE TABLE sales_def_2018q3
  PARTITION OF emp_def (
    sales_in_usd DEFAULT 0
  ) FOR VALUES FROM ('2018-07-01') TO ('2018-09-30');

Implementing List Table Partitioning with Inheritance Tables

Create a parent table from which all child tables (partitions) will inherit.

Create child tables (which act similar to table partitions) that inherit from the parent table, the child tables should have an identical structure to the parent table.

Create indexes on each child table. Optionally, add constraints to define allowed values in each table (for example, primary keys or check constraints).

Create a database trigger to redirect data inserted into the parent table to the appropriate child table.

Make sure that the PostgreSQL constraint_exclusion parameter is enabled and set to partition. This parameter ensures that the queries are optimized for working with table partitions.

show constraint_exclusion;
constraint_exclusion
----------------------
partition

For more information, see constraint_exclusion in the PostgreSQL documentation.

PostgreSQL 9.6 doesn’t support declarative partitioning as well as several of the table partitioning features available in Oracle. Alternatives for replacing Oracle interval table partitioning include using application-centric methods using PL/pgSQL or other programming languages.

PostgreSQL 9.6 table partitioning doesn’t support the creation of foreign keys on the parent table. Alternative solutions include application-centric methods such as using triggers/functions or creating these on the individual tables.

PostgreSQL doesn’t support SPLIT and EXCHANGE of table partitions. For these actions, you will need to plan your data migrations manually (between tables) to re-place the data into the right partition.

Examples

The following examples demonstrate how to create a PostgreSQL list-partitioned table.

Create the parent table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMERIC NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR(500),
  ERROR_CODE VARCHAR(10));

Create child tables (partitions) with check constraints.

CREATE TABLE SYSTEM_LOGS_WARNING (
  CHECK (ERROR_CODE IN('err1', 'err2', 'err3'))) INHERITS (SYSTEM_LOGS);

CREATE TABLE SYSTEM_LOGS_CRITICAL (
  CHECK (ERROR_CODE IN('err4', 'err5', 'err6'))) INHERITS (SYSTEM_LOGS);

Create indexes on each of the child tables.

CREATE INDEX IDX_SYSTEM_LOGS_WARNING ON SYSTEM_LOGS_WARNING(ERROR_CODE);

CREATE INDEX IDX_SYSTEM_LOGS_CRITICAL ON SYSTEM_LOGS_CRITICAL(ERROR_CODE);

Create a function to redirect data inserted into the parent table.

CREATE OR REPLACE FUNCTION SYSTEM_LOGS_ERR_CODE_INS()
  RETURNS TRIGGER AS
  $$
  BEGIN
    IF (NEW.ERROR_CODE IN('err1', 'err2', 'err3')) THEN
      INSERT INTO SYSTEM_LOGS_WARNING VALUES (NEW.*);
    ELSIF (NEW.ERROR_CODE IN('err4', 'err5', 'err6')) THEN
      INSERT INTO SYSTEM_LOGS_CRITICAL VALUES (NEW.*);
    ELSE
      RAISE EXCEPTION 'Value out of range,
        check SYSTEM_LOGS_ERR_CODE_INS () Function!';
    END IF;
  RETURN NULL;
  END;
$$
LANGUAGE plpgsql;

Attach the trigger function that you created before to log to the table.

CREATE TRIGGER SYSTEM_LOGS_ERR_TRIG
  BEFORE INSERT ON SYSTEM_LOGS
  FOR EACH ROW EXECUTE PROCEDURE SYSTEM_LOGS_ERR_CODE_INS();

Insert data directly into the parent table.

INSERT INTO SYSTEM_LOGS VALUES(1, '2015-05-15', 'a...', 'err1');
INSERT INTO SYSTEM_LOGS VALUES(2, '2016-06-16', 'b...', 'err3');
INSERT INTO SYSTEM_LOGS VALUES(3, '2017-07-17', 'c...', 'err6');

View results from across all the different child tables.

SELECT * FROM SYSTEM_LOGS;

event_no  event_date  event_str
1         2015-05-15  a...
2         2016-06-16  b...
3         2017-07-17  c...

SELECT * FROM SYSTEM_LOGS_WARNING;

event_no  event_date  event_str  error_code
1         2015-05-15  a...       err1
2         2016-06-16  b...       err3

SELECT * FROM SYSTEM_LOGS_CRITICAL;

event_no  event_date  event_str  error_cod
3         2017-07-17  c...       err6

The following examples demonstrate how to create a PostgreSQL range-partitioned table.

Create the parent table.

CREATE TABLE SYSTEM_LOGS
  (EVENT_NO NUMERIC NOT NULL,
  EVENT_DATE DATE NOT NULL,
  EVENT_STR VARCHAR(500));

Create child tables (partitions) with check constraints.

CREATE TABLE SYSTEM_LOGS_2015
  (CHECK (EVENT_DATE >= DATE '2015-01-01'
    AND EVENT_DATE < DATE '2016- 01-01'))
  INHERITS (SYSTEM_LOGS);

CREATE TABLE SYSTEM_LOGS_2016
  (CHECK (EVENT_DATE >= DATE '2016-01-01'
    AND EVENT_DATE < DATE '2017-01-01'))
  INHERITS (SYSTEM_LOGS);
CREATE TABLE SYSTEM_LOGS_2017
  (CHECK (EVENT_DATE >= DATE '2017-01-01'
    AND EVENT_DATE <= DATE '2017-12-31'))
  INHERITS (SYSTEM_LOGS);

Create indexes on each child table.

CREATE INDEX IDX_SYSTEM_LOGS_2015 ON SYSTEM_LOGS_2015(EVENT_DATE);
CREATE INDEX IDX_SYSTEM_LOGS_2016 ON SYSTEM_LOGS_2016(EVENT_DATE);
CREATE INDEX IDX_SYSTEM_LOGS_2017 ON SYSTEM_LOGS_2017(EVENT_DATE);

Create a function to redirect data inserted into the parent table.

CREATE OR REPLACE FUNCTION SYSTEM_LOGS_INS ()
  RETURNS TRIGGER AS
  $$
  BEGIN
    IF (NEW.EVENT_DATE >= DATE '2015-01-01'
      AND NEW.EVENT_DATE < DATE '2016-01-01') THEN
        INSERT INTO SYSTEM_LOGS_2015 VALUES (NEW.*);
    ELSIF (NEW.EVENT_DATE >= DATE '2016-01-01'
      AND NEW.EVENT_DATE < DATE '2017-01-01') THEN
        INSERT INTO SYSTEM_LOGS_2016 VALUES (NEW.*);
    ELSIF (NEW.EVENT_DATE >= DATE '2017-01-01'
      AND NEW.EVENT_DATE <= DATE '2017-12-31') THEN
        INSERT INTO SYSTEM_LOGS_2017 VALUES (NEW.*);
    ELSE
      RAISE EXCEPTION 'Date out of range.
        check SYSTEM_LOGS_INS () function!';
    END IF;
  RETURN NULL;
  END;
$$
LANGUAGE plpgsql;

Attach the trigger function that you created before to log to the SYSTEM_LOGS table.

CREATE TRIGGER SYSTEM_LOGS_TRIG BEFORE INSERT ON SYSTEM_LOGS
  FOR EACH ROW EXECUTE PROCEDURE SYSTEM_LOGS_INS ();

Insert data directly to the parent table.

INSERT INTO SYSTEM_LOGS VALUES (1, '2015-05-15', 'a...');
INSERT INTO SYSTEM_LOGS VALUES (2, '2016-06-16', 'b...');
INSERT INTO SYSTEM_LOGS VALUES (3, '2017-07-17', 'c...');

Test the solution by selecting data from the parent and child tables.

SELECT * FROM SYSTEM_LOGS;

event_no  event_date  event_str
1         2015-05-15  a...
2         2016-06-16  b...
3         2017-07-17  c...

SELECT * FROM SYSTEM_LOGS_2015;

event_no  event_date  event_str
1         2015-05-15  a...

Examples of New Partitioning Features of PostgreSQL11

Default partitions.

CREATE TABLE tst_part(i INT) PARTITION BY RANGE(i);
CREATE TABLE tst_part1 PARTITION OF tst_part FOR VALUES FROM (1) TO (5);
CREATE TABLE tst_part_dflt PARTITION OF tst_part DEFAULT;
INSERT INTO tst_part SELECT generate_series(1,10,1);

SELECT * FROM tst_part1;
i
1
2
3
4
4 rows)

SELECT * FROM tst_part_dflt;
i

5
6
7
8
9
10
(6 rows)

Hash partitioning.

CREATE TABLE tst_hash(i INT) PARTITION BY HASH(i);
CREATE TABLE tst_hash_1 PARTITION OF tst_hash FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE tst_hash_2 PARTITION OF tst_hash FOR VALUES WITH (MODULUS 2, REMAINDER 1);
INSERT INTO tst_hash SELECT generate_series(1,10,1);

SELECT * FROM tst_hash_1;
i
1
2
(2 rows)

SELECT * FROM tst_hash_2;
i
3
4
5
6
7
8
9
10
(8 rows)

UPDATE on partition key.

CREATE TABLE tst_part(i INT) PARTITION BY RANGE(i);
CREATE TABLE tst_part1 PARTITION OF tst_part FOR VALUES FROM (1) TO (5);
CREATE TABLE tst_part_dflt PARTITION OF tst_part DEFAULT;

INSERT INTO tst_part SELECT generate_series(1,10,1);

SELECT * FROM tst_part1;
i
1
2
3
4
(4 rows)

SELECT * FROM tst_part_dflt;
i
5
6
7
8
9
10
(6 rows)

UPDATE tst_part SET i=1 WHERE i IN (5,6);

SELECT * FROM tst_part_dflt;
i
7
8
9
10
(4 rows)

SELECT * FROM tst_part1;
1
2
3
4
1
1
(6 rows)

Index propagation on partitioned tables.

CREATE TABLE tst_part(i INT) PARTITION BY RANGE(i);
CREATE TABLE tst_part1 PARTITION OF tst_part FOR VALUES FROM (1) TO (5);
CREATE TABLE tst_part2 PARTITION OF tst_part FOR VALUES FROM (5) TO (10);
CREATE INDEX tst_part_ind ON tst_part(i);

\d+ tst_part
Partitioned table "public.tst_part"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition key: RANGE (i)
Indexes: "tst_part_ind" btree (i)
Partitions: tst_part1 FOR VALUES FROM (1) TO (5),
  tst_part2 FOR VALUES FROM (5) TO (10)

\d+ tst_part1
Table "public.tst_part1"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition of: tst_part FOR VALUES FROM (1) TO (5)
Partition constraint: ((i IS NOT NULL) AND (i &gt;= 1) AND (i &lt; 5))
Indexes: "tst_part1_i_idx" btree (i)
Access method: heap

\d+ tst_part2
Table "public.tst_part2"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition of: tst_part FOR VALUES FROM (5) TO (10)
Partition constraint: ((i IS NOT NULL) AND (i &gt;= 5) AND (i &lt; 10))
Indexes: "tst_part2_i_idx" btree (i)
Access method: heap

Foreign keys propagation on partitioned tables.

CREATE TABLE tst_ref(i INT PRIMARY KEY);
ALTER TABLE tst_part ADD CONSTRAINT tst_part_fk FOREIGN KEY (i) REFERENCES tst_ref(i);

\d+ tst_part
Partitioned table "public.tst_part"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition key: RANGE (i)
Indexes: "tst_part_ind" btree (i)
Foreign-key constraints: "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i)
Partitions: tst_part1 FOR VALUES FROM (1) TO (5), tst_part2 FOR VALUES FROM (5) TO (10)

\d+ tst_part1
Table "public.tst_part1"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition of: tst_part FOR VALUES FROM (1) TO (5)
Partition constraint: ((i IS NOT NULL) AND (i &gt;= 1) AND (i &lt; 5))
Indexes: "tst_part1_i_idx" btree (i)
Foreign-key constraints:
  TABLE "tst_part" CONSTRAINT "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i)
Access method: heap

\d+ tst_part2
Table "public.tst_part2"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition of: tst_part FOR VALUES FROM (5) TO (10)
Partition constraint: ((i IS NOT NULL) AND (i &gt;= 5) AND (i &lt; 10))
Indexes: "tst_part2_i_idx" btree (i)
Foreign-key constraints:
  TABLE "tst_part" CONSTRAINT "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i)
Access method: heap

Triggers propagation on partitioned tables.

CREATE TRIGGER some_trigger AFTER UPDATE ON tst_part FOR EACH ROW EXECUTE FUNCTION
some_func();

\d+ tst_part
Partitioned table "public.tst_part"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition key: RANGE (i)
Indexes: "tst_part_ind" btree (i)
Foreign-key constraints: "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i)
Triggers: some_trigger AFTER UPDATE ON tst_part FOR EACH ROW EXECUTE FUNCTION some_func()
Partitions: tst_part1 FOR VALUES FROM (1) TO (5), tst_part2 FOR VALUES FROM (5) TO (10)

\d+ tst_part1
Table "public.tst_part1"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition of: tst_part FOR VALUES FROM (1) TO (5)
Partition constraint: ((i IS NOT NULL) AND (i &gt;= 1) AND (i &lt; 5))
Indexes: "tst_part1_i_idx" btree (i)
Foreign-key constraints:
  TABLE "tst_part" CONSTRAINT "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i)
Triggers: some_trigger AFTER UPDATE ON tst_part1 FOR EACH ROW EXECUTE FUNCTION some_func()
Access method: heap

\d+ tst_part2
Table "public.tst_part2"
Column  Type     Collation  Nullable  Default  Storage  Stats target  Description
i       integer                                plain

Partition of: tst_part FOR VALUES FROM (5) TO (10)
Partition constraint: ((i IS NOT NULL) AND (i &gt;= 5) AND (i &lt; 10))
Indexes: "tst_part2_i_idx" btree (i)
Foreign-key constraints:
  TABLE "tst_part" CONSTRAINT "tst_part_fk" FOREIGN KEY (i) REFERENCES tst_ref(i)
Triggers: some_trigger AFTER UPDATE ON tst_part2 FOR EACH ROW EXECUTE FUNCTION some_func()
Access method: heap

Summary

Oracle table partition type Built-in PostgreSQL support

List

Yes

Range

Yes

Hash

Yes

Composite partitioning (sub partitioning)

No

Interval partitioning

No

Partition advisor

No

Reference partitioning

No

Virtual column-based partitioning

No

Automatic list partitioning

No

Split / exchange partitions

No

For more information, see Table Partitioning in the PostgreSQL documentation.