Relational databases typically require you to define which type of data you would like to store in each column. This is done at the time of table creation.
The SQL Standard defines over 20 different data types for things like numbers, text, dates and more. The various database vendors such as PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database may not support them all and they may define their own extra types.
This example uses a built-in SQLite function called pragma_table_info() on our profiles table to show each column and its data type.
SQLite is different in that instead of exposing all of these different types, it simplifies things for the user by using just five basic types:
INTEGERfor whole numbers.
REALfor floating-point (decimal) numbers.
TEXTfor text-based data.
BLOBfor binary data.
NULLfor representing the absence of any type of data. Typically you can define columns to allow or disallow NULLs using Constraints.
ANY is a special keyword for a column that can accept any type of data. SQLite is unique in that it also allows dynamically-typed columns. See Datatypes in SQLitefor more details on data types.
Each type has a maximum storage size in bytes which will limit how much data or how big of a value you can store in each row. Depending on your vocation and reasons for learning SQL you may or may not need to know those details, for example the maximum byte size of a data type.
Don't worry if you don't understand what a byte is — that information is included as a starting point for those who want to learn more.
Below you will see a more complete list of types from the SQL Standard and/or available to use in other database engines.
SELECT name AS column_name, type
from pragma_table_info('profiles');
| column_name | type |
|---------------|---------|
| id | INTEGER |
| username | TEXT |
| active | INTEGER |
| friends | INTEGER |
| rating | REAL |
| profile_image | BLOB |
| created_at | TEXT |
| data_storage | ANY |
Numeric types:
TINYINT, SMALLINT and MEDIUMINTfor smaller positive/negative whole numbers; takes less space.
INT or INTEGERfor positive/negative whole numbers.
FLOAT, DOUBLE PRECISION and REALfor floating point (decimal) numbers. Typically a FLOAT can store 4 bytes (around 6 digits) of data, a DOUBLE PRECISION can store 8 bytes (around 15 digits), and REAL is usually equivalent to a FLOAT. Sometimes database vendors use FLOAT(n) where n is the storage space in bytes to use.
DECIMAL(p,s) or NUMERIC(p,s)for exact-precision floating point numbers. The p stands for "precision" and specifies the maximum amount of total digits. The s stands for "scale" and specifies the maximum amount of digits to the right of the decimal point. Both types exist for historical reasons but most modern databases treat DECIMAL and NUMERIC as synonyms. See your database's documentation to be sure.
CHAR(n)for fixed-length strings. n specifies how many characters the strings will have. Every row will store the same size of string regardless of how large the inserted strings are and the string will be padded or truncated.
VARCHAR(n)for variable-length strings. n specifies the maximum amount of characters a string can have.
TEXTfor variable-length strings without a specified maximum. Typically this type has a very large maximum size and varies by database.
TIMEfor times of day including the hour, minute and second.
TIMESTAMP or DATETIMEfor a combination of both date and time.
SQLite does not include any special type for storing dates or times; it uses TEXTfor this purpose and includes several built-in Functionsfor working with dates and times.
SQLite requires you to store dates/times in one of a few certain formats. The most human-readable format to use is an ISO-8601 formatted string.
BINARY(n)for fixed length binary data, for example image data. n specifies the amount of bytes all rows will have. Every row will store the same size of data regardless of how large the inserted data is and the data will be padded or truncated.
VARBINARY(n)for variable length binary data. n specifies the maximum amount of bytes rows can have.
BLOBfor variable-length binary objects without a specified maximum. Typically this type has a very large maximum size and varies by database. "BLOB" stands for "Binary Large Object".
This example shows a text representation of what binary data would look like if stored in a SQLite BLOB column. In reality, binary data in its native format cannot be rendered as text and would look garbled or display squares. The x'...' format shown here can actually be used to insert the hexadecimal string representation of binary data into the column.
...[]for array types. TEXT[] is an example of an array of text data. Array types allow you to store lists of items together in a single column. SQLite does not support array data types but other database engines such as PostgreSQL support arrays for any valid type, like TIMESTAMP[] and BOOLEAN[].
Databases that support array types also provide syntax and functions for accessing elements from an array, for example users.names[0] to select the first element of an array-type row.
JSONfor JSON-formatted data. Many modern databases have features and functions to deal specifically with JSON data. Some databases require you to use JSONfor array data types and functions.
XMLfor XML-formatted data. Several modern databases have features specifically for working with XML data.
UUIDfor UUID-formatted IDs. Some databases have this as a built-in type so the data can be stored, found and compared more efficiently than a TEXT-like type.