PostgreSQL to Oracle Migration

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

Databases:

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

Tools

Migration Reference

Technical information on migration from PostgreSQL to Oracle.

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

SQL Language Elements

Converting SQL language elements:

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

Data Types

Converting built-in SQL data types:

	PostgreSQL		Oracle
1	BIGINT	64-bit integer	NUMBER(19)
2	BIGSERIAL	64-bit autoincrementing integer	Sequence and trigger
3	BIT(n)	Fixed-length bit string	RAW(n/8)
4	BIT VARYING(n)	Variable-length bit string	RAW(n/8)
5	BOOLEAN, BOOL	True, false or NULL	CHAR(1)
6	BYTEA	Variable-length binary data, ⇐ 2G	BLOB
7	CHARACTER(n), CHAR(n)	Fixed-length string, 1 ⇐ n ⇐ 1G	CHARACTER(n), CHAR(n)
8	CHARACTER VARYING(n)	Variable-length string, 1 ⇐ n ⇐ 1G	VARCHAR2(n)
9	DATE	Date (year, month and day)	DATE	Includes time part
10	DECIMAL(p,s), DEC(p,s)	Fixed-point number	NUMBER(p,s)
11	DOUBLE PRECISION	Double-precision floating-point	BINARY_DOUBLE
12	FLOAT4	Single-precision floating-point	BINARY_FLOAT
13	FLOAT8	Double-precision floating-point	BINARY_DOUBLE
14	INTEGER, INT	32-bit integer	NUMBER(10)
15	INT2	16-bit integer	NUMBER(5)
16	INT4	32-bit integer	NUMBER(10)
17	INT8	64-bit integer	NUMBER(20)
18	INTERVAL	Date and time interval	INTERVAL YEAR TO MONTH
18	INTERVAL	Date and time interval	INTERVAL DAY TO SECOND
19	INTERVAL unit	Date and time interval	NUMBER(5)
20	INTERVAL YEAR TO MONTH	Date interval	INTERVAL YEAR TO MONTH
21	INTERVAL DAY TO HOUR	Day and time interval	INTERVAL DAY(5) TO SECOND
22	INTERVAL DAY TO MINUTE	Day and time interval	INTERVAL DAY(5) TO SECOND
23	INTERVAL DAY TO SECOND[(p)]	Day and time interval	INTERVAL DAY(5) TO SECOND[(p)]
24	INTERVAL HOUR TO MINUTE	Time interval	INTERVAL DAY(5) TO SECOND
25	INTERVAL HOUR TO SECOND[(p)]	Time interval	INTERVAL DAY(5) TO SECOND[(p)]
26	INTERVAL MINUTE TO SECOND[(p)]	Time interval	INTERVAL DAY(5) TO SECOND[(p)]
27	MONEY	Currency amount	NUMBER(17,2)
28	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
29	REAL	Single-precision floating-point	BINARY_FLOAT
30	SERIAL	32-bit autoincrementing integer	Sequence and trigger
31	SERIAL2	16-bit autoincrementing integer	Sequence and trigger
32	SERIAL4	32-bit autoincrementing integer	Sequence and trigger
33	SERIAL8	64-bit autoincrementing integer	Sequence and trigger
34	SMALLINT	16-bit integer	NUMBER(5)
35	SMALLSERIAL	16-bit autoincrementing integer	Sequence and trigger
36	TEXT	Variable-length character data, ⇐ 1G	CLOB
37	TIME(p)	Time with fraction	TIMESTAMP(p)
38	TIME(p) WITH TIME ZONE	Time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
39	TIMETZ(p)	Time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
40	TIMESTAMP(p)	Date and time with fraction	TIMESTAMP(p)
41	TIMESTAMP(p) WITH TIME ZONE	Date and time with time zone	TIMESTAMP(p) WITH TIME ZONE
42	TIMESTAMPTZ(p)	Date and time with time zone	TIMESTAMP(p) WITH TIME ZONE
43	UUID	Universally unique identifier	CHAR(36)
44	VARBIT(n)	Variable-length bit string	RAW(n/8)
45	VARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 1G	VARCHAR2(n)
46	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)

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

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference