SQL Data Types Explained: Numbers, Text, Dates and More

Every column in a table has a data type. The type decides what values the column can hold, how much space they take, how they sort, and which operations make sense. Picking good types is one of the easiest ways to keep data correct.

Why types matter#

• Correctness: a DATE column cannot hold 'next Tuesday', and a number column cannot hold 'N/A'.
• Behaviour: numbers sort as numbers (9 before 100); text sorts character by character ('100' before '9').
• Performance and storage: smaller, precise types make tables and indexes smaller and faster.

Whole numbers#

Integer division drops the remainder in many databases:

SELECT 7 / 2 AS int_div,
       7 / 2.0 AS dec_div;

int_div | dec_div
--------+--------
3       | 3.5

PostgreSQL, SQL Server and SQLite return 3 for 7 / 2; MySQL and Oracle return 3.5. Write 7 / 2.0 (or cast) when you need decimals.

Exact decimals: DECIMAL / NUMERIC#

DECIMAL(p, s) stores numbers exactly. p (precision) is the total number of digits; s (scale) is how many of them come after the decimal point.

NUMERIC and DECIMAL are the same thing. Use them for money and anything else where every digit must be right.

Approximate decimals: FLOAT / REAL / DOUBLE#

Floating-point types are fast and handle very large and very small numbers, but they store a close approximation in binary. Store 0.1 and 0.2 in FLOAT columns, add them, and the result is 0.30000000000000004, not 0.3.

Use FLOAT / DOUBLE PRECISION for scientific measurements and statistics, never for money.

Text#

VARCHAR(n) is the everyday choice. Its limit protects you from accidentally storing a whole essay in a name column. SQL Server uses NVARCHAR for full Unicode text (Hindi, Tamil, emoji); in PostgreSQL and MySQL with utf8mb4, ordinary VARCHAR already handles Unicode.

Dates and times#

Always write dates in ISO format, 'YYYY-MM-DD'. It is unambiguous and sorts correctly even as text. You will learn to work with dates in Date Functions.

True or false: BOOLEAN#

PostgreSQL has a real BOOLEAN type with values TRUE, FALSE, and NULL. MySQL accepts BOOLEAN but stores it as TINYINT(1) (0 or 1). SQL Server uses BIT. SQLite stores 0 and 1 in an integer.

Other useful types#

• UUID: globally unique identifiers such as a3f1c2e4-... (native in PostgreSQL; UNIQUEIDENTIFIER in SQL Server).
• JSON / JSONB: structured documents inside a column (PostgreSQL, MySQL).
• BLOB / BYTEA: binary data such as small files. Large files usually belong in file storage, with only the path in the database.

Converting between types: CAST#

CAST(value AS type) converts a value:

SELECT CAST('42' AS INTEGER) + 8 AS total;

total
-----
50

Conversion fails (or, in some databases, silently produces 0 or NULL) when the value does not fit, such as CAST('abc' AS INTEGER). PostgreSQL also has the shorthand '42'::integer.

The same idea, different names#

Common mistakes#

Practice#

Key takeaway#