Limitations for partitioning commands compatible with Oracle databases v16

Limitations for primary or unique keys

For Oracle compatibility, EDB Postgres Advanced Server allows you to use a CREATE TABLE statement to create a unique or primary key constraint on a partitioned table that includes a non-partitioned key column. This capability differs from PostgreSQL, and it has the following limitations:

• The primary or unique key is created only on a child table. The key isn't created on a parent or root table.

• When using an ALTER statement to create a new partition, the primary or unique key isn't created automatically. You must create it manually.

• The uniqueness of the key is limited to the child partition. The unique key doesn't apply across the partitioning hierarchy.

Interval partitioning limitations

Interval range partitioning is a useful capability, but it has the following limitations:

• Interval partitioning is restricted to a single partition key. If you try to create or alter an existing partitioned table having a multi-column partitioned key, it fails.

• The supported key must be a numerical or date range type.

• You can't define DEFAULT and MAXVALUE for an interval-partitioned table.

• Data to insert can't have NULL, Not-a-Number, or Infinity values specified in the partitioning key column.

• The interval-partitioning expression must yield a constant value and can't be a negative value.

• For the interval-partitioned table, define at least one partition first.

Automatic partitioning limitations

Automatic list partitioning has the following limitations:

• Like interval partitioning, automatic partitioning is restricted to a single partition key.

• An automatic partitioned table can't have a DEFAULT partition.

• On this page
• Limitations for primary or unique keys
• Interval partitioning limitations
• Automatic partitioning limitations