I'm preparing migration of our asset management system database from Oracle 12c to PostgreSQL 10. I'm using ora2pg and a bit of sed to mangle the SQL ready for import to pg but I've hit first problem: ERROR: referenced relation "..." is not a table
Our Oracle databases uses single 'admin' schema and dedicated schema for each customer. For example usergroup mappings are held in admin.usergroup table. Then admin schema has customer-specific view on this table admin.usergroup_customer1 view which limits the full view to just those of that customer. And then each customers own schema has synonym to that view like customer1.usergroup. The applications queries use the "usergroup" table to query the group mappings.
Here's hopefully a bit more detailed description of the database structure:
ADMIN SCHEMA: TABLES: usergroup unit user ... VIEWS: usergroup_customer1 unit_customer1 user_customer1 ... CUSTOMER1 SCHEMA: TABLES: resource ... SYNONYM: usergroup (refers to admin.usergroup_customer1) user (refers to admin.user_customer1) unit (refers to admin.unit_customer1) ...
So it seems that postgresql doesn't support foreign keys in views like Oracle. Would you have any suggestions how the above Oracle structure would best be handled in PostgreSQL? I'm pretty new to PostgreSQL so I might overlook something if I try to solve this by myself.