Migrating to postgresql from oracle

From: Timo Myyrä <timo(dot)myyra(at)bittivirhe(dot)fi>
To: pgsql-general(at)postgresql(dot)org
Subject: Migrating to postgresql from oracle
Date: 2017-12-23 18:53:15
Message-ID: 1514055195.1570504.1214442624.3DB93FFD@webmail.messagingengine.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2017-12-23 19:05:43 Re: Migrating to postgresql from oracle
Previous Message Melvin Davidson 2017-12-23 18:14:50 Re: Array of foreign key