Topic

Tables & columns

Definition

A table is a named collection of rows that all share the same fixed set of typed columns, and each column definition fixes a name, a data type, and any constraints for every row.

In simpler words

A table is a strict spreadsheet: every row has the same columns, and each column only accepts one kind of value.

Everything else in this week — keys, joins, TypeORM entities — is just more structure on top of tables and columns. Read raw SQL before you read decorators.

Open official example (opens in new tab)

After this you can

Read a CREATE TABLE statement and describe the resulting shape
Name the common Postgres types (uuid, varchar, text, timestamptz)
Explain NOT NULL, UNIQUE, and DEFAULT as column-level rules

What CREATE TABLE actually says

Definition

A CREATE TABLE statement declares a table name and an ordered list of columns, each with a data type and optional constraints, before any row exists.

In simpler words

It is the blueprint: nothing is stored yet, but every future row must match this shape.

A typical first migration creates the core tables this way — read it like a spec, not like magic.

Every column line has the same anatomy: name, type, then constraints (NOT NULL is implicit unless the column is nullable).

tickets table

CREATE TABLE "tickets" (
  "id" uuid NOT NULL DEFAULT gen_random_uuid(),
  "title" character varying(200) NOT NULL,
  "description" text,
  "status" character varying(32) NOT NULL DEFAULT 'open',
  "assignee_id" uuid,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT "PK_tickets" PRIMARY KEY ("id")
);

description and assignee_id omit NOT NULL — they are the only nullable columns on this table.

Choosing column types

Definition

A column’s data type constrains which values Postgres accepts, how much space it reserves, and which operations (sorting, comparing, indexing) are valid on it.

In simpler words

The type is a promise: “this column is always a UUID / always text / always a timestamp,” so the database can reject anything that breaks that promise.

uuid — used for every primary key here; gen_random_uuid() (from the pgcrypto extension) generates one automatically.

character varying(n) — bounded text, good for titles and emails where a max length is a real business rule.

text — unbounded text, used for ticket.description where a length cap would be arbitrary.

timestamptz — always store timestamps with timezone; created_at/updated_at both use it with a now() default.

UUID primary key needs the extension

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- then columns can default to gen_random_uuid()

Without pgcrypto, gen_random_uuid() does not exist and the default fails.

Constraints are column-level rules

Definition

A constraint restricts which values a column or row may take, such as forbidding NULL, requiring uniqueness, or supplying a value when none is given.

In simpler words

Constraints are how the table enforces its own rules, so bad data cannot slip in even if application code has a bug.

NOT NULL (the default unless a column is written without it) — description and assignee_id are the exceptions on tickets.

UNIQUE — users.email has UQ_users_email so two accounts can never share an email.

DEFAULT — tickets.status defaults to 'open'; users.role defaults to 'member'; both save a value even if the app forgets to send one.

users.email uniqueness

"email" character varying NOT NULL,
...
CONSTRAINT "UQ_users_email" UNIQUE ("email")

A duplicate INSERT on email raises a Postgres error before any application code runs.

Keep in mind

Read the migration SQL before the entity — the table is the ground truth TypeORM is trying to describe.
A column with no explicit NOT NULL and no default is nullable; check both before assuming.
Prefer a type that matches the real constraint (length, timezone) over a generic “big enough” type.

Test

Check your understanding

At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥80% (enforced by the API) to save progress.

Checking your session…

15 questions · concept 5 · syntax 3 · practical 4 · logic 3

1. What is a Postgres table?

Concept

An unordered set of rows where each row defines its own columnsA materialized view that caches the result of a saved queryA schema namespace that groups several related tables togetherA named relation with a fixed set of typed columns that every row follows

2. Can one column hold different data types per row?

Syntax

No — each column has a declared type that values must satisfyYes — a jsonb column lets each row store a different scalar typeYes — a text column accepts numbers, dates, and booleans interchangeablyOnly columns without a NOT NULL constraint may vary their type per row

3. What does CREATE TABLE define?

