Tables, Rows, Columns & Types¶

Before you can CRUD anything, you need a place to store it. In a relational database that place is a table.

Anatomy of a Table¶

            ┌──── columns ────┐
            id   title   status
          ┌────┬───────┬────────┐
   rows   │ 1  │ Buy   │ TODO   │
          │ 2  │ Ship  │ DONE   │
          └────┴───────┴────────┘

A table models one kind of thing (tasks, users, orders).
A row is a single record.
A column is a single attribute, with a fixed type.

Choosing Column Types¶

Pick the narrowest type that fits. Good types prevent bad data.

Data	PostgreSQL type
Auto ID	`BIGSERIAL` or `UUID`
Short text	`VARCHAR(n)` / `TEXT`
Long text	`TEXT`
Whole number	`INTEGER`, `BIGINT`
Money	`NUMERIC(10,2)` (never `FLOAT`!)
True/false	`BOOLEAN`
Date + time	`TIMESTAMPTZ`
Fixed set of values	`ENUM` or a `CHECK` constraint

Money rule: never store currency in floating point. 0.1 + 0.2 !== 0.3. Use NUMERIC/DECIMAL.

Our First Table: tasks¶

sql

9 lines

1CREATE TABLE tasks (
2  id          BIGSERIAL PRIMARY KEY,
3  title       VARCHAR(200) NOT NULL,
4  description TEXT,
5  status      VARCHAR(20) NOT NULL DEFAULT 'TODO',
6  due_date    DATE,
7  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
8  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
9);

Each clause matters:

PRIMARY KEY — uniquely identifies the row (more next lesson).
NOT NULL — the column must always have a value.
DEFAULT — value used when none is supplied.

NULL Is Not Zero¶

NULL means "unknown / not provided" — it is not 0 or "". A due_date of NULL means "no due date set", which is different from any actual date. Comparisons with NULL use IS NULL, never = NULL.

Anatomy of a Table¶

┌──── columns ────┐ id title status ┌────┬───────┬────────┐ rows │ 1 │ Buy │ TODO │ │ 2 │ Ship │ DONE │ └────┴───────┴────────┘

A table models one kind of thing (tasks, users, orders).

A row is a single record.

A column is a single attribute, with a fixed type.

Choosing Column Types¶

Pick the narrowest type that fits. Good types prevent bad data.

Data	PostgreSQL type
Auto ID	`BIGSERIAL` or `UUID`
Short text	`VARCHAR(n)` / `TEXT`
Long text	`TEXT`
Whole number	`INTEGER`, `BIGINT`
Money	`NUMERIC(10,2)` (never `FLOAT`!)
True/false	`BOOLEAN`
Date + time	`TIMESTAMPTZ`
Fixed set of values	`ENUM` or a `CHECK` constraint

Money rule: never store currency in floating point. 0.1 + 0.2 !== 0.3. Use NUMERIC/DECIMAL.

Our First Table: tasks¶

sql

9 lines

1CREATE TABLE tasks (
2  id          BIGSERIAL PRIMARY KEY,
3  title       VARCHAR(200) NOT NULL,
4  description TEXT,
5  status      VARCHAR(20) NOT NULL DEFAULT 'TODO',
6  due_date    DATE,
7  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
8  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
9);

Each clause matters:

PRIMARY KEY — uniquely identifies the row (more next lesson).

NOT NULL — the column must always have a value.

DEFAULT — value used when none is supplied.