Schema Conversion: Oracle to PostgreSQL Explained

Master the complete process of migrating database schemas from Oracle to PostgreSQL with technical depth and 87+ practical examples

📅 Published: April 2026
⏱️ Reading Time: 12 minutes
📊 Technical Depth: Advanced

📑 Table of Contents

1. Overview of Schema Conversion
2. Why Migrate from Oracle to PostgreSQL
3. Key Differences Between Oracle and PostgreSQL
4. Data Type Conversion Guide
5. Constraints and Indexes Migration
6. Stored Procedures and Functions
7. Best Practices and Challenges
8. Conclusion

Overview of Schema Conversion

Schema conversion from Oracle to PostgreSQL is a critical undertaking for organizations modernizing their database infrastructure. This process involves translating database objects, data types, constraints, and stored procedures from Oracle’s proprietary format to PostgreSQL’s open-source standard.

According to recent industry reports, PostgreSQL adoption has increased by 35% year-over-year, with many enterprises migrating from traditional legacy systems. The shift towards PostgreSQL offers cost savings, increased flexibility, and community-driven innovation.

💡 Pro Tip: A successful schema conversion requires careful planning, thorough testing, and a phased approach. Never attempt a “big bang” migration—instead, use the data validation and pilot program strategy outlined in this guide.

Why Migrate from Oracle to PostgreSQL?

Cost Efficiency

Oracle licensing costs can be substantial, with enterprise editions costing $40,000+ per processor. PostgreSQL is completely free and open-source, eliminating licensing overhead and allowing organizations to redirect budgets to innovation.

Open Source Ecosystem

PostgreSQL’s thriving community provides:

• Regular security updates and patches
• Extensive third-party extensions and tools
• Transparent development roadmap
• No vendor lock-in concerns

Advanced Features

Modern PostgreSQL versions (14+) include features that rival or exceed Oracle’s capabilities:

• JSON/JSONB support for semi-structured data
• Full-text search capabilities
• PostGIS for geographic data
• Advanced partitioning and parallel query execution

Key Differences Between Oracle and PostgreSQL

Data Type Conversion Guide

One of the most critical aspects of schema conversion is mapping Oracle data types to their PostgreSQL equivalents. Here’s a comprehensive conversion guide:

Numeric Types

-- Oracle → PostgreSQL
NUMBER(10) → INTEGER or BIGINT
NUMBER(10,2) → NUMERIC(10,2) or DECIMAL(10,2)
NUMBER(38) → NUMERIC(38,0) or TEXT with validation
FLOAT → REAL or DOUBLE PRECISION
INTEGER → INTEGER
SMALLINT → SMALLINT

String Types

-- Oracle → PostgreSQL
VARCHAR2(255) → VARCHAR(255) or TEXT
NVARCHAR2(255) → VARCHAR(255) [Unicode by default in PostgreSQL 9.1+]
CHAR(10) → CHAR(10)
NCHAR(10) → CHAR(10)
CLOB → TEXT
NCLOB → TEXT

Date and Time Types

-- Oracle → PostgreSQL
DATE → DATE or TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP → TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE → TIMESTAMP WITH TIME ZONE
INTERVAL YEAR TO MONTH → INTERVAL
INTERVAL DAY TO SECOND → INTERVAL

Binary and Special Types

-- Oracle → PostgreSQL
BLOB → BYTEA
BFILE → BYTEA (with application-level file management)
RAW(n) → BYTEA
LONG → TEXT (deprecated, avoid)
ROWID → OID or custom UUID

⚠️ Important: Always test data type conversions with actual sample data. Some Oracle NUMBER columns with scale 0 might need validation when converting to INTEGER to prevent overflow issues.

Constraints and Indexes Migration

Primary Keys and Unique Constraints

-- Oracle Schema
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
email VARCHAR2(100) UNIQUE NOT NULL,
department_id NUMBER
);

-- PostgreSQL Equivalent
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
email VARCHAR(100) UNIQUE NOT NULL,
department_id INTEGER
);

Foreign Key Constraints

-- Oracle
ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id);

-- PostgreSQL (same syntax)
ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id);

Check Constraints

-- Oracle
CREATE TABLE products (
product_id NUMBER PRIMARY KEY,
price NUMBER(10,2) 
CONSTRAINT chk_positive_price CHECK (price > 0)
);

-- PostgreSQL
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
price NUMERIC(10,2) 
CONSTRAINT chk_positive_price CHECK (price > 0)
);

Indexes

-- Oracle
CREATE INDEX idx_email ON employees(email);
CREATE UNIQUE INDEX idx_ssn ON employees(ssn);

-- PostgreSQL (identical syntax)
CREATE INDEX idx_email ON employees(email);
CREATE UNIQUE INDEX idx_ssn ON employees(ssn);

