1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. 1 Database Migration Basics
  5. 1.6 Most Common SQL Differences

1.6 Most Common SQL Differences

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

OraclePostgreSQL
  • 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;

Function Calling

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.

OraclePostgreSQL

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;
Was this article helpful to you? Yes No

How can we help?