1.5 SQL Differences – Oracle vs PostgreSQL
Both Oracle and PostgreSQL database are ANSI compliant and have their version of SQL with many features. So, it’s essential to understand the basic SQL differences in both these databases.
Oracle DUAL Table
- DUAL is a table automatically created by Oracle Database along with the data dictionary.
- It has one column, “DUMMY”, defined as VARCHAR2(1), and contains one row with a value “X”. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement.
SELECT * FROM DUAL; SELECT 1+20*3 as res FROM DUAL;
DUAL table in Oracle is very useful in creating on the fly tables with data or inserting dummy data to any table.
--Creating on the fly table with 10 rows CREATE TABLE Test_dummy AS SELECT LEVEL id, 'Name-'||LEVEL Tname FROM DUAL WHERE LEVEL <= 10;
PostgreSQL does not provide DUAL table as FROM clause is not mandatory in a SELECT statement in all situations. The FROM clause specifies one or more source tables for the SELECT.
The FROM clause if present can contain the following elements:
- The name of an existing table or view
- Can contain joins of two or more tables/views
- A inline view can appear in the FROM clause
- A WITH query
- Function calls can appear in the FROM clause
To evaluate an expression in PostgreSQL database, you write the SELECT statement without FROM clause.
SELECT 1+20*3 as res;
PostgreSQL provides different functions like generate_series which can produce sequence of values. You can use generate_series for creating on the fly tables with data or inserting dummy data to any table.
--Creating on the fly table with 10 rows CREATE TABLE Test_dummy AS SELECT seq id, 'Name-'||Seq Tname FROM generate_series(1, 10) seq;
There are several methods for invoking a function:
- Include a function call inside SELECT projection list.
- Call the function from SQL using a subquery.
- Call a function within an assignment operator.
- Call a function from inside an “IF-ELSE” conditional expression.
All these function call methods are valid in both Oracle and PostgreSQL. However PostgreSQL also provides function calling in query FROM clause.
Functions in Oracle can not be referred in FROM clause of a query.
--Functions can not be referred in FROM clause SELECT * FROM TO_CHAR(now(), 'YYYY-MON-DD') currdate;
Functions in PostgreSQL can be called directly, referred inside the other blocks or can appear in a query FROM clause.
--Functions can be referred in FROM clause SELECT * FROM TO_CHAR(now(), 'YYYY-MON-DD') f;