1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. 1 Database Migration Basics
  5. 1.2 Database Migration Outline

1.2 Database Migration Outline

Every database offers various schema objects, referred as database objects. Working with a database requires you to know the following.

  • Database objects
  • SQL Statement and Clauses
  • Writing SQL query

During migration from Oracle to PostgreSQL, you must first know what Oracle database objects are supported in PostgreSQL and to what extent Oracle SQL and PostgreSQL are different.

This chapter describes you high level overview of Oracle to PostgreSQL differences.

ORACLEPOSTGRESQL
TABLETABLE
VIEWVIEW
CONSTRAINT (Primary Key, Foreign Key, Unique Key, Check)CONSTRAINT (Primary Key, Foreign Key, Unique Key, Check)
INDEXINDEX
MATERIALIZED VIEWMATERIALIZED VIEW
SEQUENCESEQUENCE
SYNONYMNot available in PostgreSQL. Ignored in PostgreSQL, but can be partially achieved through Views. SET search_path can also be a work-around for synonyms.
TYPEDOMAIN/ TYPE
COLLECTION TYPESNot available in PostgreSQL. Can be achieved through Arrays in PostgreSQL.
STORED FUNCTIONFUNCTION
STORED PROCEDUREProcedures are available in PostgreSQL 11 onwards.
PACKAGENot available in PostgreSQL. Achieved through creating individual schemas.
TRIGGERTRIGGER

Object DDL differences

Schema objects (such as views, synonyms, constraints, indexes, functions, procedures) in Oracle database can be either in valid or invalid state. But in PostgreSQL, objects other than indexes do not have any status associated. All objects once compiled are always in valid state.

Oracle and PostgreSQL work quite differently in that respect:

OraclePostgreSQL

In Oracle, you can always drop a table that has views dependent on it. The dependent views become invalid when the referenced table is dropped.

In PostgreSQL, you cannot drop a table that has dependent views. You have to drop the dependent views first and drop the table. Alternately you can specify CASCADE with DROP TABLE statement to drop table with all dependents.

Was this article helpful to you? Yes No

How can we help?