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.
