Migrating from Oracle SQL to PostgreSQL

Differences to consider in the migration Oracle -> PGSQL

DataTypes

Oracle to PostgreSQL have quite a lot differences when to comes to data types. We need to do mapping and make sure things are being done correctly.

Oracle	PostgreSQL
CHAR(n)	CHAR(n)
VARCHAR2(n)	VARCHAR(n)
NUMBER(n,m)	NUMERIC(n,m)
NUMBER(4)	SMALLINT
NUMBER(9)	INT
NUMBER(18)	BIGINT
NUMBER(n)	NUMERIC(n)
DATE	TIMESTAMP(0)
TIMESTAMP WITH LOCAL TIME ZONE	TIMESTAMPTZ
CLOB	TEXT
BLOB RAW(n)	BYTEA(1 GB limit) Large object

Synonyms

Use views instead.

However, for sequences views won't work. Instead of using synonyms, the user for one schema should give explicit access for the other schema user to use their sequences.

This can b done as below:

sql

GRANT USAGE ON SCHEMA MYSCHEMA1 TO MYSCHEMA2;
GRANT USAGE, SELECT ON SEQUENCE MYSCHEMA1.MYSEQUENCE TO MYSCHEMA2;

Then, to use the sequence, the schema should be added as prefix, like below:

sql

SELECT NEXTVAL('MYSCHEMA1.MYSEQUENCE')

Table DUAL

In Oracle, the FROM clause is mandatory for every SELECT statement. Hence, Oracle database uses DUAL table for SELECT statements where the table name is not required. In PostgreSQL, the FROM clause is not mandatory. Hence, DUAL table is not required.

### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: Relation »dual« does not exist

sql

#In Oracle
SELECT MYTABLE.NEXTVAL FROM DUAL

#In Postgresql  
SELECT MYTABLE.NEXTVAL

SYSDATE

org.postgresql.util.PSQLException: ERROR: column "sysdate" does not exist

Oracle's SYSDATE function returns date and time, although it's using a DATE type.

PostgreSQL can return the date with CURRENT_DATE but this will not contain the time (when converted to timestamp the time will be 00:00:00).

For returning the time as well, use one of the the following methods from PostgreSQL:

Time Retrieval Method	Required Function
SQL statement start time	STATEMENT_TIMESTAMP()
Transaction start time	TRANSACTION_TIMESTAMP() or NOW()
Time of function implementation	CLOCK_TIMESTAMP()

For other functions see: https://www.postgresql.org/docs/8.2/functions-datetime.html

SYSTIMESTAMP

org.postgresql.util.PSQLException: ERROR: column "systimestamp" does not exist

Oracle's SYSTIMESTAMP the system time and date, including time zone, of the system. This time using a TIMESTAMP WITH TIME ZONE data type.

In PostgreSQL CURRENT_TIMESTAMP function returns the current date and time with time zone.

Empty String and NULL

There is a clear difference in behavior when it comes to these two important parameters. In Oracle, the IS NULL operator is used to check whether a string is empty or not. In PostgreSQL, the result is FALSE for empty string and TRUE for NULL.

The DELETE Statement

In Oracle, the DELETE statement can work without the FROM clause, which is not the case in PostgreSQL. will need to add the FROM clause in PostgreSQL DELETE statement manually.

TRANSACTIONS

Oracle has built-in transaction functionality, which has to be activated manually in PostgreSQL. In Oracle, the transaction starts when executing any statement and ends with the COMMIT statement.

In PostgreSQL, the transaction commences with BEGIN and ends with the COMMIT statement. Also default isolation level of PostgreSQL is Read Committed.

sql

    UPDATE statements using alias

The following UPDATE statement in Oracle will return an error due to the lack of support for aliases in the SET list:

sql

		UPDATE MYTABLE A
		   SET A.JB_STS_CD = #{jobStatusCode,jdbcType=VARCHAR}
		       ,A.JB_END_DT = #{jobEndDateTime,jdbcType=DATE}
		       ,A.MDFR_ID = 'BatchJob'
		       ,A.MDFY_DT = STATEMENT_TIMESTAMP()
		       ,A.TOT_CNT = #{totalCount,jdbcType=INTEGER}
		 WHERE A.JB_SEQ = #{jobSeq,jdbcType=VARCHAR}

This would give the following error:

