Define tables with CREATE TABLE, pick the right PostgreSQL data type for each column, change tables with ALTER TABLE, group them into schemas, and clean up with DROP and TRUNCATE.
Why: a table is a named grid of rows and columns, and each column has a type that decides what it can hold. These six tables are the schema used throughout the whole course — create them once now and every later query will have something to run against. Note: order matters because of foreign keys (REFERENCES) — a table that others point at, like users, must exist first. GENERATED ALWAYS AS IDENTITY auto-numbers the id (1, 2, 3, …); it is the modern replacement for the older "serial" type.
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text,
country text,
bio text,
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now()
);CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL UNIQUE,
name text NOT NULL,
category text,
price numeric(10, 2) NOT NULL,
metadata jsonb
);CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id),
total numeric(10, 2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);CREATE TABLE order_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id bigint NOT NULL REFERENCES products(id),
quantity integer NOT NULL CHECK (quantity > 0)
);CREATE TABLE employees (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
manager_id bigint REFERENCES employees(id)
);CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
owner text NOT NULL,
balance numeric(12, 2) NOT NULL DEFAULT 0
);Why: choosing the right type keeps your data correct and your queries fast. When in doubt: use text for any string, numeric for money, timestamptz for dates/times, and boolean for true/false.
-- numbers
age integer -- whole numbers
price numeric(10, 2) -- exact decimals: 10 digits total, 2 after the dot
rating real -- approximate floating-point number
-- text
code varchar(20) -- text up to 20 characters
bio text -- text of any length (use this most of the time)
-- true / false
is_admin boolean
-- dates & times
born_on date
created_at timestamptz -- timestamp WITH time zone — always prefer this
-- structured data
tags text[] -- an array of text values
metadata jsonb -- JSON stored in an efficient binary form
public_id uuid -- a universally unique identifierWhy: real tables change over time — you add columns, tighten rules, or rename things. ALTER TABLE edits the table’s shape without losing the data already in it. Run each of these on its own to see the effect.
ALTER TABLE users ADD COLUMN last_login timestamptz;ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;ALTER TABLE users RENAME COLUMN bio TO about;ALTER TABLE users DROP COLUMN about;Why: a schema is a folder inside a database that groups related tables. You refer to a table inside one as schema_name.table_name. Note: every database starts with a default schema called "public" — if you never create your own, all your tables live there.
CREATE SCHEMA billing;CREATE TABLE billing.invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(10, 2) NOT NULL
);Why: TRUNCATE empties a table fast while keeping its structure; DROP deletes the table itself. We practise on a throwaway "scratch" table so we do not disturb the schema. Note: both are permanent — there is no undo once the transaction commits. Note: PostgreSQL refuses to TRUNCATE or DROP a table that another one points at with a foreign key (for example users, which orders references) — you would need CASCADE, or remove the referencing rows/table first.
-- a throwaway table to practise on
CREATE TABLE scratch (id int);TRUNCATE scratch; -- delete every row, keep the empty tableDROP TABLE scratch; -- delete the table and all its dataDROP TABLE IF EXISTS scratch; -- no error if the table was already gone