SCT reporting "postgresql does not support foreign keys that referencing partitioned tables" while migrating from oracle to RDS for postgres 13.9

Certainly! The error message "postgresql does not support foreign keys that referencing partitioned tables" can occur when migrating from Oracle to Amazon RDS for PostgreSQL 13.9, specifically when dealing with partitioned tables.

In PostgreSQL 13, the support for foreign keys referencing partitioned tables was not yet implemented, which can cause issues during migration from Oracle, where partitioned tables are more commonly used.

To resolve this issue, you have a few options:

Partition the tables in PostgreSQL: If possible, restructure your tables in PostgreSQL to match the partitioning scheme used in Oracle. This can help avoid the foreign key constraint issue.

Use table inheritance instead of partitioning: Instead of using partitioned tables, you can use table inheritance to achieve a similar effect. This can help work around the foreign key constraint issue.

Disable foreign key constraints: As a temporary workaround, you can disable the foreign key constraints that reference the partitioned tables during the migration process. Remember to re-enable the constraints once the migration is complete.

Upgrade to a newer version of PostgreSQL: If possible, upgrade to a newer version of PostgreSQL (e.g., PostgreSQL 14 or later) as the support for foreign keys referencing partitioned tables was introduced in later versions.

Here's an example SQL code to disable and re-enable foreign key constraints:

-- Disable foreign key constraints ALTER TABLE my_table DROP CONSTRAINT my_foreign_key;

-- Perform the migration

-- Re-enable the foreign key constraint ALTER TABLE my_table ADD CONSTRAINT my_foreign_key FOREIGN KEY (column_name) REFERENCES referenced_table(column_name);

It's important to carefully plan and test the migration process, as disabling foreign key constraints can have implications for data integrity and consistency. Ensure that you have a thorough understanding of your data and the relationships between your tables before proceeding with the migration.