Oracle to PostgreSQL Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, PL/SQL stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to PostgreSQL (Postgres).

Databases:

Migration Reference

SQL Language Elements

Operators:

Oracle PostgreSQL
1 str1 || str2 || ... String concatenation, NULLs ignored CONCAT(str1, str2, ...) str1 || str2 || ...
2 + and - Operators for datetime Datetime arithmetic Interval expressions

Special variables:

Oracle PostgreSQL
1 column%TYPE Derived data type attribute column%TYPE
2 SYS_REFCURSOR Cursor reference REFCURSOR
3 SQL%ROWCOUNT Number of rows affected GET DIAGNOSTICS var = ROW_COUNT
4 SQL%NOTFOUND No rows affected NOT FOUND
5 SQL%FOUND One or more rows affected FOUND

Expressions:

Oracle PostgreSQL
1 '0' < 1 String and integer comparison '0' < 1 Cast may be required
2 1/2 = 0.5 Division of integers produces decimal 1/2 = 0 1/2::FLOAT = 0.5 Casting is required

Data Types

Character data types:

Oracle PostgreSQL
1 CHAR(n), CHARACTER(n) Fixed-length string, 1 ⇐ n ⇐ 2000 CHAR(n), CHARACTER(n)
2 CLOB Character large object, 4 GB TEXT
3 LONG Character data, 2 GB TEXT
4 NCHAR(n) Fixed-length UTF-8 string, 1 ⇐ n ⇐ 2000 CHAR(n)
5 NCHAR VARYING(n) Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
6 NCLOB Variable-length Unicode string, ⇐ 4 GB TEXT
7 NVARCHAR2(n) Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
8 VARCHAR(n) Synonym for VARCHAR2 VARCHAR(n)
9 VARCHAR2(n) Variable-length string, 1 ⇐ n ⇐ 32767 VARCHAR(n)

Numeric data types:

Oracle PostgreSQL
1 BINARY_FLOAT 32-bit floating-point number REAL
2 BINARY_DOUBLE 64-bit floating-point number DOUBLE PRECISION
3 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
4 DOUBLE PRECISION Synonym for FLOAT(126) DOUBLE PRECISION
5 FLOAT(p) Floating-point number DOUBLE PRECISION
6 INTEGER, INT 38 digits integer DECIMAL(38)
7 NUMBER(p,0), NUMBER(p) 8-bit integer, 1 ⇐ p < 3 SMALLINT
16-bit integer, 3 ⇐ p < 5 SMALLINT
32-bit integer, 5 ⇐ p < 9 INT
64-bit integer, 9 ⇐ p < 19 BIGINT
Large integer, 19 ⇐ p ⇐ 38 DECIMAL(p)
8 NUMBER(p,s) Fixed-point number, s > 0 DECIMAL(p,s)
9 NUMBER, NUMBER(*) Exact floating-point number DECIMAL DOUBLE PRECISION
10 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
11 REAL Synonym for FLOAT(63) REAL
12 SMALLINT 38 digits integer DECIMAL(38)

Date and time data types:

Oracle PostgreSQL
1 DATE Date and time TIMESTAMP(0)
2 INTERVAL YEAR(p) TO MONTH Date interval INTERVAL YEAR TO MONTH
3 INTERVAL DAY(p) TO SECOND(s) Day and time interval INTERVAL DAY TO SECOND(s)
4 TIMESTAMP(p) Date and time with fraction TIMESTAMP(p)
5 TIMESTAMP(p) WITH TIME ZONE Date and time with fraction and time zone TIMESTAMP(p) WITH TIME ZONE

Binary data types:

Oracle PostgreSQL
1 BLOB Binary large object, ⇐ 4G BYTEA
2 LONG RAW Binary data, ⇐ 2G BYTEA
3 RAW(n) Variable-length binary string, 1 ⇐ n ⇐ 2000 BYTEA

Other data types:

