Oracle vs PostgreSQL: Schema Migration Differences

April 24, 2026 · 7 min read · Back to blog

Oracle and PostgreSQL are both powerful relational databases, but their schema migration syntax diverges in ways that can trip up even experienced DBAs. If you're moving from Oracle to PostgreSQL — or supporting both — you need to know where the pitfalls are.

This guide covers the most common migration differences: data types, identity columns, ALTER TABLE syntax, constraints, and quoting rules. Every example shows both Oracle and PostgreSQL side by side.

Type Mapping: The First Migration Hurdle

Oracle's type system is older and more granular than PostgreSQL's. Many Oracle types have no direct equivalent, which means you need to make intentional choices during migration.

The NUMBER trap

Oracle's NUMBER without precision is extremely flexible — it stores integers, decimals, and arbitrarily large values. PostgreSQL has no single type that matches this behavior.

-- Oracle: one type fits all
CREATE TABLE measurements (
  id NUMBER PRIMARY KEY,
  value NUMBER,        -- could be integer or decimal
  precise NUMBER(19,4) -- definitely decimal
);

-- PostgreSQL: choose explicitly
CREATE TABLE measurements (
  id BIGINT PRIMARY KEY,
  value NUMERIC,       -- matches arbitrary precision
  precise NUMERIC(19,4)
);

Migration tip: profile your Oracle data first. If a NUMBER column contains only whole numbers under 9 quintillion, use BIGINT. If it contains decimals, use NUMERIC with appropriate precision.

Identity Columns and Sequences

Oracle 12c introduced identity columns, but many Oracle schemas still use explicit sequences. PostgreSQL has had serial/auto-increment support for decades and added true identity columns in version 10.

-- Oracle 12c+ identity column
CREATE TABLE users (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR2(255)
);

-- PostgreSQL identity column (recommended)
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255)
);

-- PostgreSQL legacy SERIAL (still common)
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255)
);

Migration tip: PostgreSQL's GENERATED ALWAYS AS IDENTITY is the closest semantic match to Oracle's identity columns. Use it for new migrations. BIGSERIAL is fine for legacy compatibility.

ALTER TABLE Syntax Differences

This is where migrations get painful. Oracle and PostgreSQL use different keywords and ordering for the same operations.

The pattern: Oracle uses MODIFY for almost everything related to existing columns. PostgreSQL uses explicit ALTER COLUMN ... TYPE/SET/DROP commands. This makes PostgreSQL more verbose but also more explicit.

Quoted Identifiers and Case Sensitivity

This difference causes more migration bugs than any other. Oracle is case-insensitive for unquoted identifiers and stores them as uppercase. PostgreSQL is case-insensitive for unquoted identifiers and stores them as lowercase.

-- Oracle: unquoted becomes uppercase
CREATE TABLE MyTable (MyCol INT);
-- Stored as: MYTABLE, MYCOL

-- PostgreSQL: unquoted becomes lowercase
CREATE TABLE MyTable (MyCol INT);
-- Stored as: mytable, mycol

-- Both: quoted preserves case
CREATE TABLE "MyTable" ("MyCol" INT);
-- Stored as: MyTable, MyCol

Migration tip: if your Oracle schema uses quoted mixed-case identifiers, you must quote them identically in PostgreSQL. If your Oracle schema uses unquoted identifiers, they will automatically become lowercase in PostgreSQL — which is usually what you want.

Constraints and Indexes

Constraint naming

Oracle auto-generates constraint names like SYS_C0012345 if you don't name them explicitly. PostgreSQL also auto-generates names, but uses a different pattern (tablename_columnname_check). Always name your constraints explicitly in both databases.

-- Good in both databases
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  CONSTRAINT uq_users_email UNIQUE (email)
);

-- Bad: relying on auto-generated names
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

Function-based indexes

Both databases support function-based indexes, but the syntax is slightly different:

-- Oracle
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- PostgreSQL
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

Good news: this one is actually identical.

Sequences and Triggers

Legacy Oracle schemas often use triggers to populate primary keys from sequences:

-- Oracle: trigger + sequence pattern
CREATE SEQUENCE users_seq START WITH 1;

CREATE OR REPLACE TRIGGER users_bi
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  :NEW.id := users_seq.NEXTVAL;
END;
/

-- PostgreSQL: simpler with identity or DEFAULT
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255)
);

-- Or with explicit sequence
CREATE SEQUENCE users_seq START 1;
CREATE TABLE users (
  id BIGINT PRIMARY KEY DEFAULT nextval('users_seq'),
  email VARCHAR(255)
);

Migration tip: replace Oracle trigger+sequence patterns with PostgreSQL identity columns or DEFAULT nextval(). Triggers are slower and harder to maintain.

Schema and User Concepts

In Oracle, a user and a schema are essentially the same thing. In PostgreSQL, they are separate: a database contains schemas, and schemas contain objects. Users (roles) have permissions on schemas.

-- Oracle: user = schema
CREATE USER app_user IDENTIFIED BY password;
GRANT CREATE SESSION, CREATE TABLE TO app_user;
-- app_user now has their own schema

-- PostgreSQL: schema is a namespace within a database
CREATE SCHEMA app_schema;
GRANT ALL ON SCHEMA app_schema TO app_user;

Migration tip: don't create a separate PostgreSQL user for every Oracle schema. Instead, create PostgreSQL schemas within a single database and grant appropriate permissions.

Quick Reference: Oracle → PostgreSQL

Related Reading

• Oracle Schema Migrations: A Practical Guide for Developers
• PostgreSQL vs MySQL: Schema Migration Gotchas
• How to Generate ALTER TABLE Scripts Automatically
• How to Design a Database Schema That Scales