How to Create Tables in PostgreSQL

Tables are the foundation of any relational database. The CREATE TABLE statement defines the structure, data types, and constraints that control what data can be stored.

Basic Table Creation

CREATE TABLE customers (
  id          SERIAL PRIMARY KEY,
  first_name  VARCHAR(100) NOT NULL,
  last_name   VARCHAR(100) NOT NULL,
  email       VARCHAR(255) UNIQUE NOT NULL,
  phone       VARCHAR(20),
  created_at  TIMESTAMP DEFAULT NOW()
);

SERIAL auto-generates incrementing integers. NOT NULL prevents empty values, UNIQUE enforces no duplicates, and DEFAULT provides a fallback when no value is supplied.

Common Data Types

CREATE TABLE products (
  id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name        TEXT NOT NULL,
  description TEXT,
  price       NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock       INTEGER DEFAULT 0,
  is_active   BOOLEAN DEFAULT true,
  tags        TEXT[],
  metadata    JSONB,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

PostgreSQL offers rich data types beyond the SQL standard: UUID for globally unique IDs, TEXT[] for arrays, JSONB for structured JSON, and TIMESTAMPTZ for timezone-aware timestamps.

Foreign Keys and Relationships

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total       NUMERIC(10, 2) NOT NULL,
  status      VARCHAR(20) DEFAULT 'pending',
  created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id  UUID NOT NULL REFERENCES products(id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  NUMERIC(10, 2) NOT NULL
);

REFERENCES creates a foreign key that ensures referential integrity. ON DELETE CASCADE automatically deletes child rows when the parent is removed.

When to Use Each Approach

• Use SERIAL for simple auto-increment IDs in small applications
• Use UUID when you need IDs that are unique across distributed systems
• Use CHECK constraints to enforce business rules at the database level
• Use JSONB for flexible, schema-less data within a structured table