Oracle PostgreSQL
1 BFILE Pointer to binary file, ⇐ 4G VARCHAR(255)
2 ROWID Physical row address CHAR(10)
3 SYS_REFCURSOR Cursor reference REFCURSOR
4 UROWID(n) Logical row addresses, 1 ⇐ n ⇐ 4000 VARCHAR(n)
5 XMLTYPE XML data XML

Built-in SQL Functions

Converting string functions:

Oracle PostgreSQL
1 INSTR(str, substr) Get position of substring POSITION(substr IN str) param order
INSTR(str, substr, pos) User-defined function
INSTR(str, substr, pos, num)
2 LISTAGG(exp, delim)... Aggregate concatenation STRING_AGG(exp, delim)
3 TO_CHAR(expr, format) Convert to string TO_CHAR(expr, format)
TO_CHAR(expr) expr::text

Converting datetime functions:

Oracle PostgreSQL
1 FROM_TZ(timestamp, timezone) Setting timezone for timestamp timestamp AT TIME ZONE timezone
2 SYSDATE Get current datetime (up to seconds) CURRENT_TIMESTAMP(0)
3 SYSTIMESTAMP Get current datetime CURRENT_TIMESTAMP
4 TRUNC(datetime, 'unit') Truncate datetime to unit DATE_TRUNC('unit', datetime)

Converting JSON functions:

Oracle PostgreSQL
1 JSON_TABLE(exp, jpath, columns ...) Extract rows from JSON array LATERAL and JSON Expressions
2 JSON_VALUE(json, jpath) Extract JSON value JSONB_PATH_QUERY(json, jpath)

Converting XML functions:

Oracle PostgreSQL
1 EXTRACT(xml_content, xpath) Extract from XML content EXTRACT(xml_content, xpath)
2 XMLAGG(element [ORDER BY order]) Create XML from elements XMLAGG(element [ORDER BY order])
3 XMLTYPE(content) Get XML value from string content XMLPARSE(CONTENT | DOCUMENT content)

ALTER TABLE Statement

Adding constraints:

Oracle PostgreSQL
1 ALTER TABLE tab ADD [CONSTRAINT cns] … Named constraint ALTER TABLE tab ADD [CONSTRAINT cns] …
2 PRIMARY KEY (col, …) Primary key PRIMARY KEY (col, …)
3 UNIQUE (col, …) Unique key UNIQUE (col, …)
4 CHECK (condition) Check condition CHECK (condition)
5 FOREIGN KEY (col, …) REFERENCES tab2 (col, …) Foreign key FOREIGN KEY (col, …) REFERENCES tab2 (col, …)

COMMENT Statement

Converting comments on objects:

Oracle PostgreSQL
1 COMMENT ON COLUMN IS 'text' Comment on column COMMENT ON COLUMN IS 'text'

CREATE INDEX Statement

Converting indexes:

Oracle PostgreSQL
1 CREATE [UNIQUE] INDEX name ON tab Create an index CREATE [UNIQUE] INDEX name ON tab cols
2 (col [ASC | DESC], …) Index columns (col [ASC | DESC], …)

See also Storage clauses conversion in CREATE TABLE.

CREATE TABLE Statement

Converting table definitions:

Oracle PostgreSQL
1 CREATE TABLE name Create a table CREATE TABLE [IF NOT EXISTS] name
2 colname data_type NOT NULL NOT NULL column constraint colname data_type NOT NULL

Storage clauses:

Oracle PostgreSQL
1 LOGGING Log operations on table Removed, logged by default

For more details, see Data Types conversion.

CREATE FUNCTION Statement

Converting CREATE FUNCTION statement from Oracle to PostgreSQL:

Oracle PostgreSQL
1 CREATE OR REPLACE FUNCTION name CREATE OR REPLACE FUNCTION name
2 (param IN | OUT | IN OUT datatype DEFAULT default, …) (param IN | OUT | INOUT datatype DEFAULT default, …)
3 RETURN data_type RETURNS data_type
4 DETERMINISTIC Not supported, removed
5 IS | AS AS $$
6 function_body function_body
7 END proc_name; END;
8 / $$ LANGUAGE plpgsql;

