Data Types

Definition

A data type constrains what kind of value a column can hold and how the engine stores, compares, and indexes it. Choosing the right type is a design decision with real correctness and performance consequences, not a formality.

How It Works — The Core Families

• Numeric: INT/INTEGER (whole numbers), DECIMAL(p,s)/NUMERIC(p,s) (exact fixed-point — essential for money), FLOAT/DOUBLE (approximate, binary floating point — never use for currency).
• String: CHAR(n) (fixed-length, space-padded), VARCHAR(n) (variable-length up to n), TEXT/CLOB (large, effectively unbounded text).
• Date/Time: DATE, TIME, TIMESTAMP/DATETIME — storing calendar/clock values as a real date type (not as a string) enables correct sorting and date arithmetic.
• Boolean: BOOLEAN where supported (PostgreSQL, MySQL); emulated with TINYINT/BIT elsewhere (SQL Server).

Example: storing a price as FLOAT seems fine until you compute 0.1 + 0.2 and get 0.30000000000000004 due to binary floating-point rounding — exactly why DECIMAL(10,2) is the correct choice for money, trading a little storage/performance for exact decimal arithmetic.

Edge Cases and Pitfalls

• Using FLOAT/DOUBLE for money is a classic, serious bug class — rounding errors accumulate and can cause off-by-a-cent (or more) discrepancies that matter a great deal in financial contexts.
• CHAR(n) silently pads shorter values with trailing spaces up to length n; comparing a CHAR(10) value to an otherwise-identical VARCHAR value can behave unexpectedly if trailing-space handling isn't accounted for.
• Storing dates as plain strings ('2026-09-14' in a VARCHAR column) loses correct chronological sorting (string sort ≠ date sort once formats or lengths vary) and date-arithmetic functions.
• Under-sizing a VARCHAR(n) (e.g. VARCHAR(20) for a field that occasionally needs 30 characters) causes silent truncation or insert failure depending on the engine's strict-mode settings.

Key Takeaways

• Pick numeric types by exactness need: DECIMAL for money and anything requiring exact arithmetic, FLOAT/DOUBLE only for genuinely approximate scientific values.
• Use real DATE/TIME/TIMESTAMP types instead of strings for anything date-related, to get correct sorting and arithmetic for free.
• CHAR pads with spaces; VARCHAR doesn't — the difference matters for both storage and comparison behavior.