SQL Data Types

Every column in a SQL table has a data type that tells the database engine what kind of values it may hold — whole numbers, decimals, text, dates, true/false flags, or raw binary data. Data types matter far more than they first appear: they determine how much storage a value uses, how fast comparisons and indexes work, how sorting behaves, and whether the database rejects bad data before it is ever stored. Picking the wrong type is one of the most common sources of subtle bugs in real applications — from money that does not add up correctly to numbers that sort like words. This lesson covers the type system from the ground up, using SQLite (the engine that verifies every example on this page) as the reference implementation, while comparing how MySQL, PostgreSQL, and SQL Server name and enforce the same concepts.

Overview: How SQL Data Types Work

Every column you define with CREATE TABLE is declared with a data type, such as INTEGER, TEXT, or DATE. The type is a contract between you and the database engine: it says what shape of data belongs in that column, and it tells the engine how to store the value efficiently, how to compare two values of that column when sorting or filtering, and how to validate incoming data.

Most database engines (MySQL, PostgreSQL, SQL Server) enforce types strictly: try to insert the text 'abc' into an INTEGER column and the insert is rejected outright. SQLite — the engine used to run every example on this page — works differently. Instead of strict types, SQLite uses type affinity. Every value SQLite stores actually belongs to one of five low-level storage classes (NULL, INTEGER, REAL, TEXT, BLOB), and a column’s declared type just gives it a preference, called an affinity, for one of those classes. SQLite tries to convert an incoming value to match the column’s affinity, but if the value cannot convert cleanly it stores it as-is in its original class. This makes SQLite forgiving and great for quick scripts, but it also means the type system catches fewer mistakes than you might expect — which is exactly why understanding data types deeply, rather than assuming “the database will catch it,” matters so much.

Regardless of engine, the query planner leans heavily on column types. An index on an INTEGER column can do fast numeric range scans; the same index on a TEXT column sorts and searches lexicographically instead. Choosing the right type up front affects storage size, index performance, sort order, and the accuracy of arithmetic.

Syntax

A data type is declared immediately after a column name inside CREATE TABLE (or added later with ALTER TABLE ... ADD COLUMN):

CREATE TABLE table_name (
    column_name  data_type  [column_constraints],
    column_name2 data_type2 [column_constraints],
    ...
);

• data_type — the declared type, e.g. INTEGER, VARCHAR(50), DATE. In SQLite this name is mapped to one of five affinities (see “Under the Hood” below); in strict engines it is enforced exactly.
• column_constraints — optional rules like NOT NULL, DEFAULT, CHECK(...), or PRIMARY KEY that add validation the type alone cannot provide.

SQLite’s Five Storage Classes

Storage class	Holds
NULL	The absence of a value
INTEGER	Signed whole numbers, stored in 1–8 bytes depending on magnitude
REAL	8-byte IEEE-754 floating-point numbers
TEXT	Character data, stored using the database encoding (usually UTF-8)
BLOB	Raw binary data, stored exactly as given

Common Data Types Across Engines

Category	SQLite	MySQL	PostgreSQL	SQL Server
Whole number	INTEGER	INT, BIGINT	INTEGER, BIGINT	INT, BIGINT
Exact decimal (money)	NUMERIC, DECIMAL	DECIMAL(p,s)	NUMERIC(p,s)	DECIMAL(p,s)
Approximate float	REAL, FLOAT	FLOAT, DOUBLE	REAL, DOUBLE PRECISION	FLOAT, REAL
Fixed-length text	CHAR(n)	CHAR(n)	CHAR(n)	CHAR(n)
Variable text	VARCHAR(n), TEXT	VARCHAR(n), TEXT	VARCHAR(n), TEXT	VARCHAR(n), NVARCHAR(n)
Date	TEXT (ISO-8601)	DATE	DATE	DATE
Date + time	TEXT (ISO-8601)	DATETIME, TIMESTAMP	TIMESTAMP	DATETIME2
Boolean	INTEGER (0/1)	BOOLEAN (alias for TINYINT(1))	BOOLEAN	BIT
Binary	BLOB	BLOB, VARBINARY	BYTEA	VARBINARY

SQLite has no dedicated DATE, BOOLEAN, or AUTO_INCREMENT type — dates are conventionally stored as ISO-8601 text ('2026-07-20') or Unix-epoch integers, booleans as 0/1 integers, and auto-incrementing keys use INTEGER PRIMARY KEY instead of MySQL’s AUTO_INCREMENT or SQL Server’s IDENTITY.

Examples

Example 1: Declaring types and seeing how SQLite actually stores them

CREATE TABLE products_catalog (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2),
    in_stock INTEGER,
    description TEXT,
    added_date TEXT
);

