Text, numbers, money and dates without regret

Most type decisions do not matter. Four of them matter enormously, and each has produced real, expensive bugs: money, time, text, and the column somebody made text because it was easier.

Money

Never float. Never double precision. Never real.

SELECT 0.1::float8 + 0.2::float8            AS float_sum,
       (0.1::float8 + 0.2::float8 = 0.3)    AS float_equals_point3,
       0.1::numeric + 0.2::numeric          AS numeric_sum,
       (0.1::numeric + 0.2::numeric = 0.3)  AS numeric_equals_point3;

      float_sum      | float_equals_point3 | numeric_sum | numeric_equals_point3
---------------------+---------------------+-------------+-----------------------
 0.30000000000000004 | f                   |         0.3 | t

Binary floating point cannot represent 0.1 exactly, in any language. This is the same 0.30000000000000004 from the JavaScript and Python courses, in the database.

And it accumulates:

SELECT sum(0.1::float8) AS float_total, sum(0.1::numeric) AS numeric_total
FROM generate_series(1, 10000);

    float_total     | numeric_total
--------------------+---------------
 1000.0000000001588 |        1000.0

Ten thousand additions of ten paise. The float is wrong by 0.00000000016, which is nothing until it is a bank reconciliation that will not balance and nobody can find the missing amount.

The two correct answers

price_paise int NOT NULL CHECK (price_paise >= 0)     -- integer paise
price       numeric(12,2) NOT NULL                     -- exact decimal

Integer paise is what this course's dataset uses and what the Full-Stack course uses. Arithmetic is exact integer arithmetic, it is fast, it is 4 bytes, and the unit is in the column name so nobody can forget it. Divide by 100 only when displaying.

numeric is exact decimal arithmetic with a declared scale. Slower than integers — it is software arithmetic, not hardware — and correct. numeric(12,2) holds up to 9,999,999,999.99.

Either is fine. Pick one and put the unit in the name — price_paise, or price_inr. A column called amount has caused more incidents than any type error.

And there is a money type in PostgreSQL. Do not use it: its fractional precision and its formatting depend on a server setting, which means the same data means different things on two servers.

Time

timestamptz, essentially always

SET timezone = 'Asia/Kolkata';
CREATE TEMP TABLE t5 (a timestamp, b timestamptz);
INSERT INTO t5 VALUES ('2026-09-27 10:00', '2026-09-27 10:00');
SELECT a, b FROM t5;

          a          |             b
---------------------+---------------------------
 2026-09-27 10:00:00 | 2026-09-27 10:00:00+05:30

Now read the same two rows from a server in UTC:

SET timezone = 'UTC';
SELECT a AS naive_unchanged, b AS aware_shifted FROM t5;

   naive_unchanged   |     aware_shifted
---------------------+------------------------
 2026-09-27 10:00:00 | 2026-09-27 04:30:00+00

The timestamptz correctly says the same instant in the reader's timezone. The plain timestamp says 10:00 to everybody, which is a different moment for each of them, and is therefore not a moment at all.

Despite the name, timestamptz does not store a timezone. It stores an instant — UTC internally — and renders it in the session's timezone. That is exactly what you want and the name misleads everybody.

Use plain timestamp only for a wall-clock time that is genuinely not an instant: "the shop opens at 09:00" in whatever timezone the shop is in.

date for a calendar day

borrowed_on date NOT NULL,
due_on      date NOT NULL

A due date is a day, not an instant. Storing it as a timestamptz means it has a time — midnight in some timezone — and the Full-Stack course has a whole lesson about the bug that follows: an order placed at 00:30 IST stored against the previous day, because midnight IST is 18:30 UTC the day before.

A calendar date and an instant are different types. The _on / _at naming convention from two lessons ago exists to keep them apart.

interval for a duration

loan_period interval NOT NULL DEFAULT '21 days'

Not an integer of days, which loses the unit.

Text

text, not varchar(n)

In PostgreSQL, text, varchar(n) and varchar are the same implementation with the same performance. varchar(n) adds a length check:

CREATE TEMP TABLE t1 (a varchar(5));
INSERT INTO t1 VALUES ('abcdefgh');

ERROR:  value too long for type character varying(5)

The problem is that the number is a guess. varchar(50) for a name excludes real names. varchar(255) is a MySQL-era habit with no meaning in PostgreSQL. And changing it later is a schema migration.

If a limit is a real business rule, write it as a CHECK, where it is visible and can be changed without altering the column type:

title text NOT NULL CONSTRAINT title_length CHECK (length(title) <= 300)

Never char(n)

CREATE TEMP TABLE c1 (a char(10));
INSERT INTO c1 VALUES ('hi');
SELECT length(a) AS length_fn, octet_length(a) AS octets, a = 'hi        ' AS equals_padded FROM c1;

 length_fn | octets | equals_padded
-----------+--------+---------------
         2 |     10 |             t

It stored ten bytes for two characters, padding with spaces — and then length() says 2 because trailing spaces are ignored in comparisons, while octet_length() says 10 because they are genuinely there.

A type whose stored size and reported length disagree is a type that will confuse somebody. Use text.

