Oracle to PostgreSQL SQL Conversion Guide: Real-World Examples for DBAs
Complete Syntax Mapping, Function Conversion, and PL/SQL to PL/pgSQL Migration
⏱️ Estimated Reading Time: 22–24 minutes
🔄 Oracle → PostgreSQL - Real Production SQL Conversions with Before/After Examples
We had 847 stored procedures written in Oracle PL/SQL. The CTO announced we were moving to PostgreSQL to cut licensing costs by $200,000 annually. The migration deadline was three months away.
I spent the first week manually converting procedures. At that pace, I'd finish in 18 months. That's when I realized: most Oracle-to-PostgreSQL conversions follow predictable patterns. Once you know these patterns, conversion becomes systematic, not guesswork.
Oracle to PostgreSQL migration isn't about rewriting everything from scratch. 70-80% of SQL works with minor syntax adjustments. The remaining 20-30% requires understanding key differences in how each database handles data types, functions, and procedural logic.
This guide covers real production SQL conversions from Oracle to PostgreSQL. If you're an Oracle DBA planning a PostgreSQL migration, these are the patterns, gotchas, and solutions that actually work.
Table of Contents
- Core SQL Syntax Differences: What Changes, What Stays
- Data Type Conversion Matrix: Oracle to PostgreSQL Mapping
- Built-in Function Conversions with Real Examples
- Sequence and Identity Column Migration
- PL/SQL to PL/pgSQL: Procedural Code Conversion
- Trigger Conversion: Oracle to PostgreSQL Patterns
- Date and Timestamp Handling Differences
- Transaction Control and Error Handling
- FAQ
- Related Reading from Real Production Systems
1. Core SQL Syntax Differences: What Changes, What Stays
Good news: Standard SQL (SELECT, INSERT, UPDATE, DELETE) works almost identically in Oracle and PostgreSQL. Here's what actually changes.
String Concatenation
SQL - Oracle Version
-- Oracle: Use || or CONCAT function
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
SQL - PostgreSQL Version
-- PostgreSQL: || works, CONCAT works, but CONCAT handles NULLs differently
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
-- Better in PostgreSQL: CONCAT automatically handles NULLs
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;
-- PostgreSQL also has concat_ws (concat with separator)
SELECT concat_ws(' ', first_name, middle_name, last_name) AS full_name
FROM employees;
DUAL Table (Oracle-Specific)
SQL - Oracle Version
-- Oracle: SELECT from DUAL for expressions
SELECT SYSDATE FROM DUAL;
SELECT 'Hello World' FROM DUAL;
SELECT 2 + 2 FROM DUAL;
SQL - PostgreSQL Version
-- PostgreSQL: No FROM clause needed for expressions
SELECT CURRENT_TIMESTAMP;
SELECT 'Hello World';
SELECT 2 + 2;
-- Or create a DUAL view for Oracle compatibility
CREATE VIEW dual AS SELECT 1 AS dummy;
SELECT CURRENT_TIMESTAMP FROM dual;
Outer Join Syntax
SQL - Oracle Old Style (Don't Use in PostgreSQL)
-- Oracle: Old (+) outer join syntax
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
SQL - PostgreSQL Version (ANSI SQL)
-- PostgreSQL: Use ANSI SQL LEFT JOIN (also works in Oracle 9i+)
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
Top N Queries
SQL - Oracle Version
-- Oracle: Use ROWNUM or FETCH FIRST (12c+)
SELECT * FROM employees
WHERE ROWNUM <= 10;
-- Oracle 12c+ ANSI SQL
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;
SQL - PostgreSQL Version
-- PostgreSQL: Use LIMIT
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10;
-- PostgreSQL also supports ANSI SQL FETCH FIRST
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;
2. Data Type Conversion Matrix: Oracle to PostgreSQL Mapping
Data type mismatches cause the most migration pain. Here's the definitive mapping.
| Oracle Data Type | PostgreSQL Equivalent | Notes |
|---|---|---|
| VARCHAR2(n) | VARCHAR(n) or TEXT | PostgreSQL TEXT has no length limit |
| NUMBER | NUMERIC or DECIMAL | Exact equivalent |
| NUMBER(p,s) | NUMERIC(p,s) | Same precision/scale |
| INTEGER | INTEGER or INT | Same 32-bit integer |
| DATE | TIMESTAMP(0) | Oracle DATE includes time |
| TIMESTAMP | TIMESTAMP | Direct mapping |
| CLOB | TEXT | TEXT unlimited in PostgreSQL |
| BLOB | BYTEA | Binary data storage |
| RAW(n) | BYTEA | Small binary data |
| LONG | TEXT | Oracle deprecated LONG |
Critical Data Type Differences
⚠️ Oracle DATE vs PostgreSQL TIMESTAMP
Oracle DATE: Stores date AND time (year, month, day, hour, minute, second)
PostgreSQL DATE: Stores only date (year, month, day)
Solution: Convert Oracle DATE to PostgreSQL TIMESTAMP(0)
SQL - Table Definition Conversion
-- Oracle table definition
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
hire_date DATE,
salary NUMBER(10,2),
department_id NUMBER,
notes CLOB
);
-- PostgreSQL equivalent
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
hire_date TIMESTAMP(0), -- Changed from DATE
salary NUMERIC(10,2), -- Changed from NUMBER
department_id INTEGER, -- Changed from NUMBER
notes TEXT -- Changed from CLOB
);
3. Built-in Function Conversions with Real Examples
Oracle and PostgreSQL have different function names for common operations. Here are the most frequently used conversions.
String Functions
| Oracle Function | PostgreSQL Equivalent | Example |
|---|---|---|
| NVL(val, default) | COALESCE(val, default) | COALESCE(middle_name, '') |
| NVL2(val, if_not_null, if_null) | CASE or COALESCE | CASE WHEN val IS NOT NULL THEN... |
| DECODE | CASE WHEN | CASE status WHEN 'A' THEN 'Active'... |
| SUBSTR(str, pos, len) | SUBSTRING(str FROM pos FOR len) | SUBSTRING(name FROM 1 FOR 10) |
| INSTR(str, substr) | POSITION(substr IN str) | POSITION('@' IN email) |
| LENGTH(str) | LENGTH(str) or CHAR_LENGTH(str) | LENGTH(description) |
NVL to COALESCE Conversion
SQL - Oracle Version
-- Oracle: NVL for NULL handling
SELECT
employee_id,
first_name,
NVL(middle_name, '') AS middle_name,
NVL(commission_pct, 0) AS commission,
NVL2(manager_id, 'Has Manager', 'No Manager') AS manager_status
FROM employees;
SQL - PostgreSQL Version
-- PostgreSQL: COALESCE for NULL handling
SELECT
employee_id,
first_name,
COALESCE(middle_name, '') AS middle_name,
COALESCE(commission_pct, 0) AS commission,
CASE WHEN manager_id IS NOT NULL
THEN 'Has Manager'
ELSE 'No Manager'
END AS manager_status
FROM employees;
DECODE to CASE Conversion
SQL - Oracle DECODE
-- Oracle: DECODE for conditional logic
SELECT
employee_id,
first_name,
DECODE(department_id,
10, 'Accounting',
20, 'Research',
30, 'Sales',
40, 'Operations',
'Unknown') AS department_name
FROM employees;
SQL - PostgreSQL CASE
-- PostgreSQL: CASE WHEN for conditional logic
SELECT
employee_id,
first_name,
CASE department_id
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END AS department_name
FROM employees;
Date Functions
| Oracle Function | PostgreSQL Equivalent | Example |
|---|---|---|
| SYSDATE | CURRENT_TIMESTAMP or NOW() | CURRENT_TIMESTAMP |
| TRUNC(date) | DATE_TRUNC('day', timestamp) | DATE_TRUNC('day', hire_date) |
| ADD_MONTHS(date, n) | date + INTERVAL 'n months' | hire_date + INTERVAL '3 months' |
| MONTHS_BETWEEN | AGE or date subtraction | AGE(end_date, start_date) |
| TO_CHAR(date, fmt) | TO_CHAR(timestamp, fmt) | TO_CHAR(hire_date, 'YYYY-MM-DD') |
| TO_DATE(str, fmt) | TO_TIMESTAMP(str, fmt) | TO_TIMESTAMP('2026-01-15', 'YYYY-MM-DD') |
SQL - Oracle Date Operations
-- Oracle: Date arithmetic and functions
SELECT
employee_id,
hire_date,
SYSDATE AS today,
TRUNC(SYSDATE) AS today_start,
ADD_MONTHS(hire_date, 3) AS probation_end,
MONTHS_BETWEEN(SYSDATE, hire_date) AS months_employed,
TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date_formatted
FROM employees;
SQL - PostgreSQL Date Operations
-- PostgreSQL: Date arithmetic and functions
SELECT
employee_id,
hire_date,
CURRENT_TIMESTAMP AS today,
DATE_TRUNC('day', CURRENT_TIMESTAMP) AS today_start,
hire_date + INTERVAL '3 months' AS probation_end,
EXTRACT(YEAR FROM AGE(CURRENT_TIMESTAMP, hire_date)) * 12 +
EXTRACT(MONTH FROM AGE(CURRENT_TIMESTAMP, hire_date)) AS months_employed,
TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date_formatted
FROM employees;
4. Sequence and Identity Column Migration
Oracle uses sequences with triggers for auto-increment. PostgreSQL offers both sequences and SERIAL/IDENTITY columns.
Oracle Sequence + Trigger Pattern
SQL - Oracle Sequence and Trigger
-- Oracle: Create sequence
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Oracle: Create trigger to auto-populate ID
CREATE OR REPLACE TRIGGER emp_bir
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.employee_id IS NULL THEN
:NEW.employee_id := emp_seq.NEXTVAL;
END IF;
END;
/
-- Insert without specifying ID
INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');
PostgreSQL SERIAL Column (Recommended)
SQL - PostgreSQL SERIAL Auto-Increment
-- PostgreSQL: Use SERIAL (creates sequence automatically)
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY, -- Automatic sequence + NOT NULL
first_name VARCHAR(50),
last_name VARCHAR(50)
);
-- Insert without specifying ID (automatically generated)
INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');
-- PostgreSQL 10+: Use IDENTITY (SQL standard)
CREATE TABLE employees (
employee_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
Manual Sequence Control
SQL - PostgreSQL Manual Sequence
-- PostgreSQL: Create sequence explicitly (Oracle-style)
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
CACHE 1;
-- Use DEFAULT with NEXTVAL
CREATE TABLE employees (
employee_id INTEGER DEFAULT nextval('emp_seq') PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
-- Or call NEXTVAL explicitly in INSERT
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (nextval('emp_seq'), 'John', 'Doe');
5. PL/SQL to PL/pgSQL: Procedural Code Conversion
This is where most migration time goes. PL/SQL and PL/pgSQL are similar but have critical differences.
Basic Stored Procedure Conversion
PL/SQL - Oracle Stored Procedure
-- Oracle: PL/SQL stored procedure
CREATE OR REPLACE PROCEDURE update_employee_salary(
p_employee_id IN NUMBER,
p_new_salary IN NUMBER
)
IS
v_old_salary NUMBER;
v_count NUMBER;
BEGIN
-- Check if employee exists
SELECT COUNT(*) INTO v_count
FROM employees
WHERE employee_id = p_employee_id;
IF v_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END IF;
-- Get current salary
SELECT salary INTO v_old_salary
FROM employees
WHERE employee_id = p_employee_id;
-- Update salary
UPDATE employees
SET salary = p_new_salary,
last_updated = SYSDATE
WHERE employee_id = p_employee_id;
-- Log change
INSERT INTO salary_history (employee_id, old_salary, new_salary, changed_date)
VALUES (p_employee_id, v_old_salary, p_new_salary, SYSDATE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Salary updated successfully');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
PL/pgSQL - PostgreSQL Stored Procedure
-- PostgreSQL: PL/pgSQL stored procedure
CREATE OR REPLACE FUNCTION update_employee_salary(
p_employee_id INTEGER,
p_new_salary NUMERIC
)
RETURNS VOID AS $$
DECLARE
v_old_salary NUMERIC;
v_count INTEGER;
BEGIN
-- Check if employee exists
SELECT COUNT(*) INTO v_count
FROM employees
WHERE employee_id = p_employee_id;
IF v_count = 0 THEN
RAISE EXCEPTION 'Employee not found'; -- Changed
END IF;
-- Get current salary
SELECT salary INTO v_old_salary
FROM employees
WHERE employee_id = p_employee_id;
-- Update salary
UPDATE employees
SET salary = p_new_salary,
last_updated = CURRENT_TIMESTAMP -- Changed from SYSDATE
WHERE employee_id = p_employee_id;
-- Log change
INSERT INTO salary_history (employee_id, old_salary, new_salary, changed_date)
VALUES (p_employee_id, v_old_salary, p_new_salary, CURRENT_TIMESTAMP);
-- No COMMIT needed (auto-commit in PostgreSQL functions)
RAISE NOTICE 'Salary updated successfully'; -- Changed from DBMS_OUTPUT
EXCEPTION
WHEN OTHERS THEN
-- ROLLBACK happens automatically
RAISE;
END;
$$ LANGUAGE plpgsql;
Key PL/SQL to PL/pgSQL Differences
| Feature | Oracle PL/SQL | PostgreSQL PL/pgSQL |
|---|---|---|
| Block terminator | END; followed by / | END; with $$ delimiters |
| Parameter modes | IN, OUT, IN OUT | IN, OUT, INOUT |
| Print output | DBMS_OUTPUT.PUT_LINE | RAISE NOTICE |
| Raise error | RAISE_APPLICATION_ERROR | RAISE EXCEPTION |
| Transaction control | COMMIT/ROLLBACK in procedure | Auto-commit (no manual control) |
| Packages | CREATE PACKAGE | Use SCHEMA (no packages) |
6. Trigger Conversion: Oracle to PostgreSQL Patterns
Oracle and PostgreSQL triggers have different syntax and execution models.
PL/SQL - Oracle Trigger
-- Oracle: Row-level BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER employees_audit_trg
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.last_updated := SYSDATE;
:NEW.last_updated_by := USER;
IF INSERTING THEN
INSERT INTO audit_log (table_name, operation, record_id, changed_date)
VALUES ('EMPLOYEES', 'INSERT', :NEW.employee_id, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO audit_log (table_name, operation, record_id, changed_date)
VALUES ('EMPLOYEES', 'UPDATE', :NEW.employee_id, SYSDATE);
END IF;
END;
/
PL/pgSQL - PostgreSQL Trigger
-- PostgreSQL: Trigger function (separate from trigger definition)
CREATE OR REPLACE FUNCTION employees_audit_func()
RETURNS TRIGGER AS $$
BEGIN
NEW.last_updated := CURRENT_TIMESTAMP;
NEW.last_updated_by := CURRENT_USER;
IF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, record_id, changed_date)
VALUES ('employees', 'INSERT', NEW.employee_id, CURRENT_TIMESTAMP);
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation, record_id, changed_date)
VALUES ('employees', 'UPDATE', NEW.employee_id, CURRENT_TIMESTAMP);
END IF;
RETURN NEW; -- Must return NEW or OLD
END;
$$ LANGUAGE plpgsql;
-- Create trigger (links function to table)
CREATE TRIGGER employees_audit_trg
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION employees_audit_func();
Critical Trigger Differences
- ❌ Oracle: Trigger logic inline with CREATE TRIGGER
- ✅ PostgreSQL: Trigger function separate, then CREATE TRIGGER links it
- ❌ Oracle: Use :NEW and :OLD
- ✅ PostgreSQL: Use NEW and OLD (no colon)
- ❌ Oracle: Use INSERTING, UPDATING, DELETING
- ✅ PostgreSQL: Use TG_OP = 'INSERT' / 'UPDATE' / 'DELETE'
- ✅ PostgreSQL: Must RETURN NEW, OLD, or NULL
7. Date and Timestamp Handling Differences
Date handling is one of the biggest sources of bugs in Oracle to PostgreSQL migrations.
Date Arithmetic
SQL - Oracle Date Arithmetic
-- Oracle: Add days directly to dates
SELECT
hire_date,
hire_date + 7 AS one_week_later,
hire_date - 7 AS one_week_earlier,
hire_date + 1/24 AS one_hour_later,
SYSDATE - hire_date AS days_employed
FROM employees;
SQL - PostgreSQL Date Arithmetic
-- PostgreSQL: Use INTERVAL for date arithmetic
SELECT
hire_date,
hire_date + INTERVAL '7 days' AS one_week_later,
hire_date - INTERVAL '7 days' AS one_week_earlier,
hire_date + INTERVAL '1 hour' AS one_hour_later,
CURRENT_TIMESTAMP - hire_date AS time_employed -- Returns INTERVAL
FROM employees;
-- To get days as number:
SELECT
EXTRACT(DAY FROM CURRENT_TIMESTAMP - hire_date) AS days_employed
FROM employees;
Date Formatting
SQL - Oracle Date Formatting
-- Oracle: TO_CHAR for formatting
SELECT
TO_CHAR(hire_date, 'YYYY-MM-DD') AS iso_date,
TO_CHAR(hire_date, 'DD-MON-YYYY') AS oracle_format,
TO_CHAR(hire_date, 'HH24:MI:SS') AS time_only
FROM employees;
SQL - PostgreSQL Date Formatting
-- PostgreSQL: TO_CHAR works similarly (some format differences)
SELECT
TO_CHAR(hire_date, 'YYYY-MM-DD') AS iso_date,
TO_CHAR(hire_date, 'DD-Mon-YYYY') AS postgres_format, -- 'Mon' not 'MON'
TO_CHAR(hire_date, 'HH24:MI:SS') AS time_only
FROM employees;
8. Transaction Control and Error Handling
PostgreSQL handles transactions differently than Oracle, especially in stored procedures.
Transaction Control Differences
| Feature | Oracle | PostgreSQL |
|---|---|---|
| COMMIT in procedures | Allowed | Not allowed in functions |
| ROLLBACK in procedures | Allowed | Not allowed in functions |
| Autonomous transactions | PRAGMA AUTONOMOUS_TRANSACTION | Use dblink or separate connection |
| Savepoints | Supported | Supported (same syntax) |
⚠️ Critical Difference: No COMMIT/ROLLBACK in PostgreSQL Functions
PostgreSQL functions run within the calling transaction. They cannot COMMIT or ROLLBACK independently. If you need transaction control, use stored procedures (PostgreSQL 11+) or handle transactions at the application level.
SQL - PostgreSQL 11+ Stored Procedure with Transaction Control
-- PostgreSQL 11+: Use PROCEDURE (not FUNCTION) for transaction control
CREATE OR REPLACE PROCEDURE batch_process_orders()
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT order_id FROM orders WHERE status = 'PENDING' LOOP
BEGIN
-- Process order
UPDATE orders SET status = 'PROCESSING' WHERE order_id = r.order_id;
-- Some complex processing...
-- Commit each order individually
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Rollback this order, continue with next
ROLLBACK;
RAISE NOTICE 'Failed to process order %', r.order_id;
END;
END LOOP;
END;
$$;
-- Call procedure
CALL batch_process_orders();
9. FAQ
Can I use automated tools to convert Oracle to PostgreSQL?
Yes, but with caution. Tools like ora2pg, AWS Schema Conversion Tool, and pgloader can convert 70-80% of basic SQL automatically. However, they struggle with complex PL/SQL procedures, packages, and Oracle-specific features. Best approach: use tools for initial conversion, then manually review and test everything. Expect to spend 30-40% of migration time fixing tool-generated code. The value is getting 80% done quickly, not getting a perfect conversion.
What's the hardest part of Oracle to PostgreSQL migration?
Complex PL/SQL packages and Oracle-specific features like: (1) DBMS packages (DBMS_JOB, DBMS_SCHEDULER, DBMS_OUTPUT), (2) Database links, (3) Materialized views with refresh groups, (4) Advanced queuing, (5) Flashback queries. These require architectural changes, not just SQL conversion. For example, Oracle database links might become PostgreSQL Foreign Data Wrappers (FDW) or application-level API calls. Budget extra time for these.
Should I mention Oracle to PostgreSQL conversion experience on my resume?
Absolutely. Write: "Converted 800+ Oracle PL/SQL procedures to PostgreSQL PL/pgSQL, including complex triggers, functions, and data type migrations. Expertise in ora2pg tool, SQL syntax differences, and performance optimization post-migration." Even if you used automated tools heavily, understanding the conversion patterns and troubleshooting migration issues is valuable experience. PostgreSQL adoption is growing rapidly—this skill is in high demand.
How do I handle Oracle packages in PostgreSQL?
PostgreSQL doesn't have packages. Solutions: (1) Use schemas to group related functions (one schema = one Oracle package), (2) Use naming conventions (hr_get_employee, hr_update_salary), (3) Consider table-based "packages" where you store package state in tables. For DBMS packages: DBMS_OUTPUT → RAISE NOTICE, DBMS_RANDOM → gen_random_uuid(), DBMS_SCHEDULER → pg_cron extension. Oracle packages provide namespace organization—PostgreSQL schemas provide similar capability.
No comments:
Post a Comment