PostgreSQL to Oracle Migration

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

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

Databases:

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

Migration Reference

SQL Language Elements

Converting SQL language elements:

	PostgreSQL		Oracle
1	SQLERRM	Error message	SQLERRM
2	SQLSTATE	Error code	SQLCODE

Data Types

Converting character data types:

	PostgreSQL		Oracle
1	CHARACTER(n), CHAR(n)	Fixed-length string, 1 ⇐ n ⇐ 1 GB	CHARACTER(n), CHAR(n)
2	CHARACTER VARYING(n)	Variable-length string, 1 ⇐ n ⇐ 1 GB	VARCHAR2(n)
3	TEXT	Variable-length string, ⇐ 1 GB	VARCHAR2(4000 \| 32767)	CLOB
4	VARCHAR(n)	Variable-length string, n ⇐ 10 MB, 1 GB	VARCHAR2(n <= 32767)	CLOB

Converting numeric data types:

	PostgreSQL		Oracle
1	BIGINT	64-bit integer	NUMBER(19)
2	BIGSERIAL	64-bit autoincrementing integer	Sequence and trigger
3	DECIMAL(p,s), DEC(p,s)	Fixed-point number	NUMBER(p,s)
4	DOUBLE PRECISION	Double-precision floating-point	BINARY_DOUBLE
5	FLOAT4	Single-precision floating-point	BINARY_FLOAT
6	FLOAT8	Double-precision floating-point	BINARY_DOUBLE
7	INTEGER, INT	32-bit integer	NUMBER(10)
8	INT2	16-bit integer	NUMBER(5)
9	INT4	32-bit integer	NUMBER(10)
10	INT8	64-bit integer	NUMBER(20)
11	MONEY	Currency amount	NUMBER(17,2)
12	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
13	REAL	Single-precision floating-point	BINARY_FLOAT
14	SERIAL	32-bit autoincrementing integer	Sequence and trigger
15	SERIAL2	16-bit autoincrementing integer	Sequence and trigger
16	SERIAL4	32-bit autoincrementing integer	Sequence and trigger
17	SERIAL8	64-bit autoincrementing integer	Sequence and trigger
18	SMALLINT	16-bit integer	NUMBER(5)
19	SMALLSERIAL	16-bit autoincrementing integer	Sequence and trigger

Converting date and time data types:

	PostgreSQL		Oracle
1	DATE	Date (year, month and day)	DATE	Includes time part
2	INTERVAL	Date and time interval	INTERVAL YEAR TO MONTH
			INTERVAL DAY TO SECOND
3	INTERVAL unit	Date and time interval	NUMBER(5)
4	INTERVAL YEAR TO MONTH	Date interval	INTERVAL YEAR TO MONTH
5	INTERVAL DAY TO HOUR	Day and time interval	INTERVAL DAY(5) TO SECOND
6	INTERVAL DAY TO MINUTE	Day and time interval	INTERVAL DAY(5) TO SECOND
7	INTERVAL DAY TO SECOND[(p)]	Day and time interval	INTERVAL DAY(5) TO SECOND[(p)]
8	INTERVAL HOUR TO MINUTE	Time interval	INTERVAL DAY(5) TO SECOND
9	INTERVAL HOUR TO SECOND[(p)]	Time interval	INTERVAL DAY(5) TO SECOND[(p)]
10	INTERVAL MINUTE TO SECOND[(p)]	Time interval	INTERVAL DAY(5) TO SECOND[(p)]
11	TIME(p)	Time with fraction	TIMESTAMP(p)
12	TIME(p) WITH TIME ZONE	Time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
13	TIMETZ(p)
14	TIMESTAMP(p)	Date and time with fraction	TIMESTAMP(p)
15	TIMESTAMP(p) WITH TIME ZONE	Date and time with time zone	TIMESTAMP(p) WITH TIME ZONE
16	TIMESTAMPTZ(p)

Converting other data types:

	PostgreSQL		Oracle
1	BIT(n)	Fixed-length bit string	RAW(n/8)
2	BIT VARYING(n)	Variable-length bit string	RAW(n/8)
3	BOOLEAN, BOOL	True, false or NULL	CHAR(1)
4	BYTEA	Variable-length binary data, ⇐ 2 GB	BLOB
5	JSON	JSON data	JSON	Since Oracle 21c	CLOB
6	UUID	Universally unique identifier	CHAR(36)
7	VARBIT(n)	Variable-length bit string	RAW(n/8)
8	XML	XML data	XMLTYPE

Built-in SQL Functions

Converting functions:

	PostgreSQL		Oracle
1	CAST(number AS VARCHAR)	Convert number to string	TO_CHAR(number)
2	CAST(datetime AS VARCHAR)	Convert datetime to string	TO_CHAR(datetime)
3	CAST(string AS INTERVAL)	Convert string to interval	TO_DSINTERVAL(string)
4	COALESCE(exp, …)	Return first non-NULL expression	COALESCE(exp, …)
5	CURRENT_TIMESTAMP	Get start date and time of the transaction	CURRENT_TIMESTAMP
6	DATE('literal')	Convert string to date	TO_DATE('literal', format)
7	DATE(timestamp)	Convert to date, set zero time	TRUNC(timestamp)
8	NOW()	Get start date and time of the transaction	SYSTIMESTAMP
9	OVERLAY(str PLACING sub FROM f)	Replace substring	User-defined function
10	POSITION(substring IN string)	Get position of substring	INSTR(string, substring)
11	SPLIT_PART(string, delimiter, n)	Get n-th item from delimited string	User-defined function
12	SUBSTRING(string, start, len)	Get a substring of string	SUBSTR(string, start, len)
13	TIMEOFDAY()	Get current date and time as string	TO_CHAR(SYSTIMESTAMP)

