Chapter 6: PostgreSQL Create Table
1. What does CREATE TABLE actually do? (Honest teacher explanation)
CREATE TABLE tells PostgreSQL: “Hey, make me a new, initially empty table inside the current database. Give it this name, these columns with these data types, and enforce these rules so the data stays clean and consistent.”
- The table starts empty (zero rows).
- You define columns (like fields in a form) and their types (text? number? date?).
- You add rules/constraints (no duplicates, must fill this field, this value links to another table…).
- PostgreSQL automatically creates supporting objects (indexes for PRIMARY KEY / UNIQUE, sequences for SERIAL/IDENTITY…).
After running it → the table exists forever (until you DROP it), and you can INSERT rows into it.
2. Basic syntax skeleton (the core shape you’ll see 90% of the time)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE table_name ( column1 data_type [constraints], column2 data_type [constraints], ... [table-level constraints] ); |
- table_name → lowercase_with_underscores is PostgreSQL convention (case-sensitive but folds to lower unless quoted).
- Columns listed inside parentheses, separated by commas.
- Ends with semicolon ;
3. Step-by-step real example – building a “students” table (like a real college app)
Connect to your database first (in psql or pgAdmin):
SQL
|
0 1 2 3 4 5 6 |
\c college_db -- or whatever your db is called |
Now let’s create a proper students table:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
CREATE TABLE students ( -- Primary key – unique identifier for each student id BIGSERIAL PRIMARY KEY, -- Basic personal info first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED, -- Contact & unique identifiers email VARCHAR(100) UNIQUE NOT NULL, roll_number VARCHAR(20) UNIQUE, phone VARCHAR(15), -- Academic details date_of_birth DATE, admission_year SMALLINT CHECK (admission_year >= 2000 AND admission_year <= EXTRACT(YEAR FROM CURRENT_DATE)), current_semester SMALLINT DEFAULT 1 CHECK (current_semester BETWEEN 1 AND 8), gpa NUMERIC(3,2) CHECK (gpa >= 0 AND gpa <= 10.00), -- Status flags is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Optional: address as JSONB (very common in 2026 apps) address JSONB ); |
Run this → you get: CREATE TABLE
What just happened?
- BIGSERIAL = auto-incrementing 64-bit integer (best modern choice over SERIAL)
- PRIMARY KEY = unique + not null + auto-index
- NOT NULL = must have value
- UNIQUE = no duplicates allowed (email & roll_number)
- CHECK = custom business rule (valid year, semester range, gpa bounds)
- DEFAULT = auto-fill if you don’t provide value
- GENERATED ALWAYS AS … STORED = computed column (full_name auto-built, stored on disk)
- TIMESTAMP WITH TIME ZONE = proper time with timezone awareness (Hyderabad = IST)
- JSONB = store flexible structured data (e.g. {“city”: “Hyderabad”, “pin”: “500081”})
4. Common column data types you’ll use every day (2026 favorites)
| Type | Use case example | Size / Notes |
|---|---|---|
| BIGSERIAL / BIGINT GENERATED ALWAYS AS IDENTITY | Primary keys, auto-increment | 64-bit, up to ~9 quintillion |
| VARCHAR(n) / TEXT | Names, emails, descriptions | TEXT = unlimited, preferred over VARCHAR without limit |
| NUMERIC(precision, scale) | Money, GPA, measurements (exact) | No rounding errors |
| INTEGER / SMALLINT | Counts, years, semesters | 32-bit / 16-bit |
| BOOLEAN | Flags (is_active, is_verified) | True/False |
| DATE / TIMESTAMP WITH TIME ZONE | Birthdays, created_at | timestamptz = best for global apps |
| JSONB | Profiles, settings, metadata | Indexed, queryable like document DB |
| UUID | External IDs (from frontend) | gen_random_uuid() default |
| BYTEA | Images/files (small ones) | Usually better in S3 + path in DB |
5. Adding relationships – Foreign Key example
Let’s create a related courses and enrollments table:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE courses ( id BIGSERIAL PRIMARY KEY, code VARCHAR(10) UNIQUE NOT NULL, -- e.g. CS101 name VARCHAR(100) NOT NULL, credits SMALLINT DEFAULT 4 CHECK (credits > 0), created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE enrollments ( id BIGSERIAL PRIMARY KEY, student_id BIGINT NOT NULL REFERENCES students(id) ON DELETE CASCADE, course_id BIGINT NOT NULL REFERENCES courses(id) ON DELETE CASCADE, grade CHAR(2), -- A+, A, B+, ... enrolled_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, -- Prevent duplicate enrollment in same course CONSTRAINT unique_student_course UNIQUE (student_id, course_id) ); |
- REFERENCES students(id) = foreign key → must point to existing student
- ON DELETE CASCADE = if student deleted → auto-remove their enrollments
- Multi-column UNIQUE = table-level constraint
6. Quick cheatsheet – most useful constraints
| What you want | How to write it | Where |
|---|---|---|
| Must fill value | NOT NULL | Column |
| No duplicates | UNIQUE | Column or table |
| Unique + not null + index | PRIMARY KEY | Column or table |
| Valid range | CHECK (gpa BETWEEN 0 AND 10) | Column or table |
| Auto-increment ID | BIGSERIAL PRIMARY KEY or BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY | Column |
| Default value | DEFAULT CURRENT_TIMESTAMP | Column |
| Computed column | full_name TEXT GENERATED ALWAYS AS (…) STORED | Column |
| Link to another table | FOREIGN KEY … REFERENCES … | Column or table |
7. After CREATE TABLE – verify it worked (in psql or pgAdmin)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- List all tables \dt -- Describe the structure \d students -- See constraints \d+ students |
In pgAdmin: expand database → Schemas → public → Tables → right-click students → Properties
8. Common beginner mistakes & tips (from real students)
- Forgot semicolon → error
- Used SERIAL instead of BIGSERIAL → fine for small apps, but BIGSERIAL safer
- Quoted table/column names → avoid unless needed (makes case-sensitive)
- No NOT NULL on important fields → leads to bad data
- Forgetting foreign keys → data gets inconsistent
- Tip: Always name constraints (CONSTRAINT valid_gpa CHECK (…)) — easier to drop/alter later
Your mini homework right now
- Create the three tables above (students, courses, enrollments) in your college_db
- Run \d+ enrollments — see the foreign keys
- Try INSERT INTO students (first_name, last_name, email) VALUES (‘Rahul’, ‘Sharma’, ‘rahul@hyd.edu’);
- See auto id appear!
Next class?
Tell me:
- Want to do INSERT, UPDATE, DELETE next?
- How to add column later (ALTER TABLE)?
- Indexing basics (CREATE INDEX)?
- Or partitioning / generated columns deeper?
Your guru is right here — what’s next? 🚀
