Hi 2017-12-23 19:53 GMT+01:00 Timo Myyrä <timo.my...@bittivirhe.fi>:
> 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. > try to ask on https://github.com/darold/ora2pg/issues Regard Pavel > Timo > >
