CREATE TABLE - pgschema, Declarative Schema Migration for Postgres

Syntax

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

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

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

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:

Column name and data type
NOT NULL (omitted if PRIMARY KEY, IDENTITY, or SERIAL)
DEFAULT value (omitted for SERIAL/IDENTITY columns)
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;

​Syntax

​Canonical Format

​Simple Table

​Complex Table

​Identity and Serial Columns

​Constraint Handling Rules