org.postgresql.util.PSQLException: ERROR: column "a" of relation "mytable" does not exist

In PostgreSQL, it can be avoided removing the aliases from the SET list, which will not really be needed anyway:

sql

		UPDATE MYTABLE A
		   SET JB_STS_CD = #{jobStatusCode,jdbcType=VARCHAR}
		       ,JB_END_DT = #{jobEndDateTime,jdbcType=DATE}
		       ,MDFR_ID = 'BatchJob'
		       ,MDFY_DT = STATEMENT_TIMESTAMP()
		       ,TOT_CNT = #{totalCount,jdbcType=INTEGER}
		 WHERE A.JB_SEQ = #{jobSeq,jdbcType=VARCHAR}

DECODE() method

In oracle the method DECODE can be used to map a value into more detailed values, like below:

decode(a.value, 'P', 'Payment', 'C', 'Cancel')

In PostgreSQL, instead we would need to use the CASE statement: https://www.postgresql.org/docs/9.4/functions-conditional.html

(CASE a.value WHEN 'P' THEN 'Payment' WHEN 'C' THEN 'Cancel' END)

NVL() method

In Oracle, NVL(A,B) returns A if it is not null, otherwise it returns B

NVL(a.value,'my_default_value')

We can replace this in PostgreSQL with COALESCE, which does the same function (for any set of arguments, it returns the first one that's not null)

COALESCE(a.value,'my_default_value')

LPAD() method

The LPAD() method has a stricter syntax in PostgreSQL than in Oracle. You might find the following error:

ERROR: function lpad(bigint, integer, unknown) does not exist

This can be fixed by explicitly converting the bigint into a varchar, which matches the expected types. See below example:

sql

#In  Oracle 
SELECT LPAD(VALUE,8,'0') AS STAT_NUM FROM ( .... ) WHERE ( .... )

#In Postgres
SELECT LPAD(VALUE::VARCHAR(255),8,'0') FROM  ( .... ) WHERE ( .... )

TO_DATE() method

Remember that in PostgreSQL, the DATE datatype is not holding the time, only the day. In many cases we might have to replace TO_DATE() with TO_TIMESTAMP().

DATE addition operations

In Oracle it's possible to perform additions to a DATE type, each unit (+1) incremented would increment the date by one day. So it's possible to to do "TO_RGT_DD + USG_DAYS" ...where "TO_RGT_DD" is a date and "USG_DAYS" is an integer scalar.

https://www.postgresql.org/docs/current/functions-datetime.html

Attempting to do the same in PostgreSQL would result in the following error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone + bigint

In PostgreSQL the following construct can be used instead of TO_RGT_DD + USG_DAYS:

sql

TO_RGT_DD + USG_DAYS * INTERVAL '1 day'

In some cases Oracle we have used ( adding 23:59:59 to date ) 

CNTY.FRM_RGT_DT + CMPI.USG_DAYS + 86399/86400

In PostgreSQL following can be used 

CNTY.FRM_RGT_DT + CMPI.USG_DAYS * INTERVAL '1 day' + (INTERVAL '1 day' - INTERVAL '1 second')

MERGE statement

In Oracle the MERGE statement allows executing actions (such as insert or update) on the rows on a table that match (or that don't match) values selected from a subquery.

Depending on what actions it's actually executing, this statement can be converted to PostgreSQL differently: MERGE that updates matching rows and inserts rows not matching

This is the most common purpose for the MERGE statement in Oracle. For example:

sql

MERGE INTO target_table tgt
  USING source_table src
  ON (tgt.id = src.id)
  WHEN MATCHED THEN
    UPDATE SET tgt.name = src.name, tgt.value = src.value
  WHEN NOT MATCHED THEN
    INSERT (id, name, value)
    VALUES (src.id, src.name, src.value);

Since PostgreSQL 9.5, this can be done using an "INSERT ... ON CONFLICT" statement (also known as "upsert").

The example above could be expressed in PostgreSQL the following way:

sql

INSERT INTO target_table (id, name, value)
  SELECT id, name, value FROM source_table
    ON CONFLICT (id)
    DO UPDATE
      SET name = EXCLUDED.name, value = EXCLUDED.value;

MERGE with no INSERT (only UPDATE)

In some cases, the MERGE statement might not have a "WHEN NOT MATCHED THEN INSERT" section. In such a case, instead of using an INSERT, we can directly replace it with an UPDATE statement. For Example:

sql

MERGE INTO target_table tgt
  USING source_table src
  ON (tgt.id = src.id)
  WHEN MATCHED THEN
    UPDATE SET tgt.name = src.name, tgt.value = src.value;

Can be made into a PostgreSQL UPDATE statement like below:

sql

UPDATE target_table
  SET name = src.name, value = src.value
  FROM source_table src
  WHERE target_table.id = src.id;

ROWNUM

In Oracle SQL, ROWNUM is a pseudocolumn which indicates the row number in a result set retrieved by a SQL query. ROWNUM in WHERE clause

If ROWNUM is used in the WHERE clause from Oracle to limit the number of results to the first 5 (with WHERE ROWNUM < 6) or the to the first one (with WHERE ROWNUM = 1) we can replace it in PostgreSQL using the “LIMIT” clause:

sql

#In  Oracle 
SELECT * FROM MYTABLE  WHERE ROWNUM < 6;

#In Postgres we can use Limit 
SELECT * FROM MYTABLE LIMIT 5;

ROWNUM in other places

If ROWNUM is used in other places such as inside an expresion for the fields in a SELECT statement. Then we can use the PostgreSQL equivalent method "ROW_NUMBER() OVER()"

sql

#In  Oracle 
SELECT #{statNum}||ROWNUM AS STAT_NUM FROM ( .... ) WHERE ( .... )

#In Postgres
SELECT #{statNum}||ROW_NUMBER() AS STAT_NUM FROM  ( .... ) WHERE ( .... )

Next value of a Sequence

In Oracle to return the current value and increment the counter: sequence_name.nextval; Possible usage in a select statement: select sequence_name.nextval from dual;

In Postgresql we can use

To return the current value and increment the counter: nextval(‘sequence_name’); Possible usage in a select statement select nextval(‘sequence_name’);

FROM_TZ() method

In Oracle, FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.

In PostgreSQL to convert a timestamp value and a time zone , you use the timezone(zone, timestamp) function.

For Example in Oracle :

sql

SELECT (FROM_TZ(
    TO_TIMESTAMP(
            CONCAT(
                TO_CHAR(SYSTIMESTAMP AT TIME ZONE A.tzone, 'YYYY-MM-DD'),
                ' 23:59:59'
            ),
            'YYYY-MM-DD HH24:MI:SS'
    ),
    A.tzone)
    AT TIME ZONE '00:00' + A.extradays
)
FROM mytable A;

Example In PostgreSQL :

sql

SELECT (timezone(A.tzone, TO_TIMESTAMP(
         CONCAT(
             TO_CHAR(STATEMENT_TIMESTAMP()::timestamp AT TIME ZONE A.tzone, 'YYYY-MM-DD'),
             ' 23:59:59'
         ),
         'YYYY-MM-DD HH24:MI:SS'
    )) at time zone '00:00' + A.extradays * INTERVAL '1 day')
	
FROM mytable A;

Note that the STATEMENT_TIMESTAMP() is casted to timestamp without timezone using ::timestamp. This is needed for it to be properly converted with the "AT TIME ZONE" statement. For details on the reasons this was done see .

Subqueries inside FROM must have an Alias

When using subqueries inside FROM, PostgreSQL requires that they are always named with an alias.

While in Oracle that would not be necessary, in PSQL we would get: ERROR: subquery in FROM must have aliases

Example :

In Oracle :

sql

SELECT * FROM (SELECT uid, COUNT(*) AS count FROM my_table GROUP BY 1 ORDER BY 2) LIMIT 1;

PostgreSQL :

sql

SELECT * FROM (SELECT uid, COUNT(*) AS count FROM my_table GROUP BY 1 ORDER BY 2) AS x LIMIT 1;

RANDOM

The DBMS_RANDOM package provides a built-in random number generator. DBMS_RANDOM.VALUE produces numbers in (0,1).

PostgreSQL provides the random() function that returns a random number between 0 and 1. https://www.postgresqltutorial.com/postgresql-random-range/

Hierarchical Queries ( START WITH ... CONNECT BY ... )

In Oracle, Hierarchical Queries are a feature that allows specifying a relationship between rows that defines each row as children of a parent row and defines a level based on this.

For example, the query below:

sql

SELECT employee_id,
       manager_id,
       LEVEL
  FROM employees
  START WITH manager_id IS NULL
  CONNECT BY PRIOR employee_id = manager_id
  ORDER SIBLINGS BY employee_id;

Can be converted to Postgress as following, using WITH RECURSIVE to introduce a self-referencing subquery (CTE):

sql

WITH RECURSIVE CTE AS (
  SELECT e.employee_id,
         e.manager_id,
         1 AS level
  FROM employees as e
  WHERE manager_id IS NULL
  
  UNION ALL
  
  SELECT lower.employee_id,
         lower.manager_id,
         s.level + 1
  FROM CTE as higher
  INNER JOIN employees as lower ON higher.employee_id = lower.manager_id
)
SELECT employee_id, manager_id, level
FROM CTE
ORDER BY level, employee_id;

Note that the CTE subquery is a union where:

In the 1st part of the union FROM employees WHERE ... is equivalent to the Oracle command “START WITH” and would retrieve the first parent row to start the hierarchy
In the 2nd part of the union FROM CTE AS higher INNER JOIN employees AS lower ON ... is equivalent to the Oracle command CONNECT BY and it will define the recursive relationship that will link the parent (higher) with its child (lower)

Sort aggregates with KEEP (DENSE_RANK LAST ORDER BY VER_NUM)

See below oracle query:

sql

SELECT employee_id,
       MAX(bonus) AS max_bonus,
       MAX(year) KEEP (DENSE_RANK LAST ORDER BY bonus) AS year_max_bonus
FROM employee_bonus
GROUP BY employee_id;

In this Oracle query:

MAX(bonus): Finds the maximum bonus amount for each employee_id.
MAX(year) KEEP (DENSE_RANK LAST ORDER BY bonus): This finds the year associated with the maximum bonus for each employee. It uses the DENSE_RANK LAST ORDER BY clause to determine which year had the highest bonus and keeps that year.

PostgreSQL doesn't support the KEEP (DENSE_RANK LAST ORDER BY ...) clause directly. However, you can achieve the same result using a different approach, typically involving an array function or a subquery with ordering and limiting.

sql

SELECT employee_id,
       max_bonus,
       (ARRAY_AGG(year ORDER BY bonus DESC))[1] AS year_max_bonus
FROM (
  SELECT employee_id, year, bonus,
         MAX(bonus) OVER (PARTITION BY employee_id) AS max_bonus
  FROM employee_bonus
) AS subquery
WHERE bonus = max_bonus
GROUP BY employee_id, max_bonus;

Here’s how the PostgreSQL query works:

Subquery Calculation: First, calculate the maximum bonus for each employee using the window function MAX(bonus) OVER (PARTITION BY employee_id).
Array Aggregation: Use ARRAY_AGG(year ORDER BY bonus DESC) to create an array of years sorted by descending bonus amounts. The first element of this array [1] will be the year where the employee received the maximum bonus.
Filtering: The WHERE bonus = max_bonus condition ensures that only rows with the maximum bonus are included when forming the array.

This PostgreSQL query effectively mimics the Oracle behavior of KEEP (DENSE_RANK LAST ORDER BY ...) by using array functions and window functions to determine both the maximum bonus and the associated year.

WM_CONTACT

Oracle's WM_CONTACT function is used to aggregate data from a number of rows into a single row, it cross-tabulates a comma delimited list. Its not available in the PostgreSQL.

We can replace this in PostgreSQL STRING_AGG function, input values concatenated into a string, separated by delimiter.

Syntax is string_agg(expression, delimiter)

For Example :

sql

-- In Oracle
SELECT  TO_CHAR(WM_CONCAT(A.field)) AS fieldlist
    FROM mytable A;

-- In Postgres
SELECT  STRING_AGG(A.field,',') AS fieldlist
    FROM mytable A;

INSTR

The Oracle INSTR() function searches for a substring in a string and returns the position of the substring in a string.

org.postgresql.util.PSQLException: ERROR: function instr(character varying, character varying) does not exist

We can replace this function in PostgreSQL with STRPOS() function, This function is used to find the position, from where the substring is being matched within the string.

Syntax : strpos(string, substring)

jdbcType Conversion

In Oracle #{dcRt, jdbcType=VARCHAR} type can be stored as numeric type in oracle database.

But in Postgres it give below error

org.postgresql.util.PSQLException: ERROR: column "dc_rt" is of type bigint but expression is of type character varying

This can be fixed by explicitly converting the varchar into a numeric

Example :

#{dcRt, jdbcType=VARCHAR}::NUMERIC

REGEXP_SUBSTR

In oracle The REGEXP_SUBSTR function use used to return the substring that matches a regular expression within a string.

We can use PostgreSQL regexp_matches function

The regexp_matches function returns a text array of all of the captured sub strings resulting from matching a POSIX regular expression pattern. And to convert arry to string we can use array_to_string funvtion.

Example :

sql

--- In Oracle :
SELECT REGEXP_SUBSTR(A.field, '[-+]')
    FROM mytable A;

--- In Postgres:
SELECT array_to_string(regexp_matches(A.field,'[-+]'), '')
    FROM mytable A;

REGEXP_LIKE

The equivalent to Oracle's REGEXP_LIKE in PostgreSQL is TEXTREGEXEQ, or using the ~ operator:

sql

-- In Oracle:
SELECT column FROM mytable WHERE REGEXP_LIKE(column, '^00');

-- In PostgreSQL:
SELECT column FROM mytable WHERE TEXTREGEXEQ(column, '^00');
-- or 
SELECT column FROM mytable WHERE (column ~ '^00');

Outer Joins (+)

The (+) notation in Oracle SQL indicates an outer join, specifically, a left outer join when used in Oracle's traditional syntax. This notation allows the query to include rows from the left table (specified before the (+)) even if there is no matching row in the right table (specified after the (+)).

Example:

In Oracle:

sql

SELECT a.employee_id, a.employee_name, T2.department_name
  FROM employees a, departments T2
  WHERE a.department_id = T2.department_id(+);

In Postgres:

sql

SELECT a.employee_id, a.employee_name, T2.department_name
  FROM employees a
  LEFT JOIN departments T2 ON a.department_id = T2.department_id;

In the PGSQL query, LEFT JOIN departments T2 ON a.department_id = T2.department_id specifies a left outer join, which includes all records from the employees table and the matched records from the departments table. If there is no match, the result is NULL on the side of the departments.

Both of these queries will provide a list of all employees along with their department names. If an employee does not belong to any department, the employee's information will still be included in the result set, but the department_name will appear as NULL.

These examples show the transition from Oracle's proprietary join syntax to the ANSI-standard SQL join syntax, which is also used by PostgreSQL and most other modern relational databases.

TO_NUMBER

In Oracle TO_NUMBER converts expr to a value of NUMBER datatype. In PostgreSQL instead of TO_NUMBER function we can do typecast using ::INTEGER

Will get : ERROR: function to_number(text) does not exist

Example :

sql

-- In Oracle:
SELECT TO_NUMBER(SUBSTR(MAX(A.column),3)
    FROM  mytable A

-- In PostgreSQL:
SELECT (SUBSTR(MAX(A.column),3)::INTEGER)
	FROM  mytable A

Update with alias before target column name

The name of a column in table. The column name can be qualified with a sub field name . Do not include the table's name or alias name in the specification of a target column.

For example below alias name A should not be used for target column SET column.

sql

--- In Oracle
UPDATE mytable A
    SET A.value1 = 'my_value',
        A.mdfy_dt = STATEMENT_TIMESTAMP(),
    WHERE A.testcol = 'test_value'

-- In PostgreSQL
UPDATE mytable A
    SET value1 = 'my_value',
        mdfy_dt = STATEMENT_TIMESTAMP(),
    WHERE A.testcol = 'test_value'

MINUS

Oracle uses MINUS operator to combine two queries substracting the values from one in the other: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

PostgresSQL uses the more standard SQL operator EXCEPT: https://www.postgresql.org/docs/9.4/queries-union.html

sql

-- In Oracle
SELECT employee_id
FROM current_employees
MINUS
SELECT employee_id
FROM former_employees;


-- In PostgreSQL
SELECT employee_id
FROM current_employees
EXCEPT
SELECT employee_id
FROM former_employees;

Both operators filter out duplicates in each set before the operation is performed, effectively working on distinct sets of rows from each table. If you want to maintain duplicates, you might need to consider variations such as EXCEPT ALL in PostgreSQL, which Oracle does not support directly but can be mimicked with additional SQL logic.

Here’s how you can use EXCEPT ALL in PostgreSQL to consider duplicates:

sql

SELECT employee_id
FROM current_employees
EXCEPT ALL
SELECT employee_id
FROM former_employees;

This would subtract each occurrence of employee_id found in former_employees from current_employees, not just remove all matching instances.

Using MINUS and EXCEPT provides a very readable and effective way to compare datasets and is commonly used in data analysis, migrations, or synchronization tasks where differences between datasets need to be identified.

add_months:

ADD_MONTHS() function returns a date with a given number of months added or subtract (date plus or minus integer months) in oracle. PostgreSQL add_months wont support we can use below syntax for the same instead.

sql

-- In Oracle
select * from MYTABLE where HIST_DT between add_months(STATEMENT_TIMESTAMP(),-1) and   STATEMENT_TIMESTAMP(); 

-- In PostgreSQL
select * from MYTABLE where HIST_DT between STATEMENT_TIMESTAMP() - INTERVAL '1 month' and STATEMENT_TIMESTAMP();

Tools can be used to migrate from oracle to PostgreSQL

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that can load into your PostgreSQL database.

Install Ora2PG And dump data from Oracle DB to PostgreSQL

Download and Install Perl on windows (from, for example http://strawberryperl.com/)
Oracle tables structure should exists prior running the tool
Oracle client "Administrator" pack to be installed (from https://www.oracle.com/database/technologies/112010-win64soft.html win64_11gR2_client)
Download ora2pg software (from https://sourceforge.net/projects/ora2pg/) and extract to a known location on windows
Go to unzipped location of ora2pg and run below commands in the Command Prompt

 cd <location of Ora2Pg>\ora2pg-20.0
 perl Makefile.PL
 gmake && gmake install

Set the environment variables:

Set ORACLE_HOME=<ORACLE_CLIENT_LOCATION>

Set LD_LIBRARY_PATH=<ORACLE_CLIENT_LOCATION>\lib

Install DBD::Oracle libraries

perl -MCPAN -e "install DBD::Oracle"

Copy the template config file "ora2pg_dist.conf " to original file name "ora2pg.conf" and edit with the details of database and schema etc.

Please see https://ora2pg.darold.net/documentation.html for details on the format for the configuration file to use.

Migrating from Oracle SQL to PostgreSQL

Differences to consider in the migration Oracle -> PGSQL ​

DataTypes ​

Synonyms ​

Table DUAL ​

SYSDATE ​

SYSTIMESTAMP ​

Empty String and NULL ​

The DELETE Statement ​

TRANSACTIONS ​

DECODE() method ​

NVL() method ​

LPAD() method ​

TO_DATE() method ​

DATE addition operations ​

MERGE statement ​

MERGE with no INSERT (only UPDATE) ​

ROWNUM ​

Next value of a Sequence ​

FROM_TZ() method ​

Subqueries inside FROM must have an Alias ​

RANDOM ​

Hierarchical Queries ( START WITH ... CONNECT BY ... ) ​

Sort aggregates with KEEP (DENSE_RANK LAST ORDER BY VER_NUM) ​

WM_CONTACT ​

INSTR ​

jdbcType Conversion ​

REGEXP_SUBSTR ​

REGEXP_LIKE ​

Outer Joins (+) ​

TO_NUMBER ​

Update with alias before target column name ​

MINUS ​

add_months: ​

Tools can be used to migrate from oracle to PostgreSQL ​

Differences to consider in the migration Oracle -> PGSQL

DataTypes

Synonyms

Table DUAL

SYSDATE

SYSTIMESTAMP

Empty String and NULL

The DELETE Statement

TRANSACTIONS

DECODE() method

NVL() method

LPAD() method

TO_DATE() method

DATE addition operations

MERGE statement

MERGE with no INSERT (only UPDATE)

ROWNUM

Next value of a Sequence

FROM_TZ() method

Subqueries inside FROM must have an Alias

RANDOM

Hierarchical Queries ( START WITH ... CONNECT BY ... )

Sort aggregates with KEEP (DENSE_RANK LAST ORDER BY VER_NUM)

WM_CONTACT

INSTR

jdbcType Conversion

REGEXP_SUBSTR

REGEXP_LIKE

Outer Joins (+)

TO_NUMBER

Update with alias before target column name

MINUS

add_months:

Tools can be used to migrate from oracle to PostgreSQL