When We use PostgreSQL as a choice for subtitude/migrate Oracle, we will face there are difference syntax. Oracle and PostgreSQL both conform to standard SQL. However, they contain several extensions and implementation details that differentiate one from the other. The most important differences are listed in this table below.
| ORACLE | POSTGRESQL | Explain |
|---|---|---|
| select sysdate from dual | select ‘now’::datetime | There is no “dual” table Unlike other RDBMS, PostgreSQL allows a “select” without the ”from” clause. This use does not affect portability because the syntax to get current time is already DBMS specific |
| CREATE SEQUENCE seqname [ INCREMENT BY integer ] [ MINVALUE integer ] [ MAXVALUE integer ] [ START WITH integer ] [ CACHE integer ] [ CYCLE | NOCYCLE ] To return the current value and increment the counter: sequence_name.nextval; Possible usage in a select statement: select sequence_name.nextval from dual; | CREATE SEQUENCE seqname [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ] To return the current value and increment the counter: nextval(‘sequence_name’); Possible usage in a select statement select nextval(‘sequence_name’); | If you don’t specify MAXVALUE, then the maximum value is 2147483647 for ascending sequences. Note that unlike other RDBMS, PostgreSQL allows a select without the ‘from’ clause. This use does not affect portability because the sequence syntax is already DBMS specific |
| SELECT product_id, DECODE (warehouse_id, 1, ’Southlake’, 2, ’San Francisco’, 3, ’New Jersey’, 4, ’Seattle’, ’Non-domestic’) quantity_on_hand FROM inventories | SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test | Converting DECODE to CASE WHEN |
| select employeeid, NVL(hire_date, sysdate) from employee where employeeid = 10; | select employeeid, coalesce(hire_date, 'now'::datetime) from employee where employeeid = 10; | Oracle also has a “coalesce” function that is a generalization of the commonly used NVL function |
Consulting