Hi,
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.
Timo
