Module 3 — Creating Tables and Data Types

1. Creating Your Workspace (Database)

In PostgreSQL, every project usually gets its own Database. This keeps things isolated and organized.

-- Create a new database
CREATE DATABASE shop_db;
 
-- List all databases to verify
-- \l
 
-- Connect to your new database
-- \c shop_db

2. Defining the Structure (Table)

A table is where the actual data lives. When you create a table, you must define the Columns, their Data Types, and their Rules (Constraints).

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price INTEGER NOT NULL,
    description TEXT,
    in_stock BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);

Breaking down the command:

• id SERIAL PRIMARY KEY:
  • SERIAL tells Postgres to auto-increment this number (1, 2, 3...) automatically.
  • PRIMARY KEY ensures every product has a unique ID (the most important constraint).
• VARCHAR(100): Variable character string, max 100 letters. Good for names.
• NOT NULL: This column must have a value. It cannot be empty.
• DEFAULT true: If you don't provide a value, it will automatically be set to true.

3. PostgreSQL Data Types - The Right Tool for the Job

Choosing the right data type is the difference between a fast, reliable database and a slow, buggy one.

Data Types Overview

4. Constraints: The Rules of the Road

Constraints are automated rules that prevent "bad" data from entering your system. It's better to have a database error than a silent bug in your app.

1. PRIMARY KEY: The unique fingerprint for each row.
2. NOT NULL: Prevents NULL (empty) values where they don't belong.
3. UNIQUE: Ensures no two rows have the same value (e.g., email in a users table).
4. CHECK: Enforces custom rules (e.g., CHECK (price > 0)).
5. DEFAULT: Provides a fallback value.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 13),
    role VARCHAR(20) DEFAULT 'user'
);

5. Professional Naming Conventions

To code like a senior engineer, follow these industry standards:

• Snake_case: Use created_at, not createdAt or CreatedAt.
• Plural Tables: Use users, products, orders.
• Lower Case: While SQL is case-insensitive, professional code is always written in lowercase (except for keywords like CREATE TABLE).
• Singular ID: Use id as the primary key name in every table.

Practice Exercise

1. Connect to your Postgres server.
2. Create a database called learning_hub.
3. Connect to learning_hub.
4. Create a table called courses with these columns:
   • id (auto-incrementing ID)
   • title (required text)
   • instructor (text)
   • price (number, must be positive)
   • is_published (true/false, default false)