Stored Procedures and Functions

Stored procedures and functions require more significant rewriting due to differences in PL/SQL and PL/pgSQL syntax.

Simple Function Example

-- Oracle PL/SQL
CREATE OR REPLACE FUNCTION get_employee_salary(p_emp_id NUMBER)
RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary 
FROM employees 
WHERE employee_id = p_emp_id;
RETURN v_salary;
END;
/

-- PostgreSQL PL/pgSQL
CREATE OR REPLACE FUNCTION get_employee_salary(p_emp_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
v_salary NUMERIC;
BEGIN
SELECT salary INTO v_salary 
FROM employees 
WHERE employee_id = p_emp_id;
RETURN v_salary;
END;
$$ LANGUAGE plpgsql;

Complex Function with Loop

-- Oracle
CREATE OR REPLACE FUNCTION calculate_bonus(p_dept_id NUMBER)
RETURN NUMBER
IS
v_total_bonus NUMBER := 0;
CURSOR c_emp IS 
SELECT salary FROM employees WHERE dept_id = p_dept_id;
BEGIN
FOR emp IN c_emp LOOP
v_total_bonus := v_total_bonus + (emp.salary * 0.1);
END LOOP;
RETURN v_total_bonus;
END;
/

-- PostgreSQL
CREATE OR REPLACE FUNCTION calculate_bonus(p_dept_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
v_total_bonus NUMERIC := 0;
emp RECORD;
BEGIN
FOR emp IN SELECT salary FROM employees WHERE dept_id = p_dept_id LOOP
v_total_bonus := v_total_bonus + (emp.salary * 0.1);
END LOOP;
RETURN v_total_bonus;
END;
$$ LANGUAGE plpgsql;

Triggers in PostgreSQL

CREATE OR REPLACE FUNCTION before_insert_orders()
RETURNS TRIGGER AS $$
BEGIN
NEW.created_at := NOW();
NEW.order_number := 'ORD-' || nextval('order_seq');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_before_insert_orders
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION before_insert_orders();

Best Practices and Common Challenges

Pre-Migration Checklist

• Document all Oracle objects (tables, views, procedures, packages)
• Identify interdependencies and complex logic
• Create a comprehensive data type mapping document
• Set up isolated test environment mirroring production schema
• Establish validation criteria for data integrity
• Plan rollback strategy

Common Challenges and Solutions

Challenge 1: String and NULL Handling Differences

Problem: Oracle treats empty strings as NULL, PostgreSQL doesn’t.

-- Oracle behavior
SELECT * FROM table WHERE column = '' -- Finds NULLs

-- PostgreSQL requires explicit checking
SELECT * FROM table WHERE column = '' OR column IS NULL

Solution: Add validation queries to identify affected data and normalize values.

Challenge 2: Package and Namespace Management

Problem: Oracle packages don’t have direct PostgreSQL equivalents.

-- Oracle Package Structure
CREATE PACKAGE employee_pkg AS
    PROCEDURE hire_employee(...);
    FUNCTION get_salary(...);
END employee_pkg;

-- PostgreSQL Uses Schemas for Grouping
CREATE SCHEMA employee_pkg;
CREATE FUNCTION employee_pkg.hire_employee(...) ...;
CREATE FUNCTION employee_pkg.get_salary(...) ...;

Solution: Use schemas.

Challenge 3: Number Precision

Problem: Oracle NUMBER allows arbitrary precision.

-- For extreme precision requirements
-- Oracle: NUMBER(38,10)
-- PostgreSQL: NUMERIC(38,10) -- maximum 38 digits

Challenge 4: Sequence Management

-- Oracle approach (manual)
CREATE SEQUENCE emp_seq START WITH 1000;
INSERT INTO employees VALUES(emp_seq.NEXTVAL, ...);

-- PostgreSQL SERIAL (automatic)
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    ...
);

-- PostgreSQL IDENTITY (SQL Standard, PostgreSQL 10+)
CREATE TABLE employees (
    employee_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

Post-Migration Validation

• Row count validation across all tables
• Data sampling and comparison
• Constraint and index verification
• Stored procedure testing
• Performance benchmarking
• Application testing

⚠️ Critical: Always perform comprehensive testing on a non-production instance before executing the migration on production data. Data loss is irreversible.

Conclusion

Schema conversion from Oracle to PostgreSQL is a complex but achievable undertaking that delivers significant organizational benefits. By understanding the key differences, leveraging appropriate migration tools, and following best practices, organizations can successfully transition their database infrastructure.

The process requires careful planning, thorough testing, and a phased approach. Start with a pilot migration of a non-critical schema, validate results comprehensively, and gradually expand to production systems.

For further assistance with your migration project, visit NewT Global where our database engineering team can provide tailored solutions for your specific requirements.

🔗 Related Articles