F-2 — Tables, Rows, and the Relational Mental Model

Who this module is for: You completed F-1, have PostgreSQL installed, and can connect with psql. Now you need to understand what a table actually is, what data types exist and which to use, and how to perform every fundamental SQL operation — creating tables, inserting data, reading it, modifying it, and deleting it. No assumed knowledge of SQL.

The Relational Mental Model

A relational database stores data in tables. A table is the simplest structure to grasp: it is a spreadsheet.

• A column (also called a field or attribute) defines a piece of data every row will have — its name and type
• A row (also called a record or tuple) is one complete entity — one user, one order, one product
• A cell is the intersection of a row and column — one specific value

The critical difference from a spreadsheet: in PostgreSQL, every value in a column must match that column's declared type. You cannot store the number 42 in a column declared as TEXT, and you cannot store the string "hello" in a column declared as INTEGER. The database enforces this for you.

Data Types — The Foundation of Every Column

Choosing the right data type for each column is one of the most important decisions you make. The wrong type causes subtle bugs, wastes storage, and breaks operations you will want to do later.

Numbers

When to use which:

• INTEGER — counts, ages, quantities, foreign key references (if table will stay under 2 billion rows)
• BIGINT — auto-incrementing IDs in high-traffic systems, timestamps as milliseconds
• NUMERIC(10, 2) — prices ($999,999.99), percentages, anything where exact decimal matters
• Never use FLOAT or DOUBLE PRECISION for financial data — 0.1 + 0.2 is not exactly 0.3 in floating point

Text

The rule: use TEXT for everything unless you have a specific reason to enforce a maximum length. VARCHAR(255) is a habit from older databases — in PostgreSQL, TEXT is just as efficient and more flexible.

True/False

PostgreSQL accepts TRUE, FALSE, 't', 'f', 'yes', 'no', '1', '0' when inserting.

Dates and Times

The most important rule in this module: always use TIMESTAMPTZ, never bare TIMESTAMP. A bare TIMESTAMP stores a date and time with no timezone context — if your server's timezone changes, or if you have users in multiple timezones, the stored values become ambiguous. TIMESTAMPTZ stores UTC internally and converts to/from the session timezone transparently.

Unique Identifiers

For primary keys: use BIGSERIAL for most tables. Use UUID when you need IDs generated client-side or across distributed systems.

CREATE TABLE

A complete example:

Breaking this down:

• id BIGSERIAL PRIMARY KEY — auto-incrementing integer, unique identifier for each row
• name TEXT NOT NULL — required text field (cannot be left empty)
• description TEXT — optional (can be NULL — the absence of a value)
• price NUMERIC(10,2) NOT NULL — exact decimal, required
• in_stock BOOLEAN NOT NULL DEFAULT true — required, defaults to true if not provided
• created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() — automatically set to the current time

Naming conventions: use snake_case (lowercase with underscores). Do not use spaces, capital letters, or reserved words (order, user, table need to be quoted if used as names — avoid them).

Verify the table was created

INSERT — Adding Rows

RETURNING — get the generated ID back:

This is essential in application code — you insert a row and immediately get back the auto-generated ID to use in subsequent operations.

SELECT — Reading Data

Why avoid SELECT * in production code:

• If you add a column later, SELECT * returns it automatically — potentially including sensitive data
• The query plan cannot be optimised as well without knowing which columns are needed
• Your application code breaks when column order changes

LIMIT — get a subset of rows:

UPDATE — Modifying Existing Rows

⚠️ The most dangerous mistake with UPDATE: forgetting the WHERE clause.

Always write the WHERE clause before running an UPDATE. Always. In psql, you can add LIMIT 1 to test your condition first:

RETURNING works with UPDATE too:

DELETE — Removing Rows

⚠️ Same danger as UPDATE: DELETE FROM products without a WHERE clause deletes every row.

TRUNCATE — faster than DELETE for removing all rows:

Use TRUNCATE when you want to empty a table completely. Use DELETE when you need to remove specific rows or want to use RETURNING.

DROP TABLE — Removing the Table Itself

DROP TABLE is permanent. Unlike DELETE (which removes rows but keeps the table structure), DROP TABLE removes everything — the structure, the data, the indexes, the constraints. There is no undo.

Practical Exercise: A Complete Products Workflow

Work through this from top to bottom in psql:

NULL: The Absence of a Value

NULL means "no value" — it is not zero, it is not an empty string, it is the absence of any value. This trips up almost every beginner.

Summary

The data types you will use 90% of the time:

• BIGSERIAL for primary keys
• TEXT for strings
• INTEGER or BIGINT for whole numbers
• NUMERIC(p,s) for exact decimals (especially money)
• BOOLEAN for true/false
• TIMESTAMPTZ for dates and times (always, never bare TIMESTAMP)

Module F-3 covers filtering and finding data — the WHERE clause in depth, sorting with ORDER BY, limiting results with LIMIT, and the string and date functions you will use constantly.

Next: F-3 — Filtering, Sorting, and Finding What You Need →