The column somebody made text because it was easier

The most common type mistake in real schemas, and it is not a performance problem:

published   text,        -- '1943', '1943-01-01', 'circa 1943', 'unknown'
price       text,        -- '295', '₹295', '295.00', 'free'
is_active   text,        -- 'true', 'TRUE', 'yes', 'Y', '1'
pincode     text         -- and this one is correct

text accepts everything, so it validates nothing. Six months later the column holds four formats and every query needs a CASE.

Use the real type and let the database refuse the nonsense — DATE '2026-02-30' is rejected by a date column and cheerfully stored by a text one.

Pincode is the exception, and it is instructive. It is text, correctly, because it is an identifier that happens to be digits — you never do arithmetic on it, leading zeros matter in some countries, and integer would silently drop them. Same for phone numbers and ISBNs.

The test: would you ever add two of them together? If not, it is text with a CHECK, not a number.

pincode text CHECK (pincode ~ '^[1-9][0-9]{5}$')

boolean, and the three-state trap

is_active boolean NOT NULL DEFAULT true

A nullable boolean has three states — true, false and unknown — and code written for two will be wrong for one of them. NOT NULL unless "unknown" is genuinely meaningful.

jsonb when the shape genuinely varies

metadata jsonb

jsonb is parsed, binary, indexable and queryable. json is stored as text and reparsed every time — use jsonb.

It is right for genuinely variable data: per-book extra attributes that differ by category, an audit payload, a third-party API response.

It is wrong as a way of avoiding schema design. A jsonb column holding fields every row has is a table you have not normalised: no constraints, no types, no foreign keys, and queries that are longer and slower. Module 10 covers this decision properly.

Enums, and the alternative

CREATE TYPE membership_kind AS ENUM ('standard','student','senior');

Compact and fast. Awkward to change: adding a value needs ALTER TYPE, and removing one is genuinely difficult.

membership text NOT NULL CHECK (membership IN ('standard','student','senior'))

Easier to change, readable in every client, and adequate. For a small fixed set, either. For a set that will grow, use the CHECK — or a lookup table with a foreign key, which is the most flexible and lets you attach a label and a sort order.

Check your work

Why never float for money. 0.1 + 0.2 is 0.30000000000000004, and 10,000 additions of 0.1 gives 1000.0000000001588.

The two correct answers. Integer paise, or numeric(12,2).

Why not PostgreSQL's money type. Its precision and formatting depend on a server setting.

What to always put in a money column's name. The unit.

What timestamptz actually stores. An instant, rendered in the session timezone — not a timezone.

What a plain timestamp says to two readers in different places. The same wall-clock time, which is two different moments.

When plain timestamp is right. A wall-clock time that is not an instant.

Why a due date is a date. It is a day, not a moment — and storing it as an instant puts it on the wrong day for some timezones.

text versus varchar(n). Same implementation; the number is a guess. Use a CHECK for a real limit.

What char(10) does with 'hi'. Stores 10 bytes, reports length 2.

Why text for everything is a mistake. It accepts everything, so it validates nothing.

Why pincode is correctly text. It is an identifier made of digits — you would never add two together.

Why a nullable boolean is a trap. Three states where the code expects two.

json or jsonb. jsonb — parsed, indexable, queryable.

When jsonb is wrong. When every row has the same fields; that is a table.

Enum or CHECK. CHECK for a set that will grow; an enum is awkward to change.

Practice

1. Compute 0.1 + 0.2 as float8 and as numeric. Compare each to 0.3.
2. Sum 0.1 ten thousand times both ways.
3. Create a table with a float price, insert twenty prices, sum them, and compare with the integer-paise version.
4. Create a timestamptz and a timestamp column. Insert the same literal into both, then change the session timezone and select again.
5. Explain in one sentence why the timestamptz value changed and the timestamp did not.
6. Store a due date as timestamptz at 00:00 IST, then read it in UTC. Note the date.
7. Insert nine characters into a varchar(5).
8. Store 'hi' in a char(10). Compare length, octet_length, and equality with a padded string.
9. Create a published text column and insert '1943', 'circa 1943' and 'unknown'. Then try to find everything published before 1950.
10. Do the same with a date column and try to insert 'circa 1943'.
11. Store a pincode as integer and insert '012345'. Explain the result.
12. Add a regex CHECK for an Indian pincode and test it both ways.
13. Create a nullable boolean, insert NULL, and write a query that gets the wrong answer because of it.
14. Create an enum, then add a value to it. Then try to remove one.
15. Go through a schema of your own and find one column whose type is a guess.

Official documentation

• PostgreSQL — Numeric types — Including the explicit warning about floating point and the note not to use money.
• PostgreSQL — Date/time types — What timestamptz stores, and the timezone handling.
• PostgreSQL — Character types — The statement that text and varchar perform identically, and the char(n) padding.
• PostgreSQL — JSON types — json versus jsonb, with the indexing options.
• PostgreSQL — Enumerated types — Including what it takes to change one.
• PostgreSQL — Don't do this — The project wiki's own list. money, char(n), timestamp and varchar(n) are all on it.

Next: changing a schema that already has data in it.