Database Data Type Matrix

Note: This page is a work-in-progress and is not comprehensive or 100% accurate.

Oracle PostgreSQL

Numeric types
Integer (2 bytes) ~ NUMBER(5,0) smallint
Integer (4 bytes) ~ NUMBER(10,0) integer
Integer (8 bytes) ~ NUMBER(20,0) bigint
Fixed-point decimal (defined precision and scale) NUMBER(precision, scale=0) numeric(precision, scale=0)
Fixed-point decimal (arbitrary precision and scale) NUMBER numeric

without any precision or scale [specified] ... numeric values of any precision and scale can be stored, up to the implementation limit on precision —PostgreSQL 9.3 Docs

Floating-point decimal (defined precision, arbitrary scale) FLOAT(precision=126 binary digits) No equivalent
Floating-point binary (32-bit) BINARY_FLOAT ~ real
Floating-point binary (64-bit) BINARY_DOUBLE ~ double precision

Character types
Variable-length text VARCHAR2(size)

size specified as n BYTE or n CHAR

 character varying(n), varchar(n)

n = characters
Fixed-length, padded CHAR(size=1)

size specified as n BYTE or n CHAR

 character(n), char(n)

n = characters. padded with spaces
Variable-length "unlimited" CLOB text

Boolean
Boolean true/false

No exact equivalent

NUMBER(1,0) or CHAR(1) with Y/N 		boolean

Date / Time types
date and time (with timezone) TIMESTAMP WITH TIME ZONE timestamp with time zone
date and time (no timezone) TIMESTAMP timestamp [without time zone]
date and time (with timezone, automatic conversion) TIMESTAMP WITH LOCAL TIME ZONE No equivalent
date only DATE date
time of day (no date)

No exact equivalent

INTERVAL DAY(0) TO SECOND

— via stackoverflow

 time [without time zone]
time of day (no date, with time zone) No equivalent time with time zone
time interval

INTERVAL YEAR(year_precision) TO MONTH

INTERVAL DAY(day_precision) TO SECOND

 interval

Binary data
variable-length binary string BLOB bytea

Monetary types
fixed-precision currency NUMBER(precision, scale) money

precision depends on lc_monetary setting

Enumerated types
Enumeration

Not supported. Use CHAR or VARCHAR2 with a CHECK constraint.

e.g.columnname VARCHAR2(length) CHECK (columnname IN ('value1', 'value2', ...))

Type must first be defined:

CREATE TYPE typename AS ENUM ('value1', 'value2', ...)