Lesson 2 · Foundations

Data types, schema & constraints

The shape of a table — and the rules the database enforces for you.

Your win: read a CREATE TABLE, name the common types and what NULL means, and list the constraints that keep data correct no matter what the app does — all from your real emails DDL.

Every column has a type

Postgres is strongly typed: each column declares a data type and the database rejects anything that doesn't fit. The ones you'll meet constantly:

Type	Holds	In our repo
`text`	variable-length string	ULID primary keys, subjects
`int` / `bigint`	integers	counts, offsets
`boolean`	true/false	flags
`timestamptz`	timestamp with time zone	`created_at`, `deleted_at`
`jsonb`	structured JSON (binary)	`emails.content`
`text[]`	an array	`email_recipients`

`NULL` is the absence of a value

NULL is not zero, not "" NULL means "unknown / no value". It uses three-valued logic: NULL = NULL is not true — it's NULL. That's why you test col IS NULL, never col = NULL. (In Go this is pgtype's Null status — the same idea you met as zero values in the Go course.)

Constraints — the database's last line of defence

Constraints are rules Postgres enforces on every write, regardless of app bugs. Your emails table uses most of them:

migrations/notificationmgmt/1004_migrate.up.sql

CREATE TABLE emails (
    email_id      text,
    status        text,
    content       jsonb,
    email_recipients text[],
    created_at    timestamptz NOT NULL DEFAULT (now() at time zone 'utc'),
    deleted_at    timestamptz,
    resource_path text NOT NULL DEFAULT autofillresourcepath(),
    CONSTRAINT pk__emails PRIMARY KEY (email_id),                 -- unique + not null
    CONSTRAINT emails_status_check CHECK (status = ANY(ARRAY['QUEUED','SENT',...]))
);
-- email_recipients table adds: CONSTRAINT fk__email_recipients ... REFERENCES emails(email_id)

Constraint	Guarantees
`PRIMARY KEY`	each row uniquely identified; unique + not-null
`FOREIGN KEY`	a referenced row must exist
`UNIQUE`	no duplicate values in the column(s)
`NOT NULL`	a value is required
`CHECK`	values satisfy a condition (our enum-style `= ANY(ARRAY[...])`)
`DEFAULT`	fills a value when omitted (`now()`, `autofillresourcepath()`)

Anchor — constraints as documentation Our repos encode enums twice: an UPPERCASE Go string constant and a CHECK (status = ANY(ARRAY[...])) in the DDL. The CHECK is the one that actually holds — even a buggy service can't write an invalid status. And DEFAULT autofillresourcepath() auto-stamps the tenant on insert (Part 6).

PostgreSQL — Data Types & Constraints

The authoritative reference for types and the constraint chapter. Skim types you'll use (text, numeric, timestamptz, jsonb, arrays) and the constraints chapter.

→ postgresql.org/docs — data types
→ constraints

Check yourself (from memory)

Q1. In SQL, NULL means…

Unknown / no value — three-valued logic. Test IS NULL, never = NULL.

Q2. A CHECK constraint is used to…

It enforces a condition on values — our status = ANY(ARRAY[...]) works as an enum the DB guarantees.

Q3. Which enforces that every row is unique by id?

A primary key is unique + not-null — our pk__emails on email_id.

Name four constraints Postgres enforces and what each guarantees — with a repo example.

recall, then click to reveal

PRIMARY KEY (unique + not-null identifier — pk__emails on email_id); FOREIGN KEY (referenced row must exist — fk__email_recipients → emails); UNIQUE (no duplicates); NOT NULL (value required); CHECK (condition holds — status = ANY(ARRAY[...]) as an enum); DEFAULT (fill when omitted — resource_path DEFAULT autofillresourcepath(),

created_at DEFAULT
    now()

). The database enforces these regardless of app bugs — the last line of defence.

Want the difference between jsonb and json, or when a text column beats varchar(n)? Ask me.

1. PostgreSQL — Data Types; Constraints.

Data types, schema & constraints

Every column has a type

NULL is the absence of a value

Constraints — the database's last line of defence

PostgreSQL — Data Types & Constraints

Check yourself (from memory)

`NULL` is the absence of a value