Postgres in depth · Chapter 9 of 65
Money, time and ids
By the end of this chapter you can choose the right Postgres type for money, whole numbers, text, moments in time and ids, and explain what goes wrong with the wrong one. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Designing LCO properly
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
LCO starts selling courses with GST, coupons and a payment gateway. Arjun adds up the first week's payments and gets 3.3000000000000003 where the receipt says 3.30. Sana gets a complaint from a student in Dubai whose live class "started an hour early". And Priya finds two courses with the same id, because somebody typed the ids by hand.
The day-one tables from chapters 3 and 4 used the first type that came to mind: price integer has no paise, launched_on date has no time and no time zone, and id integer is a number anyone can type. Before LCO's real data arrives (chapter 15), you redesign the tables properly, starting with the types.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
wallet.sql: two credits, two kinds of number
Sana adds two wallet credits, 0.10 and 0.20 rupees, and wants to see why LCO stores money as numeric. Write one query that returns one row.
Return four columns: float_total (0.10 plus 0.20, both as double precision), numeric_total (0.10 plus 0.20 as numeric), float_exact (whether the float total equals 0.30) and numeric_exact (whether the numeric total equals 0.30). Postgres works out all four.
- Task 2
courses.sql: the new courses table
Create LCO's new courses table, called courses, with these columns and types:
id: a bigint identity that Postgres fills in when the id is left out, but that still accepts an id given by an import. title, instructor and level: text. price: numeric with 10 digits in all and 2 after the point. is_published: boolean. published_at and created_at: moments in time, with the time zone.
No other columns (the day-one launched_on is replaced by published_at). Rules such as NOT NULL come in the next chapter.
- Task 3
signup.sql: a moment from the payment gateway
The payment gateway reports a signup at '2026-02-28 22:00:00+00' (UTC, the +00 at the end). Return one row with two columns:
signed_up_at: that text as a moment in time with the time zone (Postgres prints it in India time), and signup_day: the date of that moment in LCO's time zone (India).
- Task 4 · interview question
Interview: ids.sql, the ids after an import
The coupons table has an id that is a bigint identity, generated by default. The old coupons were imported with their own ids 1, 2 and 3, so the sequence was never asked and still hands out 1 next.
Write the statement that makes the next coupon added without an id get 4. Do not add, change or delete any coupon.
What you will learn
- Whole numbers: integer and bigint, and what happens past their limits.
- Money: numeric(p, s), exact, versus double precision, fast and approximate.
- Text: text, varchar(n) and char(n).
- Time: date, timestamp and timestamptz, and the time zone in between.
- Ids: identity columns, the older serial, and uuid.
More in Designing LCO properly
Build "Money, time and ids" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.