SQL Data Types And Numerics

SQL Data Types and Numerics

"It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with numeric values yield exact results where possible, e.g., addition, subtraction, multiplication." — PostgreSQL — Numeric Types

"Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies." — PostgreSQL — Numeric Types (floating-point)

The column type is a promise about what a value means: how much it can hold, whether it is exact, and what comparisons make sense. Picking a type for convenience instead of meaning is how a price ends up a cent short, a surrogate key dies at 2.1 billion, or a legitimate name is rejected by an arbitrary length cap. This skill assumes the set-and-NULL semantics owned by sql-relational-and-null-discipline (a wrong type still propagates NULL and still fails a CHECK the same way) and focuses on the one decision a CREATE TABLE forces: which type, and how wide.

1. Exact vs Approximate Numerics — Money Is NUMERIC, Never Float (the centerpiece)

There are two numeric families and they are not interchangeable. Exact types (NUMERIC, alias DECIMAL) store a value digit-for-digit: "Calculations with numeric values yield exact results where possible, e.g., addition, subtraction, multiplication," and the type "is especially recommended for storing monetary amounts and other quantities where exactness is required" (PostgreSQL — Numeric). Approximate types (REAL, DOUBLE PRECISION, the spelling FLOAT) are binary floating point: they "are inexact, variable-precision numeric types," meaning "some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies" (PostgreSQL — Numeric). The docs state the rule outright: "If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead."

The reason is base-2. A binary float cannot represent the decimal fraction 0.1 exactly — only the nearest representable approximation — and every arithmetic step carries that error forward. It compounds:

-- WRONG — money in binary floating point; each value is an approximation
CREATE TABLE invoice_line (
    description  TEXT,
    amount       DOUBLE PRECISION   -- 0.10 is NOT stored as exactly 0.10
);

-- RIGHT — exact decimal: 2 fractional digits, up to 17 total digits
CREATE TABLE invoice_line (
    description  TEXT,
    amount       NUMERIC(19, 2) NOT NULL   -- standard DECIMAL(19,2) is identical
);

Worked rounding error — sum ten dimes and compare to a dollar:

-- WRONG — floating point: the sum drifts off the exact value
SELECT 0.1::double precision * 10 = 1.0::double precision;   -- may be FALSE
SELECT SUM(amount) FROM invoice_line;  -- 10 rows of 0.10 can total 0.9999999999999999

-- RIGHT — exact decimal: the sum is precisely 1.00, and the equality holds
SELECT 0.1::numeric * 10 = 1.0::numeric;                     -- TRUE
SELECT SUM(amount) FROM invoice_line;  -- 10 rows of 0.10 total exactly 1.00

Across a million rows those sub-cent drifts accumulate into a real discrepancy that no ROUND at the end can reliably undo, because the error is already baked into stored values. Use NUMERIC/DECIMAL(p,s) for currency, tax rates, balances, exact counts, and any quantity a human will reconcile. Reserve DOUBLE PRECISION for genuinely approximate scientific or statistical values (measurements, ratios, coordinates) where a tiny relative error is acceptable and exact equality is never tested.

2. Precision and Scale — and Overflow

NUMERIC(p, s) declares precision p (total significant digits) and scale s (digits after the decimal point). The integer part is therefore bounded by p − s digits, and exceeding it is a hard error, not a silent wrap: "if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised" (PostgreSQL — Numeric). Scale, by contrast, rounds: "a column declared as NUMERIC(3, 1) will round values to 1 decimal place and can store values between -99.9 and 99.9, inclusive" (PostgreSQL — Numeric).

-- WRONG — NUMERIC(5,2) allows only 3 integer digits (max 999.99); 1000.00 errors out
price NUMERIC(5, 2)        -- a $1,000 item cannot be stored

-- RIGHT — size the integer part to the real maximum (here up to 9,999,999.99)
price NUMERIC(9, 2)

Choose p and s from the domain: pick s for the smallest unit you must represent exactly (2 for dollars-and-cents, 4 for many FX rates) and p large enough that p − s covers the biggest integer part you will ever store. Bare NUMERIC with no (p, s) stores any precision but loses the schema-level guard against an out-of-range value.

3. Integer Sizing — Pick by Range, Mind the Ceiling

