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).

SQLines SQL Converter - SQL Scripts Conversion and Assessment tool. Try Online
SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

Oracle 23, 22c, 21c, 19c, 18c, 12c, 11g, 10g and 9i
PostgreSQL 16.x, 15.x, 14.x, 13.x, 12.x, 11.x, 10.x and 9.x

Migration Reference

SQL Language Elements

Converting SQL and PL/SQL language elements from Oracle to PostgreSQL:

	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

Operators:

	Oracle		PostgreSQL
1	'0' < 1	String and integer comparison	'0' < 1	Cast may be 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 date and time functions:

	Oracle		PostgreSQL
1	FROM_TZ(timestamp, timezone)	Setting timezone for timestamp	timestamp AT TIME ZONE timezone
2	SYSDATE	Get current date and time (up to seconds)	CURRENT_TIMESTAMP(0)
3	SYSTIMESTAMP	Get the current timestamp	CURRENT_TIMESTAMP
4	TRUNC(datetime)	Truncate datetime to day	DATE_TRUNC('day', 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;

Pipelined Table Functions Conversion

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	REFERENCING NEW AS … OLD AS …	Column reference predicates	REFERENCING NEW TABLE AS … OLD TABLE AS …
4	:NEW.column	New column value reference	NEW.column
5	:OLD.column	Old column value reference	OLD.column
6	INSERTING	Conditional predicate for INSERT operation	TG_OP = 'INSERT'
7	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)

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 [USING …]		EXECUTE sql_string [USING …]
2	:1, :2, …	Referencing parameters in SQL string	$1, $2, …

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

Zero character code in CHAR or VARCHAR

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference