{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"rows-columns-and-types","title":"What a column type actually buys you","url":"https://addaly.com/learn/data-and-sql/rows-columns-and-types","position":3,"of":66,"minutes":8,"answer":"A column type is a refusal the database makes on your behalf; choose numeric for money, because a binary float cannot hold 0.10 exactly and the errors add up.","keyIdea":"A column type is a refusal the database makes on your behalf; choose numeric for money, because a binary float cannot hold 0.10 exactly and the errors add up."},"body":{"format":"markdown","text":"## A type is a refusal\n\nEvery column in a table has a declared type, and the type is not documentation. It is a rule the database enforces at the moment of writing. `class_level int` does not mean \"we intend to put numbers here\". It means an attempt to store `Grade Nine` fails, loudly, in front of the person who typed it, instead of quietly three months later in a total.\n\nThat is the whole value proposition. A type moves an error from the moment of *reading* (where it is expensive, subtle and yours) to the moment of *writing*, where it is cheap and belongs to whoever caused it.\n\n## The types you will actually use\n\n**Whole numbers.** `int` holds roughly ±2.1 billion. `bigint` holds ±9.2 quintillion. Use `int` for a class level or a quantity, `bigint` for anything that counts events or acts as an id. Tables have overflowed a 32-bit id column in production more than once; the fix is a long outage.\n\n> Figure: A type moves the error from read time to write time. A two by two grid, rows Typed column and Text column, columns 'Grade 9' is typed and The total is read: Typed column and 'Grade 9' is typed: Refused; Typed column and The total is read: Right; Text column and 'Grade 9' is typed: Stored; Text column and The total is read: Wrong. The typed column produces an error in front of the person who caused it, at the moment they caused it. The text column produces no error at all: it produces a class-wise total that is short by eleven students three months later, and looks perfectly ordinary.\n\n**Money and exact decimals.** `numeric(10,2)` in Postgres, `DECIMAL(10,2)` elsewhere. Ten digits total, two after the point. It is stored exactly.\n\n**Approximate numbers.** `real` and `double precision` are binary floating point. They are fast, they are right for sensor readings and physics, and they are wrong for money:\n\n```sql\nSELECT 0.1::float + 0.2::float = 0.3::float;   -- false\nSELECT 0.1::numeric + 0.2::numeric = 0.3::numeric;  -- true\n```\n\nThat first line is not a bug in the database. One tenth has no exact representation in binary, exactly as one third has none in decimal. Sum a hundred thousand float prices and the total will be a few paise or cents away from the truth, in a direction nobody can predict. Finance teams notice.\n\n**Text.** In Postgres, `text` and `varchar(n)` are the same machinery; `varchar(n)` merely adds a length check. Use `text` unless a length limit is a genuine business rule. A `varchar(20)` on a name column is not a rule, it is a guess about the world's names, and it will be wrong.\n\n**Dates and times.** `date` for a calendar day. `timestamptz` for a moment in time. `timestamp` (without time zone) is the one that silently causes trouble, and the next module gives it a lesson of its own.\n\n**Boolean.** `true`, `false`, and (this is the part people forget) `NULL`. A three-state column pretending to be two-state is a recurring source of wrong counts.\n\n**Identifiers.** `bigserial` or `generated always as identity` for an auto-incrementing id; `uuid` when ids must be generated in several places without collisions.\n\n## The constraints that go next to the type\n\nA type says what shape the value has. Constraints say what values are allowed.\n\n```sql\nCREATE TABLE payments (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  student_id  bigint       NOT NULL REFERENCES students(id),\n  paid_on     date         NOT NULL DEFAULT current_date,\n  amount      numeric(10,2) NOT NULL CHECK (amount > 0),\n  method      text         NOT NULL CHECK (method IN ('cash','upi','card','transfer')),\n  reference   text         UNIQUE\n);\n```\n\nRead what each buys. `NOT NULL` refuses a payment with no amount. `CHECK (amount > 0)` refuses a negative charge dressed as a payment: refunds get their own sign convention or their own table, decided once, on purpose. `CHECK (method IN (...))` is a small controlled vocabulary that stops `UPI`, `Upi` and `upi ` becoming three payment methods. `UNIQUE` on the provider's reference means re-running an import cannot create a duplicate charge; the second insert fails rather than doubling your revenue.\n\nSix lines of schema removing six classes of bug permanently, for free, at write time. This is the cheapest quality work available anywhere in data.\n\n## Where types stop helping\n\nBe honest about the limit. A type checks *shape*, never *meaning*. `amount numeric(10,2)` accepts 4,500 rupees typed into a column that holds dollars. `paid_on date` accepts 2019 in a table that only covers this year. `class_level int` accepts 97.\n\nFor meaning you need CHECK constraints where the rule is stable (`class_level BETWEEN 1 AND 12`), and you need the profiling habits from the cleaning lessons where it is not. A well-typed table is not a correct table. It is a table with one entire category of nonsense already excluded, so your attention is free for the categories that are left.\n\n## The rule of thumb\n\nChoose the narrowest type that can hold every legitimate value, and no wider. Narrow types reject bad data, use less memory, and (because the database knows more about them) let the query planner make better decisions later. `text` for everything is the choice that costs you at every subsequent stage."},"check":{"question":"A finance report sums 40,000 order totals stored in a `double precision` column and lands a few cents away from the payment provider's figure, with the gap varying in size and direction from run to run. What is the most likely cause?","options":[{"text":"Rounding is being applied at display time but not at storage time, so the report and the provider round each total differently before summing.","correct":false,"tempting":"Display rounding produces a consistent, explainable difference; the give-away here is that the direction and size vary unpredictably."},{"text":"The column is too narrow and some large totals are overflowing, losing precision at the top end.","correct":false,"tempting":"Overflow in a double happens around 10^308 and would produce enormous errors, not a few cents."},{"text":"Some rows were inserted with NULL totals, and SUM is skipping them.","correct":false,"tempting":"Skipped NULLs would produce a consistent shortfall of whole order amounts, not a drift of cents."},{"text":"Binary floating point cannot represent most decimal fractions exactly, so each stored total is minutely off and the errors accumulate.","correct":true}]},"prev":{"slug":"a-place-to-practise","title":"Getting a database onto the machine you have","url":"https://addaly.com/learn/data-and-sql/a-place-to-practise"},"next":{"slug":"keys-and-identity","title":"Keys: how a database knows one thing from another","url":"https://addaly.com/learn/data-and-sql/keys-and-identity"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'What a column type actually buys you', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/rows-columns-and-types (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"What a column type actually buys you","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":1,"slug":"tables-types-and-the-question","title":"Tables, types and the question you are actually asking"},"answer":null,"keyIdea":"A column type is a refusal the database makes on your behalf; choose numeric for money, because a binary float cannot hold 0.10 exactly and the errors add up.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"cols":["‘Grade 9’ is typed","The total is read"],"rows":["Typed column","Text column"],"type":"matrix","cells":[["Refused","Right"],["Stored","Wrong"]],"title":"A type moves the error from read time to write time","caption":"The typed column produces an error in front of the person who caused it, at the moment they caused it. The text column produces no error at all: it produces a class-wise total that is short by eleven students three months later, and looks perfectly ordinary.","cellLabels":[["rejected at once","nothing to clean up"],["accepted in silence","short by eleven"]]},"alt":"Figure: A type moves the error from read time to write time. A two by two grid, rows Typed column and Text column, columns ‘Grade 9’ is typed and The total is read: Typed column and ‘Grade 9’ is typed: Refused; Typed column and The total is read: Right; Text column and ‘Grade 9’ is typed: Stored; Text column and The total is read: Wrong. The typed column produces an error in front of the person who caused it, at the moment they caused it. The text column produces no error at all: it produces a class-wise total that is short by eleven students three months later, and looks perfectly ordinary."}},"blocks":[{"t":"h2","id":"a-type-is-a-refusal","runs":[{"text":"A type is a refusal"}]},{"t":"p","runs":[{"text":"Every column in a table has a declared type, and the type is not documentation. It is a rule the database enforces at the moment of writing. "},{"text":"class_level int","code":true},{"text":" does not mean “we intend to put numbers here”. It means an attempt to store "},{"text":"Grade Nine","code":true},{"text":" fails, loudly, in front of the person who typed it, instead of quietly three months later in a total."}]},{"t":"p","runs":[{"text":"That is the whole value proposition. A type moves an error from the moment of "},{"text":"reading","i":true},{"text":" (where it is expensive, subtle and yours) to the moment of "},{"text":"writing","i":true},{"text":", where it is cheap and belongs to whoever caused it."}]},{"t":"h2","id":"the-types-you-will-actually-use","runs":[{"text":"The types you will actually use"}]},{"t":"p","runs":[{"text":"Whole numbers.","b":true},{"text":" "},{"text":"int","code":true},{"text":" holds roughly ±2.1 billion. "},{"text":"bigint","code":true},{"text":" holds ±9.2 quintillion. Use "},{"text":"int","code":true},{"text":" for a class level or a quantity, "},{"text":"bigint","code":true},{"text":" for anything that counts events or acts as an id. Tables have overflowed a 32-bit id column in production more than once; the fix is a long outage."}]},{"t":"p","runs":[{"text":"Money and exact decimals.","b":true},{"text":" "},{"text":"numeric(10,2)","code":true},{"text":" in Postgres, "},{"text":"DECIMAL(10,2)","code":true},{"text":" elsewhere. Ten digits total, two after the point. It is stored exactly."}]},{"t":"p","runs":[{"text":"Approximate numbers.","b":true},{"text":" "},{"text":"real","code":true},{"text":" and "},{"text":"double precision","code":true},{"text":" are binary floating point. They are fast, they are right for sensor readings and physics, and they are wrong for money:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT 0.1::float + 0.2::float = 0.3::float;   -- false\nSELECT 0.1::numeric + 0.2::numeric = 0.3::numeric;  -- true"},{"t":"p","runs":[{"text":"That first line is not a bug in the database. One tenth has no exact representation in binary, exactly as one third has none in decimal. Sum a hundred thousand float prices and the total will be a few paise or cents away from the truth, in a direction nobody can predict. Finance teams notice."}]},{"t":"p","runs":[{"text":"Text.","b":true},{"text":" In Postgres, "},{"text":"text","code":true},{"text":" and "},{"text":"varchar(n)","code":true},{"text":" are the same machinery; "},{"text":"varchar(n)","code":true},{"text":" merely adds a length check. Use "},{"text":"text","code":true},{"text":" unless a length limit is a genuine business rule. A "},{"text":"varchar(20)","code":true},{"text":" on a name column is not a rule, it is a guess about the world’s names, and it will be wrong."}]},{"t":"p","runs":[{"text":"Dates and times.","b":true},{"text":" "},{"text":"date","code":true},{"text":" for a calendar day. "},{"text":"timestamptz","code":true},{"text":" for a moment in time. "},{"text":"timestamp","code":true},{"text":" (without time zone) is the one that silently causes trouble, and the next module gives it a lesson of its own."}]},{"t":"p","runs":[{"text":"Boolean.","b":true},{"text":" "},{"text":"true","code":true},{"text":", "},{"text":"false","code":true},{"text":", and (this is the part people forget) "},{"text":"NULL","code":true},{"text":". A three-state column pretending to be two-state is a recurring source of wrong counts."}]},{"t":"p","runs":[{"text":"Identifiers.","b":true},{"text":" "},{"text":"bigserial","code":true},{"text":" or "},{"text":"generated always as identity","code":true},{"text":" for an auto-incrementing id; "},{"text":"uuid","code":true},{"text":" when ids must be generated in several places without collisions."}]},{"t":"h2","id":"the-constraints-that-go-next-to-the-type","runs":[{"text":"The constraints that go next to the type"}]},{"t":"p","runs":[{"text":"A type says what shape the value has. Constraints say what values are allowed."}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE payments (\n  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  student_id  bigint       NOT NULL REFERENCES students(id),\n  paid_on     date         NOT NULL DEFAULT current_date,\n  amount      numeric(10,2) NOT NULL CHECK (amount > 0),\n  method      text         NOT NULL CHECK (method IN ('cash','upi','card','transfer')),\n  reference   text         UNIQUE\n);"},{"t":"p","runs":[{"text":"Read what each buys. "},{"text":"NOT NULL","code":true},{"text":" refuses a payment with no amount. "},{"text":"CHECK (amount > 0)","code":true},{"text":" refuses a negative charge dressed as a payment: refunds get their own sign convention or their own table, decided once, on purpose. "},{"text":"CHECK (method IN (...))","code":true},{"text":" is a small controlled vocabulary that stops "},{"text":"UPI","code":true},{"text":", "},{"text":"Upi","code":true},{"text":" and "},{"text":"upi ","code":true},{"text":" becoming three payment methods. "},{"text":"UNIQUE","code":true},{"text":" on the provider’s reference means re-running an import cannot create a duplicate charge; the second insert fails rather than doubling your revenue."}]},{"t":"p","runs":[{"text":"Six lines of schema removing six classes of bug permanently, for free, at write time. This is the cheapest quality work available anywhere in data."}]},{"t":"h2","id":"where-types-stop-helping","runs":[{"text":"Where types stop helping"}]},{"t":"p","runs":[{"text":"Be honest about the limit. A type checks "},{"text":"shape","i":true},{"text":", never "},{"text":"meaning","i":true},{"text":". "},{"text":"amount numeric(10,2)","code":true},{"text":" accepts 4,500 rupees typed into a column that holds dollars. "},{"text":"paid_on date","code":true},{"text":" accepts 2019 in a table that only covers this year. "},{"text":"class_level int","code":true},{"text":" accepts 97."}]},{"t":"p","runs":[{"text":"For meaning you need CHECK constraints where the rule is stable ("},{"text":"class_level BETWEEN 1 AND 12","code":true},{"text":"), and you need the profiling habits from the cleaning lessons where it is not. A well-typed table is not a correct table. It is a table with one entire category of nonsense already excluded, so your attention is free for the categories that are left."}]},{"t":"h2","id":"the-rule-of-thumb","runs":[{"text":"The rule of thumb"}]},{"t":"p","runs":[{"text":"Choose the narrowest type that can hold every legitimate value, and no wider. Narrow types reject bad data, use less memory, and (because the database knows more about them) let the query planner make better decisions later. "},{"text":"text","code":true},{"text":" for everything is the choice that costs you at every subsequent stage."}]}],"check":{"question":[{"text":"A finance report sums 40,000 order totals stored in a "},{"text":"double precision","code":true},{"text":" column and lands a few cents away from the payment provider’s figure, with the gap varying in size and direction from run to run. What is the most likely cause?"}],"options":[{"text":[{"text":"Rounding is being applied at display time but not at storage time, so the report and the provider round each total differently before summing."}],"correct":false,"tempting":[{"text":"Display rounding produces a consistent, explainable difference; the give-away here is that the direction and size vary unpredictably."}]},{"text":[{"text":"The column is too narrow and some large totals are overflowing, losing precision at the top end."}],"correct":false,"tempting":[{"text":"Overflow in a double happens around 10^308 and would produce enormous errors, not a few cents."}]},{"text":[{"text":"Some rows were inserted with NULL totals, and SUM is skipping them."}],"correct":false,"tempting":[{"text":"Skipped NULLs would produce a consistent shortfall of whole order amounts, not a drift of cents."}]},{"text":[{"text":"Binary floating point cannot represent most decimal fractions exactly, so each stored total is minutely off and the errors accumulate."}],"correct":true,"tempting":null}],"order":[1,2,3,0],"why":null},"prev":{"slug":"a-place-to-practise","title":"Getting a database onto the machine you have","minutes":8},"next":{"slug":"keys-and-identity","title":"Keys: how a database knows one thing from another","minutes":8}}}