> ## Documentation Index
> Fetch the complete documentation index at: https://www.pgschema.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CREATE TABLE

## Syntax

```sql theme={null}
create_table ::= CREATE TABLE [ IF NOT EXISTS ] table_name
                 ( [ { column_definition | like_clause } [, ...] ] [ table_constraint [, ...] ] )
                 [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } ) ]

table_name ::= [schema.]name

column_definition ::= column_name data_type
                     [ NOT NULL | NULL ]
                     [ DEFAULT default_value ]
                     [ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( identity_option [, ...] ) ] ]
                     [ GENERATED ALWAYS AS ( expression ) STORED ]
                     [ column_constraint [, ...] ]

like_clause ::= LIKE source_table [ like_option [...] ]

like_option ::= INCLUDING { DEFAULTS | CONSTRAINTS | INDEXES | COMMENTS | ALL }
              | EXCLUDING { DEFAULTS | CONSTRAINTS | INDEXES | COMMENTS | ALL }

column_constraint ::= [ CONSTRAINT constraint_name ] 
                     { PRIMARY KEY | UNIQUE | CHECK ( expression ) | 
                       REFERENCES referenced_table [ ( referenced_column ) ] 
                       [ ON DELETE { CASCADE | RESTRICT | SET NULL | SET DEFAULT } ]
                       [ ON UPDATE { CASCADE | RESTRICT | SET NULL | SET DEFAULT } ]
                       [ DEFERRABLE [ INITIALLY DEFERRED ] ] }

table_constraint ::= [ CONSTRAINT constraint_name ]
                    { PRIMARY KEY ( column_name [, ...] ) |
                      UNIQUE ( column_name [, ...] ) |
                      CHECK ( expression ) |
                      FOREIGN KEY ( column_name [, ...] ) 
                      REFERENCES referenced_table [ ( referenced_column [, ...] ) ]
                      [ ON DELETE { CASCADE | RESTRICT | SET NULL | SET DEFAULT } ]
                      [ ON UPDATE { CASCADE | RESTRICT | SET NULL | SET DEFAULT } ]
                      [ DEFERRABLE [ INITIALLY DEFERRED ] ] }

data_type ::= { SMALLINT | INTEGER | BIGINT | DECIMAL | NUMERIC | REAL | DOUBLE PRECISION |
                SMALLSERIAL | SERIAL | BIGSERIAL |
                VARCHAR [ ( length ) ] | CHAR [ ( length ) ] | CHARACTER [ ( length ) ] |
                CHARACTER VARYING [ ( length ) ] | TEXT |
                DATE | TIME | TIMESTAMP | TIMESTAMPTZ | TIMETZ |
                BOOLEAN | UUID | JSONB | JSON |
                user_defined_type | ARRAY }

identity_option ::= { START WITH start_value | INCREMENT BY increment_value |
                     MAXVALUE max_value | NO MAXVALUE |
                     MINVALUE min_value | NO MINVALUE |
                     CYCLE | NO CYCLE }

default_value ::= literal | expression | function_call
```

pgschema understands the following `CREATE TABLE` features:

* **Schema-qualified names**: Tables can be created in specific schemas
* **Columns**:
  * All PostgreSQL data types including user-defined types
  * NULL/NOT NULL constraints
  * DEFAULT values with expressions and function calls
  * IDENTITY columns with GENERATED ALWAYS or BY DEFAULT
  * Generated columns with GENERATED ALWAYS AS (expression) STORED
  * Serial types (SMALLSERIAL, SERIAL, BIGSERIAL)
* **LIKE clause**:
  * Copy column definitions from another table
  * INCLUDING DEFAULTS, CONSTRAINTS, INDEXES, COMMENTS, or ALL
  * EXCLUDING options to omit specific elements
* **Constraints**:
  * PRIMARY KEY (single or composite)
  * UNIQUE constraints (single or composite)
  * FOREIGN KEY references with referential actions (CASCADE, RESTRICT, SET NULL, SET DEFAULT)
  * CHECK constraints with arbitrary expressions
  * DEFERRABLE constraints with INITIALLY DEFERRED option
* **Partitioning**: PARTITION BY RANGE, LIST, or HASH
* **Row-level security**: RLS policies (handled separately)
* **Indexes**: Created via separate CREATE INDEX statements
* **Triggers**: Created via separate CREATE TRIGGER statements

## Canonical Format

When dumping or creating tables, pgschema produces tables in the following canonical format. **All constraints** (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) are defined as **separate named table-level constraints** for consistency and explicit naming.

**Note**: When generating migration SQL for `ALTER TABLE ADD COLUMN` operations, single-column constraints may be inlined to reduce the number of statements (see ALTER TABLE documentation).

### Simple Table

```sql theme={null}
CREATE TABLE IF NOT EXISTS users (
    id SERIAL,
    email VARCHAR(255) NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT now(),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT users_age_check CHECK (age >= 0)
);
```

### Complex Table