CREATE PROCEDURE Statement

Converting stored procedures:

Oracle PostgreSQL
1 CREATE OR REPLACE PROCEDURE name CREATE OR REPLACE PROCEDURE name
2 param IN | OUT | IN OUT datatype DEFAULT default param IN | OUT | INOUT datatype DEFAULT default
param OUT SYS_REFCURSOR param REFCURSOR ... RETURNS REFCURSOR
3 No () if procedure is without parameters Empty () are required
4 IS | AS AS $$
5 procedure_body procedure_body
6 END proc_name; END;
7 / $$ LANGUAGE plpgsql;

Procedure calls:

Oracle PostgreSQL
1 proc_name(param, …) PERFORM proc_name(param, …) From a PL/pgSQL block
SELECT proc_name(param, …) Standalone call
2 proc_name(name => value, …) Named parameters proc_name(name => value, …)

CREATE SEQUENCE Statement

Converting sequences:

Oracle PostgreSQL
1 CREATE SEQUENCE name CREATE SEQUENCE [IF NOT EXISTS] name
2 INCREMENT BY num Positive or negative increment, default is 1 INCREMENT BY num
3 START WITH num Initial value START [WITH] num
4 MAXVALUE num Maximum value is num MAXVALUE num
NOMAXVALUE System limit NO MAXVALUE
5 MINVALUE num Minimum value is num MINVALUE num
NOMINVALUE System limit NO MINVALUE
6 CYCLE Reuse values after reaching the limit CYCLE
NOCYCLE No reuse, this is default NO CYCLE
7 CACHE num Cache num values, default is 20 CACHE num Default is 1
NOCACHE Values are not preallocated CACHE 1
8 ORDER Guarantee numbers in order of requests Option not supported, commented
NOORDER No guarantee, this is default Removed as it is default

By default MAXVALUE is 9999999999999999999999999999 in Oracle, and this value is out of range for type BIGINT in PostgreSQL, so MAXVALUE is commented in such case.

CREATE TRIGGER Statement

Converting triggers:

Oracle PostgreSQL
1 CREATE TRIGGER Create a trigger CREATE FUNCTION ... RETURNS TRIGGER
CREATE TRIGGER … EXECUTE FUNCTION
2 schema.name Trigger name name Cannot be schema-qualified,
uses table schema
3 BEFORE | AFTER | INSTEAD OF BEFORE | AFTER | INSTEAD OF
4 INSERT OR UPDATE OR DELETE INSERT OR UPDATE OR DELETE
5 REFERENCING NEW AS …
OLD AS … 		Column reference predicates REFERENCING NEW TABLE AS …
OLD TABLE AS …
6 :NEW.column New column value reference NEW.column
7 :OLD.column Old column value reference OLD.column
8 INSERTING Conditional predicate for INSERT operation TG_OP = 'INSERT'
9 UPDATING Conditional predicate for UPDATE operation TG_OP = 'UPDATE'

DROP TRIGGER statement:

Oracle PostgreSQL
1 DROP TRIGGER schema.name DROP TRIGGER [IF EXISTS] name ON table

CREATE TYPE Statement

Converting CREATE TYPE statement:

Oracle PostgreSQL
1 CREATE OR REPLACE TYPE name AS OBJECT (col1 type, …) Object type CREATE TYPE name AS (col1 type, …)
2 CREATE OR REPLACE TYPE name IS TABLE OF type Table type CREATE TYPE name AS (row type[])

CREATE VIEW Statement

Converting views:

Oracle PostgreSQL
1 CREATE [OR REPLACE] VIEW name AS query Create a view CREATE [OR REPLACE] VIEW name AS query
2 WITH READ ONLY View cannot be updated Not supported, removed.
Revoke permissions to modify views

