This is an old revision of the document!
SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored functions (procedures), triggers, queries and SQL scripts from PostgreSQL to Oracle.
Technical information on migration from PostgreSQL to Oracle.
Last Update: PostgreSQL 9.2 and Oracle 11g R2
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 |
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 |
Converting SQL language elements:
PostgreSQL | Oracle | ||
1 | SQLERRM | Error message | SQLERRM |
2 | SQLSTATE | Error code | SQLCODE |
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 | ||
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) | ||||
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) | ||||
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 |
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) |
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 |
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 | RETURNS datatype | Returned data type | RETURN datatype | |
4 | RETURNS void | No value returned | Converted to CREATE PROCEDURE | |
5 | $$ or $body$ | Start of the body | Removed | |
6 | DECLARE keyword | Before variables declaration | Removed | |
7 | SELECT exp INTO var | Assign a value | SELECT exp INTO var FROM dual | |
8 | TRUNCATE TABLE name | Truncate a table | EXECUTE IMMEDIATE 'TRUNCATE TABLE name' | |
9 | $$ LANGUAGE plpgsql | End of the body | / | |
10 | VOLATILE | Function attribute | Removed | |
11 | COST num | Execution cost | Removed |
For more information, see CREATE FUNCTION Conversion Examples and Conversion of SQL and PL/pgSQL Statements.
Converting SQL statements:
PostgreSQL | Oracle | ||
1 | EXECUTE (sql) | Execute dynamic SQL | EXECUTE IMMEDIATE sql |
2 | PERFORM name(params) | Execute a function | name(params) |
3 | RAISE NOTICE 'format', params | Output a message | DBMS_OUTPUT.PUT_LINE(concatenated_params) |
4 | UPDATE table FROM table2, ... | Update-from | UPDATE table WHERE EXISTS |
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 |