ALTER TABLE Statement

Converting table modification statement:

	PostgreSQL		Oracle
1	ALTER TABLE t ADD COLUMN	Add column	ALTER TABLE t ADD
2	ALTER TABLE t ALTER COLUMN c SET NOT NULL	Set non-nullable	ALTER TABLE t MODIFY c NOT NULL
3	ALTER TABLE t ALTER COLUMN c DROP NOT NULL	Set nullable	ALTER TABLE t MODIFY c NULL

Adding constraints:

	PostgreSQL		Oracle
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:

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

CREATE INDEX Statement

Converting indexes:

	PostgreSQL		Oracle
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], …)

CREATE PROCEDURE Statement

Converting stored procedures:

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

CREATE SEQUENCE

Converting sequences:

	PostgreSQL		Oracle
1	CREATE SEQUENCE [IF NOT EXISTS] name		CREATE SEQUENCE 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
	NO MAXVALUE	System limit	NOMAXVALUE
5	MINVALUE num	Minimum value is num	MINVALUE num
	NO MINVALUE	System limit	NOMINVALUE
6	CYCLE	Reuse values after reaching the limit	CYCLE
	NO CYCLE	No reuse, this is default	NOCYCLE
7	CACHE num	Cache num values, default is 1	CACHE num	Default is 20

CREATE VIEW Statement

Converting views:

	PostgreSQL		Oracle
1	CREATE [OR REPLACE] VIEW name AS query	Create a view	CREATE [OR REPLACE] VIEW name AS query

INSERT Statement

Converting inserting rows:

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

SELECT Statement

Converting SQL SELECT statement and its clauses:

	PostgreSQL		Oracle
1	FROM table AS alias	Optional AS keyword in FROM	FROM table alias	AS not allowed, removed

CREATE FUNCTION Statement

Converting CREATE FUNCTION statement that defines a user-defined function or stored procedure in PostgreSQL:

	PostgreSQL		Oracle
1	CREATE OR REPLACE FUNCTION		CREATE OR REPLACE FUNCTION \| PROCEDURE
2	param datatype(length)	Parameter definition	param datatype	No length and precision
3	()	Function with empty parameters	Not allowed, () is removed
4	RETURNS datatype	Returned data type	RETURN datatype
5	RETURNS void	No value returned	Converted to CREATE PROCEDURE
6	$$ or $body$	Start of the body	Removed
7	DECLARE keyword	Before variables declaration	Removed
8	SELECT exp INTO var	Assign a value	SELECT exp INTO var FROM dual
9	TRUNCATE TABLE name	Truncate a table	EXECUTE IMMEDIATE 'TRUNCATE TABLE name'
10	$$ LANGUAGE plpgsql	End of the body	/
11	VOLATILE	Function attribute	Removed
12	COST num	Execution cost	Removed

For more information, see CREATE FUNCTION Conversion Examples and Conversion of SQL and PL/pgSQL Statements.

SQL Statements

Converting SQL statements:

	PostgreSQL		Oracle
1	CREATE SCHEMA name	Create schema	CREATE USER name IDENTIFIED BY pwd
2	EXECUTE (sql)	Execute dynamic SQL	EXECUTE IMMEDIATE sql
3	PERFORM name(params)	Execute a function	name(params)
4	RAISE NOTICE 'format', params	Output a message	DBMS_OUTPUT.PUT_LINE(concatenated_params)
5	UPDATE table FROM table2, ...	Update-from	UPDATE table WHERE EXISTS

PL/pgSQL Statements

Converting PL/pgSQL statements and clauses:

	PostgreSQL		Oracle
1	var datatype = value	Variable initialization	var datatype := value
2	var = value	Variable assignment	var := value
3	FETCH [FROM] cursor INTO	Fetch a cursor	FETCH cursor INTO	FROM keyword removed
4	GET DIAGNOSTICS var = ROW_COUNT	Get affected rows	var := SQL%ROWCOUNT
5	IF NOT FOUND THEN EXIT	Exit a cursor loop	EXIT WHEN cursor%NOTFOUND

Flow-of-control statements:

	PostgreSQL		Oracle
1	IF condition THEN statements END IF	IF statement	IF condition THEN statements END IF

Most Complex Migration Issues

PostgreSQL features that may require significant re-design when migrating to Oracle:

	PostgreSQL	Oracle
1	Export data to .csv file from a stored procedure using COPY command	Using UTL_FILE, DBMS_SQL or cursor

Most Popular Expressions and Queries

Converting most popular expressions and queries:

	PostgreSQL		Oracle
1	CAST(TIMEOFDAY() AS TIMESTAMP)	Get the current date and time	SYSTIMESTAMP
2	EXTRACT YEAR * 12 + EXTRACT MONTH	Get interval in months	MONTHS_BETWEEN function

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