Practical

Only the table name; columns are added later with INSERTThe table name, columns, types, and constraintsThe rows and their initial values for the tableThe indexes and query plans used to read the table

4. How does a TypeORM entity relate to a table?

Logic

It generates the table automatically at import time without migrationsIt is a runtime copy of the table rows cached in memoryIt describes the SQL table shape in TypeScript metadataIt is a validation DTO that only shapes incoming HTTP requests

5. What is a NOT NULL column?

Concept

A column that substitutes a default value when one is missingA column that also rejects empty strings and zero-length valuesA column that rejects missing values on insert/updateA column that must additionally be unique across all rows

6. Why prefer explicit column types?

Practical

They document the contract and prevent invalid dataThey let Postgres skip building indexes on those columnsThey allow the same column to switch types as data growsThey remove the need to write WHERE clauses when querying

7. What does ALTER TABLE typically change?

Syntax

The rows stored in the table, such as updating a valueThe database-wide settings shared by every tableThe roles and permissions allowed to read the tableExisting table structure such as adding a column

8. Are CREATE TABLE and CRUD the same?

Logic

No — CREATE TABLE is DDL; CRUD refers to row operationsYes — CREATE TABLE is the C (Create) in CRUDNo — but TRUNCATE is the D (Delete) verb in CRUDNo — CRUD only covers SELECT and INSERT, not schema or updates

9. What happens if you insert a wrong type into a typed column?

Concept

Postgres rejects the value (or fails cast) rather than silently storing arbitrary shapesPostgres stores the value as text and flags the row for later reviewPostgres truncates the value to fit the column type and continuesPostgres accepts it as long as the column has no NOT NULL constraint

10. Which claim about JSON columns is still true?

Practical

jsonb columns let each row declare its own extra table columnsEven jsonb columns live inside a typed table definitionjsonb columns are stored outside the table in a separate document storejsonb columns cannot participate in indexes or WHERE filters

11. Which insert will this table reject?

Conceptintermediate

CREATE TABLE tickets (
  id uuid PRIMARY KEY,
  title text NOT NULL,
  priority int
);

INSERT INTO tickets (id) VALUES (...), because title is NOT NULL and would be missingINSERT that sets priority to NULL, because int columns cannot store NULLINSERT that provides id and title but omits priority, because all columns are mandatoryINSERT that supplies a uuid literal for id, because PRIMARY KEY values are auto-generated

12. What happens when this statement runs against the table above?

Syntaxadvanced

INSERT INTO tickets (id, title, priority)
VALUES ('11111111-1111-1111-1111-111111111111', 'Bug', 'high');

It succeeds by casting "high" to its character codes and storing that integerIt stores NULL in priority because "high" could not be parsed as a numberPostgres raises a type error because priority is int but "high" is textIt rounds "high" to the nearest valid int and stores it

13. What does this statement do to existing rows?

Practicalintermediate

ALTER TABLE tickets ADD COLUMN priority int DEFAULT 1;

Adds the column but leaves existing rows NULL until they are updatedAdds the column and applies the default only to rows inserted afterwardAdds a priority column and sets existing rows to the default of 1Rewrites every existing row so all columns hold the value 1

14. What is true about the payload column?

Logicadvanced

CREATE TABLE events (
  id uuid PRIMARY KEY,
  payload jsonb
);

It is a typed jsonb column inside a fixed table schemaEach row may store JSON of a different shape, so the table has no fixed columnsThe payload column is stored in a separate NoSQL engine bundled with PostgresAdding jsonb turns the whole events table into a schemaless collection

15. What is created_at set to when you omit it on insert?

Conceptintermediate

CREATE TABLE tickets (
  id uuid PRIMARY KEY,
  created_at timestamptz DEFAULT now()
);

The time the table was created, captured once at CREATE TABLEThe current timestamp, from the DEFAULT now()NULL, because DEFAULT only applies when you pass DEFAULT explicitlyThe server start time, since now() is evaluated at boot

Checking your session…