FB

Do you want to know what the SQL data types are in different database vendors? Learn all about them and how they compare in this guide.

This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.

 

What is an SQL Data Type?

Each column in the database must have a data type. This indicates what data can be stored in the column and how it can be used. You specify these when using CREATE TABLE or ALTER TABLE statements.

While you could store all of your data in large text-based columns, it will perform slowly and won’t have the validation that dates or numbers need to have.

There are a few categories of data types that the lists below are split into:

  • Numeric: stores all kinds of numbers, including whole numbers and decimal numbers
  • Character: stores text values
  • Date: stores date and time values
  • Other: all other data types not covered in the categories above

Let’s take a look at the data types available in each database vendor, and see how they compare at the end of this article.

 

Oracle Data Types

Here’s a list of all of the SQL data types available in Oracle. This list is accurate as of Oracle 19c. For more information, refer to the official documentation here.

I’ve also written about all of the Oracle data types, their parameters, and which ones to use in different situations here: Guide to the Oracle Data Types.

 

Numeric Data Types

Data TypeExplanationRange
NUMBER (p, s)Numeric data, with a precision of p and scale of s.

Precision p ranges from 1 to 38, and scale ranges from -84 to 127

– 10^38 +1 through 10^38 – 1
BINARY_FLOAT32-bit, single-precision floating-point numberFrom 1.17549E-38F to 3.40282E+38F
BINARY_DOUBLE64-bit, double-precision floating-point numberFrom 2.22507485850720E-308 to 1.79769313486231E+308
INTEGERSynonym for NUMBER(38)– 10^38 +1 through 10^38 – 1
FLOATTranslated to NUMBER1 to 126 binary digits, and up to 22 bytes
DECIMALTranslated to NUMBER1 to 126 binary digits, and up to 22 bytes
NUMERICSynonym for NUMBER
INTSynonym for NUMBER(38)
SMALLINTSynonym for NUMBER(38)
DOUBLE PRECISIONSynonym for FLOAT(126)
REALSynonym for FLOAT(63)

Note: the BOOLEAN, PLS_INTEGER, and BINARY_INTEGER are only available in PL/SQL and cannot be used for columns in tables, which is why I have not listed them here.

 

Character Data Types

