Postgres in depth · Chapter 3 of 65

The courses table

By the end of this chapter you can create a table with the right type for every column, describe it, drop it safely, and explain why a table's rows have no order. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Day one: an empty server
  • 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

Kabir wants LCO's first courses online tonight. For each course the website needs a title, the teacher, a level, a price, whether it is published, and the day it launched. The students who buy them need a name, an email, a phone number, a city and a signup date.

The spreadsheet let any cell hold anything: one price said free, another 1,999, a launch date said next week. A schema from chapter 2 is only a folder; nothing in it can hold a row yet. You need tables, and every column of a table says what kind of value it accepts.

What you build: 4 tasks

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

  1. Task 1

    courses.sql: the courses table

    Create LCO's courses table, with these seven columns in this order: id (whole number), title (text), instructor (text), level (text), price (whole number of rupees), is_published (true or false) and launched_on (a calendar day).

    Use the types integer, text, boolean and date. No rows yet, and no other columns or rules.

  2. Task 2

    students.sql: the students table

    Create LCO's students table, with these six columns in this order: id (whole number), name (text), email (text), phone (text), city (text) and signed_up_on (a calendar day).

    Use the types integer, text and date. No rows yet, and no other columns or rules.

    Careful: a phone number like +91 9810012345 has a plus sign and a space, so it is not a number.

  3. Task 3

    cleanup.sql: Kabir's leftover tables

    Kabir's experiments left three tables next to courses: courses_old, test1, and one named exactly Courses Copy (capital C, with a space). Drop all three.

    Afterwards the only table left in the database is courses, untouched.

    Careful: a name with a capital and a space was created with quotes, and must be dropped with the same quotes.

  4. Task 4 · interview question

    Interview: rebuild.sql, a script that can run twice

    Priya wants a setup script for the table course_drafts with three columns in this order: title (text), price (integer) and ready (boolean).

    The script must work on a database where course_drafts does not exist, and on one where an old course_drafts exists with the wrong columns. In both cases it must run twice in a row without an error, and leave course_drafts with exactly the three columns above.

    Careful: creating a table only if it does not exist yet would keep the old, wrong table.

What you will learn

  • CREATE TABLE: a table's name and its columns.
  • Types: integer, text, boolean and date, and what each refuses.
  • Describing a table: \d in psql, and information_schema.columns.
  • DROP TABLE: removing a table, and IF EXISTS for scripts.
  • A table has no order: why rows come back in any order.

More in Day one: an empty server

Build The courses table 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.