CREATE TABLE - Designing Your Database Schema

Introduction

So far, you've queried existing tables. Now it's time to create your own!

CREATE TABLE defines the structure of a table: column names, data types, and constraints. This is the foundation of database design—getting it right from the start saves countless headaches later.

Basic CREATE TABLE Syntax

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);

Simple Example

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Result: A new table with 4 columns is created!

Column Data Types

Numeric Types

CREATE TABLE numeric_examples (
    small_int SMALLINT,        -- -32,768 to 32,767
    regular_int INTEGER,       -- -2 billion to 2 billion
    big_int BIGINT,            -- Very large numbers
    decimal_val DECIMAL(10,2), -- Exact: 10 digits, 2 after decimal
    float_val REAL,            -- Approximate floating point
    double_val DOUBLE PRECISION, -- Higher precision float
    serial_id SERIAL           -- Auto-incrementing integer
);

When to use:

• SERIAL/BIGSERIAL: Auto-incrementing IDs
• INTEGER: Counts, IDs
• DECIMAL/NUMERIC: Money (exact precision)
• REAL/DOUBLE PRECISION: Scientific calculations

String Types

CREATE TABLE string_examples (
    fixed_char CHAR(10),        -- Fixed-length, padded with spaces
    variable_char VARCHAR(100), -- Variable-length, max 100 chars
    unlimited_text TEXT         -- Unlimited length
);

When to use:

• CHAR(n): Fixed-length codes (e.g., country codes 'US', 'CA')
• VARCHAR(n): Names, emails, addresses (with reasonable max length)
• TEXT: Long descriptions, articles, JSON

Date and Time Types

CREATE TABLE datetime_examples (
    just_date DATE,                  -- 2024-12-20
    just_time TIME,                  -- 14:30:00
    timestamp_val TIMESTAMP,         -- 2024-12-20 14:30:00 (no timezone)
    timestamp_tz TIMESTAMPTZ,        -- 2024-12-20 14:30:00+00 (with timezone)
    time_interval INTERVAL           -- 1 day, 3 hours, etc.
);

Best practice: Use TIMESTAMPTZ for most timestamp needs!

Boolean Type

CREATE TABLE boolean_example (
    is_active BOOLEAN,          -- TRUE, FALSE, or NULL
    is_verified BOOLEAN DEFAULT FALSE
);

JSON Types

CREATE TABLE json_example (
    data JSON,        -- JSON text (stores as-is)
    data_binary JSONB -- Binary JSON (faster, supports indexing)
);

Best practice: Use JSONB for better performance and indexing!

Array Types (PostgreSQL)

CREATE TABLE array_example (
    tags TEXT[],              -- Array of text
    scores INTEGER[]          -- Array of integers
);

UUID Type

CREATE TABLE uuid_example (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100)
);

Column Constraints

NOT NULL

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,  -- Email is required
    name VARCHAR(100) NOT NULL,   -- Name is required
    bio TEXT                      -- Bio is optional (can be NULL)
);

UNIQUE

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,  -- No duplicate emails
    username VARCHAR(50) UNIQUE NOT NULL  -- No duplicate usernames
);

DEFAULT

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',  -- Default status
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,  -- Auto-set creation time
    total DECIMAL(10,2) DEFAULT 0.00
);

CHECK

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price >= 0),  -- Price must be non-negative
    stock INTEGER CHECK (stock >= 0),         -- Stock can't be negative
    rating DECIMAL(3,2) CHECK (rating BETWEEN 0 AND 5)  -- Rating 0-5
);

PRIMARY KEY

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,  -- Auto-incrementing unique ID
    name VARCHAR(100) NOT NULL
);

-- Or define at end (useful for composite keys)
CREATE TABLE customers (
    id SERIAL,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

Composite Keys

Composite Primary Key

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)  -- Combination must be unique
);

Composite Unique Constraint

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (student_id, course_id)  -- Student can enroll in each course only once
);

Naming Constraints

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CONSTRAINT price_positive CHECK (price >= 0),
    stock INTEGER CONSTRAINT stock_non_negative CHECK (stock >= 0)
);

Benefits:

• Clearer error messages
• Easier to drop/modify specific constraints later

Table-Level Constraints

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    CHECK (check_out > check_in)  -- Table-level CHECK constraint
);

CREATE TABLE ... AS (CTAS)

Create a table from a query result:

-- Create new table with data from query
CREATE TABLE high_value_customers AS
SELECT
    c.id,
    c.name,
    c.email,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email
HAVING SUM(o.total) > 1000;

Note: CTAS doesn't copy constraints (except NOT NULL)!

CREATE TABLE IF NOT EXISTS

-- Safe: Won't error if table already exists
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2)
);

Temporary Tables

-- Exists only for current session
CREATE TEMP TABLE temp_calculations (
    id SERIAL PRIMARY KEY,
    value DECIMAL(10,2)
);

-- Automatically dropped when session ends

Use cases:

• Complex multi-step calculations
• Intermediate results
• Data transformations