INSERT INTO products_catalog (id, name, price, in_stock, description, added_date)
VALUES
    (1, 'Wireless Mouse', 19.99, 1, 'Ergonomic wireless mouse', '2026-01-15'),
    (2, 'USB-C Cable', 8.50, 1, NULL, '2026-02-03'),
    (3, 'Mechanical Keyboard', 74.00, 0, 'RGB backlit keyboard', '2026-03-21');

SELECT name, price, typeof(price) AS price_storage, in_stock, typeof(in_stock) AS instock_storage
FROM products_catalog
ORDER BY id;

Result:

name	price	price_storage	in_stock	instock_storage
Wireless Mouse	19.99	real	1	integer
USB-C Cable	8.5	real	1	integer
Mechanical Keyboard	74	integer	0	integer

The price column is declared NUMERIC(10,2), which SQLite maps to NUMERIC affinity (the (10,2) precision/scale is accepted but not enforced). Notice that 74.00 was actually stored as the integer 74, not as a float — SQLite’s NUMERIC affinity always converts a value into the most compact storage class that can hold it losslessly, so a whole-number decimal collapses to INTEGER storage. This is genuine SQLite behavior, not a display quirk.

Example 2: Converting between types with CAST

CREATE TABLE payroll (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary REAL
);

INSERT INTO payroll (id, name, salary) VALUES
    (1, 'Priya', 65999.75),
    (2, 'Diego', 82000.00),
    (3, 'Wei', 47250.40);

SELECT name, salary, CAST(salary AS INTEGER) AS salary_rounded_down
FROM payroll
ORDER BY id;

Result:

name	salary	salary_rounded_down
Priya	65999.75	65999
Diego	82000.0	82000
Wei	47250.4	47250

The salary column is declared REAL, so every value is forced into floating-point storage, even 82000.00. CAST(salary AS INTEGER) truncates toward zero — it drops the fractional part rather than rounding, so 65999.75 becomes 65999, not 66000. Use CAST(expression AS type) whenever you need to force a value into a specific storage class, such as dividing two integers and expecting a fractional result with CAST(a AS REAL) / b.

Example 3: Why REAL is the wrong type for money

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance REAL
);

INSERT INTO accounts (id, balance) VALUES (1, 0.1), (2, 0.2);

SELECT SUM(balance) AS total_balance, SUM(balance) = 0.3 AS equals_expected
FROM accounts;

Result:

total_balance	equals_expected
0.30000000000000004 (displays as approximately 0.3)	0

REAL stores an 8-byte IEEE-754 binary floating-point number, and 0.1 and 0.2 cannot be represented exactly in binary — the same reason 0.1 + 0.2 does not exactly equal 0.3 in almost every programming language. The sum comes out infinitesimally larger than 0.3, so the equality check returns 0 (false) even though the numbers look identical. This is why financial and other exact-decimal data should use NUMERIC/DECIMAL (or integer cents) instead of REAL/FLOAT.

How It Works Step by Step: Type Affinity Under the Hood

When SQLite processes a CREATE TABLE statement, it reduces the declared type name to one of five affinities using fixed text-matching rules, applied in order:

Rule	Resulting affinity	Example type names
Type name contains “INT”	INTEGER	INTEGER, INT, BIGINT, TINYINT
Type name contains “CHAR”, “CLOB”, or “TEXT”	TEXT	VARCHAR(50), NCHAR, TEXT
Type name contains “BLOB”, or no type given	BLOB	BLOB
Type name contains “REAL”, “FLOA”, or “DOUB”	REAL	REAL, FLOAT, DOUBLE PRECISION
Anything else	NUMERIC	NUMERIC, DECIMAL(10,2), DATE, BOOLEAN

Once a column has an affinity, every value written to it is nudged toward that affinity: TEXT affinity converts numbers to their text representation; INTEGER/NUMERIC affinity converts numeric-looking text into INTEGER or REAL while leaving non-numeric text alone; REAL affinity behaves like NUMERIC but always pushes integers into floating-point form; and BLOB affinity (“no affinity”) performs no conversion at all. You can see REAL affinity’s behavior directly against the shared products table, where price is declared REAL:

SELECT typeof(product_id), typeof(product_name), typeof(price), typeof(category)
FROM products
LIMIT 1;

Result: integer, text, real, text — price reports real no matter what value was inserted, because a REAL-declared column always forces floating-point storage, unlike the NUMERIC column in Example 1 which could collapse to INTEGER.

This affinity conversion is also why ORDER BY and comparisons behave the way they do: SQLite compares values by storage class first (NULL < numbers < TEXT < BLOB), then within a class using that class’s natural ordering — numeric order for numbers, byte-wise order for text. A TEXT-affinity column storing '2', '9', and '10' sorts them as text, not as numbers, because the conversion happens the moment a row is written, well before WHERE, GROUP BY, or ORDER BY ever run — by the time the query engine evaluates a clause it is already working with the column’s storage-class value, not your original literal.

