SQL Database Types: Data Types in MySQL, PostgreSQL, SQL Server and SQLite

SQL database types usually means one of two things: the data types you can assign to a column (integer, text, date and so on), or the kinds of database system that speak SQL (MySQL, PostgreSQL, SQL Server, SQLite). This guide leads with column data types, because that is what you have to decide when you design a research dataset, and covers the four database systems whose official manuals define the types. Every type name below comes from the vendor’s own documentation, checked on 2026-10-29.

What “SQL database type” means in research work

People who search for database type in SQL or database types in SQL are almost always after the list of types a column can hold. Each SQL system publishes its own list, and the lists overlap but are not identical. A column declared DATETIME in MySQL has no counterpart of that name in PostgreSQL, and SQLite works differently from the other three (see the affinity section below).

The second meaning, the kind of system, is covered briefly in the next section. For a side-by-side of two of them beyond data types, see MySQL vs PostgreSQL.

SQL database systems covered here

The four systems below each publish a type reference, and each column of the table further down follows one of those manuals. They are listed for orientation, not ranked.

• PostgreSQL 18: Chapter 8, Data Types.
• MySQL 8.4: Reference Manual, Data Types.
• SQL Server: Microsoft Learn, Data types (Transact-SQL).
• SQLite 3: Datatypes In SQLite.

Data type categories

The manuals group types in similar ways. Microsoft’s Transact-SQL reference uses these categories: exact numerics, approximate numerics, date and time, character strings, Unicode character strings, binary strings, and other types. MySQL’s manual groups its types as numeric, date and time, string, spatial and JSON. The categories used in the table below are a reading aid built from those groupings.

Exact versus approximate numbers

MySQL’s manual describes INTEGER, SMALLINT, DECIMAL and NUMERIC as exact numeric types, and FLOAT, REAL and DOUBLE PRECISION as approximate numeric types. SQL Server draws the same line between exact numerics and approximate numerics (float, real). The Transact-SQL page also states that the decimal and numeric data types are identical. For measured quantities where rounding matters, such as currency amounts or counts, the exact categories are the ones to read first in each manual.

Fixed-length and variable-length text

PostgreSQL’s table describes character(n) (alias char(n)) as fixed-length and character varying(n) (alias varchar(n)) as variable-length, with text as a variable-length character string.

Data types by database system

A dash means the manual’s type list does not include a type for that category. It does not mean the system cannot store that kind of value.

Category	PostgreSQL 18	MySQL 8.4	SQL Server	SQLite 3
Integer	smallint, integer, bigint	TINYINT, SMALLINT, MEDIUMINT, INT / INTEGER, BIGINT	tinyint, smallint, int, bigint	INTEGER storage class
Exact decimal	numeric / decimal	DECIMAL, NUMERIC	decimal, numeric	NUMERIC affinity
Floating point	real, double precision	FLOAT, DOUBLE	float, real	REAL (8-byte IEEE floating point)
Text	character varying(n), character(n), text	CHAR, VARCHAR, TEXT	char, varchar, text; Unicode: nchar, nvarchar, ntext	TEXT
Binary	bytea	BINARY, VARBINARY, BLOB	binary, varbinary, image	BLOB
Date and time	date, time, timestamp, timestamp with time zone, interval	DATE, DATETIME, TIMESTAMP, TIME, YEAR	date, time, datetime2, datetimeoffset, datetime, smalldatetime	—
Boolean / bit	boolean; bit, bit varying	BIT	bit	—
JSON	json, jsonb	JSON	json	—
Spatial	point, line, lseg, box, path, polygon, circle	Spatial types	geography, geometry	—
Other	uuid, xml, money, inet, cidr, macaddr, tsvector, tsquery	ENUM, SET	uniqueidentifier, xml, rowversion, sql_variant, hierarchyid	NULL storage class

Two details from the manuals are worth knowing. SQL Server’s documentation says the bit data type is used for storing Boolean values, and that SQL Server provides data type synonyms for ISO compatibility. PostgreSQL’s table lists aliases too, for example int4 for integer and float8 for double precision, so a type name in someone else’s schema may be an alias rather than a different type.

Which types are standard SQL

If a dataset has to move between systems, the portable core matters. The PostgreSQL manual states: “The following types (or spellings thereof) are specified by SQL: bigint, bit, bit varying, boolean, char, character varying, character, varchar, date, double precision, integer, interval, numeric, decimal, real, smallint, time (with or without time zone), timestamp (with or without time zone), xml.”

