SQL Dialects Reference/Data structure definition/Data types/Numeric types

From Wikibooks, open books for an open world
Jump to navigation Jump to search

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Integer (1 byte) [1] N/A N/A tinyint
integer1
N/A tinyint (always unsigned) TINYINT TINYINT NUMBER(1) "char" (including quotation marks) INTEGER[2] TINYINT
? Integer (2 bytes) [1] SMALLINT SMALLINT smallint
integer2
SMALLINT smallint SMALLINT SMALLINT NUMBER(2) SMALLINT
INT2
INTEGER[2] SMALLINT
? Integer (3 bytes) [1] N/A N/A N/A N/A N/A MEDIUMINT N/A NUMBER(3) N/A INTEGER[2] N/A
? Integer (4 bytes) [1] INTEGER INTEGER integer
integer4
INT
INTEGER
int INT INT NUMBER(4) INT
INTEGER
INT4
INTEGER[2] INT
INTEGER
? Integer (8 bytes) [1] BIGINT BIGINT bigint
integer8
BIGINT bigint BIGINT BIGINT INTEGER BIGINT
INT8
INTEGER[2] BIGINT
? Float (single precision, 4 bytes) [3] REAL FLOAT float4
real
float(23)
FLOAT
REAL
FLOAT(n), n≤24
real
float(24)
FLOAT REAL BINARY_FLOAT REAL
FLOAT(24)
N/A REAL
? Float (double precision, 8 bytes) [3] DOUBLE
FLOAT
DOUBLE PRECISION float
float8
double precision
float(53)
DOUBLE
DOUBLE PRECISION
FLOAT(n), n>24
float
float(53)
DOUBLE FLOAT
DOUBLE
BINARY_DOUBLE DOUBLE PRECISION
FLOAT(53)
REAL[2] FLOAT
? Fixed precision (p) and scale (s) number [1] DECIMAL(p, s)
NUMERIC(p, s)
DECIMAL(p, s)
NUMERIC(p, s)
DECIMAL(p, s)
NUMERIC(p, s)
DECIMAL(p, s)
DEC(p,s)
NUMERIC(p, s)
NUMBER(p,s)
decimal(p, s)
numeric(p, s)
DECIMAL(p, s)
NUMERIC(p, s)
DECIMAL(p, s)
NUMERIC(p, s)
NUMBER(p, s)/NUMBER(p)/NUMBER
DECIMAL(p, s)/DECIMAL(p)/DECIMAL
NUMERIC(p, s)/NUMERIC(p)/NUMERIC
FLOAT(p)
DECIMAL(p, s)
NUMERIC(p, s)
N/A[2] DECIMAL(p, s)
NUMERIC(p, s)
? Fixed precision p and s limits no limits p ≤ 31, 0 ≤ s ≤ p p ≤ 18, 0 ≤ s ≤ p p ≤ 31, 0 ≤ s ≤ p p ≤ 30, 0 ≤ s ≤ p, s ≤ 10, p-s ≤ 20 p ≤ 38, 0 ≤ s ≤ p p ≤ 65, 0 ≤ 30 ≤ s ≤ p p ≤ 18, 0 ≤ s ≤ p p ≤ 38, -84 ≤ s ≤ 127 (for negative scale the least significant digit is on the left side of the decimal point) p ≤ 1000, 0 ≤ s ≤ p
? UNSIGNED support No No No No No No Yes No Yes No No No
? Set display width in integer No No No No No No Yes No No No No No
  1. a b c d e f SMALLINT, INTEGER, INT, BIGINT, DECIMAL and NUMERIC. See: Wikibook SQL
  2. a b c d e f g uses dynamic typing; allows any type name
  3. a b FLOAT, REAL, DOUBLE PRECISION. See: Wikibook SQL