Integers are fixed-width; the type is a promise about range, and overflow errors rather than wrapping. The standard three (PostgreSQL — Numeric):

The trap is the INTEGER ceiling of +2,147,483,647 — about 2.1 billion. It is a comfortable-looking number that a growing table of orders, events, or log rows will quietly reach, at which point the next INSERT fails.

-- WRONG — a high-volume surrogate key in a 4-byte INTEGER; dies near 2.1 billion
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- RIGHT — 8-byte BIGINT for any key/counter that can grow without a hard cap
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Default to BIGINT for surrogate keys and unbounded counters; INTEGER is fine for bounded foreign attributes; SMALLINT suits small enumerations or ages. (Auto-increment columns are not gap-free — "there may be 'holes' or gaps in the sequence of values ... A value allocated from the sequence is still 'used up' even if a row ... is never successfully inserted" (PostgreSQL — Serial Types); identity/sequence mechanics are owned by sql-generated-and-identity-columns.)

4. CHARACTER VARYING and the VARCHAR(255) Cargo Cult

The standard variable-length string type is CHARACTER VARYING(n) (alias VARCHAR(n)); fixed-length is CHARACTER(n) (CHAR(n)) (PostgreSQL — Data Types). The reflexive VARCHAR(255) on every text column is a cargo cult: 255 is an artifact of an old MySQL row-format encoding boundary, not a business rule, and the limit buys nothing. "There is no performance difference among these three types, apart from increased storage space when using the blank-padded type ... In most situations text or character varying should be used instead" (PostgreSQL — Character Types).

Worse, an arbitrary limit actively rejects valid data — it does not silently truncate on insert: "An attempt to store a longer string into a column of these types will result in an error, unless the excess characters are all spaces" (PostgreSQL — Character Types).

-- WRONG — an arbitrary cap that errors on a legitimate long name or email
full_name  VARCHAR(255)
email      VARCHAR(255)

-- RIGHT — unbounded text, or a length that reflects a REAL domain limit
full_name  VARCHAR        -- (or TEXT) no business maximum on a name
email      VARCHAR(320)   -- 320 = the actual RFC 5321 maximum, a real constraint
country    CHAR(2)        -- genuinely fixed-width: ISO 3166-1 alpha-2 code

Reach for CHARACTER(n) only for genuinely fixed-width codes, and know it blank-pads: "Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way" (PostgreSQL — Character Types). That trailing padding surprises comparisons and round-trips, which is why variable-length is the default.

5. BOOLEAN — Real Type, Uneven Support

Standard SQL has a first-class BOOLEAN type holding "logical Boolean (true/false)" (PostgreSQL — Data Types). Use it; do not encode truth as 0/1 integers or 'Y'/'N' strings, which invite invalid third values and lose three-valued NULL semantics (a BOOLEAN can also be NULL — see sql-relational-and-null-discipline).

-- WRONG — a flag faked as an integer or char; nothing stops the value 2 or 'X'
is_active SMALLINT NOT NULL DEFAULT 0
is_active CHAR(1)  NOT NULL DEFAULT 'N'

-- RIGHT — the real type
is_active BOOLEAN NOT NULL DEFAULT FALSE

Support is uneven, which is the portability story: SQL Server has no BOOLEAN (use BIT), older MySQL maps BOOLEAN to TINYINT(1), and SQLite "does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true)" (SQLite — Datatypes). Write BOOLEAN in portable DDL and route the per-engine spellings to sql-standard-vs-dialect-map.

6. Large Objects — BLOB and CLOB

For data too large for ordinary columns, standard SQL provides BLOB (Binary Large OBject) and CLOB (Character Large OBject). The spellings diverge sharply: PostgreSQL has no BLOB keyword — it uses bytea for "binary data ('byte array')" (PostgreSQL — Data Types) and text in place of CLOB; SQLite uses a BLOB storage class; MySQL and Oracle support BLOB/CLOB directly. Because the keywords are not portable, treat the choice as semantic (binary vs character payload) and route the concrete type name to sql-standard-vs-dialect-map. Storing large blobs inline versus by reference is a schema-design question owned by sql-schema-design-and-normalization.

7. SQLite Dynamic Typing — Declared Types Are Only Advisory (the big deviation)

