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

The Relational Mental Model

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

Table: users
┌────┬───────────┬──────────────────────┬─────────────────────────────┐
│ id │   name    │        email         │         created_at          │
├────┼───────────┼──────────────────────┼─────────────────────────────┤
│  1 │ Alice     │ alice@example.com    │ 2026-05-01 09:00:00+00      │
│  2 │ Bob       │ bob@example.com      │ 2026-05-02 14:30:00+00      │
│  3 │ Carol     │ carol@example.com    │ 2026-05-03 11:15:00+00      │
└────┴───────────┴──────────────────────┴─────────────────────────────┘

• 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

sql
SMALLINT          -- -32,768 to 32,767 (2 bytes) — rarely needed
INTEGER           -- -2,147,483,648 to 2,147,483,647 (4 bytes) — counts, quantities
BIGINT            -- -9.2 quintillion to 9.2 quintillion (8 bytes) — IDs, large counts
NUMERIC(p, s)     -- exact decimal, p digits total, s after decimal point — MONEY, percentages
FLOAT / DOUBLE    -- approximate decimal (DO NOT use for money — loses precision)

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

sql
TEXT              -- variable-length string, no size limit — use this almost always
VARCHAR(n)        -- variable-length string, max n characters — use only when you need to enforce a limit
CHAR(n)           -- fixed-length string, padded with spaces to n characters — almost never useful

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

sql
BOOLEAN           -- true, false, or NULL

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

Dates and Times

sql
DATE              -- calendar date only (2026-05-17) — no time, no timezone
TIME              -- time of day only (14:30:00) — no date, no timezone
TIMESTAMP         -- date + time, NO timezone (dangerous — avoid this)
TIMESTAMPTZ       -- date + time WITH timezone (always use this)
INTERVAL          -- a duration ('3 days', '2 hours 30 minutes')

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

