Tables & Columns¶

Handlers: TableHandler, RenameTableHandler, ColumnHandler, PgTableHandler, StorageParamsHandler (DIFF phase)

Table Lifecycle¶

Operation	DDL
Create table	`CREATE TABLE name (...)`
Drop table	`DROP TABLE IF EXISTS name CASCADE;`
Rename table	`ALTER TABLE old_name RENAME TO new_name;`
Alter table comment	`COMMENT ON TABLE name IS 'comment';`

Column Lifecycle¶

Operation	DDL
Add column	`ALTER TABLE t ADD COLUMN c type;`
Drop column	`ALTER TABLE t DROP COLUMN c;`
Change type	`ALTER TABLE t ALTER COLUMN c TYPE newtype;`
Change nullable	`ALTER TABLE t ALTER COLUMN c SET/DROP NOT NULL;`
Change default	`ALTER TABLE t ALTER COLUMN c SET/DROP DEFAULT;`
Change comment	`COMMENT ON COLUMN t.c IS 'comment';`
Change autoincrement	`CREATE/DROP SEQUENCE` + `SET/DROP DEFAULT nextval`
Change PG meta	`SET STORAGE`, `SET COMPRESSION`, `SET COLLATION`, identity options
Rename column	`ALTER TABLE t RENAME COLUMN c TO new_name;`

Table Properties¶

Property	Meta Attribute	DDL
Fillfactor	`pg_fillfactor`	`ALTER TABLE t SET (fillfactor = N);`
Tablespace	`pg_tablespace`	`ALTER TABLE t SET TABLESPACE name;`
Unlogged	`pg_unlogged`	`CREATE UNLOGGED TABLE` / `ALTER TABLE t SET UNLOGGED;`
Inheritance	`pg_inherits`	`ALTER TABLE t INHERIT parent;`
Storage params	(via handler)	`ALTER TABLE t SET (param = value);`
ON COMMIT	`pg_on_commit`	`ON COMMIT DELETE ROWS` / `DROP` / `PRESERVE ROWS`

Storage Params¶

pg_storage_params stores raw PostgreSQL table storage options. pg_fillfactor is kept as a shorthand and is folded into pg_storage_params["fillfactor"] during discovery.

class Meta(PGTableMeta):
    pg_storage_params = {
        "fillfactor": 80,
        "autovacuum_enabled": "false",
    }

Generated DDL:

ALTER TABLE users SET (fillfactor = 80, autovacuum_enabled = false);

ALTER TABLE Operations¶

SET SCHEMA¶

Move a table between schemas:

ALTER TABLE users SET SCHEMA app;

SET LOGGED / SET UNLOGGED¶

Toggle between logged and unlogged modes:

ALTER TABLE users SET LOGGED;
ALTER TABLE users SET UNLOGGED;

SET LOGGED converts an unlogged table back to logged mode (all data is written to WAL).

ALTER COLUMN SET STATISTICS¶

Set column-level statistics target:

ALTER TABLE users ALTER COLUMN email SET STATISTICS 500;

Higher values improve query planner estimates for columns with non-uniform distributions. Values range from -1 (use default) to 10000.

ALTER COLUMN SET (attribute_option)¶

Set column-level attribute options:

ALTER TABLE users ALTER COLUMN email SET (n_distinct = 0.01);
ALTER TABLE users ALTER COLUMN email RESET (n_distinct);

Common attribute options: n_distinct, n_distinct_inherited.

CLUSTER¶

Cluster a table based on an index:

ALTER TABLE users CLUSTER ON ix_users_email;

ENABLE / DISABLE TRIGGER¶

Control trigger execution:

ALTER TABLE users DISABLE TRIGGER trg_users_updated_at;
ALTER TABLE users ENABLE TRIGGER trg_users_updated_at;
ALTER TABLE users ENABLE REPLICA TRIGGER trg_users_updated_at;
ALTER TABLE users ENABLE ALWAYS TRIGGER trg_users_updated_at;

TRUNCATE¶

TRUNCATE TABLE users;
TRUNCATE TABLE users, posts CASCADE;

TRUNCATE is not generated by make-migrations but can be run manually for bulk data removal.

Temporary Tables¶

Temporary tables use ON COMMIT for cleanup behaviour:

CREATE TEMPORARY TABLE temp_data (id int) ON COMMIT DELETE ROWS;

Supported ON COMMIT values:

Value	Behaviour
`PRESERVE ROWS`	Default: rows persist across transaction boundaries
`DELETE ROWS`	All rows deleted at transaction end
`DROP`	Table dropped at transaction end

LIKE Clause¶

Create a table with the same structure as an existing one:

CREATE TABLE users_archive (LIKE users INCLUDING ALL);

INCLUDING ALL copies defaults, constraints, indexes, and storage. This is a DDL operation (no data copied).

Storage Params¶

pg_storage_params stores raw PostgreSQL table storage options. pg_fillfactor is kept as a shorthand and is folded into pg_storage_params["fillfactor"] during discovery.

class Meta(PGTableMeta):
    pg_storage_params = {
        "fillfactor": 80,
        "autovacuum_enabled": "false",
    }

Generated DDL:

ALTER TABLE users SET (fillfactor = 80, autovacuum_enabled = false);

Snapshot Format¶

Column Extras¶

{
  "name": "bio",
  "type": "text",
  "pg_column": {
    "collation": "en_US.UTF-8",
    "storage": "EXTENDED",
    "compression": "pglz",
    "generated": null,
    "identity": "always",
    "identity_start": 1,
    "identity_increment": 1
  }
}

Table Extras¶

{
  "pg_table": {
    "pg_fillfactor": 80,
    "pg_tablespace": "fastspace",
    "pg_unlogged": false,
    "pg_inherits": "base_entity",
    "pg_partition": {
      "strategy": "RANGE",
      "columns": ["created_at"]
    },
    "pg_excludes": [
      {"name": "excl_room_booking", "expression": "EXCLUDE USING gist (room_id WITH =, during WITH &&)"}
    ]
  }
}

Reverse Engineering¶

generate-models queries pg_class, pg_attribute, pg_constraint, pg_inherits, pg_tablespace, pg_partitioned_table, and pg_collation to reverse-engineer all metadata.

$ dbwarden generate-models -d primary

Generated output for a table with identity, storage, compression, collation, and fillfactor:

class User(Base):
    __tablename__ = "users"

    id:    Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True)
    bio:   Mapped[str | None] = mapped_column(Text, nullable=True)

    class Meta(PGTableMeta):
        comment = "Core user accounts"
        pg_fillfactor = 80

        class id(PGColumnMeta):
            pg = pg.field(identity="always", identity_start=100, identity_increment=1)

        class bio(PGColumnMeta):
            pg = pg.field(storage="EXTENDED", compression="pglz", collation="en_US.UTF-8")

For a partitioned table:

class Event(Base):
    __tablename__ = "events"

    id: Mapped[int] = mapped_column(primary_key=True)
    created_at: Mapped[datetime] = mapped_column(DateTime)

    class Meta(PGTableMeta):
        pg_partition = {"strategy": "RANGE", "columns": ["created_at"]}