Types in that manual’s table that are absent from this list, such as money, jsonb, inet, uuid and the geometric types, are therefore not part of that quoted standard list. A schema built only from the listed types is the one least tied to a single product, although each system’s exact behaviour for a listed type is still defined by its own manual.

How SQLite differs: type affinity

SQLite does not use the type lists above. Its documentation defines five storage classes: NULL, INTEGER (signed, stored in 0, 1, 2, 3, 4, 6 or 8 bytes depending on magnitude), REAL (an 8-byte IEEE floating point number), TEXT (stored in the database encoding: UTF-8, UTF-16BE or UTF-16LE) and BLOB (“stored exactly as it was input”). Each column is then assigned one of five type affinities: TEXT, NUMERIC, INTEGER, REAL or BLOB.

For tables that are not STRICT, the affinity comes from the declared type name, using these rules in order:

1. If the declared type contains the string “INT”, the column has INTEGER affinity.
2. Otherwise, if it contains “CHAR”, “CLOB” or “TEXT”, the affinity is TEXT. A VARCHAR column gets TEXT affinity because its name contains “CHAR”.
3. Otherwise, if it contains “BLOB” or no type is specified, the affinity is BLOB.
4. Otherwise, if it contains “REAL”, “FLOA” or “DOUB”, the affinity is REAL.
5. Otherwise, the affinity is NUMERIC.

The documentation’s own example shows why the order matters: a column whose declared type is “CHARINT” matches both rule 1 and rule 2, and because the first rule takes precedence, the column affinity is INTEGER. Running a declared type name through these rules shows which affinity a column will get.

Where to read the official type references

Use the vendor documentation for the version you actually run, because type lists change between releases. Official type references, checked on 2026-10-29:

• PostgreSQL documentation, Chapter 8: Data Types
• MySQL 8.4 Reference Manual: Data Types
• Microsoft Learn: Data types (Transact-SQL)
• SQLite: Datatypes In SQLite

Documenting types in a research dataset

Whichever system holds the data, the column types are part of the dataset’s description. A metadata schema and a data management plan are where those choices get recorded; see the dictionary entry on the data management plan (DMP). Where a project adopts a published relational model, such as the OMOP Common Data Model, the model fixes the table structure, and the target database’s own type list decides how each field is stored. For analytical storage that sits downstream of operational databases, see data warehouse.

FAQ

What are the main SQL data types?

Across the four manuals cited here the recurring groups are integers, exact decimals, floating-point numbers, character strings, binary strings, dates and times, and JSON, with spatial and system-specific types on top. The table above lists the type names each system uses for these groups.

What is the difference between CHAR and VARCHAR?

In PostgreSQL’s table, character(n) (alias char(n)) is fixed-length and character varying(n) (alias varchar(n)) is variable-length. Both char and varchar are in the list of types the PostgreSQL manual says SQL specifies.

Are DECIMAL and NUMERIC different?

In SQL Server they are identical, according to the Transact-SQL data types page. MySQL describes both as exact numeric types, and PostgreSQL lists decimal as the alias of numeric.

Is boolean a standard SQL type?

The PostgreSQL manual includes boolean in its list of types specified by SQL. SQL Server’s documentation says its bit type is used for storing Boolean values.

Does SQLite have the same types as MySQL or PostgreSQL?

No. SQLite documents five storage classes (NULL, INTEGER, REAL, TEXT, BLOB) and assigns each column one of five affinities from its declared type name, as described above.

Which SQL database types are used for JSON?

PostgreSQL has json (textual) and jsonb (binary, decomposed), MySQL has a JSON type, and the SQL Server types page lists json among its other types.

Sources

• PostgreSQL documentation, Chapter 8: Data Types (version 18). https://www.postgresql.org/docs/current/datatype.html. Checked 2026-10-29.
• MySQL 8.4 Reference Manual: Data Types. https://dev.mysql.com/doc/refman/8.4/en/data-types.html. Checked 2026-10-29.
• Data types (Transact-SQL), Microsoft Learn (page dated 2024-05-21). https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql. Checked 2026-10-29.
• SQLite. Datatypes In SQLite. https://www.sqlite.org/datatype3.html. Checked 2026-10-29.