Tables¶

PostgreSQL tables are the fundamental data storage structures containing rows and columns.

PostgreSQL Specification¶

CREATE TABLE Syntax¶

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

Reference: PostgreSQL 17 CREATE TABLE

ALTER TABLE Syntax¶

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]

-- Where action is one of:
ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
-- ... and many more

Reference: PostgreSQL 17 ALTER TABLE

pgdelta Support¶

✅ Currently Supported¶

CREATE TABLE¶

Basic table creation with columns
Column data types with precision/scale
NOT NULL constraints
DEFAULT expressions
Generated columns (GENERATED ALWAYS AS ... STORED)
Table inheritance (INHERITS clause)
Storage parameters (WITH clause)
Sequence ownership (ALTER SEQUENCE ... OWNED BY)

CREATE TABLE "public"."users" (
  "id" serial PRIMARY KEY,
  "email" text NOT NULL,
  "created_at" timestamp DEFAULT now(),
  "full_name" text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

DROP TABLE¶

Table deletion
Cascade behavior through dependency resolution

DROP TABLE "public"."users";

ALTER TABLE¶

ADD COLUMN with all supported column options
DROP COLUMN
ALTER COLUMN TYPE with USING expression
ALTER COLUMN SET/DROP DEFAULT
ALTER COLUMN SET/DROP NOT NULL

ALTER TABLE "public"."users" ADD COLUMN "phone" text;
ALTER TABLE "public"."users" DROP COLUMN "deprecated_field";
ALTER TABLE "public"."users" ALTER COLUMN "email" TYPE varchar(255);

❌ Not Yet Supported¶

CREATE TABLE Options¶

Column STORAGE settings (PLAIN, EXTERNAL, EXTENDED, MAIN)
Column COMPRESSION settings
Column COLLATE settings
LIKE clause for copying table structure
PARTITION BY clause (RANGE, LIST, HASH partitioning)
USING method for access method
TABLESPACE clause
Identity columns (GENERATED BY DEFAULT AS IDENTITY)

ALTER TABLE Operations¶

Table renaming (RENAME TO)
Schema changes (SET SCHEMA)
Owner changes (OWNER TO)
Storage parameter modifications
Constraint modifications (handled separately)

🚫 Intentionally Not Supported¶

Temporary Objects¶

TEMPORARY/TEMP tables (not persistent schema objects)
UNLOGGED tables (storage detail that changes automatically)

Conditional Operations¶

IF NOT EXISTS (pgdelta tracks existence)
IF EXISTS (pgdelta tracks existence)

Environment-Specific Features¶

TABLESPACE clause (file system layout)
ON COMMIT behavior (session-specific)
WITHOUT OIDS (deprecated PostgreSQL feature)

Usage Examples¶

Basic Table Creation¶

from pgdelta import extract_catalog, generate_sql

# Target schema with new table
target_sql = """
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);
"""

# Generate diff
changes = source_catalog.diff(target_catalog)
sql = generate_sql(changes[0])
# Result: CREATE TABLE "public"."users" (...)

Table with Generated Columns¶

target_sql = """
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    tax_rate DECIMAL(5,4) DEFAULT 0.0825,
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);
"""

# pgdelta generates:
# CREATE TABLE "public"."products" (
#   "id" serial NOT NULL,
#   "name" text NOT NULL,
#   "price" numeric(10,2) NOT NULL,
#   "tax_rate" numeric(5,4) DEFAULT 0.0825,
#   "total_price" numeric(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
# );

Table Inheritance¶

target_sql = """
CREATE TABLE base_log (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP DEFAULT now(),
    message TEXT
);

CREATE TABLE error_log (
    error_code INTEGER,
    stack_trace TEXT
) INHERITS (base_log);
"""

# pgdelta generates inheritance relationship

Column Modifications¶

# Add column
source_sql = "CREATE TABLE users (id SERIAL PRIMARY KEY);"
target_sql = "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);"

# Generate: ALTER TABLE "public"."users" ADD COLUMN "email" text;

# Change column type
source_sql = "CREATE TABLE users (id SERIAL, email TEXT);"
target_sql = "CREATE TABLE users (id SERIAL, email VARCHAR(255));"

# Generate: ALTER TABLE "public"."users" ALTER COLUMN "email" TYPE varchar(255);

Implementation Details¶

Table Model¶

@dataclass(frozen=True)
class CreateTable:
    stable_id: str                          # Format: "t:schema.table"
    namespace: str                          # Schema name
    relname: str                           # Table name
    columns: list[PgAttribute]             # Column definitions
    table_options: dict[str, Any] | None   # Storage parameters
    inherits_from: list[str] | None        # Parent tables

Column Model¶

@dataclass(frozen=True)
class PgAttribute:
    attname: str                # Column name
    type_name: str             # Data type
    attnotnull: bool           # NOT NULL constraint
    default_value: str | None  # DEFAULT expression
    is_generated: bool         # Generated column flag
    generated_expression: str | None  # Generation expression

    @property
    def formatted_type(self) -> str:
        """Format type with precision/scale."""
        # e.g., "varchar(255)", "numeric(10,2)"

SQL Generation¶

def generate_create_table_sql(change: CreateTable) -> str:
    """Generate CREATE TABLE SQL."""
    quoted_schema = f'"{change.namespace}"'
    quoted_table = f'"{change.relname}"'

    sql_parts = [f"CREATE TABLE {quoted_schema}.{quoted_table} ("]

    # Add columns
    column_defs = []
    for col in change.columns:
        col_def = f'  "{col.attname}" {col.formatted_type}'

        if col.is_generated:
            col_def += f" GENERATED ALWAYS AS ({col.generated_expression}) STORED"
            if col.attnotnull:
                col_def += " NOT NULL"
        else:
            if col.default_value:
                col_def += f" DEFAULT {col.default_value}"
            if col.attnotnull:
                col_def += " NOT NULL"

        column_defs.append(col_def)

    sql_parts.append("\n" + ",\n".join(column_defs) + "\n")
    sql_parts.append(")")

    # Add inheritance
    if change.inherits_from:
        inherits_tables = [f'"{table}"' for table in change.inherits_from]
        sql_parts.append(f" INHERITS ({', '.join(inherits_tables)})")

    return "".join(sql_parts) + ";"

Testing¶

Unit Tests¶

def test_create_table_basic():
    """Test basic table creation."""
    column = PgAttribute(
        attname="id",
        type_name="integer",
        attnotnull=True,
        default_value="nextval('users_id_seq'::regclass)"
    )

    change = CreateTable(
        stable_id="t:public.users",
        namespace="public",
        relname="users",
        columns=[column]
    )

    sql = generate_create_table_sql(change)
    assert "CREATE TABLE \"public\".\"users\"" in sql
    assert "\"id\" integer DEFAULT nextval('users_id_seq'::regclass) NOT NULL" in sql

Integration Tests¶

def test_table_roundtrip(postgres_session):
    """Test table creation roundtrip fidelity."""
    # Create table
    postgres_session.execute(text("""
        CREATE TABLE test_table (
            id SERIAL PRIMARY KEY,
            name TEXT NOT NULL DEFAULT 'unnamed',
            created_at TIMESTAMP DEFAULT now()
        )
    """))
    postgres_session.commit()

    # Extract catalog
    catalog = extract_catalog(postgres_session)

    # Find table
    table = next(t for t in catalog.tables if t.relname == "test_table")
    assert len(table.columns) == 3

    # Verify column properties
    id_col = next(c for c in table.columns if c.attname == "id")
    assert id_col.attnotnull
    assert "nextval" in id_col.default_value

ALTER TABLE Tests¶

def test_add_column():
    """Test ADD COLUMN generation."""
    source_sql = "CREATE TABLE users (id SERIAL PRIMARY KEY);"
    target_sql = "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);"

    changes = generate_changes(source_sql, target_sql)

    alter_change = next(c for c in changes if isinstance(c, AlterTable))
    assert alter_change.add_columns
    assert alter_change.add_columns[0].attname == "email"

    sql = generate_alter_table_sql(alter_change)
    assert "ALTER TABLE \"public\".\"users\" ADD COLUMN \"email\" text;" in sql

Error Handling¶

Common Errors¶

# Type conversion errors
try:
    sql = "ALTER TABLE users ALTER COLUMN id TYPE text;"
    # May fail if data cannot be converted
except Exception as e:
    # pgdelta can generate USING expression for safe conversion
    safe_sql = "ALTER TABLE users ALTER COLUMN id TYPE text USING id::text;"

Data Type Validation¶

def validate_type_compatibility(old_type: str, new_type: str) -> bool:
    """Check if type conversion is safe."""
    safe_conversions = {
        ("integer", "bigint"),
        ("text", "varchar"),
        ("varchar", "text"),
        ("numeric", "text"),
    }

    return (old_type, new_type) in safe_conversions

Advanced Features¶

Generated Columns¶

# Generated column support
target_sql = """
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    subtotal DECIMAL(10,2) NOT NULL,
    tax_rate DECIMAL(5,4) DEFAULT 0.0825,
    total DECIMAL(10,2) GENERATED ALWAYS AS (subtotal * (1 + tax_rate)) STORED
);
"""

# pgdelta handles generated column syntax correctly

Table Inheritance¶

# Inheritance support
target_sql = """
CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    make TEXT,
    model TEXT
);

CREATE TABLE cars (
    doors INTEGER
) INHERITS (vehicles);
"""

# pgdelta tracks inheritance relationships

Storage Parameters¶

# Storage parameter support
target_sql = """
CREATE TABLE large_table (
    id BIGSERIAL PRIMARY KEY,
    data TEXT
) WITH (
    fillfactor = 70,
    autovacuum_vacuum_scale_factor = 0.1
);
"""

# pgdelta preserves storage parameters

Future Enhancements¶

Planned Features (v0.2.0)¶

Partitioning Support¶

CREATE TABLE measurements (
    id SERIAL,
    measurement_date DATE,
    value NUMERIC
) PARTITION BY RANGE (measurement_date);

CREATE TABLE measurements_2024 PARTITION OF measurements
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Identity Columns¶

CREATE TABLE products (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

Advanced ALTER TABLE¶

-- Table renaming
ALTER TABLE old_name RENAME TO new_name;

-- Schema changes
ALTER TABLE public.table1 SET SCHEMA app;

-- Storage parameter changes
ALTER TABLE table1 SET (fillfactor = 80);

Implementation Challenges¶

Partitioning¶

Complex dependency relationships
Partition constraint management
Partition pruning considerations

Identity Columns¶

Sequence management
Identity property preservation
Conversion between serial and identity

Best Practices¶

Column Naming¶

# Good column names
good_names = [
    "id", "user_id", "created_at",
    "first_name", "last_name", "email",
    "is_active", "status", "version"
]

# Avoid reserved words
avoid_names = [
    "order", "group", "user", "table",
    "select", "where", "from", "join"
]

Data Type Selection¶

# Recommended type mappings
type_mappings = {
    "id": "SERIAL or BIGSERIAL",
    "text": "TEXT (unlimited) or VARCHAR(n)",
    "money": "DECIMAL(10,2) not MONEY",
    "boolean": "BOOLEAN not CHAR(1)",
    "timestamp": "TIMESTAMP WITH TIME ZONE",
    "uuid": "UUID with uuid-ossp extension",
}

Default Values¶

# Good default patterns
good_defaults = {
    "created_at": "now()",
    "updated_at": "now()",
    "is_active": "true",
    "version": "1",
    "status": "'pending'",
}

# Avoid non-deterministic defaults in some cases
avoid_defaults = {
    "random()": "Use at application level",
    "current_user": "May vary by connection",
}