Common Mistakes

Mistake 1: Storing numbers as TEXT and expecting numeric sorting

CREATE TABLE scores (
    id INTEGER PRIMARY KEY,
    player TEXT,
    score TEXT
);

INSERT INTO scores (id, player, score) VALUES
    (1, 'Ana', '9'),
    (2, 'Ben', '10'),
    (3, 'Cy', '2');

-- Mistake: score has TEXT affinity, so ORDER BY sorts alphabetically
SELECT player, score FROM scores ORDER BY score;

-- Fix: cast to a number (or declare the column as INTEGER from the start)
SELECT player, score FROM scores ORDER BY CAST(score AS INTEGER);

The first query returns Ben/10, Cy/2, Ana/9 — because '1' sorts before '2' and '2' sorts before '9' character by character, exactly like sorting words alphabetically. The second, corrected query returns Cy/2, Ana/9, Ben/10, the true numeric order.

Mistake 2: Assuming VARCHAR(n) enforces a length limit

CREATE TABLE users_demo (
    id INTEGER PRIMARY KEY,
    username VARCHAR(5)
);

INSERT INTO users_demo (id, username) VALUES (1, 'ThisIsWayTooLong');

SELECT username, length(username) AS char_count FROM users_demo;

This insert succeeds without error, and char_count comes back as 16. SQLite only looks at the base type name (VARCHAR contains “CHAR”, so it gets TEXT affinity) and silently discards the (5) — it never enforces declared lengths. MySQL, PostgreSQL, and SQL Server all do enforce this limit and would truncate or reject the insert. If you need a real length limit in SQLite, add it explicitly with a constraint, e.g. username TEXT CHECK (length(username) <= 5).

Mistake 3: Relying on the type name instead of a constraint to enforce valid values

CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    quantity INTEGER CHECK (quantity >= 0)
);

INSERT INTO inventory (id, quantity) VALUES (1, -5);

This fails with a CHECK constraint failed: quantity >= 0 error — the constraint is working as intended. The mistake it illustrates is thinking that declaring a column INTEGER alone guarantees a valid, non-negative quantity. A type only restricts the storage class; it says nothing about the acceptable range of values. Without the CHECK, SQLite would happily accept -5 as a perfectly valid integer.

Best Practices

• Use INTEGER for whole numbers and IDs; do not store numeric IDs as TEXT unless they need leading zeros or non-numeric characters.
• Use NUMERIC/DECIMAL, or integer cents, for money and anything that must add up exactly; never use REAL/FLOAT for currency.
• Store dates and timestamps as ISO-8601 text ('YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS') in SQLite so they sort correctly as plain text and work with the built-in date functions.
• Pair types with constraints (NOT NULL, CHECK, UNIQUE) — a data type alone never enforces business rules like ranges, formats, or required fields.
• Use explicit CAST(... AS type) instead of relying on implicit conversion, especially before arithmetic, sorting, or comparisons across mixed types.
• When writing SQL meant to run on multiple databases, avoid engine-specific type names (AUTO_INCREMENT, SERIAL, IDENTITY) without calling out the differences explicitly.
• Keep a single, consistent type per logical column across every table that stores it — joining an INTEGER foreign key against a TEXT primary key forces conversions that can silently defeat indexes.

Practice Exercises

1. Write a CREATE TABLE books statement with a title (text), a price (exact decimal, not float), a page count (whole number), and a publication date (stored as ISO-8601 text). Insert two sample rows.
2. Given a table where a quantity column was mistakenly created as TEXT and contains the values '5', '40', '100', '9', write a query that returns them in correct numeric order without changing the column’s declared type. (Hint: you saw the function to use in Example 2.)
3. Explain, in your own words, why adding 0.1 and 0.2 as REAL values does not exactly equal 0.3 in SQLite, and rewrite the accounts table from Example 3 to store balances as whole cents in an INTEGER column instead, avoiding the problem entirely.

Summary

• A column’s data type governs storage size, comparison/sort order, and what validation the engine performs automatically.
• SQLite uses five storage classes (NULL, INTEGER, REAL, TEXT, BLOB) and maps every declared type name to one of five affinities using fixed text-matching rules.
• Other engines (MySQL, PostgreSQL, SQL Server) enforce declared types strictly, including length limits that SQLite ignores.
• Use NUMERIC/DECIMAL for exact values like money; REAL/FLOAT introduces binary rounding error.
• Storing numbers as TEXT causes lexicographic (alphabetical) rather than numeric sorting — cast or declare the correct type.
• A data type is not a validation rule — pair it with NOT NULL and CHECK constraints to enforce business logic.