sql
SERIAL            -- auto-incrementing INTEGER (shorthand for INTEGER DEFAULT nextval())
BIGSERIAL         -- auto-incrementing BIGINT — use this for IDs
UUID              -- 128-bit universally unique identifier (e.g. 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11')

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

CREATE TABLE

sql
CREATE TABLE table_name (
  column_name  data_type  [constraints],
  column_name  data_type  [constraints],
  ...
);

A complete example:

sql
CREATE TABLE products (
  id          BIGSERIAL    PRIMARY KEY,
  name        TEXT         NOT NULL,
  description TEXT,
  price       NUMERIC(10, 2) NOT NULL,
  in_stock    BOOLEAN      NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

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

sql
\d products

-- Output:
--                            Table "public.products"
--    Column    |          Type          | Collation | Nullable |      Default
-- -------------+------------------------+-----------+----------+-------------------
--  id          | bigint                 |           | not null | generated always as...
--  name        | text                   |           | not null |
--  description | text                   |           |          |
--  price       | numeric(10,2)          |           | not null |
--  in_stock    | boolean                |           | not null | true
--  created_at  | timestamp with time zone |         | not null | now()

INSERT — Adding Rows

sql
-- Insert a single row, specifying all columns
INSERT INTO products (name, description, price, in_stock)
VALUES ('Mechanical Keyboard', 'TKL, Cherry MX Blue switches', 129.99, true);

-- Insert a single row, letting defaults apply (id and created_at auto-fill)
INSERT INTO products (name, price)
VALUES ('USB-C Cable', 12.99);
-- description will be NULL, in_stock will be true (default), created_at will be NOW()

-- Insert multiple rows in one statement (more efficient than separate INSERTs)
INSERT INTO products (name, price, in_stock) VALUES
  ('Wireless Mouse', 49.99, true),
  ('Monitor Stand', 39.99, true),
  ('Laptop Sleeve', 24.99, false);

RETURNING — get the generated ID back:

sql
INSERT INTO products (name, price)
VALUES ('Headphones', 89.99)
RETURNING id, created_at;

-- Output:
--  id |          created_at
-- ----+-------------------------------
--   6 | 2026-05-17 10:30:00.123456+00

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

sql
-- Read all columns from all rows
SELECT * FROM products;

-- Read specific columns only (always prefer this over SELECT *)
SELECT id, name, price FROM products;

-- Alias a column in the output
SELECT name, price, price * 1.2 AS price_with_tax FROM products;

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:

sql
-- Get only the first 5 rows
SELECT id, name, price FROM products LIMIT 5;

-- Skip the first 5 rows, then get the next 5
SELECT id, name, price FROM products LIMIT 5 OFFSET 5;

UPDATE — Modifying Existing Rows

sql
-- Update the price of a specific product
UPDATE products
SET price = 119.99
WHERE id = 1;

-- Update multiple columns at once
UPDATE products
SET price = 44.99, in_stock = false
WHERE id = 4;

-- Update all rows that match a condition
UPDATE products
SET in_stock = true
WHERE in_stock = false AND price < 50.00;

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

sql
-- THIS UPDATES EVERY ROW IN THE TABLE
UPDATE products SET price = 0;

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

sql
-- Check what you're about to update before doing it
SELECT * FROM products WHERE price > 100;
-- If this looks right, then run:
UPDATE products SET in_stock = false WHERE price > 100;

RETURNING works with UPDATE too:

sql
UPDATE products
SET price = price * 0.9
WHERE in_stock = false
RETURNING id, name, price;
-- Shows you what the new prices are after the update

DELETE — Removing Rows

sql
-- Delete a specific row
DELETE FROM products WHERE id = 6;

-- Delete all rows matching a condition
DELETE FROM products WHERE in_stock = false AND price < 10;

-- Get back what you deleted
DELETE FROM products WHERE price > 200
RETURNING id, name, price;

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

sql
-- DELETES EVERYTHING
DELETE FROM products;

TRUNCATE — faster than DELETE for removing all rows:

sql
TRUNCATE TABLE products;
-- Removes all rows instantly (does not scan each row, so much faster than DELETE)
-- Cannot be used with a WHERE clause — it always removes everything
-- Does not fire row-level triggers (important to know in complex schemas)

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

sql
-- Remove the table and all its data
DROP TABLE products;

-- Remove only if it exists (prevents error if table doesn't exist)
DROP TABLE IF EXISTS products;

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:

sql
-- Step 1: Create the database and connect
CREATE DATABASE shop;
\c shop

-- Step 2: Create the table
CREATE TABLE products (
  id          BIGSERIAL    PRIMARY KEY,
  name        TEXT         NOT NULL,
  description TEXT,
  price       NUMERIC(10,2) NOT NULL,
  category    TEXT         NOT NULL DEFAULT 'general',
  in_stock    BOOLEAN      NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Step 3: Insert products
INSERT INTO products (name, description, price, category) VALUES
  ('Mechanical Keyboard', 'TKL, Cherry MX Blue', 129.99, 'peripherals'),
  ('Wireless Mouse', '2.4GHz, 1600 DPI', 49.99, 'peripherals'),
  ('Monitor Stand', 'Adjustable height', 39.99, 'accessories'),
  ('USB-C Hub', '7-in-1 hub', 64.99, 'accessories'),
  ('Laptop Sleeve', '15-inch, water resistant', 24.99, 'accessories'),
  ('Webcam HD', '1080p, built-in mic', 89.99, 'peripherals');

-- Step 4: Read all products
SELECT * FROM products;

-- Step 5: Read only specific columns
SELECT id, name, price, category FROM products;

-- Step 6: Update a price and see the change
UPDATE products SET price = 119.99 WHERE id = 1 RETURNING id, name, price;

-- Step 7: Mark one product as out of stock
UPDATE products SET in_stock = false WHERE id = 5 RETURNING id, name, in_stock;

-- Step 8: Delete the out-of-stock product
DELETE FROM products WHERE in_stock = false RETURNING id, name;

-- Step 9: Verify what's left
SELECT id, name, price, in_stock FROM products;

-- Step 10: Check your table structure
\d products

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.

sql
-- NULL is not equal to anything, including itself
SELECT NULL = NULL;    -- returns NULL, not true
SELECT NULL = 0;       -- returns NULL, not false
SELECT NULL = '';      -- returns NULL, not false

-- Check for NULL with IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;

-- COALESCE: return the first non-NULL value
SELECT name, COALESCE(description, 'No description') FROM products;
-- Returns 'No description' for rows where description is NULL

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 →