Postgres in depth · Chapter 15 of 65
Moving day
By the end of this chapter you can load a CSV file into Postgres, clean it in a staging table, move the good rows into the real tables with INSERT ... SELECT, and back a database up and restore it with pg_dump and pg_restore. 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
- 3 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
The new design is ready: types, rules, foreign keys, enums and a money domain, built over the last six chapters. But LCO's students still live in the old spreadsheet, and Kabir wants the new site running on the new tables by tonight. Sana exported the students' sheet as a CSV file, old-students.csv, and warned that "some rows are a bit messy".
You cannot type 16 INSERTs by hand, let alone the thousands behind them, and the messy rows would break the new rules halfway through. The move needs a plan: load everything as it is, look at it, clean it, then move it in one statement, and keep a backup of the result.
What you build: 3 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
staging.sql: load the CSV as it is
Create a staging table old_students with five text columns, named like the CSV's header: name, email, phone, city, signed_up. Then load old-students.csv (in this chapter's folder, with a header line) into it with psql's copy command, so all 16 rows arrive exactly as written.
- Task 2 · interview question
Interview: bad-rows.sql, the rows that cannot move
old_students holds the CSV exactly as written. Return the rows that cannot move into students: the email is missing (NULL) or has no @ in it, or signed_up is not something Postgres can read as a timestamptz.
Return three columns: name, email, signed_up, as they are in old_students. Order by name.
- Task 3
move.sql: move the good rows into students
Move every good row of old_students into students in one INSERT ... SELECT. A good row has an email with an @ and a signed_up that reads as a timestamptz. Clean on the way: name without spaces at either end; email without spaces and in lower case; phone without spaces, and NULL when nothing is left; city as it is; signed_up_at from signed_up, read in India time.
Let the identity number the students. After cleaning, two rows are the same student: move them once.
What you will learn
- CSV and \copy: loading a file into a table, and why psql's \copy is not COPY.
- Staging tables: all-text tables that accept anything, so nothing fails on the way in.
- Finding bad rows: pg_input_is_valid, and NULL in a filter again.
- INSERT ... SELECT: cleaning and moving many rows in one statement.
- pg_dump and pg_restore: a backup of a whole database, and getting it back.
More in Designing LCO properly
Build "Moving day" 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.