This article will continue to be updated as we find additional information that we think is useful.
General
| Oracle/B5 | Postgres/B6 | Notes |
| and ROWNUM < 5 | LIMIT 5 |
|
| ||(CONCATENATE bars) if any values may be null | CONCAT_WS() | Unlike Oracle, the || operator in PostgreSQL returns null if any of the values being concatenated are null. |
| OUTER JOINS (+) syntax | not available, use "select ... from a left join b on ... " | The conversion of views, functions, etc. will convert this syntax unless there is a space before or after the + ( + ) |
delimlist order by:
'ORDER BY d_origin_id' | ' ORDER BY d_origin_id' | must have a space before "ORDER" |
CASE statement that ends with
else null end | else '' end | |
| HDMSDEL.(deleted schema) | DEL. |
|
| select 1+2 from dual | select 1+2 | leave off the "from" clause entirely |
Dates
| Oracle/B5 | Postgres/B6 | Notes |
| TO_DATE(date, fmt) | works here too |
|
| TRUNC(date/time [, fmt]) | DATE_TRUNC('day', date/time) | e.g., select DATE_TRUNC('year','2026-09-30'::date) |
| SYSDATE | LOCALTIMESTAMPorCURRENT_TIMESTAMP |
|
| TRUNC(CURRENT_DATE)or TRUNC(SYSDATE) | CURRENT_DATE | CURRENT_DATE does not include time in Postgres, but does in Oracle |
Functions
| Oracle/B5 | Postgres/B6 | Notes |
| DELIMLIST() | STRING_AGG() | convert as possible; delimlist is still available, but less desirable. See DELIMLIST replacement. |
| DECODE() | CASEstatement | |
| NVL() | COALESCE()(orCASEstatement) | COALESCE returns the first non-NULL value from a list of expressions. |
| GETNEXTSEQ() | leave primary key out of insert statement |
|
| INSTR(eg.g_rank,'T') | POSITION('T' in eg.g_rank) |
|
Other lexical differences
| Oracle/B5 | Postgres/B6 | Notes |
| mixed case allowed in usernames, fieldnames, etc. | any mixed case names must be enclosed in quotes | Postgres expects lowercase |
| "table or view" | "relation" | e.g., 'relation "dual" does not exist' |
