What a column type actually buys you

A type is a refusal

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. 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.

That 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.

The types you will actually use

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.

Money and exact decimals. numeric(10,2) in Postgres, DECIMAL(10,2) elsewhere. Ten digits total, two after the point. It is stored exactly.

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:

SELECT 0.1::float + 0.2::float = 0.3::float;   -- false
SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric;  -- true

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.

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.

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.

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.

Identifiers. bigserial or generated always as identity for an auto-incrementing id; uuid when ids must be generated in several places without collisions.

The constraints that go next to the type

A type says what shape the value has. Constraints say what values are allowed.

CREATE TABLE payments (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id  bigint       NOT NULL REFERENCES students(id),
  paid_on     date         NOT NULL DEFAULT current_date,
  amount      numeric(10,2) NOT NULL CHECK (amount > 0),
  method      text         NOT NULL CHECK (method IN ('cash','upi','card','transfer')),
  reference   text         UNIQUE
);

Read 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.

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.

Where types stop helping

Be 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.

For 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.

The rule of thumb

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 for everything is the choice that costs you at every subsequent stage.