Data TypeExplanationRange
CHAR (s)Fixed-length character with a length of s.2,000 bytes.
NCHAR (s)Fixed length national character with a length of s.2,000 bytes.
VARCHARNot implemented in Oracle.
VARCHAR2 (s)Variable-length character string with a maximum length of s bytes32,767 bytes
NVARCHAR2 (s)Variable-length national character string with a maximum length of s bytes32,767 bytes
LONGVariable-length character string. Larger than VARCHAR2. Deprecated2 GB
RAW (s)Raw binary data of length size32,767 bytes
LONG RAWRaw binary data of variable length. Deprecated2 GB
CHARACTER VARYINGSynonym for VARCHAR2
CHARACTERSynonym for CHAR
BLOBStores large unstructured binary objects8 TB to 128 TB (4 GB -1 * (database block size
CLOBStores large single-byte and multi-byte objects8 TB to 128 TB (4 GB -1 * (database block size
NCLOBStores Unicode data8 TB to 128 TB (4 GB -1 * (database block size

 

Date Data Types

Data TypeExplanationRange
DATEStores date and time values.From Jan 1, 4712 BC to Dec 31 9999 AD
TIMESTAMPA date value that stores time and fractional secondsFrom Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
TIMESTAMP WITH TIME ZONEA timestamp that stores data in the specified timezoneFrom Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
TIMESTAMP WITH LOCAL TIME ZONEA timestamp that stores data in your local timezoneFrom Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
INTERVAL YEAR TO MONTHStores a period of time in years and monthsUp to 2 digits in the year
INTERVAL DAY TO SECONDStores a period of time in days, hours, minutes, and secondsBetween 0 and 9 digits in the day, between 0 and 9 digits fractional seconds

 

Other Data Types

Data TypeExplanationRange
BFILEA file locator that points to a file on the server4GB
ROWIDFixed-length binary data for a database row ID10 bytes
UROWIDUniversal RowID4,000 bytes

 

SQL Server Data Types

Here’s a list of all of the SQL data types available in SQL Server. For more information, refer to the official documentation here.

 

Numeric Data Types

Data TypeExplanationRange
BIGINTInteger value, uses 8 bytes.-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
BITInteger data type, can take 1, 0 or NULL. Often used as a boolean.1, 0, NULL
DECIMAL (p [,s])Decimal number.

Uses precision “p” which is the total number of decimal digits (both left and right of decimal). Default 18.

The scale “s” is the number of digits to the right of the decimal. Default 0.

– 10^38 +1 through 10^38 – 1
INTInteger value, uses 4 bytes-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
INTEGERSynonym for INT
MONEYRepresents monetary values, uses 8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
NUMERIC (p [,s])Same as DECIMAL. See above– 10^38 +1 through 10^38 – 1
SMALLINTInteger value, uses 2 bytes-2^15 (-32,768) to 2^15-1 (32,767)
SMALLMONEYRepresents monetary values, uses 4 bytes-214,748.3648 to 214,748.3647
TINYINTInteger value, uses 1 byte0 to 255
FLOAT(n)Approximate number, with “n” is the number of bits used to store the value. Default is 53.– 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
REALSynonym for FLOAT(24)– 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38
DECSynonym for DECIMAL
DOUBLE PRECISIONSynonym for FLOAT

 

Character Data Types

Data TypeExplanationRange
CHAR (n)Fixed-size string. Parameter “n” is the number of bytes between 1 and 8,000. Unused characters are padded with spaces.Up to 8,000 bytes.
VARCHAR (n)Variable-size string. Parameter “n” is the maximum number of bytes between 1 and 8,000.

If “n” is max, then the value of 2^31-1 is used (2 GB)

2^31-1 (2,147,483,647 or 2GB)
TEXTVariable-length non-Unicode data.2^31-1 (2,147,483,647 or 2GB)
NCHAR (n)Fixed-size string for Unicode data. Parameter “n” is the number of bytes between 1 and 4,000. Unused characters are padded with spaces.Up to 4,000 bytes.
NVARCHAR (n)Variable-size string for Unicode data.

Parameter “n” is the maximum number of bytes between 1 and 4,000.

If “n” is max, then the value of 2^30-1 is used (1 GB)

2^30 – 1 (1,073,741,823 or 1GB) bytes
NTEXTVariable-length Unicode data.2^30 – 1 (1,073,741,823 or 1GB) bytes
BINARY (n)Fixed-length binary data with a length of “n” bytes. “n” can be from 1 to 8,000Up to 8,000 bytes.
VARBINARYVariable-length binary data with a length of “n” bytes.”n” can be from 1 to 8,000. If “n” is max, then the value of 2^31-1 is used (2 GB)2^31-1 (2,147,483,647 or 2GB)
CHARACTER VARYINGSynonym for VARCHAR
BINARY VARYINGSynonym for VARBINARY

 

Date Data Types

Data TypeExplanationRange
DATEDefines and stores a date value0001-01-01 through 9999-12-31
DATETIMEDate and time, with fractional seconds.Date: January 1, 1753, through December 31, 9999

Time: 00:00:00 through 23:59:59.997

DATETIME2 (s)Date and time, with fractional seconds, with a larger range than DATETIME.

Scale “s” is the number of digits for the fraction of seconds

Date: 0001-01-01 through 9999-12-31

Time: 00:00:00 through 23:59:59.9999999

DATETIMEOFFSET( s)Date and time, with a timezone.

Scale “s” is the number of digits for the fraction of seconds

Date: 0001-01-01 through 9999-12-31

Time: 00:00:00 through 23:59:59.9999999

Timezone Offset: -14:00 to +14:00

SMALLDATETIMEDate and time. Seconds are always 0, and no fractional seconds.Date: 1900-01-01 through 2079-06-06

Time: 00:00:00 through 23:59:59

TIME (s)Time of day, without a timezone.

Scale “s” is the number of digits for the fraction of seconds

00:00:00.0000000 through 23:59:59.9999999

Note: it is recommended to use DATETIME2 instead of DATETIME as it aligns with the SQL standard.

 

Other Data Types

Data TypeExplanationRange
IMAGEVariable-length binary data0 through 2^31-1 (2,147,483,647 or 2GB) bytes.
CURSORUsed for variables or stored procedure out parameters to refer to a cursor
ROWVERSIONUsed to store automatically-generated unique binary numbers.8 bytes
HEIRARCHYIDVariable length system data type
UNIQUEIDENTIFIERA 16-byte GUID (Global Unique Identifier)16 bytes
SQL_VARIANTStores values of different SQL Server data types
XMLStores XML data
GEOMETRYA spatial data type used to represent coordinates.
GEOGRAPHYA spatial data type used to represent GPS latitude and longitude coordinates
TABLEStores a temporary set of results.

 

MySQL Data Types

Here’s a list of all of the SQL data types available in MySQL as of version 8.0. For more information, refer to the official documentation here.

 

Numeric Data Types

Data TypeExplanationRange
BIT (n)Bit-value type. Parameter “n” indicates the number of bits per value, from 1 to 64. Default 1.
TINYINT (n) [UNSIGNED] [ZEROFILL]A very small integer. Can be signed or unsigned.Signed: -128 to 127

Unsigned: 0 to 255.

SMALLINT (n) [UNSIGNED] [ZEROFILL]A small integer. Can be signed or unsigned.Signed: -32,768 to 32,767

Unsigned: 0 to 65,535

MEDIUMINT (n) [UNSIGNED] [ZEROFILL]A medium-sized integer. Can be signed or unsigned.Signed: -8,388,608 to 8,388,607

Unsigned: 0 to 16,777,215

INT (n) [UNSIGNED] [ZEROFILL]A normal-sized integer. Can be signed or unsigned.Signed: -2,147,483,648 to 2,147,483,647

Unsigned: 0 to 4,294,967,295

INTEGER (n) [UNSIGNED] [ZEROFILL]A synonym for INT. Can be signed or unsigned.Signed: -2,147,483,648 to 2,147,483,647

Unsigned: 0 to 4,294,967,295

BIGINT (n) [UNSIGNED] [ZEROFILL]A large integer. Can be signed or unsigned.Signed: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Unsigned: 0 to 18,446,744,073,709,551,615

DECIMAL (n [, d])  [UNSIGNED] [ZEROFILL]A number with decimal places. Parameter “n” is the precision or number of digits, and “d” is the number of digits after the decimal point (the scale). Maximum for n is 65 and maximum for d is 30.

(Note the UNSIGNED parameter is deprecated)

65-digit number
DEC (n [,d])Synonym for DECIMAL65-digit number
NUMERIC (n [,d])Synonym for DECIMAL65-digit number
FIXED (n [,d])Synonym for DECIMAL65-digit number
FLOAT (n [,d])A small single-precision floating-point number.

Uses parameters of “n” for number of digits and “d” for number of digits after the decimal place.

Note: the parameters of FLOAT are deprecated as of v8.0.17

-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38
DOUBLE (n [,d])A normal-sized double-precision floating-point number.

Uses parameters of “n” for number of digits and “d” for number of digits after the decimal place.

Note: the parameters of FLOAT are deprecated as of v8.0.17

-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308
DOUBLE PRECISIONSynonym for DOUBLE-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308
REALSynonym for DOUBLE-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308

 

Character Data Types

Data TypeExplanationRange
CHAR (n)A fixed-length string. Right-padded with spaces up to the specified length of “n”Up to 255 bytes
VARCHAR (n)A variable-length string. Thee length parameter of “n” can be from 0 to 65,535.Up to 65,535 bytes.
BINARY (n)Similar to CHAR but stores binary byte strings rather than nonbinary strings. Parameter “n” is the number of bytes.
VARBINARY (n)Similar to VARCHAR but stores binary byte strings rather than nonbinary strings. Parameter “n” is the number of bytes.
BLOB (n)A BLOB column that can store a value up to “n” bytes.65,535 bytes
TINYBLOBA BLOB column with a smaller maximum length255 bytes
TEXT (n)A text column, and parameter “n” is the maximum number of bytes.65,535 bytes
TINYTEXTA text column with a smaller maximum length255 bytes
MEDIUMBLOBA BLOB with a higher maximum length than BLOB16,777,215 (2^24 − 1) bytes
MEDIUMTEXTA text column with a higher maximum length than TEXT16,777,215 (2^24 − 1) bytes
LONGBLOBA BLOB column with a high maximum length4,294,967,295 or 4GB (2^32 − 1) bytes
LONGTEXTA text column with a high maximum length4,294,967,295 or 4GB (2^32 − 1) bytes
ENUM (value_list)A string object that can have only one value from the list of values specified, or NULL. Can have up to 65,535 items in its list.255 bytes
SET (value_list)A string object that can have zero or more values from the list of values specified. Can have up to 64 items in its list.255 bytes

 

Date Data Types

Data TypeExplanationRange
DATEA date value (no time)1000-01-01 to 9999-12-31
DATETIME (fsp)A date and time value.

The parameter “fsp” is fractional seconds precision or the number of fractional seconds that can be stored,

1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999
TIMESTAMP (fsp)A timestamp value, stores date and time. Has a smaller range than DATETIME.

The parameter “fsp” is fractional seconds precision or the number of fractional seconds that can be stored,

1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.999999
TIME (fsp)A time value.

The parameter “fsp” is fractional seconds precision or the number of fractional seconds that can be stored,

-838:59:59.000000 to 838:59:59.000000
YEARA year in a 4-digit format.1901 to 2155

 

Other Data Types

Data TypeExplanation
GEOMETRYStores geometry values of any type
POINTStores a point in geometry
LINESTRINGStores a line shape
POLYGONStores a polygon shape
MULTIPOINTStores a collection of points
MULTILINESTRINGStores a collection of lines
MULTIPOLYGONStores a collection of polygons
GEOMETRYCOLLECTIONStore a collection of geometry objects
BOOLSynonym for TINYINT(1). Zero is false, nonzero values are true.
BOOLEANSynonym for TINYINT(1). Zero is false, nonzero values are true.

 

PostgreSQL Data Types

Here’s a list of all of the SQL data types available in PostgreSQL. For more information, refer to the official documentation here.

 

Numeric Data Types

Data TypeExplanationRange
SMALLINTA small integer number-32,768 to +32,767
INTEGERAn integer number-2,147,483,648 to +2,147,483,647,
BIGINTA large integer number-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807
DECIMALA decimal number with precisionup to 131,072 digits before the decimal point; up to 16,383 digits after the decimal point
NUMERIC (p, s)A decimal number with precision of “p” and scale of “s”up to 131,072 digits before the decimal point; up to 16,383 digits after the decimal point
REALA floating-point variable-precision number6 decimal digits precision
DOUBLE PRECISIONA floating-point variable-precision number15 decimal digits precision
SMALLSERIALA small automatically incrementing integer1 to 32,767
SERIALAn automatically incrementing integer1 to 2,147,483,647
BIGSERIALA large automatically incrementing integer1 to 9,223,372,036,854,775,807
MONEYA currency amount-92,233,720,368,547,758.08 to +92,233,720,368,547,758.07

 

Character Data Types

Data TypeExplanationRange
CHARACTER VARYING (n)A variable-length string up to “n” characters.Up to 10,485,760 characters (1GB)
VARCHAR (n)A variable-length string up to “n” characters.Up to 10,485,760 characters (1GB)
CHARACTER (n)A fixed-length string, padded to a length of “n” charactersUp to 10,485,760 characters (1GB)
CHAR (n)A fixed-length string, padded to a length of “n” charactersUp to 10,485,760 characters (1GB)
TEXTA variable length string
BYTEAA variable-length binary string. Similar to BLOB
ENUMA set of values that can be used for a column.

 

Date Data Types

Data TypeExplanationRange
TIMESTAMP (p)A date and time value with no time zone.

Precision “p” can be specified which is the number of fractional seconds.

4713 BC to 294276 AD
TIMESTAMP (p) WITH TIME ZONEA date and time value with time zone.

Precision “p” can be specified which is the number of fractional seconds.

4713 BC to 294276 AD
DATEA date but no time4713 BC to 5874897 AD
TIME (p)A time of day with no date

Precision “p” can be specified which is the number of fractional seconds.

00:00:00 to 24:00:00
TIME (p) WITH TIME ZONEA time of day with no date and a time zone

Precision “p” can be specified which is the number of fractional seconds.

00:00:00+1459 to 24:00:00-1459
INTERVAL [fields] (p)An interval of time.

Precision “p” can be specified which is the number of fractional seconds.

The parameter “fields” can be used to specify the type of data (e.g. YEAR, MONTH, DAY TO HOUR)

-178,000,000 years to 178,000,000 years

 

Other Data Types

Data TypeExplanationRange
BOOLEANStores either true or falseTrue, yes, on, 1.
False, no off, 0.
POINTA point of geometry
LINEA line of geometry
LSEGA segment of a line
BOXA rectangular box
PATHAn open path
POLYGONA polygon or shape
CIRCLEA circle
CIDRStores IPv4 and IPv6 network addresses
INETStores IPv4 and IPv6 hosts and network addresses
MACADDRStores MAC addresses using 6 bytes.
MACADDR8Stores MAC addresses using 8 bytes (the EUI-64 format)
TSVECTORA sorted list of words
TSQUERYA list of words to be searched for
UUIDStores a Universally Unique Identifier (or GUID). A 128-bit generated value128 bits
XMLStores XML data
JSONStores JSON data
JSONBStores JSON data in binary format, and can support indexing.
PG_LSNPostgreSQL Log Sequence Number
TXID_SNAPSHOTA user-level transaction ID snapshot

 

Data Type Comparison

How do all of the data types compare between each database vendor? And which data types are part of the SQL standard?

Take a look at this table to find out how the data types compare across vendors. Some data types are common across all databases mentioned. Others are only present in one database, or all but one.

 

Numeric Data Types For Each Database

Data TypeOracleSQL ServerMySQLPostgreSQL
INTEGERYYYY
INTYYY
TINYINTYY
SMALLINTYYYY
MEDIUMINTY
BIGINTYYY
NUMERICYYYY
DECIMALYYYY
DECYY
NUMBERY
FLOATYYY
DOUBLEY
DOUBLE PRECISIONYYYY
REALYYYY
BINARY_FLOATY
BINARY_DOUBLEY
BITYY
MONEYYY
SMALLMONEYY
FIXEDY
SMALLSERIALY
SERIALY
BIGSERIALY

 

Character Data Types For Each Database

Data TypeOracleSQL ServerMySQLPostgreSQL
CHARYYYY
CHARACTERYY
NCHARYY
VARCHARYYYY
CHARACTER VARYINGYYY
VARCHAR2Y
NVARCHARY
NVARCHAR2Y
BLOBYY
BYTEAY
CLOBY
NCLOBY
TEXTYYY
NTEXTY
TINYBLOBY
TINYTEXTY
MEDIUMBLOBY
MEDIUMTEXTY
LONGBLOBY
LONGTEXTY
BINARYYY
BINARY VARYINGY
VARBINARYY
ENUMYY
SETY
LONGY
RAWY
LONG RAWY

 

Date Data Types For Each Database

Data TypeOracleSQL ServerMySQLPostgreSQL
DATEYYYY
TIMEYYY
TIME WITH TIME ZONEY
TIMESTAMPYYY
TIMESTAMP WITH TIME ZONEYY
TIMESTAMP WITH LOCAL TIME ZONEY
DATETIMEYY
DATETIME2Y
INTERVAL YEAR TO MONTHY
INTERVAL DAY TO SECONDY
INTERVALY
DATETIMEOFFSETY
SMALLDATETIMEY
YEARY

 

Other Data Types For Each Database

Data TypeOracleSQL ServerMySQLPostgreSQL
BFILEY
BOOLY
BOOLEANYY
BOXY
CIDRY
CIRCLEY
CURSORY
GEOGRAPHYY
GEOMETRYYY
GEOMETRYCOLLECTIONY
HEIRARCHYIDY
IMAGEY
INETY
JSONY
JSONBY
LINEY
LINESTRINGY
LSEGY
MACADDRY
MACADDR8Y
MULTILINESTRINGY
MULTIPOINTY
MULTIPOLYGONY
PATHY
PG_LSNY
POINTYY
POLYGONYY
ROWIDY
ROWVERSIONY
SQL_VARIANTY
TABLEY
TSQUERYY
TSVECTORY
TXID_SNAPSHOTY
UNIQUEIDENTIFIERY
UROWIDY
UUIDY
XMLYY

 

Conclusion

There are many different data types available in each SQL database. Some data types have the same name across vendors and behave the same (such as INTEGER). Others are not the same but perform the same functionality (such as PostgreSQL BYTEA data type which can be used like a BLOB).

If you ever need to know what data types exist in each database, refer to this list to refresh your memory.

Get Your SQL Cheat Sheets Now:

Want Some SQL Cheat Sheets?

sql-cheat-sheet-images-v2

Download the SQL Cheat Sheets: common commands and syntax - to save you time.

You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.

Print them or use them as an easy reference.