PostgreSQL database :
DDL : creating table DEPT and EMP:
SQL Script:
--DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMERIC(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR(14) ,
LOC VARCHAR(13) ) ;
--DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMERIC(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATE,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO NUMERIC(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
varchar2 of Oracle runs as character varying.
| Data-Types | Oracle | PostgreSQL | SQL-Ref |
| Numeric | |||
| smallint | synonym of number | 2 bytes | SQL 89 |
| integer (ANSI) | synonym Number | 4 bytes | SQL92 |
| bigint | none | 8 bytes , 18 digit PostgreSQL specific | |
| decimal(p,s) | DEC, SUBTYPE OF NUMBER(38) | 131072 digits before to 16383 digits after, decimal | SQL89 |
| numeric(p,s) | NUMBER(p,s) | 131072 digits before to 16383 digits after, decimal | SQL99 |
| real | real , float(64) | real 4 bytes | SQL89 |
| double precision | decimal precision, float(126) | 8 bytes | SQL99 |
| serial | na | 4 byte auto increment integer | |
| bigserial | 8 byte auto increment integer | ||
| BINARY _INTEGER |
slower version of PLS_INTEGER | ||
| pls_integer | pl/sql only | ||
| boolean | n/a use with number or Char | ||
| RAW | MUST SPECIFY A SIZE, max 2000 bytes | ||
| LONG RAW | DEPRECATED | ||
| ROWIS | hexa decimal 10 bytes, DBMS_ROWID | ||
| UROWID | Hex string in indexed table | ||
| CLOB | 8 TO 128 tb | ||