Every column declares what it can hold and how much space that costs. Push values past the edge of a type, watch FLOAT lose money that DECIMAL keeps, and see why the right type is a correctness decision, not a formality.
SQL types describe the kinds of values a column accepts, but details differ across database engines. Integer types represent whole numbers; decimal or numeric types represent fixed decimal precision where supported; floating-point types approximate many real numbers; text types store strings; and date/time types model temporal values. Choose by required range and operations, not by the shortest type name.
SQLite uses type affinity rather than rigid column typing in ordinary tables: a declaration influences storage but does not enforce the same strict type contract as PostgreSQL. SQLite VARCHAR(20) does not impose a 20-character limit. SQLite STRICT tables add stricter rules, with SQLite-specific type names. Do not assume that a declaration behaves identically across engines.
Binary floating point cannot represent many decimal fractions exactly. For money, use an exact decimal type where the engine supports it, or store an integer number of the smallest currency unit when the scale and range are well-defined. The right choice depends on currency precision, rounding rules, and database behavior.
A timestamp's meaning also matters: is it a local wall-clock time, a date, or an instant on the global timeline? PostgreSQL timestamp with time zone normalizes instants and displays them in the session time zone; it does not retain the original zone name. SQLite has no dedicated date/time storage class, so applications commonly store ISO-8601 text, Julian day values, or Unix timestamps. Document the convention and convert at system boundaries.
For each field, ask: what values are valid, what comparisons are needed, can it be absent, and what precision or time zone is meaningful? Enforce important limits with constraints as well as types. Avoid storing numbers as text when numeric sorting and arithmetic are required. See constraints and NULL handling.
A type limits representation, but it may not capture the whole business rule. A valid percentage may need a range check; an identifier may need a format check; a timestamp may need a stated time zone. Use CHECK constraints where the database supports them and validate user input before storage. For exact decimals, specify precision and scale thoughtfully: a value outside the chosen range can round or fail. Add tests for boundary values and conversions between application types and database types.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “Types are engine-specific contracts”?
What does this module say about “Money, time, and text”?
What does this module say about “Make a type decision”?
Choose between INT, BIGINT, DECIMAL and VARCHAR, and see why storing money in FLOAT silently corrupts your arithmetic.