This is a document Template
Please refer SQL_Standards1.htm :

Although the most of the database vendors adhere to ANSI - SQL standards; also have their own flavors added to SQL language.

SQL Querying:
PostgreSQL and MySQL : 
: :SELECT ename e, job empjob, deptno FROM emp limit 5 
Oracle 10. and 11.2 g
:SELECT ename e, job empjob, deptno, sal * 0.025 esal FROM emp where rownum < 6

This document introduces a basic know how of SQL Data Types.

Oracle emp Table:

PostgreSQL: EMP Table


Data Type: The programming languages like c/c++ and java were used to create the databases like oracle and PostgreSQL , and therefore columns in a table also support derivatives of  similar data types.

As you noticed in the above two images that data type   integer (o,d), where 'o' stands for over all number and 'd' stands of decimal point , was declared as NUMBER(o,d)  data type in Oracle and 'numeric (o,d) in PostgreSQL.

The variable character data type, was declared as  VARCHAR2(n) in Oracle, and character varying (n) in PostgreSQL 9.2 and up.

Common Built in data-types in a Database: PGS (PostgreSQL)
Name/Uses Feature Special Notes:
stores large binary objects up to 4gb LONG RAW,RAW(N)
BOOLEAN True/False Oracle does not have boolean **
CHAR(n) Fixed length character data  
CLOB Long character string LONG VARCHAR
DATE Y,M,D,HR,MIN,SEC No fractional second or time zone
INTEGER Stores whole number/No fraction  
LONG stores value up to 2gb, only one column per table, backward compatibility don't create tables with LONG, use BLOB, CLOB
NUMBER stores up to 40 digits sub types are float
NUMBER(o,d) unlimited number with exact precision (money matters) of decimal points numeric(n) in postgresql
REAL float with binary precision single for 32 bit 4 bytes, double for 64bit 8 bytes
TIMESTAMP extension of DATE, stores Y,M,D hr:min:sec has fractional second, but no time zone
TIMESTAMP with TIMEZONE extension of DATE, stores Y,M,D hr:min:sec has both time-zone and fractional second
VARCHAR2(n) variable-length of a character string, with n=max character varying(n) in PGSQL
**Serial types: Stores a sequential INTEGER increment at the succeeding row insertions.
**Ref: postgresql 9.3 pdf  data types real and double precision are inexact, which means that some values could be stored with approximation.

Oracle :

  • In Oracle-SQL there is no data type boolean. It can, however, be simulated by using either char(1) or number(1).


PostgreSQL : Please refer postgresql-9.3-A4.pdf Ch-8


Pseudo columns:


SELECT ename e, job empjob, deptno, sal * 0.025 esal FROM emp where rownum < 6

PostgreSQL (9.2 and up)


test Scripts : SELECT ename e, job empjob, deptno, sal * 0.025 esal FROM emp limit 5