Example: E-Commerce Schema

-- Customers table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    active BOOLEAN DEFAULT TRUE
);

-- Products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Order Items table
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    UNIQUE (order_id, product_id)
);

Modifying Tables After Creation

ADD COLUMN

ALTER TABLE products
ADD COLUMN weight DECIMAL(10,2);

-- With constraints
ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) NOT NULL DEFAULT 'Unknown';

DROP COLUMN

ALTER TABLE products
DROP COLUMN weight;

RENAME COLUMN

ALTER TABLE products
RENAME COLUMN name TO product_name;

CHANGE COLUMN TYPE

ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(12,2);

ADD CONSTRAINT

ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price >= 0);

DROP CONSTRAINT

ALTER TABLE products
DROP CONSTRAINT price_positive;

Dropping Tables

DROP TABLE

DROP TABLE products;

-- Safe: Won't error if table doesn't exist
DROP TABLE IF EXISTS products;

-- Drop multiple tables
DROP TABLE products, categories, orders;

-- Drop with CASCADE (also drops dependent objects)
DROP TABLE customers CASCADE;

Warning: DROP TABLE is permanent and irreversible!

Truncating Tables

-- Delete all rows, keep table structure
TRUNCATE TABLE products;

-- Faster than DELETE (doesn't scan rows)
TRUNCATE TABLE products RESTART IDENTITY;  -- Reset SERIAL counters

Best Practices

1. Always Use Primary Keys

-- ✅ Good
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    ...
);

-- ❌ Bad: No primary key
CREATE TABLE users (
    email VARCHAR(255),
    ...
);

2. Choose Appropriate Data Types

-- ✅ Good
CREATE TABLE products (
    price DECIMAL(10,2),  -- Exact for money
    rating DECIMAL(3,2)   -- 0.00 to 5.00
);

-- ❌ Bad
CREATE TABLE products (
    price REAL,    -- Floating point imprecision!
    rating INTEGER -- Can't store 4.5 stars
);

3. Use NOT NULL Wisely

-- ✅ Good: Required fields
CREATE TABLE users (
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    bio TEXT  -- Optional
);

-- ❌ Bad: Everything optional
CREATE TABLE users (
    email VARCHAR(255),
    name VARCHAR(100)
);

4. Add Timestamps

-- ✅ Good: Track when records were created/updated
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

5. Use CHECK Constraints for Validation

CREATE TABLE users (
    email VARCHAR(255) CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$'),
    age INTEGER CHECK (age BETWEEN 0 AND 150),
    status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'suspended'))
);

6. Name Constraints

CREATE TABLE products (
    id SERIAL,
    name VARCHAR(100),
    price DECIMAL(10,2),
    CONSTRAINT pk_products PRIMARY KEY (id),
    CONSTRAINT chk_price_positive CHECK (price >= 0),
    CONSTRAINT uq_product_name UNIQUE (name)
);

Common Mistakes

Mistake 1: Wrong Data Type for Money

-- ❌ Bad: REAL/FLOAT causes rounding errors
CREATE TABLE orders (
    total REAL
);

-- ✅ Good: DECIMAL for exact precision
CREATE TABLE orders (
    total DECIMAL(10,2)
);

Mistake 2: VARCHAR Too Small

-- ❌ Bad: Too restrictive
CREATE TABLE users (
    email VARCHAR(50)  -- Many emails are longer!
);

-- ✅ Good: Reasonable limit
CREATE TABLE users (
    email VARCHAR(255)
);

Mistake 3: No Primary Key

-- ❌ Bad: No way to uniquely identify rows
CREATE TABLE logs (
    message TEXT,
    created_at TIMESTAMP
);

-- ✅ Good: Always have a primary key
CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Practice Exercises

Exercise 1

Create a users table with id, username (unique), email (unique), password_hash, and created_at.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Exercise 2

Create a blog_posts table with id, title, content, author_id (foreign key to users), published_at, and a CHECK constraint ensuring title is not empty.

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL CHECK (LENGTH(TRIM(title)) > 0),
    content TEXT NOT NULL,
    author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Exercise 3

Create a product_reviews table with a composite primary key (product_id, user_id) and rating between 1 and 5.

CREATE TABLE product_reviews (
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    review_text TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id, user_id)
);

Key Takeaways

• ✅ CREATE TABLE defines table structure
• ✅ Choose appropriate data types (DECIMAL for money, TIMESTAMPTZ for timestamps)
• ✅ Use constraints (NOT NULL, UNIQUE, CHECK, PRIMARY KEY)
• ✅ Always add PRIMARY KEY for unique row identification
• ✅ DEFAULT values for automatic field population
• ✅ SERIAL/BIGSERIAL for auto-incrementing IDs
• ✅ Name constraints for better error messages
• ✅ ALTER TABLE to modify existing tables
• ✅ DROP TABLE carefully (it's permanent!)

Next Steps

You've learned how to create tables! In the next lesson, we'll explore Primary Keys and Constraints in depth—the rules that ensure data integrity and consistency.

Let's build robust databases!