What are the schema differences between Oracle and Postgres to pay attention
to when migrating?
When preparing for schema conversion, pay special attention
to the following differences between Oracle and Postgres.
Sequences:
Oracle uses sequences to generate unique numeric values, often
for primary key columns. In PostgreSQL, sequences are similar but may have different syntax
and behavior, such as the usage of SERIAL or BIGSERIAL data types.
Columns:
Before version 12, PostgreSQL didn't have something like
virtual columns, which are columns whose values are automatically computed based on other
columns' values. When migrating from Oracle, users were advised to use views instead of
virtual columns. Now, PostgreSQL has something called generated columns, which are similar
to Oracle's virtual columns.
Constraints:
In both database systems the Primary and Foreign Key, Check,
Not-Null and Unique constraints all work the same way.
Identifiers:
In Oracle, names of schemas, tables, columns, and functions are automatically converted to
uppercase unless they're put inside quotes. But in PostgreSQL, they're converted to
lowercase unless they're quoted. As long as you're consistent in how you quote or don't
quote these names in your application, everything should work smoothly during migration.
Indexes:
Both Oracle and PostgreSQL support indexes for improving query
performance, but syntax and options may differ.
Views:
Views in Oracle and PostgreSQL provide virtual tables based
on queries, but there may be differences in syntax and supported features. Pay attention to
differences in view definitions, such as the handling of NULLs, column aliases, and
functions.
Stored Procedures and Functions:
Oracle uses PL/SQL for stored procedures and functions, while
PostgreSQL uses PL/pgSQL. Pay attention to differences in syntax, language features, and
built-in functions.
Triggers:
Triggers in Oracle and PostgreSQL allow you to define actions
to be executed automatically in response to certain database events. Pay attention to
differences in trigger syntax, supported events, and timing (e.g., BEFORE or AFTER
triggers).
Partitions:
Hash, List, and Range partitions should all work in Postgres.
Tables:
Oracle and PostgreSQL tables have similar concepts, but there may be differences in
supported features and syntax.
Tablespaces:
There are differences between Oracle and Postgres versions of
tablespaces, but they serve the same purpose and should work.
Data types:
Oracle and PostgreSQL have similar but not identical data types. For example, Oracle's
VARCHAR2 is equivalent to PostgreSQL's VARCHAR, and Oracle's NUMBER corresponds to
PostgreSQL's NUMERIC. Pay attention to differences in data type sizes, precision, and
storage requirements.
Schema Objects Ownership:
Oracle uses a schema-based ownership model, where objects
belong to a specific schema/user. PostgreSQL uses a database-based ownership model, where
objects belong to a specific database.