This article will continue to be updated as we find additional information that we think is useful. If you have information or sample queries to share, or if you'd like to request help on addition SQL differences, submit a ticket to Biotics Help Desk either by emailing biotics@natureserve.org or filling in the online new ticket form.
General
| Oracle/B5 | Postgres/B6 | Notes |
| and ROWNUM < 5 | LIMIT 5 | use LIMIT at very end of query |
| || (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 tableA left join tableB on ... " | |
| 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 |
| CLOB | no such thing! | Hurray! |
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 / SYSTIMESTAMP | LOCALTIMESTAMP or CURRENT_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() | CASE statement | |
| NVL() | COALESCE() (or CASE statement) | 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) |
Views
| Oracle/B5 | Postgres/B6 | Notes |
| ORDER BY | avoid | Records may not sort correctly. Sort when querying the view instead. |
Other lexical differences
| Oracle/B5 | Postgres/B6 | Notes |
| mixed case allowed in usernames, fieldnames, etc. | mixed case names must be enclosed in quotes when used in SQL | Postgres expects lowercase |
| "table or view" in error message | "relation" | e.g., 'relation "dual" does not exist' |