INSERT Statement

Converting inserting rows:

Oracle PostgreSQL
1 INSERT INTO name alias Insert rows INSERT INTO name AS alias

SELECT Statement

Converting SQL queries:

Oracle PostgreSQL
1 DUAL table A single row, single column dummy table FROM clause can be omitted, DUAL removed
2 FROM (SELECT …) Optional alias for subquery FROM (SELECT …) s Alias required
3 SELECT … MINUS SELECT … Exclude rows returned by 2nd query SELECT … EXCEPT SELECT …
4 CONNECT BY PRIOR Hierarchical queries Recursive Common Table Expressions (CTE)

Row limitation:

Oracle PostgreSQL
1 ROWNUM = 1 Return 1 row only LIMIT 1
ROWNUM <= n Row limit LIMIT n
ROWNUM < n LIMIT n - 1

Anonymous Block

Anonymous code block:

Oracle PostgreSQL
1 [DECLARE declarations] BEGIN statements END; / DO $$ [DECLARE declarations] BEGIN statements END; $$;

PL/SQL Statements

Cursor operations:

Oracle PostgreSQL
1 cur SYS_REFCURSOR Reference cursor declaration cur REFCURSOR = 'cur'
2 CURSOR cur IS select_stmt Cursor declaration cur CURSOR FOR select_stmt
3 FOR rec IN cur LOOP … END LOOP Fetch data from cursor FOR rec IN cur LOOP … END LOOP
4 FOR rec IN (select_stmt) LOOP … Fetch data from query DECLARE rec RECORD;
FOR rec IN (select_stmt) LOOP …
5 OPEN cur Open a cursor OPEN cur
6 FETCH cur INTO v1, v2, … Fetch data FETCH cur INTO v1, v2, …
7 EXIT WHEN cur%NOTFOUND Terminate loop when no row found EXIT WHEN NOT FOUND
8 CLOSE cur Close a cursor CLOSE cur

Flow-of-control statements:

Oracle PostgreSQL
1 IF condition THEN statements END IF IF statement IF condition THEN statements END IF
2 LOOP statements END LOOP A loop statement LOOP statements END LOOP
3 EXIT WHEN condition Exit from loop when condition is true EXIT WHEN condition

Dynamic SQL execution:

Oracle PostgreSQL
1 EXECUTE IMMEDIATE sql_string INTO ... USING ... EXECUTE sql_string INTO ... USING ...
2 :1, :2, … Referencing parameters in SQL string $1, $2, …

Executing stored procedures from a PL/SQL block:

Oracle PostgreSQL
1 sp_name(param1, ...) Execute procedure CALL sp_name(param1, ...)
sp_name Procedure without parameters CALL sp_name

Error handling:

Oracle PostgreSQL
1 RAISE_APPLICATION_ERROR(code, message) Raise an user error RAISE EXCEPTION '%s', message
USING ERRCODE = code
2 SQLCODE Error code SQLSTATE
3 SQLERRM Error message SQLERRM

Built-in PL/SQL Packages

Converting built-in PL/SQL packages from Oracle to PostgreSQL:

DBMS_LOB Package:

Oracle PostgreSQL
1 DBMS_LOB.APPEND(dest_lob, src_lob) Append a LOB value dest_lob := dest_lob || src_lob

DBMS_OUTPUT Package:

Oracle PostgreSQL
1 DBMS_OUTPUT.PUT_LINE(text) Output a message RAISE NOTICE '%', text

SQL*Plus Commands

Converting Oracle SQL*Plus commands:

Oracle PostgreSQL
1 CALL proc_name(name => value, …) Execute a stored procedure CALL proc_name(name => value, …)
EXECUTE proc_name(name => value, …)
2 SET SERVEROUTPUT ON | OFF Enable output for DBMS_OUTPUT Commented

Database Migration Issues