Numeric types split into exact integers (INTEGER, BIGINT, SMALLINT) and exact or approximate decimals (NUMERIC/DECIMAL for exact precision like currency, FLOAT/REAL for approximate values). Text types include fixed-length CHAR, variable-length VARCHAR(n), and unbounded TEXT. Date/time types (DATE, TIME, TIMESTAMP) and BOOLEAN round out the common set — but PostgreSQL enforces these strictly, MySQL is somewhat more lenient, and SQLite uses "type affinity," where a column has a preferred type but will still store a value of a different type without an error.
Numeric types: exact vs approximate
NUMERIC/DECIMAL store an exact value with fixed precision and scale — essential for money, where FLOAT's rounding behavior would be a real bug, not a rounding curiosity.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
price NUMERIC(10, 2), -- exact: up to 10 digits, 2 after the decimal — use for money
weight FLOAT -- approximate: fine for measurements, NOT for currency
);Text: CHAR vs VARCHAR vs TEXT
CHAR(n) pads shorter values with spaces to always occupy n characters; VARCHAR(n) stores only what's given, up to a maximum length; TEXT has no length limit at all. In practice, VARCHAR or TEXT covers almost all real use cases — fixed-width CHAR is rarely the right choice outside of specific legacy formats.
CREATE TABLE users (
country_code CHAR(2), -- always exactly 2 chars, e.g. "US"
username VARCHAR(50), -- up to 50 chars
bio TEXT -- unlimited length
);SQLite's type affinity: the odd one out
SQLite is dynamically typed at the storage level — declaring a column INTEGER only sets a preference, not an enforcement. This is a genuine, documented behavior difference worth knowing before assuming SQLite behaves identically to PostgreSQL or MySQL.
-- in SQLite only:
CREATE TABLE demo (num INTEGER);
INSERT INTO demo VALUES ('hello'); -- this SUCCEEDS in SQLite (stored as text)
-- the same statement would be a TYPE ERROR in PostgreSQL or MySQL