SQL CREATE TABLE

CREATE TABLE is how you define a new table — its name, the columns it contains, the data type of each column, and any rules (constraints) that data must follow. Getting table design right upfront saves significant pain later when you’re trying to migrate schemas on live data.

Basic Syntax

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

A minimal but complete example:

CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,
    email         VARCHAR(255) NOT NULL UNIQUE,
    full_name     VARCHAR(200) NOT NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT NOW(),
    is_active     BOOLEAN NOT NULL DEFAULT TRUE
);

Choosing the Right Data Types

Picking the right data type matters for storage efficiency, query performance, and data integrity.

Numeric types:

-- Integer types
age             SMALLINT,        -- -32,768 to 32,767
quantity        INTEGER,         -- -2.1B to 2.1B
product_count   BIGINT,          -- very large integers

-- Auto-incrementing integers (PostgreSQL)
id              SERIAL,          -- INTEGER + auto-increment sequence
id              BIGSERIAL,       -- BIGINT + auto-increment sequence

-- Decimal (exact)
price           NUMERIC(10, 2),  -- up to 10 digits, 2 decimal places
tax_rate        DECIMAL(5, 4),   -- e.g., 0.0875

-- Floating point (approximate — avoid for money)
lat             DOUBLE PRECISION,
lng             REAL

Text types:

name            VARCHAR(200),    -- variable-length up to 200 chars
country_code    CHAR(2),         -- fixed-length, padded with spaces
description     TEXT,            -- unlimited length
slug            VARCHAR(100)     -- short identifiers

Use TEXT over VARCHAR without a length limit in PostgreSQL — there’s no performance difference and you avoid the constraint. Use CHAR(n) only for truly fixed-length fields like ISO country codes.

Date and time:

birth_date      DATE,                     -- date only, no time
created_at      TIMESTAMP,                -- date and time (no timezone)
updated_at      TIMESTAMPTZ,              -- date, time, timezone (PostgreSQL)
start_time      TIME,                     -- time only
duration        INTERVAL                  -- time duration (PostgreSQL)

Use TIMESTAMPTZ (timestamp with timezone) for any timestamp that represents a moment in time — not naive TIMESTAMP. This matters when users are in different timezones.

Boolean:

is_active       BOOLEAN NOT NULL DEFAULT TRUE,
verified        BOOLEAN

JSON (PostgreSQL):

metadata        JSONB,           -- binary JSON, indexable, preferred
raw_payload     JSON             -- text JSON, preserved as-is

Constraints

Constraints enforce data integrity rules at the database level — they catch problems before bad data gets stored.

NOT NULL:

email   VARCHAR(255) NOT NULL   -- this column must always have a value

UNIQUE:

email   VARCHAR(255) UNIQUE,     -- no two rows can have the same email
-- or as a table-level constraint:
UNIQUE (first_name, last_name)   -- combination must be unique

PRIMARY KEY:

-- Column-level
customer_id   INTEGER PRIMARY KEY,

-- Table-level (required for composite keys)
PRIMARY KEY (order_id, product_id)

FOREIGN KEY:

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount      NUMERIC(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT    -- prevent deleting a customer with orders
        ON UPDATE CASCADE     -- if customer_id changes, update here too
);

Foreign key ON DELETE options:

RESTRICT — prevent the delete if referenced rows exist
CASCADE — delete child rows when parent is deleted
SET NULL — set the FK column to NULL when parent is deleted
NO ACTION — similar to RESTRICT, checked at end of transaction

CHECK:

price           NUMERIC(10, 2) CHECK (price >= 0),
status          VARCHAR(20) CHECK (status IN ('pending', 'active', 'cancelled')),
-- Table-level check
CHECK (end_date > start_date)

DEFAULT:

status          VARCHAR(20) NOT NULL DEFAULT 'pending',
created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
is_active       BOOLEAN NOT NULL DEFAULT TRUE

A Complete, Production-Style Table

CREATE TABLE products (
    product_id      SERIAL PRIMARY KEY,
    sku             VARCHAR(50) NOT NULL UNIQUE,
    name            VARCHAR(200) NOT NULL,
    description     TEXT,
    price           NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    cost            NUMERIC(10, 2) CHECK (cost >= 0),
    category_id     INTEGER NOT NULL
                    REFERENCES categories(category_id) ON DELETE RESTRICT,
    stock_quantity  INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata        JSONB,

    CONSTRAINT chk_cost_below_price CHECK (cost IS NULL OR cost < price)
);

-- Index for category-based queries (foreign key doesn't auto-create an index in PostgreSQL)
CREATE INDEX idx_products_category ON products(category_id);

-- Partial index for active products only
CREATE INDEX idx_products_active_sku ON products(sku) WHERE is_active = TRUE;

CREATE TABLE IF NOT EXISTS

Prevents an error when the table already exists — useful in migration scripts:

CREATE TABLE IF NOT EXISTS audit_log (
    log_id      BIGSERIAL PRIMARY KEY,
    table_name  VARCHAR(100) NOT NULL,
    operation   VARCHAR(10) NOT NULL,
    changed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE AS (CTAS)

Creates a new table and populates it from a SELECT query in one step. Useful for materializing complex queries or creating working tables:

-- Create a summary table from existing data
CREATE TABLE customer_summary AS
SELECT
    customer_id,
    COUNT(*) AS total_orders,
    SUM(amount) AS lifetime_value,
    MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id;

Note: CREATE TABLE AS does not inherit constraints from the source — add them afterward with ALTER TABLE.

Temporary Tables

Temporary tables exist only for the duration of your session (or transaction, depending on the option):

CREATE TEMP TABLE staging_orders AS
SELECT * FROM orders WHERE status = 'pending';

-- Explicit temp table with columns
CREATE TEMPORARY TABLE temp_results (
    id      INTEGER,
    result  TEXT
);

Temp tables are useful in stored procedures, complex ETL steps, or when you need an intermediate result set that you’ll query multiple times.

Table Design Best Practices

Use surrogate keys, not natural keys, as primary keys. Email addresses, product codes, and phone numbers can change. An auto-increment integer or UUID as the primary key decouples identity from business data.

Every table should have a primary key. Without one, you can end up with duplicate rows you can’t distinguish.

Add created_at and updated_at to most tables. Knowing when records were created and last changed is almost always needed eventually.

Use TIMESTAMPTZ (with timezone), not TIMESTAMP. Applications in multiple timezones will thank you.

Name constraints explicitly. CONSTRAINT fk_orders_customer FOREIGN KEY ... is far easier to drop or reference in error messages than the auto-generated name orders_customer_id_fkey.

Think about indexes when creating tables. PostgreSQL doesn’t automatically create indexes on foreign keys (though primary keys and unique constraints do create indexes). Add indexes for columns you’ll filter and join on frequently.