Postgres in depth · Chapter 13 of 65

Changing a table while people use it

By the end of this chapter you can change a table that already holds data and serves users: add and change columns, convert types with USING, and add a rule without stopping the site. 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

Until now every redesign was easy: the tables were empty, so you dropped them and created them again. Not any more. Instructors have started adding courses through Priya's new dashboard, the beta signup form writes into students, and the mobile team's live_classes table is in use. Dropping any of them would lose real rows.

Kabir wants courses in Hindi as well as English. Sana wants to know who referred whom, and which students closed their accounts. The mobile team stored class times without a time zone. And student cities still accept anything, including "Bangalore", which is not in cities. All of it has to change in place, while people use the tables.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    language.sql: courses in Hindi too

    Kabir wants to record each course's language. Add a column language to courses: text, required, default 'English'. The courses already in the table get 'English'; nothing else changes. Change the table in place: its rows and the tables that point at it must stay.

  2. Task 2

    referrals.sql: who referred whom, and who left

    Add two columns to students. referred_by: a bigint, optional, the id of the student who referred this one; when that student is deleted, it becomes NULL. deleted_at: a timestamptz, optional, the moment a student closed their account.

    Existing students get NULL in both. Change the table in place.

  3. Task 3

    live-times.sql: class times with a time zone

    live_classes stores starts_at and ends_at as timestamp without a time zone, and the mobile app wrote them in UTC. Change both columns to timestamptz so they hold the right moments: the React from Zero live Q&A 1 that says 2026-02-05 13:30:00 started at 2026-02-05 19:00:00+05:30 in India.

    Change the table in place; the rows, their ids and the check that a class ends after it starts must stay.

  4. Task 4 · interview question

    Interview: city-link.sql, a foreign key on a live table

    Make students.city and instructors.city reference cities (name), the safe way for big live tables: add each foreign key NOT VALID first (instant, new rows are checked at once), fix the rows that break it, then validate it.

    Two students have the city 'Bangalore', which is not in cities: change it to 'Bengaluru'. Afterwards both rules are validated, every other row is unchanged, and a student with a city that is not in cities is refused.

What you will learn

  • ALTER TABLE: add, drop, rename and change columns and rules.
  • Fast defaults: why adding a column with a default can take a millisecond or a long rewrite.
  • USING: converting a column's values to a new type, and AT TIME ZONE.
  • NOT VALID then VALIDATE: adding a rule to a big table without a long pause.
  • Locks, lightly: why ALTER TABLE makes other queries wait.

More in Designing LCO properly

Build "Changing a table while people use it" 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.