```sql theme={null}
CREATE TABLE IF NOT EXISTS order_items (
    order_id INTEGER NOT NULL,
    item_id INTEGER NOT NULL,
    supplier_id INTEGER,
    region VARCHAR(50),
    position INTEGER,
    quantity INTEGER DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    updated_at TIMESTAMP,
    CONSTRAINT pk_order_item PRIMARY KEY (order_id, item_id),
    CONSTRAINT uk_order_item_position UNIQUE (order_id, position),
    CONSTRAINT order_items_order_id_fkey FOREIGN KEY (order_id)
        REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT order_items_item_id_fkey FOREIGN KEY (item_id)
        REFERENCES items(id),
    CONSTRAINT fk_order_item_supplier FOREIGN KEY (supplier_id, region)
        REFERENCES suppliers(id, region) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT order_items_quantity_check CHECK (quantity > 0),
    CONSTRAINT order_items_discount_percent_check CHECK (discount_percent >= 0 AND discount_percent <= 100),
    CONSTRAINT chk_valid_dates CHECK (created_at <= updated_at),
    CONSTRAINT chk_pricing CHECK (unit_price >= 0 AND (unit_price * quantity * (1 - discount_percent/100)) >= 0)
) PARTITION BY HASH (order_id);
```

### Identity and Serial Columns

```sql theme={null}
CREATE TABLE IF NOT EXISTS products (
    id BIGSERIAL,
    uuid UUID DEFAULT gen_random_uuid(),
    version INTEGER GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(50),
    price DECIMAL(10,2) DEFAULT 0.00,
    CONSTRAINT products_pkey PRIMARY KEY (id),
    CONSTRAINT products_sku_key UNIQUE (sku)
);
```

**Note on SERIAL types**: pgschema automatically detects and normalizes SERIAL columns (integer types with `nextval()` defaults) to their canonical SERIAL forms (SMALLSERIAL, SERIAL, BIGSERIAL) in CREATE TABLE statements.

### Constraint Handling Rules

**CREATE TABLE Format** - All constraints are defined as separate named table-level constraints:

* **PRIMARY KEY**: Both single-column and composite (`CONSTRAINT table_pkey PRIMARY KEY (col1, ...)`)
* **UNIQUE**: Both single-column and composite (`CONSTRAINT table_col_key UNIQUE (col1, ...)`)
* **FOREIGN KEY**: Both single-column and composite (`CONSTRAINT table_col_fkey FOREIGN KEY (col1, ...) REFERENCES...`)
* **CHECK**: All CHECK constraints (`CONSTRAINT table_col_check CHECK (expression)`)

**ALTER TABLE ADD COLUMN Format** (migration optimization) - Single-column constraints may be inlined:

* **PRIMARY KEY**: `ALTER TABLE t ADD COLUMN id INTEGER CONSTRAINT t_pkey PRIMARY KEY`
* **UNIQUE**: `ALTER TABLE t ADD COLUMN email TEXT CONSTRAINT t_email_key UNIQUE`
* **FOREIGN KEY**: `ALTER TABLE t ADD COLUMN user_id INTEGER CONSTRAINT t_user_id_fkey REFERENCES users(id)`
* **CHECK**: `ALTER TABLE t ADD COLUMN age INTEGER CONSTRAINT t_age_check CHECK (age > 0)`
* Composite constraints still require separate ALTER TABLE ADD CONSTRAINT statements

**Column Element Order** - When defining columns:

1. Column name and data type
2. NOT NULL (omitted if PRIMARY KEY, IDENTITY, or SERIAL)
3. DEFAULT value (omitted for SERIAL/IDENTITY columns)
4. GENERATED...AS IDENTITY

**Constraint Naming Conventions** - Auto-generated constraint names follow PostgreSQL conventions:

* PRIMARY KEY: `{table}_pkey`
* UNIQUE: `{table}_{column}_key`
* FOREIGN KEY: `{table}_{column}_fkey`
* CHECK: `{table}_{column}_check`

**Key characteristics of the canonical format:**

* Always uses `CREATE TABLE IF NOT EXISTS` for safety and idempotency
* **All constraints are table-level with explicit names** in CREATE TABLE output
* Constraints are ordered: PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK
* Column elements appear in order: data\_type, NOT NULL, DEFAULT, GENERATED...AS IDENTITY
* SERIAL types are normalized to uppercase canonical forms (SMALLSERIAL, SERIAL, BIGSERIAL)
* Data types include appropriate precision/scale modifiers (e.g., VARCHAR(255), NUMERIC(10,2))
* Schema prefixes are stripped from user-defined types when they match the target schema
* Foreign key constraints include full referential action specifications when present
* IDENTITY columns preserve their generation mode and options
* NOT NULL is omitted for PRIMARY KEY, IDENTITY, and SERIAL columns (implicit)
* DEFAULT is omitted for SERIAL and IDENTITY columns
* CHECK constraints are simplified to developer-friendly format (e.g., `IN('val1', 'val2')` instead of `= ANY (ARRAY[...])`)
* Partitioned tables include the PARTITION BY clause
* Proper indentation with 4 spaces for readability
* All table creation operations can run within transactions
* For DROP operations: `DROP TABLE IF EXISTS table_name CASCADE;`