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

Type	Description	Example
INTEGER	Integer	42
SERIAL	Auto-incrementing integer	1, 2, 3...
VARCHAR(n)	Variable-length string	'hello'
TEXT	Unlimited text	'long text...'
BOOLEAN	Boolean value	TRUE, FALSE
DATE	Date	'2024-01-15'
TIMESTAMP	Timestamp	'2024-01-15 10:30:00'
DECIMAL(p,s)	Exact numeric	99.99
JSON/JSONB	JSON 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

Constraint	Description
PRIMARY KEY	Primary key, uniquely identifies each row
NOT NULL	Does not allow null values
UNIQUE	Values must be unique
DEFAULT	Default value
CHECK	Check condition
REFERENCES	Foreign 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

Option	Description
ON DELETE CASCADE	Cascade delete when parent is deleted
ON DELETE SET NULL	Set to NULL when parent is deleted
ON DELETE RESTRICT	Prevent deletion of referenced parent
ON UPDATE CASCADE	Cascade 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';