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 Type Explanation Range
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_FLOAT 32-bit, single-precision floating-point number From 1.17549E-38F to 3.40282E+38F
BINARY_DOUBLE 64-bit, double-precision floating-point number From 2.22507485850720E-308 to 1.79769313486231E+308
INTEGER Synonym for NUMBER(38) – 10^38 +1 through 10^38 – 1
FLOAT Translated to NUMBER 1 to 126 binary digits, and up to 22 bytes
DECIMAL Translated to NUMBER 1 to 126 binary digits, and up to 22 bytes
NUMERIC Synonym for NUMBER
INT Synonym for NUMBER(38)
SMALLINT Synonym for NUMBER(38)
DOUBLE PRECISION Synonym for FLOAT(126)
REAL Synonym 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 Type Explanation Range
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.
VARCHAR Not implemented in Oracle.
VARCHAR2 (s) Variable-length character string with a maximum length of s bytes 32,767 bytes
NVARCHAR2 (s) Variable-length national character string with a maximum length of s bytes 32,767 bytes
LONG Variable-length character string. Larger than VARCHAR2. Deprecated 2 GB
RAW (s) Raw binary data of length size 32,767 bytes
LONG RAW Raw binary data of variable length. Deprecated 2 GB
CHARACTER VARYING Synonym for VARCHAR2
CHARACTER Synonym for CHAR
BLOB Stores large unstructured binary objects 8 TB to 128 TB (4 GB -1 * (database block size
CLOB Stores large single-byte and multi-byte objects 8 TB to 128 TB (4 GB -1 * (database block size
NCLOB Stores Unicode data 8 TB to 128 TB (4 GB -1 * (database block size

 

Date Data Types

Data Type Explanation Range
DATE Stores date and time values. From Jan 1, 4712 BC to Dec 31 9999 AD
TIMESTAMP A date value that stores time and fractional seconds From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
TIMESTAMP WITH TIME ZONE A timestamp that stores data in the specified timezone From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
TIMESTAMP WITH LOCAL TIME ZONE A timestamp that stores data in your local timezone From Jan 1, 4712 BC to Dec 31 9999 AD. Fractional seconds must be 0 to 9
INTERVAL YEAR TO MONTH Stores a period of time in years and months Up to 2 digits in the year
INTERVAL DAY TO SECOND Stores a period of time in days, hours, minutes, and seconds Between 0 and 9 digits in the day, between 0 and 9 digits fractional seconds

 

Other Data Types

Data Type Explanation Range
BFILE A file locator that points to a file on the server 4GB
ROWID Fixed-length binary data for a database row ID 10 bytes
UROWID Universal RowID 4,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 Type Explanation Range
BIGINT Integer value, uses 8 bytes. -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
BIT Integer 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
INT Integer value, uses 4 bytes -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
INTEGER Synonym for INT
MONEY Represents 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
SMALLINT Integer value, uses 2 bytes -2^15 (-32,768) to 2^15-1 (32,767)
SMALLMONEY Represents monetary values, uses 4 bytes -214,748.3648 to 214,748.3647
TINYINT Integer value, uses 1 byte 0 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
REAL Synonym for FLOAT(24) – 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38
DEC Synonym for DECIMAL
DOUBLE PRECISION Synonym for FLOAT

 

Character Data Types

Data Type Explanation Range
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)
TEXT Variable-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
NTEXT Variable-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,000 Up to 8,000 bytes.
VARBINARY Variable-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 VARYING Synonym for VARCHAR
BINARY VARYING Synonym for VARBINARY

 

Date Data Types

Data Type Explanation Range
DATE Defines and stores a date value 0001-01-01 through 9999-12-31
DATETIME Date 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

SMALLDATETIME Date 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 Type Explanation Range
IMAGE Variable-length binary data 0 through 2^31-1 (2,147,483,647 or 2GB) bytes.
CURSOR Used for variables or stored procedure out parameters to refer to a cursor
ROWVERSION Used to store automatically-generated unique binary numbers. 8 bytes
HEIRARCHYID Variable length system data type
UNIQUEIDENTIFIER A 16-byte GUID (Global Unique Identifier) 16 bytes
SQL_VARIANT Stores values of different SQL Server data types
XML Stores XML data
GEOMETRY A spatial data type used to represent coordinates.
GEOGRAPHY A spatial data type used to represent GPS latitude and longitude coordinates
TABLE Stores 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 Type Explanation Range
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 DECIMAL 65-digit number
NUMERIC (n [,d]) Synonym for DECIMAL 65-digit number
FIXED (n [,d]) Synonym for DECIMAL 65-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 PRECISION Synonym for DOUBLE -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308
REAL Synonym for DOUBLE -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308

 

Character Data Types

Data Type Explanation Range
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
TINYBLOB A BLOB column with a smaller maximum length 255 bytes
TEXT (n) A text column, and parameter “n” is the maximum number of bytes. 65,535 bytes
TINYTEXT A text column with a smaller maximum length 255 bytes
MEDIUMBLOB A BLOB with a higher maximum length than BLOB 16,777,215 (2^24 − 1) bytes
MEDIUMTEXT A text column with a higher maximum length than TEXT 16,777,215 (2^24 − 1) bytes
LONGBLOB A BLOB column with a high maximum length 4,294,967,295 or 4GB (2^32 − 1) bytes
LONGTEXT A text column with a high maximum length 4,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 Type Explanation Range
DATE A 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
YEAR A year in a 4-digit format. 1901 to 2155

 

Other Data Types

Data Type Explanation
GEOMETRY Stores geometry values of any type
POINT Stores a point in geometry
LINESTRING Stores a line shape
POLYGON Stores a polygon shape
MULTIPOINT Stores a collection of points
MULTILINESTRING Stores a collection of lines
MULTIPOLYGON Stores a collection of polygons
GEOMETRYCOLLECTION Store a collection of geometry objects
BOOL Synonym for TINYINT(1). Zero is false, nonzero values are true.
BOOLEAN Synonym 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 Type Explanation Range
SMALLINT A small integer number -32,768 to +32,767
INTEGER An integer number -2,147,483,648 to +2,147,483,647,
BIGINT A large integer number -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807
DECIMAL A decimal number with precision up 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
REAL A floating-point variable-precision number 6 decimal digits precision
DOUBLE PRECISION A floating-point variable-precision number 15 decimal digits precision
SMALLSERIAL A small automatically incrementing integer 1 to 32,767
SERIAL An automatically incrementing integer 1 to 2,147,483,647
BIGSERIAL A large automatically incrementing integer 1 to 9,223,372,036,854,775,807
MONEY A currency amount -92,233,720,368,547,758.08 to +92,233,720,368,547,758.07

 

Character Data Types

Data Type Explanation Range
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” characters Up to 10,485,760 characters (1GB)
CHAR (n) A fixed-length string, padded to a length of “n” characters Up to 10,485,760 characters (1GB)
TEXT A variable length string
BYTEA A variable-length binary string. Similar to BLOB
ENUM A set of values that can be used for a column.

 

Date Data Types

Data Type Explanation Range
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 ZONE A date and time value with time zone.

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

4713 BC to 294276 AD
DATE A date but no time 4713 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 ZONE A 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 Type Explanation Range
BOOLEAN Stores either true or false True, yes, on, 1.
False, no off, 0.
POINT A point of geometry
LINE A line of geometry
LSEG A segment of a line
BOX A rectangular box
PATH An open path
POLYGON A polygon or shape
CIRCLE A circle
CIDR Stores IPv4 and IPv6 network addresses
INET Stores IPv4 and IPv6 hosts and network addresses
MACADDR Stores MAC addresses using 6 bytes.
MACADDR8 Stores MAC addresses using 8 bytes (the EUI-64 format)
TSVECTOR A sorted list of words
TSQUERY A list of words to be searched for
UUID Stores a Universally Unique Identifier (or GUID). A 128-bit generated value 128 bits
XML Stores XML data
JSON Stores JSON data
JSONB Stores JSON data in binary format, and can support indexing.
PG_LSN PostgreSQL Log Sequence Number
TXID_SNAPSHOT A 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 Type Oracle SQL Server MySQL PostgreSQL
INTEGER Y Y Y Y
INT Y Y Y
TINYINT Y Y
SMALLINT Y Y Y Y
MEDIUMINT Y
BIGINT Y Y Y
NUMERIC Y Y Y Y
DECIMAL Y Y Y Y
DEC Y Y
NUMBER Y
FLOAT Y Y Y
DOUBLE Y
DOUBLE PRECISION Y Y Y Y
REAL Y Y Y Y
BINARY_FLOAT Y
BINARY_DOUBLE Y
BIT Y Y
MONEY Y Y
SMALLMONEY Y
FIXED Y
SMALLSERIAL Y
SERIAL Y
BIGSERIAL Y

 

Character Data Types For Each Database

Data Type Oracle SQL Server MySQL PostgreSQL
CHAR Y Y Y Y
CHARACTER Y Y
NCHAR Y Y
VARCHAR Y Y Y Y
CHARACTER VARYING Y Y Y
VARCHAR2 Y
NVARCHAR Y
NVARCHAR2 Y
BLOB Y Y
BYTEA Y
CLOB Y
NCLOB Y
TEXT Y Y Y
NTEXT Y
TINYBLOB Y
TINYTEXT Y
MEDIUMBLOB Y
MEDIUMTEXT Y
LONGBLOB Y
LONGTEXT Y
BINARY Y Y
BINARY VARYING Y
VARBINARY Y
ENUM Y Y
SET Y
LONG Y
RAW Y
LONG RAW Y

 

Date Data Types For Each Database

Data Type Oracle SQL Server MySQL PostgreSQL
DATE Y Y Y Y
TIME Y Y Y
TIME WITH TIME ZONE Y
TIMESTAMP Y Y Y
TIMESTAMP WITH TIME ZONE Y Y
TIMESTAMP WITH LOCAL TIME ZONE Y
DATETIME Y Y
DATETIME2 Y
INTERVAL YEAR TO MONTH Y
INTERVAL DAY TO SECOND Y
INTERVAL Y
DATETIMEOFFSET Y
SMALLDATETIME Y
YEAR Y

 

Other Data Types For Each Database

Data Type Oracle SQL Server MySQL PostgreSQL
BFILE Y
BOOL Y
BOOLEAN Y Y
BOX Y
CIDR Y
CIRCLE Y
CURSOR Y
GEOGRAPHY Y
GEOMETRY Y Y
GEOMETRYCOLLECTION Y
HEIRARCHYID Y
IMAGE Y
INET Y
JSON Y
JSONB Y
LINE Y
LINESTRING Y
LSEG Y
MACADDR Y
MACADDR8 Y
MULTILINESTRING Y
MULTIPOINT Y
MULTIPOLYGON Y
PATH Y
PG_LSN Y
POINT Y Y
POLYGON Y Y
ROWID Y
ROWVERSION Y
SQL_VARIANT Y
TABLE Y
TSQUERY Y
TSVECTOR Y
TXID_SNAPSHOT Y
UNIQUEIDENTIFIER Y
UROWID Y
UUID Y
XML Y Y

 

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.