Every type rule above assumes the engine enforces the declared type. SQLite does not. "SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container" (SQLite — Datatypes). A column's declared type sets only an affinity — a preference — and "the type is recommended, not required. Any column can still store any type of data" (SQLite — Datatypes).

-- In SQLite, the declared types below DO NOT constrain values:
CREATE TABLE t (n INTEGER, amount NUMERIC, flag BOOLEAN);
INSERT INTO t VALUES ('hello', 3.14159, 'maybe');   -- accepted; no error

Two consequences for type choices that work elsewhere:

• Affinity is decided by substring matching on the declared name — "INT" → INTEGER, "CHAR"/"CLOB"/"TEXT" → TEXT, "BLOB" or no type → BLOB, "REAL"/"FLOA"/"DOUB" → REAL, "Otherwise ... NUMERIC" (SQLite — Datatypes). So a standard DDL parses and "works," but enforces nothing.
• There is no exact-decimal storage class. A NUMERIC/DECIMAL column may fall back to REAL (binary float): on insert "the storage class of the text is converted to INTEGER or REAL (in order of preference)" (SQLite — Datatypes). So the money rule from §1 cannot be enforced by the column type in SQLite — store money as integer minor units (cents) or as TEXT, and there is no separate BOOLEAN or DATE class either. Route the deviation details to sql-standard-vs-dialect-map.

8. Portability Snapshot

The standard families — exact NUMERIC/DECIMAL, approximate REAL/DOUBLE PRECISION, the integer trio, CHARACTER VARYING/CHARACTER, BOOLEAN, BLOB/CLOB — are widely shared in spirit; the spellings and enforcement diverge:

The two deviations most likely to bite: SQLite does not enforce declared types (§7), so a schema that "compiles" can silently hold mistyped data; and BOOLEAN/money have no universal spelling, so BOOLEAN, BIT, TINYINT(1) and the decimal/float fallbacks must be reconciled per engine. For every concrete spelling, route to sql-standard-vs-dialect-map.

9. Who Suffers When the Type Is Wrong

A type mistake rarely errors at CREATE TABLE — it surfaces later as a wrong number or a rejected row, and someone downstream pays:

• The accountant reconciling a ledger that is off by a penny, because amounts were stored as DOUBLE PRECISION and ten 0.10 rows summed to 0.9999999999999999 (§1). No error was ever raised; the books just do not balance, and the discrepancy grows with volume.
• The user whose legitimately long name or email is rejected — not truncated — by a VARCHAR(255) that someone copied from a tutorial, never matching any real limit (§4). Support files a bug; the fix is a migration that should never have been needed.
• The on-call engineer paged at 2.1 billion when an INTEGER surrogate key overflows and every INSERT starts failing in production (§3) — a BIGINT from day one would have cost nothing.
• The SQLite maintainer who trusted a NUMERIC column to hold exact money and a BOOLEAN to hold true/false, and finds floats and the string 'maybe' in there, because the declared types were only advice (§7).

Choosing the type that matches the data's meaning is empathy in the schema: the NUMERIC instead of DOUBLE, the BIGINT instead of INTEGER, and the honest length limit are all gifts to whoever maintains this under load.

10. Routing to Related Skills

• sql-relational-and-null-discipline — the foundation: set semantics and three-valued logic that every typed column still obeys (NULL propagation, CHECK passing on UNKNOWN).
• sql-datetime-and-intervals — DATE/TIME/TIMESTAMP/INTERVAL types and time-zone semantics (deliberately out of scope here).
• sql-generated-and-identity-columns — GENERATED ... AS IDENTITY, sequences, and the serial gaps noted in §3.
• sql-constraints-and-integrity — CHECK/NOT NULL/DEFAULT that complement a type to bound a column's domain.
• sql-standard-vs-dialect-map — BOOLEAN/BIT/TINYINT(1), money/decimal-vs-float fallbacks, bytea/BLOB/CLOB, and SQLite's affinity deviation.

11. Reference Files

High-frequency data-type and numeric anti-patterns in LLM-generated SQL, each with wrong/right code and a primary-source citation:

references/common-mistakes.md

Source provenance for every claim in this skill:

references/sources.yaml