Kenhuang Academy
  • English

      • PostgreSQL Create Table

      Overview

      Tables are the basic structure for storing data in PostgreSQL. This chapter explains how to create tables using the CREATE TABLE statement.

      CREATE TABLE Statement

      Basic Syntax

      CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
);

      Simple Examples

      -- Create a simple users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    description TEXT
);

      Data Types

      Common Data Types

      TypeDescriptionExample
      INTEGERInteger42
      SERIALAuto-incrementing integer1, 2, 3...
      VARCHAR(n)Variable-length string'hello'
      TEXTUnlimited text'long text...'
      BOOLEANBoolean valueTRUE, FALSE
      DATEDate'2024-01-15'
      TIMESTAMPTimestamp'2024-01-15 10:30:00'
      DECIMAL(p,s)Exact numeric99.99
      JSON/JSONBJSON data'{"key": "value"}'

      Column Constraints

      Common Constraints

      CREATE TABLE employees (
    -- Primary key constraint
    id SERIAL PRIMARY KEY,

    -- Not null constraint
    name VARCHAR(100) NOT NULL,

    -- Unique constraint
    email VARCHAR(100) UNIQUE,

    -- Default value
    status VARCHAR(20) DEFAULT 'active',

    -- Check constraint
    age INTEGER CHECK (age >= 18),

    -- Foreign key constraint
    department_id INTEGER REFERENCES departments(id)
);

      Constraint Descriptions

      ConstraintDescription
      PRIMARY KEYPrimary key, uniquely identifies each row
      NOT NULLDoes not allow null values
      UNIQUEValues must be unique
      DEFAULTDefault value
      CHECKCheck condition
      REFERENCESForeign key reference

      Table-Level Constraints

      Composite Primary Key

      CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

      Composite Unique Constraint

      CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    UNIQUE (user_id, role_id)
);

      Named Constraints

      CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    CONSTRAINT positive_price CHECK (price > 0),
    CONSTRAINT unique_name UNIQUE (name)
);

      Creating Tables with Foreign Keys

      -- Create parent table first
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Then create child table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

      Foreign Key Options

      OptionDescription
      ON DELETE CASCADECascade delete when parent is deleted
      ON DELETE SET NULLSet to NULL when parent is deleted
      ON DELETE RESTRICTPrevent deletion of referenced parent
      ON UPDATE CASCADECascade update when parent key changes

      Conditional Creation

      -- Create only if table doesn't exist
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

      Create Table from Query

      -- Create new table from existing table
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

-- Copy structure only without data
CREATE TABLE users_backup (LIKE users INCLUDING ALL);

      Temporary Tables

      -- Create temporary table (auto-deleted at session end)
CREATE TEMPORARY TABLE temp_results (
    id INTEGER,
    value TEXT
);

-- Or use TEMP keyword
CREATE TEMP TABLE temp_data (
    id INTEGER,
    data JSONB
);

      Practical Example

      E-commerce Database Schema

      -- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 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 DEFAULT 0 CHECK (stock >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP 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),
    unit_price DECIMAL(10, 2) NOT NULL
);

      View Table Structure

      -- Using psql command
\d table_name
\d+ table_name  -- Detailed info

-- Using SQL query
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users';