Introduction
When migrating partitioned tables from Oracle to Azure PostgreSQL Flexible Server, many customers prefer to preserve their existing Oracle table design exactly as defined in the original DDLs. Specifically, they want to avoid altering the partition key structure, especially by not adding the partition key to any primary or unique constraints, because doing so would change the table’s original design integrity.
The challenge arises because PostgreSQL enforces a rule: any primary key or unique constraint on a partitioned table must include the partition key. This difference in constraint handling creates a migration roadblock for customers aiming for a like-for-like move from Oracle without schema changes.
To bridge this gap and emulate Oracle’s partitioning behavior, the pg_partman extension offers a practical solution. It supports declarative partitioning in PostgreSQL while eliminating the need to modify primary or unique constraints to include the partition key. This enables successful migrations while preserving complete compatibility with Oracle’s partitioning model and eliminating the need for schema changes.
Background
For example, consider the following Oracle “Orders” table partitioned by the order_date column.
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE NOT NULL,
status TEXT,
total_amount NUMERIC(10,2)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2025_m1 PARTITION OF orders FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');
CREATE TABLE orders_2025_m2 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_m3 PARTITION OF orders FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE orders_2025_m4 PARTITION OF orders FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
In Oracle, it’s valid to define a primary key only on order_id without including the partition key (order_date). Many customers want to preserve this design when migrating to Azure PostgreSQL Flexible Server. However, Azure PostgreSQL Flexible Server requires that any primary or unique constraint on a partitioned table must also include the partition key. Attempting to keep a primary key solely on order_id will result in an error.
To replicate the Oracle’s behavior, the pg_partman extension along with the table template can be used. It allows partition management without forcing the partition key into primary or unique constraints, enabling the migration to retain the original table structure.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(20),
total_amount NUMERIC(10,2)
)
PARTITION BY RANGE (order_date);
unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "orders" lacks column "order_date" which is part of the partition key.
Enable the Server Level Parameters for PG_PARTMAN
To configure the server-level parameter, go to the Azure portal, open the left-hand panel, and search for ‘Server Parameters’ under the Settings section. Then, search for azure.extensions, check the box for PG_PARTMAN in the value field, and click Save
Once the above is completed search for “shared_preload_libraries” and in the value section click the checkbox for PG_PARTMAN_BGW and then click SAVE.
The above step would prompt the restart of the server.
Prerequisites at Database level
Once the server is restarted login to the database either by using PgAdmin or through psql. And set up the role and following permissions.
CREATE ROLE partman_role WITH LOGIN;
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman; --- Create extension if not already created
GRANT ALL ON SCHEMA partman TO partman_role;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman_role;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman_role;
GRANT ALL ON SCHEMA public TO partman_role;
GRANT TEMPORARY ON DATABASE postgres to partman_role;
And if you have the partition table not part of partman_role ensure that usage on the schema granted. Example:- GRANT USAGE ON SCHEMA partman TO partman_role;
Create partition table without Primary Key
Create the parent table including the partition key without including the Primary key.
CREATE TABLE orders (
order_id BIGINT,
customer_id BIGINT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(20),
total_amount NUMERIC(10,2)
)
PARTITION BY RANGE (order_date);
Create a Template Table
In order to use the Primary Key part of the table and not to include it in the partition key use the template as shown below. Notice that it’s the same structure as parent table and included primary key for the column order_id.
CREATE TABLE orders_template (
order_id BIGINT ,
customer_id BIGINT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(20),
total_amount NUMERIC(10,2),
PRIMARY KEY (order_id)
);
Create parent table using Pg_Partman
Once the above tables are completed, the next step is to invoke the create_parent function as shown below.
SELECT partman.create_parent(
p_parent_table := 'public.orders',
p_control := 'order_date',
p_type := 'native',
p_interval := 'monthly',
p_template_table := 'public.orders_template'
);Notice that, the above script included orders_template table as a parameter for template table, this would ensure that partitions are created with the primary keys automatically.
Validate the partition table
After inserting the records validate the partitions created
SELECT tableoid::regclass AS partition, * FROM orders;
EXPLAIN SELECT * FROM orders WHERE order_date > '2025-01-01';
EXPLAIN SELECT * FROM orders WHERE order_id > 100;
The query plan above shows that the partition key (order_date) is primarily used for date-range queries, independent of the primary key. In contrast, queries filtering by order_id rely on the primary key, which is defined separately from the partition key.
Feedback and Suggestions
If you have feedback or suggestions for improving this asset, please contact the Data SQL Ninja Team (datasqlninja@microsoft.com).
Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.
Thank you for your support!
Microsoft