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 Data - Data transfer, schema migration and validation tool
• SQLines SQL Converter - SQL scripts conversion and assessment tool

Databases:

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

• SQL Language Elements
• Data Types
• Built-in SQL Functions
• SELECT Statement
• CREATE SEQUENCE
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE TRIGGER
• CREATE TYPE
• Anonymous Block
• PL/SQL Statements
• Built-in PL/SQL Packages
• SQL*Plus Commands

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

Operators:

	Oracle		PostgreSQL
1	'0' < 1	String and integer comparison	'0' < 1	Cast may be required

Data type mapping:

	Oracle		PostgreSQL
1	BINARY_FLOAT	32-bit floating-point number	REAL
2	BINARY_DOUBLE	64-bit floating-point number	DOUBLE PRECISION
3	BLOB	Binary large object, ⇐ 4G	BYTEA
4	CHAR(n), CHARACTER(n)	Fixed-length string, 1 ⇐ n ⇐ 2000	CHAR(n), CHARACTER(n)
5	CLOB	Character large object, ⇐ 4G	TEXT
6	DATE	Date and time	TIMESTAMP(0)
7	DECIMAL(p,s), DEC(p,s)	Fixed-point number	DECIMAL(p,s), DEC(p,s)
8	DOUBLE PRECISION	Floating-point number	DOUBLE PRECISION
9	FLOAT(p)	Floating-point number	DOUBLE PRECISION
10	INTEGER, INT	38 digits integer	DECIMAL(38)
11	INTERVAL YEAR(p) TO MONTH	Date interval	INTERVAL YEAR TO MONTH
12	INTERVAL DAY(p) TO SECOND(s)	Day and time interval	INTERVAL DAY TO SECOND(s)
13	LONG	Character data, ⇐ 2G	TEXT
14	LONG RAW	Binary data, ⇐ 2G	BYTEA
15	NCHAR(n)	Fixed-length UTF-8 string, 1 ⇐ n ⇐ 2000	CHAR(n)
16	NCHAR VARYING(n)	Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000	VARCHAR(n)
17	NCLOB	Variable-length Unicode string, ⇐ 4G	TEXT
18	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
		Fixed-point number, 19 <= p <= 38	DECIMAL(p)
19	NUMBER(p,s)	Fixed-point number, s > 0	DECIMAL(p,s)
20	NUMBER, NUMBER(*)	Floating-point number	DOUBLE PRECISION
21	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
22	NVARCHAR2(n)	Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000	VARCHAR(n)
23	RAW(n)	Variable-length binary string, 1 ⇐ n ⇐ 2000	BYTEA
24	REAL	Floating-point number	DOUBLE PRECISION
25	SMALLINT	38 digits integer	DECIMAL(38)
26	TIMESTAMP(p)	Date and time with fraction	TIMESTAMP(p)
27	TIMESTAMP(p) WITH TIME ZONE	Date and time with fraction and time zone	TIMESTAMP(p) WITH TIME ZONE
28	UROWID(n)	Logical row addresses, 1 ⇐ n ⇐ 4000	VARCHAR(n)
29	VARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 4000	VARCHAR(n)
30	VARCHAR2(n)	Variable-length string, 1 ⇐ n ⇐ 4000	VARCHAR(n)

Other data types:

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:

Converting XML functions:

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)

Converting sequences:

	Oracle		PostgreSQL
1	CREATE SEQUENCE seqname		CREATE SEQUENCE [IF NOT EXISTS] seqname
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	Option not supported, commented
8	ORDER	Guarantee numbers in order of requests	Option not supported, commented
	NOORDER	No guarantee, this is default	Option not supported, 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.

Converting CREATE FUNCTION statement from Oracle to PostgreSQL:

• Pipelined Table Functions Conversion

Converting CREATE PROCEDURE statement from Oracle to PostgreSQL:

	Oracle		PostgreSQL
1	CREATE OR REPLACE PROCEDURE		CREATE OR REPLACE FUNCTION
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		RETURNS VOID AS $$
5	procedure_body		procedure_body
6	END proc_name;		END;
7	/		$$ LANGUAGE plpgsql;

Procedure calls:

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

Converting CREATE TYPE statement:

Anonymous code block:

Cursor operations:

Flow-of-control statements:

Dynamic SQL execution:

Error handling:

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

DBMS_LOB Package:

DBMS_OUTPUT Package:

Converting Oracle SQL*Plus commands: